MySQL Security Best Practices

MySQL is one of the best opensource databases that is easy to use. Normally security configuration will not be part of the “ready-to-use” MySQL packages. From MySQL 5.0 Security Configuration is made as a separate package and can be configured with the mysql_secure_installation command. This will be available in the bin folder of MySQL. In addition to this, the following are some of the best practices followed.

Configuration File

The MySQL configuration is organizaised in a single file. On Linux my.cnf file will be placed on the “/etc/my.cnf”. On Windows my.ini file will be placed on the installation directory of the MySQL server.

Secure your server

Threats to MySQL is possible only if the physical access to a machine has been acquired. Hence it is best to have the application server and the database server on different machines. In case both reside on the same machine, then greater care must be taken. If this is not done, then by executing remote commands via an application server anyone can attack the system without any permissions.

1. Disable the use of LOCAL INFILE

SQL – Injection Attack, which is a common vulerability on today’s web application are using the “LOAD DATA LOCAL INFILE” command.

Using the he “LOCAL INFILE” command one can easily gain access to other files on the operating system.

The most common target file on a Linux environmnt is “/etc/passwd”. To execute this the following Command:

mysql> SELECT load_file()

To prevent the database from such SQL-Injection attacks disable the LOCAL INFILE command. Add the following to the configuration file in the [mysqld] section: set-variable=local-infile=0

2. Change root username

The user is the default administrator on the MySQL server. So brute-force attack is commonly used by the hackers to gain access. To make this task harder, you can rename the user using the following command:

mysql> RENAME USER root TO new-user;
The “RENAME USER” command will be available from MySQL 5.0.2 version.

An older version root user can be renamed by issuing the following commands.

mysql> use mysql;
mysql> update user set user= “new-username” where user=”root”;
mysql> flush privileges;

3. Controlling network access and configure the remote access

On a Linux Environment, access can be confiured with the help of iptables. On a Windows environment access can be controlled using the firewall.

To restrict MySQL from opening a network socket configure the following on the [mysqld] section of your Configuration file i.e my.cnf or my.ini: skip-networking

This will disable the remote connections but will allow any request from the localhost. This can also be achived by configuring the bind address forcefully and make the MySQL server to listen to the localhost.

bind-address=127.0.0.1

Note this can also be configured with any remote IP, in that case only the request from the remote server will be listened.

In case if need to be provide access to any remote host the following grant syntax can be used. Make sure only requried permissions are provided to host.

mysql> GRANT SELECT, INSERT ON .* TO @;

4. Remove the default database and anonymous/old users accounts

This option comes with the mysql_secure_installation package. This can be also achived by the following steps.

MySQL comes with a database to verify the installation. By default the database is open to all users, and so this is most commonly targeted by attackers. To remove this database the “drop” command is used as follows: mysql> drop database test;

The installation of MySQL database is configured with default database with anonymous user and blank password. To get all the anonymous users, use the following:

mysql> select * from mysql.user where user=”;
mysql> DROP USER ”;
The “DROP USER” command is supported only from MySQL 5.0. If you use an older version of MySQL, follwoing command can be used.
mysql> use mysql;
mysql> DELETE FROM user WHERE user=”;
mysql> flush privileges;

Arasu is a software engineer at Compassites Software and has 8 years of IT experience. He holds an MCA degree from Hindusthan College, Coimbatore. His areas of expertise include Oracle, MySQL, MS-SQL, PHP, Zend, SAP-ABAP and he is passionate about open-source development. Apart from work he is interested in reading books and playing chess.

Leave a comment