Modernizing Authentication — What It Takes to Transform Secure Access
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.
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.
MySQL also has built-in tools for blocking hosts that failed authentication.
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.
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.
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.
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]:
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.
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!
Do not give users global privileges, with the possible exceptions of, for example, root, backup user, monitoring user, and replication user.
Carefully give out privileges such as SUPER or FILE.
Install the variable secure_file_priv in order to limit the operation of import and export by certain server directories.
Restrict user addresses from which they can connect to the database:
GRANT ... TO ‘developer’@’172.20.0.1’ ... GRANT ... TO ‘api_user’@’192.168.5.%’ ...
By default, the MySQL server is listening on all network interfaces. If there is no need for remote access to the database, turn it off by using the skip-networking option.
Use the bind-address and port options in the [mysqld] section to accurately set the interface and port to be used by the MySQL server. It is recommended to use a port number different from the standard one.
Use the variable max_connect_errors to restrict the hosts that have failed to establish a connection to the database.
Use the general query log for detailed logging of user activity:
[mysqld] general_log_file =
/mysql.log general_log = 1
Error log may be used for searching failed authentication attempts:
[mysqld_safe] log_error=/var/log/mysql/mysql_error.log [mysqld] log_error=/var/log/mysql/mysql_error.log
Install log_error_verbosity (log_warnings ) = 2 to receive information exclusively on warnings and errors.
Connection encryption is disabled by default. It is recommended to use encryption when transmitting data over insecure channels. Customize it by setting the following variables:
[mysqld] ssl-ca = "ca-cert.pem" ssl-cert = "server-cert.pem" ssl-key = "server-key.pem"
Using the option ssl-mode=REQUIRED is also recommended.
For easier setup, the script mysql_ssl_rsa_setup comes together with the MySQL package.
There is also a possibility of restricting user access based on SSL:
GRANT ... FOR ‘api’@’192.168.5.%’ ... REQUIRE SSL GRANT ... FOR ‘api’@’192.168.5.%’ ... REQUIRE X509 GRANT ... FOR ‘api’@’192.168.5.%’ ... REQUIRE [ISSUER|SUBJECT] '/C=US/L=Boston/O=API/CN=API Service‘
It’s vital to remember that connection encryption may have negative affect on DBMS performance.
MySQL has a long list of bugs, some of which belong to the category of security vulnerabilities. It is important to install updates regularly and on time, and be aware of how often they are available.
Databases must be protected by a firewall. The influence of Linux iptables does not significantly affect performance.
By using such solutions as the MySQL Router or creating your own API intermediates between the database and the application, you will be able to protect the data in the database from most threats.
And one final thought: the file with the history of MySQL commands can contain sensitive information entered during initial setup. Clean it up this way:
cat /dev/null > ~/.mysql_history
Maxim Sovetkin, lead system engineer, joined Itransition in 2010. He has broad experience in system and network administration and engineering, hardware evaluation, internal project management, systems and network security, incident analysis and recovery. His technical interests are in automation, hardware, *nix, networking, SAN, security, system integration, planning and design, virtualization, VoIP, wireless technologies, Windows and workforce management. Sovetkin graduated from Belarusian State University with a degree in mathematics, system analysis and IT systems modeling.
Photo courtesy of Shutterstock.