Securing the MySQL Root Account

Monday Feb 13th 2012 by Rob Gravelle

The world's favorite open source DBMS has made itself a target of hackers. A little time spent in this area can yield big dividends down the road. One of the easiest steps you can take in securing your data is to lock down the root and anonymous accounts.

MySQL's enormous popularity is a double-edged sword for the people who depend on it. On one hand, it's not liable to become outdated any time soon.  On the other, the world's favorite open source DBMS has made itself a target of hackers who desire to steal its precious data, or just destroy it for kicks or notoriety.  Too many development teams spend an inordinate amount of time and energy hardening the applications that interface with the database, while leaving the back door wide open to malicious individuals.  The funny thing is that protecting your data from outsiders is not terribly difficult.  A little time spent in this area can yield big dividends down the road.  One of the easiest steps you can take in securing your data is to lock down the root and anonymous accounts.

What is the Root Account?

When you first install MySQL, there is a default root account. It's a superuser account that has god-like privileges in all the MySQL databases. The initial root account password is empty by default, so anyone can connect to the MySQL server as root without a password and be granted all privileges!  What's worse, if you activate the Enable root access from remote machines option during installation, the installer creates another root account that permits connections from any host!

The MySQL Server Instance Configuration Wizard

In Windows, you can run the MySQL Server Instance Configuration Wizard immediately after installation to set the root password:

MySQL Server Instance Configuration Wizard
MySQL Server Instance Configuration Wizard

If you haven't already, now would be a very good time to consider how you intend to connect to your database(s).  Obviously, the most secure option is to only connect locally, meaning that your database(s) and User Interface (UI) reside on the same machine.  While it would be great to have the luxury of working this way, in just about any real-world application that I've come across, the database was accessed across a network.  Now that may not be such a bad thing if the network itself is secured or an internal LAN.  In this regard, the worst situation to be in is to have your database(s) accessible via the Web, which ironically, just happens to be one of the most common uses of MySQL.  That's the risk of doing business in the twenty first century!

As a rule of thumb, if you're hosting MySQL on a machine that has Internet access, and you are only using it as a stand-alone application, don't just rely on your firewall (although a firewall sure helps!). Deselect the Enable root access from remote machines checkbox so that only local connections are accepted.  For TCP/IP connections, root accounts permit connections from localhost, the IP address, or the IPv6 address of ::1

Anonymous Accounts

The MySQL Server Instance Configuration Wizard provides an even more convenient way to connect to your databases, using an anonymous account.  That's an account that doesn't even require a logon ID!  In case you haven't surmised from the big question mark on the user's head (see screenshot above) and the note that states that this option “can lead to an insecure system”, that checkbox should never be selected, unless your data is so unimportant that you could care less what happens to it.  Off the top of my head, it's difficult to think of any use for a robust DBMS that involves frivolous data. 

Setting/Changing the Root Account ID and Password

Whatever steps you take during installation to secure the Root account, you should verify what's been created by querying the mysql.user table.  Using the wizard, you should wind up with a fairly short list of user accounts:

mysql> SELECT host,user,password FROM mysql.user;
| host      | user | password                                  |
| localhost | root | *D4FA16B3275E6619F3029FDDBA9A90EBA0DDFBEA |
1 row in set (0.02 sec)

That long string in the password field is the encrypted password.  What you don't want is to see empty password fields, such as the following:

mysql> SELECT host,user,password FROM mysql.user;
| host         | user | password                                  |
|   | root |                                           |
| localhost    | root |                                           |
| server.local | root |                                           |
|    | root |                                           |
| ::1          | root |                                           |
| localhost    |      |                                           |
|   |      |                                           |

From the above query results, we can determine that the server accepts TCP/IP connections from the three local IP designations as well as one remote server.  Unfortunately, the default root accounts were all created without passwords.  Even more worrisome, there are anonymous accounts for both the localhost and the remote server.

To assign the password for a given account, use the SET PASSWORD command:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

You can omit the FOR clause to set/change your own password, as long as you are not connected as an anonymous user (Would you really want to grant update privileges to an anonymous account?!):

mysql> SET PASSWORD = PASSWORD('newpassword');

A Couple of Quick Password Guidelines

Please don't construe the passwords above as good examples for your own use.  Coming up with good passwords is as much science as art, due to the opposing challenges of thwarting would-be hackers, while being able to remember your passwords without having to write them down on a sticky note under your keyboard, which is done all-too-often, sadly.

Entire articles have been dedicated to this subject, but here are a few guidelines:

  • Don't use one word passwords. Combine words together in creative ways instead.
  • Include a mix of upper and lower case letter, numbers, and special characters.
  • Don't use your birthday, or family members' birthdays.
  • Don't use adjacent keys, i.e., asdf, qwerty.
  • Use letters from a phrase, such as “TowamfN” for “There once was a man from Nantucket”
  • Separate words that you like with numbers and/or symbols. For instance, I like the word “guitar” and “lederhosen”.  From that I could have a password like “guitar#lederhosen”.  Even better, “Gtr#lederhosen”.  It's shorter and harder to crack!
  • Don't use the same password for everything.  At least keep your private affairs separate from work.

You should also change everyone's password from time to time, say every 6 months.

Change the Root Account ID

Hackers almost always try to access the root account's permissions as a starting point. Renaming "root" to something else provides the first blow in a one-two punch combo that will discourage all but the most determined of intruders. The second punch is of course the use of a complex password as described above.  The command to rename the root account is RENAME USER:

mysql> RENAME USER 'root'@'localhost' TO new_user'@'localhost';


The goal is never to design an impregnable system, but rather to get the most bang for your buck so to speak in doing things that make the most difference.  You don't need the most secure database on the planet; it only needs to be secure enough to convince hackers to move on to easier targets.  To this end, securing the root account is a great place to start.

See all articles by Rob Gravelle

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved