One-way SSL Configuration Steps

With the increasing need for security in data transfer, MySQL provides an option for enabling SSL/TLS encryption to secure communication between the client and the server. SSL (Secure Sockets Layer) and its successor TLS (Transport Layer Security) use a combination of symmetric and asymmetric encryption algorithms to protect the data from unauthorized access and tampering.

There are two methods for SSL connections: one-way and two-way. One-way SSL connection, also known as server-side SSL authentication, allows the server to authenticate itself to the client. The client verifies the server’s identity in this connection by checking its SSL certificate. Once the server’s identity is verified, the client and server can exchange data over a secure channel.

On the other hand, a two-way SSL connection, also known as mutual SSL authentication, requires both the client and the server to authenticate themselves to each other. Therefore, in addition to the server’s SSL certificate, the client must present its own SSL certificate to the server. This provides an extra layer of security, as both parties can verify each other’s identities before exchanging data.

Overall, one-way SSL is suitable for most applications where only the server needs to be authenticated. To configure one-way SSL connections on a Linux server, you can follow the steps listed below.

1. Generate SSL Certificates (If Not Running MySQL 5.7 or Later)

Check to see if the certificates have already been generated. Use this command on the command line to find the locations of certificates:

$ find /var/lib/mysql -name '*.pem' -ls

Prior to MySQL 5.7, you needed to generate SSL certificates for the MySQL server. This can be done using a tool like OpenSSL. You will need to create a private key and a public key (certificate). You can invoke openssl using the MySQL utility mysql_ssl_rsa_setup or using the commands below:

$ openssl genrsa 1024 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -config myssl.cnf > ca-cert.pem
$ openssl req -newkey rsa:1024 -days 1000 -nodes -keyout server-key.pem -config myssl.cnf > server-req.pem
$ openssl x09 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
$ openssl req -newkey rsa:1024 -days 1000 -nodes -keyout client-key.pem -config myssl.cnf > client-req.pem
$ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 client-cert.pem

After creation, move the certificates into the default location, /var/lib/mysql, to avoid restarting mysqld. If not, add the following lines to the [mysqld] in the mysqld.cnf:

[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

Here, ssl-ca is the path to the Certificate Authority (CA) certificate, ssl-cert is the path to the server certificate, and ssl-key is the path to the server private key.

2. Enable SSL on MySQL Server

The following system variable, require_secure_transport, needs to be enabled. I recommend enabling it dynamically and adding the system variable to the my.cnf or mysqld.cnf file. Again, so you don’t have to restart the MySQL server.

Set require_secure_transport dynamically.

mysql> set global require_secure_transport = ON;

Add this line to the [mysqld] section in the mysqld.cnf or my.cnf file.

[mysqld]
require_secure_transport = ON

3. Grant Privileges for SSL Connections:

You need to grant privileges to users who want to connect to the MySQL server using SSL. You can use the GRANT command to do this:

mysql> GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

Here, username is the username of the user you want to grant privileges to.

If you already have users created with the ‘%’ hostname (and a lot of users to alter), you can update all users to require SSL at the same time with the following command executed on the command line:

mysql> UPDATE mysql.user SET ssl_type = 'ANY';

This will set the ssl_type field to ‘ANY’ for all rows in the mysql.user table. Note that you need to have the appropriate privileges to perform this operation, such as the UPDATE privilege for the mysql database.

4. Connect to MySQL Server Using SSL:

Next, specify the SSL parameters when connecting from the remote server. You can use the following command to connect to the MySQL server using SSL:

$ mysql -h hostname -u username -p

Here, you need to specify the hostname of the host server, username, and password to connect to the MySQL server.

Once you have completed these steps, confirm that you are using SSL to connect to the MySQL server. Log into MySQL and run the following queries:

mysql> \s

Expected Output:
mysql  Ver 8.0.32-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:          9
Current database:
Current user:           xtivia@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384

mysql> SELECT * FROM performance_schema.session_status WHERE VARIABLE_NAME IN ('Ssl_version','Ssl_cipher');

Expected Output:
+---------------+------------------------+
| VARIABLE_NAME | VARIABLE_VALUE         |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |  
| Ssl_version   | TLSv1.3                |  
+---------------+------------------------+

If you see that the cipher is being used, then you know your connection is encrypted.

Additional tests can be performed to ensure that users cannot connect when ssl-mode is disabled. The following command can be executed on the command line.

$ mysql -h hostname -u username -ssl-mode=disabled -p

Expected Error:

ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

Running into issues? XTIVIA can help set up SSL connections. Contact us today!