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.
PlanetMySQL Voting:
Vote UP /
Vote DOWN
This Thursday (June 10th,
14:00 UTC), Darren Cassar will rerun his February 25 presentation of Securich - Security Plugin
for MySQL.
(Recording of the session failed in February; hopefully it will succeed this time.) According to Darren, the author of the plugin, Securich is an
incredibly handy and versatile tool for managing user privileges on
MySQL through the use of roles. It basically makes granting and
revoking rights a piece of cake, not to mention added security it
provides through password expiry and password history, the
customization level it permits, the fact that it runs on any MySQL 5.0
or later and it's easily deployable on any official MySQL binary,
platform independent.