The MySQL database is one of the most popular databases for applications, particularly Web-based applications....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
Unfortunately, many IT shops consider your MySQL databases "set and forget" installations, unknowingly leaving them vulnerable to attacks. A successful intrusion could expose sensitive data contained in your databases or provide a conduit for further attacks into your environment.
This tip will take you through simple steps to secure your MySQL installation. The tip focuses on infrastructure security rather than application security. It assumes that you design and code your applications securely.
Before we look at specific steps, there are some high-level items that you need to consider:
- Keep your MySQL version up-to-date. This tip is relevant for versions 4 and 5 of MySQL. If you're running version 3 (or earlier!), then I recommend you upgrade.
- Ensure your database and application is adequately secured. MySQL has a permissions model with varying roles and capabilities that can be assigned to users. You should ensure your application and database users only have the permissions they absolutely need to function.
- Don't neglect your host security. You should ensure that the host your database is running on is also up to date, secure and well-managed. There are a number of guides out there that can help you with this.
- Ensure your network design is appropriate and secure. The three-tier model of Web, Application, Database - each separated by firewalls - is the recommended deployment model for web applications. Your database servers are best located behind appropriate firewall infrastructure and at the minimum you should avoid locating them directly on the Internet.
Now we're going to demonstrate six basic steps to secure our MySQL database:
1. "root" is not a user for your applications
By default, most distributions should run the MySQL database as a non-root user instead of usually running as the user "mysql". You should ensure your distribution runs the daemon as a non-root user. You can check this by confirming either the mysqld binary with the --user option like so:
# mysqld --user=mysql
Or by ensuring the user is configured in the my.cnf configuration file like so:
Further security can potentially be achieved by a chroot of the MySQL daemon. A chroot changes the root directory for a particular process to another directory. The process cannot access any files outside that directory - locking into a "chroot jail". A chroot jail ensures that if attackers compromise your application, they will be limited to accessing only those files and objects in the jail. Chrooting MySQL can be a complicated process; it varies between distributions and platforms, and is beyond the scope of this tip. You can find an example of how to chroot MySQL on the Debian distribution here.
2. Secure passwords
One of the biggest traps with MySQL is that the password for MySQL's root user is not set automatically. When you first install and start the MySQL daemon you need to set a password. You can do this like so:
# /usr/bin/mysqladmin -u root password 'new-password'
This command tells MySQL to reset the root user password to 'new-password'. When setting the password you should select a strong password, according to the following rules:
- At least 12 characters long
- Not on a dictionary word or based on a dictionary word
- Contains a mix of numbers, upper case, lower case, and special characters
The strength of the passwords for MySQL users is also impacted by the old_passwords configuration option. The option can be set in the my.cnf configuration file or on the command line.old_passwords=1
This option controls compatibility with older, weaker, version 3 password hashes and is used because PHP4 packages often bundle the older version 3 MySQL client library. This client library needs the old_passwords configuration option turned on. A number of distributions, including Red Hat Enterprise Linux, enable this option by default. More modern PHP versions such as PHP5 and PHP6, don't require this setting.If your application is developed in these you should disable this option.old_passwords=0
If you have to still use PHP4 then you also have some possible workarounds. You can either make use of the MySQL2i plug-in or compile your version of PHP against a more recent MySQL version.
You can find more information about MySQL password hashes here.
It is important to note that if you change your MySQL password on the command line that some record of that password may remain or can be gleaned, for example if you use the Bash shell in the history file or via ps output during the change. Using the MySQL command line client or a GUI tool may mitigate this exposure. Even with other tools be aware of the .mysql_history file, which contains all the commands issued to MySQL. You should periodically purge this file, and also ensure that it has sufficiently secure permissions:
# cat /dev/null > ~/.mysql_history # chmod 0600 ~./mysql_history
3. Renaming the root user
We're also going to rename the root user to make it even harder for an attacker to brute force passwords. We do this by launching the command line mysql binary and editing the "mysql" database that holds MySQL's credentials. This database is automatically created when MySQL is installed.
# mysql –u root –p Mysql> use mysql; mysql> update user set user="newadmin" where user="root"; mysql> flush privileges;
Here we've connected to the "mysql" database, changed all references from root to "newadmin" (you'd choose something appropriate for your host) and then used the "flush privileges" command to refresh MySQL's security state.
4. Delete any anonymous users
Next we're going to remove any anonymous access to our MySQL databases. By default one or more anonymous accounts - identified by their lack of a username - are created when MySQL is installed. We remove these accounts by deleting users without a user name from the "mysql" database like so:
mysql> use mysql; mysql> DELETE from user where user=''; mysql> flush privileges;
Here we've selected the "mysql" database that holds MySQL's credential information, and then deleted any user without a user name and finally flushed privileges.5. Remove the sample database
We're also going to delete the sample "test" database to clean up our MySQL environment. This database is designed to be a test space and can be accessed by the anonymous users we deleted in step 4. To remove the database we use the drop command like so:
mysql> drop database test;
6. Ensuring only local access to the instance is allowed (if possible)
There are two methods by which applications can access MySQL databases: via a network socket or via a file-system socket. With network sockets the application can access the database from any host that has connectivity to the MySQL host (including applications running locally). With file-system sockets, the application doing the accessing is located on the same host as the MySQL database.
Running a MySQL network socket on a publicly available interface, especially on the Internet, could expose your host to attack and potential compromise. To reduce the risk of this, it is recommended that you restrict access to your database to the local host only. For applications that reside on a remote host this won't be possible.. In this case you can use a firewall like iptables to restrict access to the MySQL port to only those hosts that require it. For example, use a rule like this:
Iiptables -A INPUT -p tcp -s 192.168.10.10 -d 192.168.10.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A INPUT -p tcp –I lo --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables –A INPUT –p tcp –dport 3306 –m state NEW,ESTABLISHED –j DROP
The first rule tells iptables to allow connections from the 192.168.10.10 host to the 192.168.10.20 host on port 3306 - MySQL's default port. The second rule allows access from the localhost. The last rule drops any other MySQL connections. You would then also need to create appropriate outgoing rules.
If you are able to restrict MySQL access to the local host, you can achieve this one of two ways:
a. Lock the network socket down to the localhost
To lock the network socket down to the local host we bind the daemon to the loopback address by using the bind-address option in the my.cnf configuration file like so:
[mysqld] bind-address = 127.0.0.1
We can then use a tool like netstat to confirm MySQL is only bound to the local host. Then only processes on the local host would be able to access the MySQL daemon.b. Disable networking and use the local file socket.
If we can use a file socket rather than networking we can disable networking via the my.cnf configuration file:
We then access MySQL via the local socket. The socket generally defaults to a location like /tmp/mysql.sock but whose location we can also configure in my.cnf:
You can then configure your application to make use of this socket.
There are a number of other steps that go beyond the scope of this tip that you could take to secure MySQL. These include:
1. SELinux or AppArmor
Recent versions of both Red Hat EL and SLE support securing MySQL with mandatory access controls implemented with either SELinux or AppArmor. The Red Hat "targeted" SELinux policy includes support for the MySQL daemon, and there is also an AppArmor profile available for MySQL.
In recent releases MySQL has also enabled support for network connectivity using SSL and certificates to encrypt and authenticate connections. Support for SSL-secured connections was introduced in MySQL 4, and can be used to secure connections between your applications and databases. You can also configure MySQL to only use SSL-based connections to further secure connections. You can read about MySQL's SSL support at http://dev.mysql.com/doc/refman/5.0/en/secure-basics.html.
For earlier releases of MySQL, you could use an SSL tunnelling application like stunnel to secure connections. The stunnel website has an example of how to secure MySQL at http://www.stunnel.org/examples/mysql.html.
Lastly, whilst not directly securing MySQL, it is important to monitor logs. MySQL authentication failures and other security related errors are logged and can give you the first indication that something is wrong. I recommend using a log correlation tool like SEC or Swatch to alert you to particular error messages and events.
You can find further information about MySQL security here.
About the author:James Turnbull works for the National Australia Bank as a Security Architect. He is also the author of Hardening Linux, which focuses on hardening Linux hosts including the base operating system, file systems, firewalling, connections, logging, testing your security and securing a number of common applications including e-mail, FTP and DNS.
James has previously worked as an Executive Manager of IT Security at the Commonwealth Bank of Australia, the CIO of a medical research foundation doing Web-based clinical trials, managing the architecture group of an outsourcing company and in a number of IT roles in gaming, telecommunications and government.