By Maxim Sovetkin, Itransition

MySQL, owned by Oracle since 2009, is the number one open source database for successful startups and Web-based applications, loved by such iconic social networks as Facebook, Twitter, YouTube and many others. The database comes in two different editions: the open source MySQL Community Server and the proprietary Enterprise Server. Today, we will discuss the MySQL Community Server, and more specifically the basic security aspects of setting up this DBMS.

Security capabilities

Managing users and their roles

MySQL allows owners to determine who has access to the database, as well as what actions they are able to perform in it. Unfortunately, this can only be done on the level of users and hosts via which they are connected. Additional features, like users forming groups or the implementation of the role-based access control (RBAC) approach, are realized with the help of proprietary or third-party extensions.

Secure connections to DBMS

MySQL supports encrypted SSL/TLS connections, with the presence of keys and certificates for both servers and clients as a main requirement. Setup and debugging present quite a large effort.

Starting from version 5.7, MySQL supports password expiration policy

User authentication can also be performed using certificates.

Additional plug-ins allow for implementing two-factor authentication or storing passwords in encrypted form.

MySQL also has built-in tools for blocking hosts that failed authentication.

Logging

In terms of information security, it makes sense to enable and analyze the error log and general query log. The error log focuses on information about MySQL-server issues. The general query log contains information about connections and data received from clients.

Audit

Unfortunately, audit is only possible through third-party plug-ins. At the moment, they are in development by MySQL Enterprise, Percona, MariaDB and McAfee.

Integrated cryptographic functions

Starting with version4.0.2, MySQL supports a set of cryptographic functions: AES_ENCRYPT (), AES_DESCRYPT (), DES_ENCRYPT (), DES_DECRYPT (). Their implementation and usage has a number of specific features.

Secure setup

The reality is that MySQL Community Server out of the box is not safe, and most developers and administrators don’t take any measures to improve the situation.

To avoid information security risks, and therefore minimize the likelihood of data loss or theft, it’s important to pay attention to the following recommendations.

Environment

By starting MySQL processes in a chroot environment, it’s possible to limit access to the file system and significantly increase the security of the server as a whole. For convenience, the socket file (if it’s used) can be transferred to the same environment by correcting the following line in the configuration file in sections [mysqld], [mysql], [client], [mysqladmin] and [mysqldump]:

socket = /chroot/mysql/tmp/mysql.sock

In order to prevent the unauthorized reading of files from the disk you can also disable the option local-inifile in the section [mysqld]:

set-variable=local-infile=0

This measure will prevent attacks by SQL injections and vulnerabilities in PHP applications.

It is important to make sure that MySQL processes have been launched from a system user with a unique UID/GID.

Users

It is necessary to change the name, set a strong password and disable remote connections for the user account of the system administrator:


use mysql;
update user set user='admin' where user='root';
#MySQL <5.7
update user set password=PASSWORD('new password') where user='admin';
#MySQL >=5.7
update user set authentication_string=PASSWORD('new password') where user='admin';
DELETE FROM mysql.user WHERE User=’admin’ AND Host NOT IN ('localhost', '127.0.0.1', '::1');
flush privileges;

If MySQL Server is used on Debian/Ubuntu OS, the rights of the system user debian-sys-maint need to be cut down from privileged to minimal:


REVOKE ALL PRIVILEGES ON *.* FROM 'debian-sys-maint'@'localhost';
GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost';
FLUSH PRIVILEGES;

By default, MySQL includes an anonymous user and a test database; to get rid of them, do the following:


delete from mysql.db where Db like 'test%';
drop user ''@'localhost';
drop user ''@'localhost.localdomain';
drop database test;

Alternatively, a mysql_secure_installation script that comes with DBMS and solves these problems can be used:


mysql_secure_installation
Set root password? [Y/n] Y
New password: 
Reenter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!
Remove anonymous users? [Y/n] Y
... Success!
Disallow root login remotely? [Y/n] n
... skipping.
Remove test database and access to it? [Y/n] Y
Dropping test database...
... Success!
Removing privileges on test database...
... Success!
Reload privilege tables now? [Y/n] Y
... Success!