The MySQL database has become the world’s most popular open source database because of its consistent fast performance, high reliability and ease of use. MySQL is used by individuals, Web developers, as well as many of the world’s largest and fastest-growing organizations such as industry leaders Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube and others to save time and money powering their high-volume websites, business-critical systems, and packaged software.
It is often used in conjunction with PHP. Besides its undoubted advantages such as easy of use and relatively high performance, MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.
This article describes the basic steps which should be performed in order to secure a MySQL database against both local and remote attacks.
1. Secure your server
Many known attacks are possible only once physical access to a machine has been acquired. For this reason, it is best to have the application server and the database server on different machines. If this is not possible, greater care must be taken; otherwise, by executing remote commands via an application server, an attacker may be able to harm your database even without permissions. For this reason, any service running on the same machine as the database should be granted the lowest possible permission that will still allow the service to operate.
Do not forget to install the whole security package: Antivirus and Antispam, Firewall, and all of the security packages recommended by your operating system’s vendor. In addition, do not forget to spend 10 minutes thinking of your server’s physical location in the wrong location, your server can be stolen, flooded, or harmed by wild animals or running children. Consider performing some operating system hardening procedures.
The Mysql database will be used only by PHP applications, installed on the same host; the default administrative tools, such as mysqladmin, mysql, mysqldump etc. eill be used to manage the database; Remote data backup will be performed by utilizing the SSH protocol
In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:
- MySQL database must be executed in a chrooted environment;
- MySQL processes must run under a unique UID/GID that is not used by any other system process;
- Only local access to MySQL will be allowed;
- MySQL root’s account must be protected by a hard to guess password;
- The administrator’s account will be renamed;
- Anonymous access to the database (by using the nobody account) must be disabled;
- All sample databases and tables must be removed.
Before we start securing MySQL, we must install the software on the server. As in the previous articles, we will start installation by creating a unique, regular group and user account on the operating system, which will be dedicated to the MySQL database:
# groupadd mysql
# useradd mysql -c “MySQL Server” -d /dev/null -g mysql -s /sbin/nologin
#chown root:sys /etc/my.cnf
#chmod 644 /etc/my.cnf
2. Disable or restrict remote access
Consider whether MYSQL wil be accessed from the network or only from its own server. If remote access is used, ensure that only defined hosts can access the server. This is typically done through TCP wrappers, iptables, or any other firewall software or hardware available on the market on the market. To restrict MYSQL from opening a network socket, the following parameter should be added in the [mysqld] section of my.cnf
This line disables the initiation of networking during MySQL startup. Please note that a local connection can still be established to the MySQL server.
Another possible solution is to force MySQL to listen only to the localhost
You may not be willing to disable network access to your database server if users in your organization connect to the server from their machines or the web server installed on a different machine. In that case, the following restrictive grant syntax should be considered:
#mysql -u root -p
mysql> GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@’somehost’ IDENTIFIED by ‘password’;
3. Disable the use of LOCAL INFILE
The next change is to disable the use of the “LOAD DATA LOCAL INFILE” command, which will help to prevent unauthorized reading from local files. This is especially important when new SQL Injection vulnerabilities in PHP applications are found.
In addition, in certain cases, the “LOCAL INFILE” command can be used to gain access to other files on the operating system, for instance “/etc/passwd” , using the following command:
mysql> LOAD DATA LOCAL INFILE ‘/etc/passwd’ INTO TABLE table1
Or even simpler:
mysql> SELECT load_file(“/etc/passwd”)
To disable the usage of the “LOCAL INFILE” command, the following parameter should be added in the [mysqld] section of the MySQL configuration file.
4. Change root username and password
The default administrator username on the MySQL server is “root”. Hackers often attempt to gain access to its permissions. To make this task harder, rename “root” to something else and provide it with a long, complex alphanumeric password.
To rename the administrator’s username, use the rename command in the MySQL console:
mysql> RENAME USER root TO new_user;
The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:
mysql> use mysql;
mysql> update user set user=”new_user” where user=”root”;
mysql> flush privileges;
To change a user’s password, use the following command-line command:
mysql> SET PASSWORD FOR ‘username’@’%hostname’ = PASSWORD(‘newpass’);
It is also possible to change the password using the “mysqladmin” utility:
shell> mysqladmin -u username -p password newpass
5. Remove the “test” database
MySQL comes with a “test” database intended as a test space. It can be accessed by the anonymous user, and is therefore used by numerous attacks.
To remove this database, use the drop command as follows:
mysql> drop database test;
Or use the “mysqladmin” command:
shell> mysqladmin -u username -p drop test
6. Remove Anonymous and obsolete accounts
The MySQL database comes with some anonymous users with blank passwords. As a result, anyone can connect to the database To check whether this is the case, do the following:
mysql> select * from mysql.user where user=””;
In a secure system, no lines should be echoed back. Another way to do the same:
mysql> SHOW GRANTS FOR ”@’localhost’;
mysql> SHOW GRANTS FOR ”@’myhost’;
If the grants exist, then anybody can access the database and at least use the default database“test”. Check this with:
shell> mysql -u blablabla
To remove the account, execute the following command:
mysql> DROP USER “”;
The MySQL “DROP USER” command is supported starting with MySQL version 5.0. If you use an older version of MySQL, you can remove the account as follows:
mysql> use mysql;
mysql> DELETE FROM user WHERE user=””;
mysql> flush privileges;
7. Lower system privileges
A very common database security recommendation is to lower the permissions given to various parties. MySQL is no different. Typically, when developers work, they use the system’s maximum permission and give less consideration to permission principles than we might expect. This practice can expose the database to significant risk.
* Any new MySQL 5.x installation already installed using the correct security measures.
To protect your database, make sure that the file directory in which the MySQL database is actually stored is owned by the user “mysql” and the group “mysql”.
shell>ls -l /var/lib/mysql
In addition, ensure that only the user “mysql” and “root” have access to the directory/var/lib/mysql.
The mysql binaries, which reside under the /usr/bin/ directory, should be owned by “root” or the specific system “mysql” user. Other users should not have write access to these files.
shell>ls -l /usr/bin/my*
8. Lower database privileges
Operating system permissions were fixed in the preceding section. Now let’s talk about database permissions. In most cases, there is an administrator user (the renamed “root”) and one or more actual users who coexist in the database. Usually, the “root” has nothing to do with the data in the database; instead, it is used to maintain the server and its tables, to give and revoke permissions, etc.
On the other hand, some user ids are used to access the data, such as the user id assigned to the web server to execute “select\update\insert\delete” queries and to execute stored procedures. In most cases, no other users are necessary; however, only you, as a system administrator can really know your application’s needs.
Only administrator accounts need to be granted the SUPER / PROCESS /FILE privileges and access to the mysql database. Usually, it is a good idea to lower the administrator’s permissions for accessing the data.
Review the privileges of the rest of the users and ensure that these are set appropriately. This can be done using the following steps.
mysql> use mysql;
mysql> select * from users;
[List grants of all users]
mysql> show grants for ‘root’@’localhost’;
The above statement has to be executed for each user ! Note that only users who really need root privileges should be granted them.
Another interesting privilege is “SHOW DATABASES”. By default, the command can be used by everyone having access to the MySQL prompt. They can use it to gather information (e.g., getting database names) before attacking the database by, for instance, stealing the data. To prevent this, it is recommended that you follow the procedures described below.
Add ” –skip-show-database” to the startup script of MySQL or add it to the MySQL configuration file
Grant the SHOW DATABASES privilege only to the users you want to use this command
To disable the usage of the “SHOW DATABASES” command, the following parameter should be added in the [mysqld] section of the /etc/my.cnf:
9. Enable Logging
If your database server does not execute many queries, it is recommended that you enable transaction logging, by adding the following line to [mysqld] section of the
This is not recommended for heavy production MySQL servers because it causes high overhead on the server.
In addition, verify that only the “root” and “mysql” ids have access to these logfiles (at least write access).
Ensure only “root” and “mysql” have access to the logfile “hostname.err”. The file is stored in the mysql data directory. This file contains very sensitive information such as passwords, addresses, table names, stored procedure names and code parts. It can be used for information gathering, and in some cases, can provide the attacker with the information needed to exploit the database, the machine on which the database is installed, or the data inside it.
Ensure only “root” and “mysql” have access to the logfile “*logfileXY”. The file is stored in the mysql data directory.
10. Change the root directory
A chroot on Unix operating systems is an operation that changes the apparent disk root directory for the current running process and its children. A program that is re-rooted to another directory cannot access or name files outside that directory, and the directory is called a “chroot jail” or (less commonly) a “chroot prison”.
By using the chroot environment, the write access of the MYSQL processes (and child processes) can be limited, increasing the security of the server.
Ensure that a dedicated directory exists for the chrooted environment. This should be something like:/chroot/mysqlIn addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of MySQL configuration file:
socket = /chroot/mysql/tmp/mysql.sock
Thanks to that line of code, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the –socket=/chroot/mysql/tmp/mysql.sock parameter every time these tools are run.
11. Remove History
During the installation procedures, there is a lot of sensitive information that can assist an intruder to assault a database. This information is stored in the server’s history and can be very helpful if something goes wrong during the installation. By analyzing the history files, administrators can figure out what has gone wrong and probably fix things up. However, these files are not needed after installation is complete.
We should remove the content of the MySQL history file (~/.mysql_history), where all executed SQL commands are stored (especially passwords, which are stored as plain text):
#cat /dev/null > ~/.mysql_history
12. Chrooting the Server
The first step of securing MySQL is to prepare the chrooted environment, in which the MySQL server will run.
In order to prepare the chrooted environment, we must create the following directory structure:
#mkdir -p /chroot/mysql/dev
#mkdir -p /chroot/mysql/etc
#mkdir -p /chroot/mysql/tmp
#mkdir -p /chroot/mysql/var/tmp
#mkdir -p /chroot/mysql/usr/local/mysql/libexec
#mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english
The access rights to the above directories should be set as follows:
#chown -R root:sys /chroot/mysql
#chmod -R 755 /chroot/mysql
#chmod 1777 /chroot/mysql/tmp
Next, the following files have to be copied into the new directory structure:
#cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
#cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
#cp /etc/hosts /chroot/mysql/etc/
#cp /etc/host.conf /chroot/mysql/etc/
#cp /etc/resolv.conf /chroot/mysql/etc/
#cp /etc/group /chroot/mysql/etc/
#cp /etc/master.passwd /chroot/mysql/etc/passwords
#cp /etc/my.cnf /chroot/mysql/etc/
Tighten passwords and groups
From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all the lines except the mysql account and group.
As in case of the Apache web server, we have to create a special device file /dev/null:
#ls -al /dev/null
#mknod /chroot/mysql/dev/null c 2 2
#chown root:sys /chroot/mysql/dev/null
#chmod 666 /chroot/mysql/dev/null
We must also copy the mysql database, which contains grant tables created during MySQL installation:
#cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
#chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var
13. Mysql with SSL
#mysql –ssl –help
#mysql -u root -p
#mysql > show variables like ‘%ssl%’;
#mysql > quit;
#service mysqld restart
check again if SSL is now enabled
#mysql -u root -p
#mysql > show variables like ‘%ssl%’;
#mysql > quit;
Generate SSL Certificates
#openssl genrsa 2048 > ca-key.pem
#openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem
#openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
#openssl x509 -req -in server-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
#openssl req -newkey rsa:2048 -days 9000 -nodes -keyout client-key.pem > client-req.pem
#openssl x509 -req -in client-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Configure the MySQL Server to use SSL Encryption
Configure the MySQL Clients to use SSL Encryption
Create MySQL User that is Required to use SSL
#mysql -u root -p
#mysql > GRANT SELECT, INSERT, UPDATE, DELETE on mydb.* to ‘ssluser’@’host’ IDENTIFIED BY ‘secretpass’ REQUIRE SSL;
#mysql > FLUSH PRIVILEGES;
Login to MySQL using SSL Encryption
#mysql–ssl-ca=ca-cert.pem –ssl-cert=client-cert.pem –ssl-key=client-key.pem –u ssluser –p
Using the [client] section in /etc/my.cnf
#mysql –u ssluser –p
Check SSL Encryption is Working
#mysql -u root -p
#mysql > show status like ‘%ssl%’;
14. GreenSQL for Preventing SQL INJECTION
GreenSQL delivers Database Security Solution for the small and medium businesses (SMB) and the enterprise markets. The company is committed to protecting information by making database security affordable and easy to manage for every company. With an all-in-one approach to database security, the GreenSQL software-based platform offers Security, Caching, Auditing and Masking in a single package
Small and medium-sized companies are often home to servers that run web applications and database management systems in parallel. An error in the web application can compromise the server or allow unauthorized access to the database through SQL injection, especially if the company lacks the staff to identify vulnerabilities and remedy them in a timely manner. Although SQL proxies such GreenSQL offer no protection against hostile server takeovers, they can effectively prevent SQL injection.
GreenSQL sits between the application and the database management system and acts as a reverse proxy or database firewall. The Security version runs on Linux can protect PostgreSQL, MariaDB, MySQL, and Microsoft SQL Server database management systems.
In firewall mode, only authorized queries are passed in to the database; GreenSQL responds to unauthorized queries with an empty result set. The commercial Activity Monitoring and Data Masking versions also monitor who has sent which queries to the database, or they mask certain data, such as employees or customers. In general, potential attackers will not notice that they are dealing with a proxy instead of the DBMS.
Ideally, you should have three computers: a web server, a dedicated server for GreenSQL, and a database server, although it is also possible to run all of these applications on a single computer
GreenSQL can operate in combination with a risk profile as an IDS or IPS. The queries themselves do not determine whether a request is allowed but, instead, result in an action taken. Depending on the configuration, the IDS blocks the database connection and informs an administrator of the alleged break-in attempt.
What IDS and IPS systems have in common is that they can detect SQL injections. To do so, they reference a database of known attack patterns compiled by GreenSQL. Additionally, all suspicious inquiries are considered to be SQL injection. In the case of a false positive, you assign the query to the Injection Patterns query group or some other group that GreenSQL accepts as legitimate requests.
Granular risk profiles are also created in Database Security. These profiles are not limited to individual queries but can refer to database operations that relate to servers, security, or databases. The profiles include querying active processes or logs, changing passwords and escalating privileges, outputting databases and tables, or retrieving information on database schemas
for more details go to GreenSQL