PCI DSS & MySQL – Requirement 2
Requirement 2 of the PCI DSS v1.2 is:
“Do not use vendor-supplied defaults for system passwords and other security parameters”
Understanding that we’re limiting the discussion solely to MySQL (OS, Network Devices, and other software will no doubt apply to overall compliance), we can do this easily. The vendor-supplied default MySQL 5.1.43 (they’re similar across other versions, but only this version was tested here) credentials can be seen as follows:
mysql> SELECT user, host, password FROM mysql.user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | | root | testbox1 | | | root | 127.0.0.1 | | | | localhost | | | | testbox1 | | +------+-----------+----------+ 5 rows in set (0.28 sec)
MySQL handily provides us with a script called mysql_secure_installation that will, assuming we choose Y to all questions, satisfy this requirement:
%> mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
(Emphasis added was mine). This gives us the following:
mysql> SELECT user, host, password FROM mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *F169C0AFEEC30BFF924130B124E6AE3E875D5F60 | +------+-----------+-------------------------------------------+ 1 row in set (0.00 sec)
Note that the mysql_secure_installation has done more than the PCI minimum of removing default accounts, but also completed items considered to be best practices:
- Remove anonymous users
- Disallow root login remotely
- Remove test database and access to it
We should take this a step further and ensure that MySQL’s old (insecure) password format is not enabled:
mysql> SHOW GLOBAL VARIABLES LIKE 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | OFF | +---------------+-------+ 1 row in set (0.00 sec)
To find any users with the old-style (or empty) passwords:
mysql> SELECT user, host FROM mysql.user WHERE LENGTH(password)!=41; Empty set (0.00 sec)
Now we’ve done this for a new install, but few of us have the privilege of fresh installs. Instead, we work with existing systems. So, here’s a query (for 5.1.43) to find out if we have any insecure user accounts or system defaults:
mysql> SELECT user, host FROM mysql.user WHERE user='' OR password='' OR host='%'; Empty set (0.00 sec)
On the face of it, we seem to have satisfied PCI DSS Requirement 2. If only it were that easy … Requirement 2 actually consists of a variety of subsections. Most of these are outside the scope of MySQL directly, but the following will apply:
2.2 – Develop configuration standards for all system components. Assure that these standards address all known security vulnerabilities and are consistent with industry-accepted system hardening standards.
This requirement is to ensure that all configurations are codified and put into use. In addition to written policies and procedures, the use of an automated configuration management system (cfengine, Chef, Puppet, etc) can simplify adherence to corporate policies and standards.
2.2.3 – Configure system security parameters to prevent misuse.
I’m still waiting for the NSA to release a Security Configuration Guide for MySQL as it has for other databases, but there are other common security configurations that prevent misuse. As every installation is different, do not consider this list comprehensive, it is meant to be a starting point.
- Be judicious with your GRANTs. If an application requires only SELECT, INSERT, UPDATE, and DELETE privileges on a particular database, limit the GRANT statement accordingly. Specifically avoid WITH GRANT OPTION and SUPER wherever possible (I mention SUPER in particular because it allows the one reserved administrative connection to be used by the app, meaning what may have only been a momentary blip in service results in extended downtime while mysqld is restarted; this goes against the “A” in the CIA Triad)
- Disable local_infile. I won’t repeat the common wisdom here, but you can read more about the security implications of this on MySQL’s website
- Disable old_passwords. The hashing algorithm has gotten more secure over the years, it is best practice (as described above) to use only the newer hash algorithm. You can read more about password hashing in MySQL 5.1 here
- Set read_only=ON. Although not typically viewed as a security configuration parameter, this setting can provide an additional layer of protection that ensures data on your replication slaves remains in sync with your masters (think I in the CIA Triad)
- Enable secure_auth. This blocks connections from all accounts that have passwords stored in the old (pre-4.1) format. Even though old_passwords will be OFF, this can provide a bit of redundancy to that option
I hope this post has helped secure at least one MySQL server. Look for more PCI DSS + MySQL coming up soon!
