MySQL is one of the most popular relational database management systems (RDBMS) around the world. Unfortunately, due to its popularity, people already know its weak points. A straight-out-of-the-box, freshly installed MySQL database can be vulnerable to many risks. To prevent attacks and security exploits, we must arm ourselves with knowledge about security improvements. So let’s start!
Chart 1. Database management systems according to their popularity.
September 2016, Source: http://db-engines.com/
You should be especially interested if you use the MySQL RDBMS. Don’t worry – I will lead you through the most important security cases step by step.
1. Always run MySQL as a user without root privileges
Running database service as a Root is a bad practice. Firstly, you should create a new user account – with only the necessary permissions (e.g. appropriate permissions to the data directory) to run the service. After that, you should always run the MySQL server through this new user.
Running MySQL as Root means that everything the server does is also done with root privileges. If you happen to make a mistake, this can cause problems:
- if you misconfigure the MySQL logfile to /etc/passwd, then that important file will probably be overwritten (a normal user can’t do that)
- Root usually has some amount of disk system reserved (per filesystem), therefore it’s more likely to fill the whole partition
- code injection: if you have scripts running (for backup of the now Root-owned files) then you need higher privileges there, and – again – any mistake might have more grievous consequences
2. Make the MySQL root user account “invisible”
After the installation, the database’s grant tables are populated with default MySQL accounts.
Some accounts have the username: root. These are superuser accounts which have all the privileges. Basically, they can do everything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges. We have to prevent this!
Firstly, assign a password to each MySQL root account that doesn’t have one.
Secondly, change the username of all root accounts to hard-to-guess names, because attackers have to provide the account name before trying to brute force the passwords.
This step will add an extra layer of security.
3. Delete all anonymous accounts
In addition to administrator accounts after installation, there are anonymous user accounts. They have no password, so anyone can use them to connect to the MySQL server. These accounts should be removed, because they give attackers an entry point in our database.
4. Recommended configuration tips
a) network access
Nowadays, we’re often in the situation where the database is running on the same server as a production application. This means we can connect to the MySQL server locally, without remote access. This remote option should be disabled to avoid the possibility of attacks from the outside.
We can do that by adding a skip-networking entry to the configuration file /etc/my.cnf (below the [mysqld] section).
Otherwise, when we’re running MySQL and application on separate servers, we should configure the MySQL server to give access only to allowed remote hosts. You can do this by making some changes in /etc/hosts.deny and /etc/hosts.allow files.
- Make sure to check if your MySQL daemon is listening on remote accessible port only when it is needed. Create an appropriate firewall rule to allow connection to MySQL port only from trusted hosts.
- In case you’re using Amazon RDS as a MySQL service, check if your instance is attached to the correct security group. It is recommended to carry out some tests of connections from various remote hosts to check if your configuration is secure.
- Set firewall rules with IP address instead of hostnames to protect yourself from DNS spoofing and similar attacks if you don’t trust your DNS. Don’t create firewall rules with wildcards.
- In case of emergency, when you need direct access to the database to fix some issue, it is better to use the SSH tunnel instead of opening port to network or change firewall rules.
- Check if your administrative accounts are accessible only from localhost. They should be.
b) disable “LOAD DATA LOCAL INFILE” command
Executing this allows users to read local files. This could be used by attackers through several methods, for example SQL Injection. Furthermore, they can access other files in the operating system. This can be dangerous.
This command should be disabled by adding:
set-variable=local-infile=0to the configuration file /etc/my.cnf (below the [mysqld] section).
c) do not grant FILE privilege to non-administrative users
This privilege is dangerous because it allows users to write file anywhere in filesystem with privileges of mysqld daemon. It allows to modify contents of MySQL’s data directory (including files that implements privilege table).
On the other hand, FILE privilege can be also used to execute LOAD DATA to read /etc/passwd or any other secret file to database table and then read it using SELECT.
I recommend to restrict access to the specified directory only by setting:
secure_file_priv=dirname
d) do not grant PROCESS or SUPER privileges on non-administrative accounts
It is good to know the meaning of two dangerous privileges that should be set only for administrative accounts.
PROCESS privilege allows to display all currently executed SQL commands. These include vulnerable ones like setting new passwords to MYSQL users or setting new credentials by user in your web application. The list of processed queries is not limited to the currently logged user and is not encrypted.
SUPER privilege allows to change server system variables, terminate client connections, control replication servers and connect regardless of the connection limit.
e) disable or restrict a “SHOW DATABASES” command
The “SHOW DATABASES” command allows users to see names of databases in our MySQL server.
If attackers are able to get the database names, they can easily get access to the data.
This command should be disabled or restricted on production server by adding:
skip-show-database
to the configuration file /etc/my.cnf (below the [mysqld] section).
f) change default port
By default, our MySQL server will run on port 3306. If we don’t change this, attackers will try to use these initial values to get access to the database.
We should change this critical value immediately after installation to prevent dummy hacker attacks.
5. Delete the Test database
After installation, your server will have a ‘test’ database which was installed by MySQL Server package. This database can be accessed by all users by default. This is a security risk. It should be removed by running:
DROP DATABASE test;
6. Clear the MySQL history file and prevent logging to this file
This file contains historical details about initial configuration and installation. It may be exploited for getting passwords (for database users). Also, other data will be logged into this file while the MySQL server works. It’s located at ~/.mysql_history and should be deleted. We should also block logging to file by creating a softlink to the /dev/null device.
Conclusion
These are the most popular cases. Of course, there are more security threats. Remember to keep your operating system environment secure. If the entire environment is unsafe, all things in it are vulnerable and can be easily exposed to attackers.
MySQL provides a simple command to help proceed with most important security settings. This command is called:
mysql_secure_installation
It guides you through setting password for root account, removing of anonymous users, disabling root remote access, removing test database with related access rules. At the end, it flushes the privilege table. It is a quick command that should be run right after mysql daemon installation.
If you want to know more about securing or configuring the MySQL server – check out the following links.
Sources and more information:
[1] http://db-engines.com/en/ranking_trend
[2] https://dev.mysql.com/doc/refman/5.5/en/default-privileges.html
[3] https://besthostingsearch.org/tutorial/secure-mysql-databases-against-attackers/
[4] http://technotif.com/hardening-mysql-security-server/
Authors:
Mateusz Palichleb, Filip Chmarzyński
