Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Sep 8, 2010

Top 10 Security Tips for MySQL

By Chris Schneider

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.

Steps:

  1. Start by making a file in the /root directory called, .mycreds
  2. Insert a key value pair with username and password for a MySQL account
		Myuseranme=mypassword
  1. Make sure that root owns the file
  2. Make sure the you chmod 700 .mycreds
  3. In roots .bashrc put in the following

Example:

		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

Etc…

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 permissions.

#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

The number one security tip, and the most basic, is the use of mysql_secure_installation. Below is an example of mysql_secure_installation usage:

[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!

» See All Articles by Columnist Chris Schneider



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date