Security is a crucial part of any database system and MySQL is no different! There are very specific techniques a database administrator can use to protect their MySQL installation. Below is a TOP 10 list with what I believe are the top security tips for MySQL database administrators.
#10 - Always check release notes
In any version of MySQL, there is a detailed list of changes that range from performance to security fixes. You can check them out on the MySQL Community Server download page; just look for the Change History link or you can go right to the change history here. It is important to know the changes in your release so you can keep track of possible bugs that your application could exploit.
#9 - Avoid LOCAL INFILE
Using LOAD DATA LOCAL INFILE is a great feature to have in QA, DEV or TEST; however, it is not a good feature to enable in production. Basically, if you connect to a remote MySQL server you could load a file from the client to the MySQL host server. This is a great feature to have when you don't want to mess with copying the file out of a remote server you don't have access to. On the other hand, this can be dangerous given that the client can load any file to the MySQL server host to which it has read access.
#8 - Monitor as much as you can
Monitoring the MySQL database and schema in general is a good idea. In MySQL versions 5.x and up you can utilize the information schema database to access a lot of great information. You can keep a running count of tables and columns within any and all databases and monitor those numbers to see if they have changed. You can also implement a similar mechanism to monitor the USER_PRIVILEGES table.
If you have MySQL Enterprise Monitor there is a security section you can enable to check if something has changed in your schema or user accounts.
#7 - Don't run MySQL as root
You should not be running your MySQL server as the root user. To do so is extremely dangerous given that any user with FILE privilege within MySQL is able to create a file as root.
I find it very easy to create an unprivileged Unix user called mysql and use this account to start and stop mysqld.
#6 - Preloading with init-file, the good and the bad
The init-file option in MySQL can be very useful; however, it is VERY dangerous as well. A good use of init-file is to preload your data into memory during the initial startup. The problem with this is that in the event someone had access to modify this file they could put in ANYTHING they want in the preload and it will be executed upon startup.
#5 - Plain text passwords in Scripts
It is very easy to be lazy and place a plain text password in your scripts. I think that it is safe to say that we've all done this at least once or twice… or still do. In any case, you should try the technique below to help hide passwords from the ordinary user.
- Start by making a file in the /root directory called, .mycreds
- Insert a key value pair with username and password for a MySQL account
- Make sure that root owns the file
- Make sure the you chmod 700 .mycreds
- In roots .bashrc put in the following
export MYUSR=`grep myusername /root/.mycred | cut -d "=" -f 2` export MYPWD=`grep mypassword /root/.mycred | cut -d "=" -f 2`
You can now login to MySQL with the following:
[root@sandbox ~]# mysql -u$MYUSR -p$MYPWD somedb Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 34015 Server version: 5.5.1-m2-community-log MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. (somedb@localhost) [(none)]>
In your daily scripts that are run out of root CRONs, you can now replace plain text usernames and passwords. Keep in mind that only the root user has access to this login method, so, if you are worried about the "what if" on the root user account you probably have bigger security issues. In that case write everything in C and compile your code.
#4 - You can't have ALL PRIVILEGES
Give specific permissions on an as needed basis and use different logins for different purposes. Below is a short list of some users that are used for different aspects of managing MySQL:
Backupuser = local user that runs backups
Root = local user for total administration
Replication = User for replication
Someappuser = local/remote user with SELECT,INSERT,UPDATE,DELETE
NOTE: You don't have to give column level permissions because they hurt
performance so try and stick to table, at the lowest, and database level
#3 - FQDN is a NO,NO
Avoid using FQDN or hostnames when granting access. Try to keep it to a subnet
(Example: 10.1.2.%). On one side, this is a security issue because it is very
easy to spoof a FQDN, on the other side this is performance related. If you
don't disable reverse DNS lookups and DNS is miss-configured or goes down, all
of your connections will be in an "unauthenticated" state for the
duration of the reverse DNS lookup, around 2 minutes. Needless to say, this is
something to avoid on at least two levels.
#2 - Beware of %
Avoid having the MySQL server open to connections from everywhere by using
'%' for any user. Although remote exploits are few and far between, it is just
better not to risk it. Limit a user's access to a specific VLAN or even better
a specific IP address.
#1 - MySQL Secure Installation
[chris@sandbox chris]# /usr/bin/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 authorization. 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!
Checking the secure installation at the end is a good practice to adopt. Below are some quick ways you can use to test and see if the installation is now secure.
[chris@sandbox chris]# mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [chris@sandbox chris]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.5-m3-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. (root@localhost) [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [mysql]> select user,host,password from user where user = '' OR password = ''; Empty set (0.00 sec) NOTE: no blank users and no users without a password. (root@localhost) [mysql]> show databases like 'test'; Empty set (0.00 sec) NOTE: the test database is gone.
Some Closing Thoughts
Securing production MySQL installations is very necessary and should not be considered an afterthought by anyone in the organization. There are many techniques you can implement to aid in the security of a MySQL installation but keep in mind that security on the database level alone is not where security ends. Make sure to collaborate with your system administrators, network engineers and development teams to ensure security everywhere!