MySQL Best Practices: User Security

It is critical that you do not use the default MySQL installation security, it’s simply insecure.

Default Installation

When installed, MySQL enables any user with physical permissions to the server to connect to the MySQL via unauthenticated users. MySQL also provides complete access to all super user privileges via the ‘root’ user with no default password.

$ mysql -uroot
mysql> SELECT host,user,password FROM mysql.user;
+--------------+------+-------------------------------------------+
| host         | user | password                                  |
+--------------+------+-------------------------------------------+
| localhost    | root |                                           |
| server.local | root |                                           |
| 127.0.0.1    | root |                                           |
| localhost    |      |                                           |
| server.local |      |                                           |
+--------------+------+-------------------------------------------+

What you see here are two types of users.

• The ‘root’ user which has MySQL super user privileges for your server or ‘localhost’ connections with no password.
• Unauthenticated users indicated by the blank ‘user’ column

The absolute minimum you should do, is run the provided optional command for immediate improvements mysql_secure_installation. When running this command, you’re prompted for the following
options — the output has been trimmed for presentations purposes.

$ mysql_secure_installation
Enter current password for root (enter for none):
Set root password? [Y/n] y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

If you revisit permissions now, you’ll see what you would expect from a more initially secure installation.

mysql> SELECT host,user,password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *FDAF706717E70DB8DDAD0C5214B13770E1A80B0E |
+-----------+------+-------------------------------------------+

This is only the first step to hardening your MySQL instance and server.

Recommendations

The following are my recommendations for the minimum MySQL security permissions:

• Always set a MySQL ‘root’ user password
  • Change the MySQL ‘root’ user id to a different name, e.g. ‘dba’
    • Only enable SUPER privileges to dba accounts, and only ever for ‘localhost’.
      • Application user permissions should be as restrictive as possible.
        • Never use ‘%’ for a hostname
          • Never use ALL TO *.*

            • Ideally the application should have at least two types of users, a read/write user and a read user.

            There is a lot more information about physical Operating System security and the MySQL permission/privilege model to be discussed. One product I know of that help is SecuRich – The MySQL Security Package featuring roles, password history and many other cool functionalities.

            References

            A recent post by Lance Miller quoted the following.

            _
            I cant tell you how many times in the past 18 months that I’ve found real enterprises running vulnerable databases with default passwords, weak passwords and no real permissions management. It’s bad enough that the stats right now are this (so I guess I can tell you):
            – 9 out of 10 organizations have a Microsoft SQL Database with a blank “sa” password (or an sa password of “sa”, “sql” or “password”)
            – 9 out of 10 organizations have a Postgres Database with a default password
            – 9 out of 10 organizations have a Sybase Database with a default password
            _

            The article didn’t include MySQL however some organizations don’t change the default password, probably not 9 of 10 in my experience.

            More Information

            • Best Practices: Additional User Security
            • Percona Live Presentation: MySQL Security Essentials
            • REPOST: A Tragically Comedic Security Flaw in MySQL