Securing MySQL on a production server
Archive - Originally posted on "The Horse's Mouth" - 2009-10-09 05:50:44 - Graham EllisThere's a conumdrum for the authors / distributors of any open source server software that's likely to be used in a productions environment - should they send it out so that it's quick and easy to try but needs securing, or so that it's well secured but therefore calls for a bit more effort when you try it out.
The MySQL folks have always been "passed masters" at providing good distributions that install easily and test well ... but there have always been warnings about setting up passwords, getting rid of anonymous accounts and test databases, and limiting direct logins to localhost or a specific subnet.
These warnings remain, but in recent versions, the MySQL folks supply a script called mysql_secure_installation which takes you through each of the areas I have warned you about and lets you close the potential loopholes (some ARE just potential) on your to-be-production server.
Before you run the script, you should set up your path to include the MySQL binaries just installed:
export PATH=/usr/local/mysql/bin:$PATH
./bin/mysql_secure_installationand if you get "cannot connect to socket" messages, you should clean up / repeat the install, getting rid of the /var/lib/mysql directory before the reinstall!
What are the issues that the mysql secure installation deals with?
1. Setting the root password ensures that nobody can log into the MySQL root user without authentication (as shipped / unpacked, there is no root password set)
2. 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 account is limited in where it can log in from and what it can do, but never the less it should normally be removed.
3. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. If your server is behind a firewall, this may be less of a concern, but if you do need root access to MySQL from a remote site, you've probably got an ssh access set up to the server too and you should use that and run the mysql client on the server.
4.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. If you've removed the anonymous user already, there's less of an issue here as the door is already closed, but it IS sensible to remove the test database. If you leave both anonymous access and the test database easily accessed, dangers include unauthorised people stuffing your discs with gobs of data you don't want, and then running slow queries on it.
Even if you go through this securing script when installing MySQL, you still need to continue to consider security at all times. A login account to MySQL with a password the same as the user account name, or a password held in plain test in a publicly readable file can compromise the whole system. And if your database can hold data submitted by users, you'll need to have an acceptable user policy and a system in place to enforce that AUP.