Guard Duty

MySQL security configurations include a range of topics, along with their possible effects on MySQL servers and corresponding applications. In this article, I look at MySQL security encryption services, account-associated authorization systems, and other required security precautions to ensure protection against misuse and attacks. This security guide will help you protect sensitive data, even if the MySQL service is compromised at some point.

Most of the advanced MySQL security configurations require changes to the server's main configuration file my.cnf. This file is generally located inside the /etc/mysql directory or in the /opt/lampp/etc/ folder for LAMPP installations. However, you can locate the file with the find or locate command in Linux.

Encryption at Rest

By default, MySQL stores plaintext data inside InnoDB tables. Any non-admin user with access to these files can read tables and pose a security threat. Data at rest or in transit both require protection to avoid potential risk. MySQL transparent data encryption (TDE) enables encryption at rest to prevent information threats and privacy breaches, even if your system is compromised at some level.

MySQL provides TDE by enabling encryption at rest for physical files in the database. It encrypts data automatically on the go before writing to the storage and decrypts before reading. In this section, I will show you how to configure encryption at rest to ensure protection from physical data theft.

Getting Started

MySQL InnoDB tablespaces are stored in .ibd file format and are generally located in the /var/lib/mysql directory. You can also find them with the locate command:

$ locate ibd | less
/var/lib/mysql/testDB/testTB.ibd
/var/lib/mysql/sys/sys_config.ibd
...

Once located, you can retrieve sensitive plaintext data with the cat or strings command:

$ cat /var/lib/mysql/testDB/testTB.ibd | head -n 20

The command output will generate plaintext information containing raw data stored in the corresponding database table.

Key Management

The centralized key management solution offers electronic code book (ECB) and cipher block chaining (CBC) block encryption for tablespace keys and data encryption in the MySQL server. Encryption at rest for the InnoDB search engine and tablespaces involve a two-tier key architecture that implements easy key management and master key rotation:

• Tablespace key: An encrypted key stored in the tablespace header.
• Master key: A key that decrypts the tablespace key.

MySQL implements InnoDB tablespace encryption with the use of tablespace keys. After tablespace encryption, the master key encrypts the tablespace key to place it inside the tablespace header.

When an authenticated user accesses the encrypted table, InnoDB uses the master key to decrypt the tablespace key. The decrypted tablespace key allows you to perform read/write operations on data.

Master Encryption Key Rotation

A decrypted tablespace key never changes; instead, you can only change the master key by key rotation, which is an instance-level operation that re-encrypts all the tablespace keys and saves them back to the tablespace header:

mysql> alter instance rotate InnoDB master key;

However, the process does not re-encrypt or decrypt the tablespace data.

MySQL Keyring Plugin

Data-at-rest encryption in MySQL supports keyring plugins that enable internal server components to retrieve sensitive content. Here, I use a keyring_file plugin to store the keyring data inside local files in the server host. Configuration requires system variables in the mysql.cnf file, which is located inside the /etc/mysql/conf.d directory:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring-data/keyring

The system variable keyring_file_data defines the keyring_file data location for data storage.

File-per-Table Tablespace Encryption

InnoDB data-at-rest encryption allows file-per-table tablespace encryption by providing the innodb_file_per_table system variable in the mysql.cnf file. Once enabled, you can provide encryption at rest for tables created in file-per-table tablespaces,

[mysqld]
innodb_file_per_table=ON

and restart the mysql service:

$ sudo service mysql restart

You can enable encryption for a new file-per-table tablespace by specifying an encryption='y' clause along with the create table statement:

mysql> create table testTB (c1 INT) encryption='y';
mysql> alter table testTB encryption='y';
Query OK, 1 row affected (0.33 sec)
Records: 1 Duplicates: 0 Warnings: 0

The alter table statement shows how the system variable also enables encryption for existing InnoDB file-per-table tablespaces.

Encryption-at-Rest Verification

Next, you should query the information_schema.tables column to identify newly created or altered encrypted file_per_table tablespaces; similarly, verify that the plugin status is set to ACTIVE to determine successful plugin configuration (Listing 1).

mysql> select table_schema, table_name, create_options from information_schema.tables where create_options like '%encryption%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | testTB     | ENCRYPTION="Y" |
+--------------+------------+----------------+
 **
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'keyring%';
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

Finally, confirm data encryption at rest with the strings command to view the encrypted output:

$ strings /var/lib/mysql/testDB/testTB.ibd | head -n 20

In this section, you learned how to configure MySQL to provide encryption at rest to ensure physical data protection. Next up, I show you how to set up the encryption-in-transit configuration for a MySQL client and server to secure data on the network.

Encryption in Transit

Data in transit or in motion is exposed to potential risk if sniffed or intercepted by a man in the middle (MITM) attack. Encryption provides effective measures against MITM to secure unprotected, in-transit data. MySQL and MariaDB databases provide encrypted communication between client and server with the SSL/TLS protocol. In this section, I walk you through the MySQL configuration of SSL to ensure secure communication between the client and server.

MySQL version 5.7.28 and above provides the handy mysql_ssl_rsa_setup tool that automatically creates required files to set up the default encrypted communication. To begin, you should check either the default SSL status connection on the MySQL server instance or the value of the Ssl_cipher variable for the current session (Listing 2). The output indicates an unencrypted connection.

mysql> show global variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | DISABLED        |
| have_ssl      | DISABLED        |
| ssl_ca        |                 |
| ssl_capath    |                 |
| ssl_cert      |                 |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       |                 |
+---------------+-----------------+
9 rows in set (0.53 sec)
 **
mysql> show session status like 'Ssl_cipher';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| Ssl_cipher    |                 |
+---------------+-----------------+
1 row in set (0.50 sec)

Intercepting the Plaintext Table

To create a client that can use its credentials to log in remotely to the MySQL server and access tables, enter:

$ mysql -u user -p -h <SSLsrvr_IPaddress>

In the meantime, initiate a tshark session at your server or client to sniff the plaintext data:

$ tshark -i any > mysql_plaintext.pcap

Hitting Ctrl+C stops the capture process and opens the mysql_plaintext.pcap file in Wireshark to retrieve the plaintext (Figure 1).

Configuring the MySQL Server

To enable SSL support, you need to create the required SSL files and keys manually. MySQL requires private keys and X509 certificates signed by a certificate authority (CA) to ensure secure encrypted communication. Similarly, the MySQL server needs private keys and X509 certificates from every client that wants to connect to the server over SSL. Table 1 lists the files you need to prepare manually.

Creating SSL Files with OpenSSL

The OpenSSL command-line tool will help you prepare and generate the required SSL files. This handy tool uses the OpenSSL library to perform various tasks, like generating X509 request certificates, providing private keys, verifying and signing X509 certificates, and so on.

Before generating SSL files, create a directory in which to place keys and certificates for encryption in transit:

$ mkdir /var/lib/mysql/new_certs && cd /var/lib/mysql/new_certs

Next, generate the RSA 2048-bit private key to create the CA X509 certificate that signs the server and client X509 certificates:

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3500 -key ca-key.pem -out ca.pem

The openssl command generates the server's private key and certificate signing request. Once obtained, you need to remove the passphrase and sign server-req.pem with the CA key and certificate to obtain the final X509 certificate for the server:

$ openssl genrsa 2048 > server-key.pem
$ openssl req -new -key server-key.pem -out server-req.pem
$ openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

MySQL configuration for SSL only requires server-key.pem, server-cert.pem, and the CA certificate.

Similarly, you need to generate the private key (cert-key.pem) and a self-signed X509 certificate for the MySQL client:

$ openssl genrsa 2048 > client-key.pem
$ openssl req -new -key client-key.pem -out client-req.pem
$ openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

The openssl verify command lets you verify that OpenSSL has generated the correct certificates:

$ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

The OK value indicates that the X509 certificate was signed correctly.

Setting System Variables in MySQL Server

To enable MySQL for encrypted communication, MySQL requires the ssl_ca, ssl_cert, and ssl_key system variables, which specify the paths to SSL files that permit clients to connect through an encrypted connection.

Next, you need to edit the mysqld.cnf configuration file located in the /etc/mysql/mysql.conf.d directory for the new certificates and enable mandatory encrypted connection requirements for the client with the require_secure_transport [1] system variable:

$ vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
ssl_ca= /var/lib/mysql/new_certs/ca.pem
ssl_cert=/var/lib/mysql/new_certs/ server-cert.pem
ssl_key=/var/lib/mysql/new_certs/server-key.pem
require_secure_transport=ON

Now, change the ownership and permissions to SSL files and restart the database service:

$ chown -R mysql:mysql /var/lib/mysql/new_certs/
$ chmod 600 client-key.pem server-key.pem ca-key.pem
$ sudo service mysql restart

Once backed up, log in to the server and check the SSL status by typing \s or by checking the value of the have_ssl variable (Listing 3).

mysql> show global variables like '%ssl%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | \etc\mysql\new_certs\ca.pem          |
| ssl_capath    |                                      |
| ssl_cert      | \etc\mysql\new_certs\server-cert.pem |
| ssl_cipher    |                                      |
| ssl_crl       |                                      |
| ssl_crlpath   |                                      |
| ssl_key       | \etc\mysql\new_certs\server-key.pem  |
+---------------+--------------------------------------+
9 rows in set (0.53 sec)

The configuration ensures an SSL connection and disables MySQL client access to the server with the --ssl-mode=DISABLED string. The client will receive the error

$ mysql -u user -p -h <MySQL_IPaddress>
ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES)

if access to the server is attempted.

Client-Side Encryption in Transit

To secure this connection further, copy the CA and client files to the client and modify the user to require a trusted certificate. To begin, create a directory in which to save client files and use scp or some other utility to transfer ca.pem, client-key.pem, and client-cert.pem files to the client machine:

$ mkdir ~/certs
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/ca-cert.pem ~/certs/
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-cert.pem ~/certs/
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-key.pem ~/certs/

If the server is not configured with the require_secure_transport system variable and the user account is created with no REQUIRE clause or the account has no specific encryption requirements, as above, connection attempts fall to an unencrypted connection. To alter the user to add the REQUIRE X509 clause, enter:

mysql> alter user 'user'@'client_ip' require X509;
mysql> flush privileges;

From now onward, every remote connection from client user will require that -ssl-key and -ssl-cert options be specified, whereas adding the --ssl-ca variable is optional. These variables contain paths to the client's SSL files under the ~/certs directory:

$ mysql -u user -p -h <SSLServer_IPAddress>-ssl-ca= ~/certs/ca.pem -ssl-cert=~/certs/client-cert.pem -ssl-key=under ~/certs/client-key.pem

After hitting Enter, the client will establish a secure SSL connection. In the meantime, start the tshark sniffer to confirm encryption in transit. You will observe an encrypted communication.

Low-Privilege Users

MySQL offers account management statements to set up user accounts and control associated account privileges. The authorization system grants privileges that differ in context and are applied at varying levels of operations. However, it's a good practice not to assign unnecessary privileges to the account users and exercise caution by enabling limited resource access. In this section, I discuss security precautions for providing only enough access required for the job.

Least privileged user accounts reduce the risk of an attacker's access to critical systems. MySQL assigns administrative, database, and specific database object-relevant privileges to users.

The most common recommended privileges are SELECT, UPDATE, DELETE, and INSERT. However, if a user only needs to add information to the database, only the INSERT privilege is required to add records into it. To assign insert permission to the user, enter:

mysql> grant insert on database.* to 'user'@'localhost';

Similarly, the lowest level privilege for access only allows the user to read, edit, or delete a column. For column-level reading privileges, enter:

mysql> grant select(coulmn_name) on database.Clients to 'user'@'localhost';
mysql> flush privileges;

Find out more about the MySQL account authorization system from the official documentation [2].

Privilege Guidelines

MySQL offers certain privilege statements, which, if assigned unnecessarily, can potentially risk subverting the privilege system or reading and writing files accessed by the server host. The following are some potentially risky user privileges that can significantly affect server security:

• GRANT OPTION: Revokes or grants certain privileges from other users that the user itself poses. However, users can use the WITH clause to combine their assigned privileges.
• ALTER: Allows non-administrative users to undermine the authorization system by renaming tables.
• SHUTDOWN: Allows the use of mysqladmin shutdown to terminate the server and restrict server services to the users.
• SUPER: Controls server behaviors and operations and lets a client kill other account threads and modify the server configuration.
• PROCESS: Lets the user see process threads of other account users in plaintext and gives access to InnoDB INFORMATION_SCHEMA FILES tables.
• FILES: Allows user to read, write, and create files on the server host and is a global privilege that allows writing to the server data directory files that implement privilege tables.

Most importantly, grants for MySQL systems that allow access to the authentication_string column of the mysql.user table can enable changing the password and connecting to the server through that account.

You can find more privilege-granting guidelines for specific clauses from the official MySQL documentation [3].

Setting Resource Limits

Another way to set low-privilege user accounts that enhances MySQL security is to set up resource limits. MySQL offers a global system variable max_user_connections that allows setting a limit of simultaneous connections by given accounts. However, it does not place any limits on what happens once the user connects. Hence, MySQL offers an individual account management system by setting per-hour resource limits in the mysql.user table for:

• max_queries_per_hour to store queries made by the user in the max_questions column,
• max_updates_per_hour to store updates issued by an account in the max_updates column, and
• max_connections_per_hour to store number of user connects with the server in the max_connections column.

To establish these limits at account creation time, use create user or check assigned resources for an already established account with select user (Listing 4, which indicates that no limits have been set on resource access) before altering existing account limits with alter user and setting a limit for per-hour queries generated by the user:

mysql> create user 'user'@'localhost' identified by 'password'
    - > with max_queries_per_hour 15
    - > max_updates_per_hour 12
    - > max_connections_per_hour 4
    - > max_user_connections 3;
mysql> alter user 'user'@'localhost' with max_queries_per_hour 25;

mysql> select user, max_questions, max_updates, max_connections, max_user_connections from mysql.user where user='user_name';
+-----------+---------------+-------------+-----------------+---------------------+
| User      | max_questions | max_updates | max_connections | max_user_connections|
+-----------+---------------+-------------+-----------------+---------------------+
| user_name | 0             | 0           | 0               | 0                   |
+-----------+---------------+-------------+-----------------+---------------------+
1 row in set (0.001 sec)

Now, recheck the mysql.user table to confirm the settings.

Disabling Dangerous Functions

Another mandatory security measure to avoid local file SQL injection is to disable functions offered through FILE privilege to low-level users. This grant enables users with global commands like load_file, outfile, and dumpfile to read or make changes to the filesystem accessed through the server.

However, if an attacker does get access to the database through an application layer vulnerability (e.g., SQL injection), disabling the function will prevent the attacker read/write privileges on local files on the system.

load_file

The load_file function lets a user load all the data from a file accessed through the server. For instance, a user with the FILE privilege can load all the file content with the command,

mysql> select load_file('/etc/passwd');

whereas a user without a FILE grant will receive the output:

mysql> select load_file('/etc/passwd');
+--------------------------+
| load_file('/etc/passwd') |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.000 sec)

outfile

The outfile function allows the user to overwrite all the files accessed through the server:

mysql> select 'Hello2' into outfile '/tmp/hello.txt';
$ cat /tmp/hello.txt
Hello2

However, the following error is received by non-privileged users:

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

dumpfile

The dumpfile function is a select clause that writes to the file without separators in an unformatted row. However, the resulting output does not return to the client.

$ cat /tmp/world
Hello world!
mysql> select 'Hello world!' into dumpfile '/tmp/world';
Query OK, 1 row affected (0.001 sec)

To read/write to the system files, disable these functions by revoking the client FILE privileges:

mysql> revoke FILE on *.* from 'user'@'localhost';

Once revoked, the function's command will generate an error or NULL output.

No Root Privileges

MySQL must never be run as a root user. This precaution isn't related to the MySQL root user. Running MySQL as a root user enables any account with the FILE privilege to modify and create server files as root.

Accessing MySQL as a root user generally returns an error. However, this restriction can be overridden by starting MySQL with the -user=root option. The ideal practice is to access MySQL as a separate Unix user by editing the MySQL configuration file:

$ vim /etc/mysql/my.cnf
user=mysql
$ sudo service mysql restart

Disable Remote Login, Particularly Root (Optional)

Remote root logins can expose MySQL databases to high risk. To disable remote root access, sign in to the server and run the commands:

mysql> delete from mysql.user where user='root' and host not in ('localhost', '127.0.0.1', '::1');
mysql> flush privileges;

Similarly, disallow all remote logins if not required by adding a skip-networking system variable to MySQL configuration files:

[mysqld]
port=XXXX
skip-networking
sudo service mysql restart

Enable Explicit Deny (Optional)

MySQL identifies any client connection by username, host value, and password. A host value allows the wildcard character % in the hostname or IP address, such that hostname % can enable connection from any client with a similar username over the Internet.

Similarly, the IP wildcard value 192.168.100.% allows connection from anyone on the subnet that can be easily exploited by naming the host 198.168.100.example.com. Hence, it is recommended to specify the host value as an IP address with a netmask that identifies bits to use for the network address:

mysql> create user 'user'@'192.168.100.0/255.255.255.0';

This host value enables the user to connect from any IP address within a user_ip ranging from 192.168.100.0 to 192.168.100.255, such that the following condition holds:

user_ip && netmask=host_ip

Change Default Port (Optional)

By default, the MySQL service runs on TCP port 3306. To check that port in the system, enter:

$ netstat -tanp | grep 3306

Attackers and IoT search engines normally scan the default port ranges and index them in their database. As a security precaution, open the MySQL configuration file located in the /etc/mysql folder to change the default port:

$ vim /etc/mysql/mysql.conf.d/mysql.cnf
#change port
port=XXXX
#restart service
$ sudo service mysql restart

Conclusion

In this article, I discussed advanced security tips for MySQL server protection. You can refer to the official MySQL documentation [4] for more information.