Guide to MySQL Encryption in Transit and Mandatory Encryption Settings

By default, MySQL data transmission between the client and the server takes place without encryption. Unencrypted data transmission is acceptable only when the client and server are within the same network that ensures security. However, the data is at potential risk if both parties are on a separate network. The lack of encryption introduces a severe risk of data interception by man-in-the-middle (MITM) attack.

To overcome this risk, MySQL supports encryption in transit between the client and the server via TLS/SSL protocol. The article focuses on the manual generation of SSL certificates and keys files in MySQL to configure SSL. Later, the article also focuses on enabling obligatory encryption requirements from clients.

Getting Started

MySQL versions 5.7.28+ provides a handy tool known as mysql_ssl_rsa_setup, which relies on OpenSSL binaries to automatically generate the required SSL certificates and keys to support a secure connection.

Therefore, before beginning, check the default SSL connection status of the MySQL server. Type the following command to verify SSL session value:

The above output shows MySQL does not support encryption in transit for the current session.

Use OpenSSL to Create SSL Certificate and Keys

To provide encryption in transit, MySQL requires client-side and server-side X509 certificates signed by the Certificate authority to validate domain ownership. We will generate self-signed certificates, as well as server and client-side certificates via command-line utility OpenSSL. It’s an OpenSSL library tool that generates private keys, creates X509 certificate requests, signs them as CA, and verifies them.

Before beginning, create a directory to store all files:

The following set of commands will generate several prompts that must have nonempty responses.

Certificate Authority key and Certificate Generation

Creating a self-signed certificate requires a Certificate Authority (CA) certificate via a private key file. Use the OpenSSL command to generate RSA 2048 bits private key for the CA.

Use the above key with an OpenSSL req command to generate a certificate for your own CA with an expiration of 3000 days.

The above commands create new files ca-key.pem and ca.pem to self-sign the X509 certificates of MySQL server and client.

Generate Private Key and Self-Signed Certificate for MySQL Server

Use OpenSSL to generate MySQL server RSA key and Certificate Signing Request (CSR):

Now, remove the passphrase from the server key:

Generate the MySQL server self-signed certificate from the certificate request by using CA private key and certificate.

Now, SSL configuration for MySQL does not require CSR.

Generate Client Key and Self-Signed Certificate

Similarly, generate the key and certificate request for the client.

Remove the passphrase from the key and generate a client certificate from the certificate request using the CA files.

The server will only accept remote connections from the clients with these files.

Lastly, verify the client and server-side certificates against the CA certificate.

The OK value indicates that the certificates were correctly generated and are ready to use.

Configuring MySQL Server

To enable TLS/SSL service for MySQL server requires setting a number of system variables inside MySQL main configuration file mysqld.conf, such as:

• Use ssl_cert and ssl_key to set the path to the server’s certificate and private key.
• Use the ssl_ca variable to set the path to the CA’s certificate on the server-side.

Use your favorite editor to edit the configuration file located inside /etc/mysql/mysql.conf.d directory.

Lastly, change SSL keys and certificate ownership, and permissions.

Restart the database to load recent changes.

Login to the server after restart and check the current MySQL SSL session status.

Client-side Configuration

Establishing a secure remote connection from the client requires the transfer of the above generated client-side OpenSSL certificate files. Create a new directory and use the SCP utility for secure file transfer.

While establishing an encrypted remote connection, the client now requires adding client-side options that verify client-side keys and certificates. The included options are similar to server-side system variables but, the –ssl-key and –ssl-cert options identify paths to the client private key and certificate. Use the –ssl-ca option to add the path to the CA certificate. This file must be the same as the server-side CA certificate.

Use the below command with all required options to establish a secure remote connection with the MySQL database server.

Configure Mandatory Encrypted Connections

For some MySQL servers, it isn’t only required for the client to connect with the server via an encrypted connection, but it is mandatory. MySQL allows the server administrator to configure obligatory encrypted connections. It’s made possible by placing three different levels of control:

• Configure MySQL that requires the client to access the database only via an encrypted connection.
• Invoke client programs to need an encrypted connection, even if MySQL allows but does not necessarily require one.
• Configure specific user accounts to access the database only over an encrypted channel.

Let’s detail each of them:

require_secure_transport

To ensure clients using an encrypted connection, enable the require_secure_transport variable in the MySQL configuration file located in /etc/mysql/mysql.cnf.d directory:

The above system variable ensures the client uses secure transport to connect with the server, and the server only permits TCP connections via SSL. Hence, the server rejects any client connection request without a secure transport and returns an error output of ER_SECURE_TRANSPORT_REQUIRED to the client’s program.

Moreover, the server configuration above also disables remote client connection to the server with a –ssl-mode=DISABLED string.

Invoking Client Program

This control level allows invoking the client program to set up secure encrypted communication, irrespective of the server settings. That is, even if the server is not configured to obligatory establish an SSL/TLS transport, it’s able to maintain a secure connection to the client’s desire.

It is possible by the use of an –ssl-mode option available in MySQL 5.7.11 along with its various values. It is helpful to specify the desired security state of the client connection to the server. The option values are applied based on the increasing level of strictness.

• DISABLED: the value established an insecure connection.
• PREFERRED: the mode is similar to when no such –ssl-mode option is specified. It establishes encryption only if the server supports it otherwise, it falls back to the default unencrypted connection.
• REQUIRED: the value ensures an encrypted communication if the server is enabled to support one. The client fails the connection attempt if MySQL does not support TLS/SSL.
• VERIFY_CA: the value functions similar to REQUIRED, but additionally, it also verifies the server’s CA certificate. The client fails to connect in case of no valid matching certificates.
• VERIFY_IDENTITY: similar to VERIFY_CA, but for an OpenSSL version 1.0.2+, clients can also verify the hostname they use for connecting against the identity in the server certificate. The connection breaks in the case of a mismatch.

However, it is important to note that hostname verification does not work for self-signed certificates. These include automatically generated certificates by the server or manually created via the mysql_ssl_rsa_setup tool.

Apart from the default encryption, MySQL allows the client to include additional security settings by supplying a CA certificate, same as the server, and enabling hostname identity verification. These settings allow both parties to trust a common entity, and the client can verify that it is connecting to the right host.

We now understand how the above mode interacts with the CA certificate options:

• specify the CA certificate with -ssl-ca option with –ssl-mode=VERIFY_CA.
• enable hostname identity verification by using –ssl-mode=VERIFY_IDENTITY
• an –ssl-mode value other than VERIFY_IDENTITY or VERIFY_CA with -ssl-ca will generate a warning stating non-verification of the server certificate.

Configure User Accounts

To enable encrypted communication by the client, configure a specific user account to access the MySQL server over SSL. Create a user account CREATE USER with the REQUIRE clause statement. Or, use the ALTER USER statement to add the REQUIRE clause. This control terminates client connection attempts to the server if it does not support an encrypted connection.

The REQUIRE clause is helpful to execute an encryption-related configuration that enforces strict security requirements. It allows specifying one or more than one tls_option value.

Let’s get into the detail of the required command options from the clients that are configured with various REQUIRE values:

NONE: does not require an SSL connection
SSL: the server only permits encrypted connection from SSL-enabled accounts.
X509: requires the client to present the private key and certificate. This value does not necessitate the need to show the CA certificate, subject, and issuer.

The clause specifies the required encryption characteristics such that there is no need to include the SSL option.

Now, the client needs to specify –ssl-key and –ssl-cert options to connect, while -ssl-ca isn’t necessary (this also applies for the issuer and subject values).

ISSUER: the account created with the REQUIRE ISSUER statement, requires the client to specify –ssl-key and –ssl-cert options with a valid certificate issued by the CA ‘issuer’. Create the user account as follows:

If the certificate is valid with a different issuer, the connection attempt fails.

SUBJECT: require the client to present the certificate with a subject value provided while creating its account. A connection with a valid certificate but a different subject result in connection terminations.

CIPHER: the account created with the statement requires the client to include the cipher method used to encrypt communication. It’s necessary to ensure if ciphers and key lengths are sufficiently strong.

Conclusion

The article illustrates how to secure communication between MySQL server and clients by enabling SSL protocol. We learn to create a manual self-signed certificate as we trust the host in the network. We also apply encryption in transit for MYSQL server communication outside the network and learn ways to configure the server for mandatory encryption requirements.