Secure Your Server – Tips for DBAs

[Chris]

I have complied a list of a good tips for DBAs to consider when implementing security policies in MySQL.

-- The List
1. Make sure that ALL root accounts (ALL PRIVILEGED accounts) have strong passwords and apply them using hashes not plain text. If you leave the password blank anyone can connect as root without a password and be granted all privileges.

2. For ALL other accounts use passwords and apply them using hashes not plain text.
A good practice is to use the following:

mysql> grant SELECT, INSERT……. ON `database`.* to ‘user’@’10.%’ IDENTIFIED BY ‘*9B9C8E678DB2D62877E829D633535CBEB2B7E6E0’;

NOTE: make sure you are using HASHS and not plain text!
If you run something like below…

mysql> update user set password = password(‘somepassword’) where user = ‘someuser’;
Make sure you remove it from the ~/.mysql_history file in your home dir and or roots home directory. Otherwise, if your server becomes compromised, the intruder can take full advantage of this file and do more damage.

3. Give specific permissions on an as needed basis and use different logins for different purposes.

NOTE: You don't have to give column level permissions because they hurt performance so try and stick to table, at the lowest, and database level permissions.

4. Avoid using FQDN or hostnames when granting access. Try to keep it to a subnet (Example: 10.1.2.%).

5. Avoid having the MySQL server open to connections from everywhere by using ‘%’ for any user. Although remote exploits are few and far between, it is just better not to risk it.

6. Remove ALL blank user accounts. Even if they just have USAGE!

mysql> DELETE from user WHERE user = ‘’;

7. Remove the following from the mysql.db table

mysql> DELETE from mysql.db WHERE Db = ‘test’ OR Db = ‘test\\\\_%’;

You might ask why for the above removal, well, here is an explanation…

Let’s say you have many different databases on a single MySQL instance where the users control the names of their databases. The traditional rules apply like database name have to be unique, but some of these users have the same idea; adding a test database to their MySQL instance. Given that test is already taken on the server one user would create a database named test_a and another would create test_b. With the grant in the mysql.db table, Db = ‘test\\\\_%’, BOTH users would have read and write access to each others database. If the overall goal is keep users from seeing each others databases than the Db = ‘test\\\\_%’ grant must be removed.

A long explanation but I have seen it happen!

[Sarah Sproehnle]

Good advice Chris! One thing I'd like to add: don't use the general log! Not only does it hurt performance, it also logs passwords as plain text. If you did SET PASSWORD for root@localhost=PASSWORD('secret'), the general log would record that statement verbatim. Whereas the binary log would log the hashed password. Additionally, see Chris' advice in step 2. :)

[Ronald Bradford]

If you touch the mysql.user table with a DML statement (i.e not GRANT/REVOKE) you also must do a FLUSH PRIVILEGES for these to be available for the mysqld process.