Privileges

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.%’ ...

Remote access

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.

Logging

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

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.

Final notes

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.