Best practices to secure mysql

Question

I don't have that much knowledge about mysql and i would like to secure a MySQL server.

We have a few VPS running around the world (6 currently) and they all need to store information in our mySQL server.

Each VPS will run 1 to 3 scripts and each script is storing 100 rows in our main table. With our estimation our main table will contain 20 000 000 rows and then will get 10k to 20k rows a day.

Currently I was thinking about allowing those 6 vpn to connect to our mysql server and a different username/password for each script. I was also thinking of limiting the rights to insert only for the script.

What do you guys think? Are my plans on the right directions? Any other advice?

Answer 1

When it comes to securing mysql, as sson as you install mysql, you must run the script mysql_secure_installation. If mysql is already in production for a while, you can perform the steps of the script manually with a lot of caveats.

For example, you have to erase all entries in mysql.db whose db column's first four(40 characters are 'test'. : Please see MySQL : Why are there "test" entries in mysql.db? (February 17, 2012)

You must drop anonymous users

• MySQL error: Access denied for user 'a'@'localhost' (using password: YES) (January 19, 2012)
• Weird MySQL Users Been Created (eg. bug115166_10073) and not by me (September 28, 2011)

Here are my other posts on securing mysql

• Limiting database security (June 6, 2012)
• Is this a normal set of MySQL privileges? (September 11, 2011)

Answer 2

The information you provided is not enough to recommend anything specific.

Security model is usually based on trust relationships between servers and clients. If all clients are run in secure environment, then you can use root with all permissions to connect to the sever. Otherwise, you have to adjust your security policy according to (as I have already mentioned) your trust relationships model. By secure environment I mean that your scripts can NOT be modified without appropriate permissions granted to trusted people only, and your network is not vulnerable. For example, your scripts should not be on computers that also have http (with some buggy, free CMS), ftp and rdp services open to the Internet. This is the technical side.

Now the business side. You probably have a certain domain model that your clients operate on. Your scripts probably have roles (say sales staff, administration staff, etc). You probably don't want sales staff to be able to access administration tables, or whatever. This rises a security question. You can implement your security tier in 2 places: mysql server, rich client. You said that you run scripts (which are not rich clients), so your only option is mysql server. You might want to setup accounts and set minimalistic permissions on them. By minimalistic permissions I mean that each user is allowed to do only and only, what he needs to able to do.

Once again, you got to provide more specific information to get more specific advices.

See here for MySQL-specific security guidelines.