Securing MySQL in 10 minutes

Much like my last entry on memcache, I'm not trying to write the ultimate security guide for securing a mysql server... I'm just outlining the kinds of steps you should take to have a level of security on par with the locks on your car... Enough to encourage the average plunderer to move along to another target.

Listen Only Where You Want to Hear

Just like memcache, the default mysql install is listening patiently on all your server's ethernet devices for someone to connect. So, just like memcache, we need to tell mysql to only listen on the ethernet device we care about. For your average single-server web host, you only need to listen to your local host.

Find your my.conf file. On servers I manage, this is either at

/etc/my.conf

or

/etc/mysql/my.conf

. This configuration file will contain all kinds of goodies about performance tuning your mysql server, connection details for your mysql client, etc. you want to find the section with the heading

[mysqld]

and add this line:

bind-address = 127.0.0.1

If you are in some kind of multi-machine environment, you'll want to set that to whatever your private IP address is - under no circumstances should this be an ip address the general public can reach.

Secure Your Root Password

When you first start mysql on a new install, it prints out a message that says something like "Don't forget to set a root password!", and gives you a couple of command-line examples. But guess what? A lot of people never get around to setting these (and I have been as guilty of that as anyone). Here are those commands again:

mysqladmin -u root password “newpassword”
mysqladmin -u root -h localhost password “newpassword”

Set yourself up a real root password. Don't forget to write it on a sticky note and put it on your monitor. Feel free to email it to me so I can keep a copy safe and sound for you (just kidding).

Use an Application-Specific User

If you're reading this blog, you probably have a Java, PHP, or Ruby application that is connecting to your mysql server and accessing data in a database. When you set everything up the first time you were happy just to get it working, and you probably used that root user we just locked down. We want to create a user specifically for our application, and give them just the abilities they need for our database.

Lets assume the name of that database is 'schpoo', and you want to create a user named 'my_app_user'. We want to give that user access to the database. There are several ways to do this in mysql; here is how I do it. Start by logging onto the mysql command-line tool:

[dbock@my.example ~]$mysql -u root -p

you'll be prompted to enter your shiny new root password. After you connect, we'll type:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL ON schpoo.* to 'my_app_user'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.11 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)

Note how we specify 'localhost' above? That is going to allow this user only from the local machine. If you want to connect from another machine on a private network, put the IP address in there instead. You can even have the same user have different passwords from different IP addresses.

Grant ALL? Seriously?

Well, I'm speaking with my Ruby on Rails hat on - and I'm generally going to use that user to read and write to the database as well as run migrations, so 'ALL' makes sense. If you want an extra dose of paranoia, you can grant different sets of privileges to different users - but doing that properly would become advice more tuned to configuring, say Hibernate, than mysql.

A Note About Passwords

We all know its bad to reuse passwords... so how do manage passwords for a dozen clients across a dozen applications, and not reuse passwords?

There is a great little command-line linux tool used for generating universally unique identification strings, called uuidgen. Here's some sample output:

[dbock@my.example ~]$ uuidgen
b70b8753-bbc0-4881-9b81-5e00baacd39a
[dbock@my.example ~]$ uuidgen
8f79aadd-29a0-41b7-bf66-6ad23ea49f04
[dbock@my.example ~]$ uuidgen
ea56ef7f-556e-44d5-a808-8baebc74c3d1

Those make great passwords. Since I typically store my application credentials in a secure location on the server and copy them into place when I deploy my client application, I don't have to remember those passwords. I typically don't have to worry about typing them either. That is a useful technique that is really easy when you're using a deploy tool like capistrano, but that is a different blog entry.

In Closing

Setting appropriate passwords, limiting privileges, and only listening on ports you expect traffic from is hardly top-secret 'advice from the mysql experts', but it is advice most mysql installs would benefit from. Give yourself 10 minutes and audit a server you are responsible for today.

Comments

[Alexandre]

Nice article.
Note also the "pwgen" Linux command to generate passwords with a number of options such as length, type of characters to include, remove ambiguous characters, etc.
For a password used only by machines, try for instance:
pwgen -s -c -n -y 32

[Ruby on Rails]

I like you wrote a solution for securing mysql and given step by step trick to did that, thanks for sharing