Linux – Multiple root user accounts for thesql on Ubuntu

linuxMySQLUbuntu

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.

Best Answer

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.

Related Topic