Multiple root user accounts for mysql on Ubuntu

Question

I'm using ubuntu 12.04 and trying to get things secured. I'm still pretty new to Linux so I'm not quite sure how to interpret this.

I logged into my root account using mysql -u root -p and then to see all of the users I typed SELECT User FROM mysql.user; which showed the following

+------------------+
| User             |
+------------------+
| root             |
| root             |
|                  |
| root             |
|                  |
| Testing          |
| debian-sys-maint |
| phpmyadmin       |
| root             |
+------------------+

I logged into phpmyadmin to check out what each of the root accounts for and noticed they all have different hosts. Localhost, 127.0.0.1, ::1 and another IP address. Is it necessary to keep all of these? I currently SSH into my server (using a key pair) and then access MySQL through the terminal or through PHPMyadmin directly from my URL, so I'm pretty sure I just access it through the localhost root account and none of the others.

If I change my root password will all of the other MySQL root accounts change (from the different hosts)? What would you guys do in this situation to make it more secure?

Here's what I was thinking of doing, but maybe there's a better way. I was going to change the MySQL root user's password to something long and random (and write it down), and create another account with a shorter password for everyday management.

For the record, I have already restricted IP access to PHPMyAdmin and made an alias, I just want to do everything I can to prevent some jerk from trying to get a hold of it.

Answer 1

Although they are all named 'root', MySQL sees each of those user entries as a unique account. The account is based on the 'user'@'host' combination. Each of them can have a separate password, although that could easily be an account management nightmare.

You have a number of ways to change the passwords for the accounts, and depending on how you do it, you may have to repeat it for each account to keep them in sync.

The first way (probably the one most people are familiar with) is using SET PASSWORD

shell> mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

If you wanted to update them all at once you can use UPDATE

shell> mysql -u root -p
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The third way if to use the mysqladmin tool

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

If using this method, you need to make note:

The mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier.

More info on the MySQL site

I agree with your idea of using a separate account for your day to day tasks, you will just need to find the right combination of permissions that will work with what you want, without getting too heavy handed.

I would also take a look at your server logs periodically (or have a tool in place) to monitor database logins, such as those for root.

There's a lot more that you can do to secure your installation, but that can easily grow beyond the scope of this.

Answer 2

A fresh-installed MySQL in Ubuntu 12.04 shows this.

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
|                  | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
|                  | myserver  |
| root             | myserver  |
+------------------+-----------+

127.0.0.1, ::1, localhost, myserver(server hostname) are set by default. These entries are practically the same with ::1 being the IPV6-equivalent of localhost, so generally they're safe.

So basically, these entries say that you're allowing these hosts to connect to MySQL as root. In your case, you're also allowing another IP address to connect as root to your MySQL. If there is no script or app connecting to MySQL from that IP address, you may want to remove that entry.

You still have one root account so changing the password will reflect to all connections regardless of hosts.

You can also run this very helpful command to secure your MySQL server.

 sudo mysql_secure_installation