Configuring SSL/TLS on MySQL. Using an encrypted connection. Part 1

Colleagues hello to all.

In today's article, we'll talk about how to make sure that our connection to the Mysql database is reliable. With an unencrypted connection between the client and the server, there is the danger that someone will be able to monitor all your traffic and check the data , sent or received between a client and a server. Mysql supports encrypted connections between clients and a server using the Transport Layer Security (TLS) protocol. TLS includes algorithms that provide authentication using the X.509 standard.

Article content:

1. Check that SSL is enabled.
2. Check supported TLS protocols.
3. SSL certificate by default.
4. Key descriptions.
5. Force users to connect over an encrypted channel.
6. SSL certificate authorization.
7. How do I know that I'm connected via an encrypted channel.
8. Summary

1. Checking that SSL is enabled.

First of all we need to check if SSL is enabled on the database. To check, use the command:

mysql> SHOW VARIABLES LIKE '%have_%';

Variables that are responsible for enabling ssl on the database are called:

• have_openssl
• have_ssl

If the value is YES, it means enabled. By default, when installing the DBMS, SSL is already enabled.

 2. Checking supported TLS protocols.

Mysql can support many encryption protocols. For example: TLSv1,TLSv1.1,TLSv1.2,TLSv1.3. The version of the encryption protocol will depend on which version of the openssl package is installed on the system. The  TLSv1 and TLSv1.1 protocol versions are considered obsolete and unreliable and will be removed in the latest database versions. In order to support modern encryption protocols, you must have the  openssl package installed at least version openssl 1.1.1.

To check which versions of the encryption protocol currently supported by my DBMS, use the command:

mysql> SHOW VARIABLES LIKE 'tls_version';

As you can see, my DBMS version 8.0.29 supports encryption protocols TLSv1.2,TLSv1.3

You can explicitly tell the DBMS which version of encryption protocol to use, for this you need to add the following parameter to the configuration file:

[mysqld]
tls_version=TLSv1.2,TLSv1.3

3. SSL certificate by default.

When you just install the DBMS on the server, the DBMS itself will independently create the first certificate and put it in the directory where you have all the database files (datadir).

You can also check the name of your keys through the database console.

mysql> SHOW VARIABLES LIKE '%ssl%';

4. Key descriptions.

• ssl_ca - Path to the CA certificate file.
• ssl_cert - Path to the public key certificate file.
• ssl_key - Path to private key file.

By the way, the public key can be transferred to someone so that a person has the opportunity to connect from client applications to the database.

5. Force users to connect over an encrypted channel.

I'm 100% sure that none of you knew that when connecting to a database first, the DBMS tries to connect to the client via an encrypted channel, and if it fails to connect, then it is already connected via a normal channel without encryption. So in order to force clients to always connect over an encrypted channel, there is a system variable called  require_secure_transport. Let's add it to the configuration file.

[mysqld]
require_secure_transport = ON

Now you need to create an account that will only be able to connect over an encrypted connection.

mysql> CREATE USER 'test_ssl'@'localhost' IDENTIFIED BY 'Jkjd#8sDF34' REQUIRE X509;

The REQUIRE X509 parameter specifies that require encrypted connection and use of a valid X.509 certificate when authorizing into the database.

And now when the user simply tries to enter the database, he will receive an error ERROR 1045 (28000).

Access denied for user 'test_ssl'@'localhost' (using password: YES)

6. Authorization by SSL certificate.

Whenever we connect to a database, we use the mysql -u username -p command, after we enter the account password and that's it, now we need to specify more parameters.

$. mysql -u test_ssl -p --ssl-ca=/app/mysql/data/ca.pem --ssl-cert=/app/mysql/data/server-cert.pem --ssl-key=/app/mysql /data/server-key.pem

See section 4 for key descriptions.

You can do without these parameters when connecting to the database. To do this, we need to specify all the parameters for encryption keys in the configuration file.

[mysqld]
ssl_ca=/app/mysql/data/ca.pem
ssl_cert=/app/mysql/data/server-cert.pem
ssl_key=/app/mysql/data/server-key.pem
require_secure_transport=ON

[client]
ssl_ca=/app/mysql/data/ca.pem
ssl_cert=/app/mysql/data/server-cert.pem
ssl_key=/app/mysql/data/server-key.pem

7. How do I know that I'm connected via an encrypted channel.

You have two options to verify that you really connected via an encrypted channel.

The first one is when you just connected to the database and you get a welcome message.

The second option is to execute the request in the same session:

mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher_list'\G;

or

mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';

If the value of the variable is not empty, it means that you connected via an encrypted channel.

8. Results.

As a result of our colleagues, today we have considered how to set up a secure connection to a database based on SSL certificates. In the next article on this topic, I will tell you how to create new certificates and how to find out when the certificate expires.

Thank you all, I hope that my article helped you in some way.