MariaDB supports the Secure Socket Layer (SSL) connections. In order to use SSL, MariaDB must be compiled with yaSSL or OpenSSL. Binary packages are built with yaSSL. To check whether our local installation has SSL support, we can look at the have_ssl server variable. If its value is YES, SSL is supported and configured; if its value is DISABLED, SSL is supported but not yet configured; and if its value is NO, SSL is not supported. For example:

MariaDB [(none)]> SELECT @@global.have_ssl;

+-------------------+

| @@global.have_ssl |

+-------------------+

| DISABLED          |

+-------------------+

1 row in set (0.00 sec)

To configure SSL, we first need to create a certificate (released by a Certification Authority also known as CA), and public and private keys for both the server and clients that need to use SSL. The certificate and keys can be generated with the OpenSSL program, which is a free software. It is usually already installed on Unix systems and can be downloaded and installed on Windows.

A suggestion about the key length: a 4096 long key is obviously much more secure than a 2048 long key, or smaller keys. Of course, a longer key also causes a bigger overhead in network communications. But tests show that the difference between a 4096 long key and a 1024 long key is mostly noticeable during the connection establishing phase, while during normal operations it is very small. So, the maximum key length is a good security choice and sometimes it does not noticeably affect the performance of the database. The difference will be more relevant for workloads with several short-lived connections. However, keep in mind that the overhead caused by SSL itself often represents a half of the total query execution time, or more.

To verify that the certificate and keys are in place and valid, you can check them with the openssl command:

root@this:/usr/local/mysql# cd /etc/ssl/mysql
root@this:/etc/ssl/mysql# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

Now we need to let MariaDB know where the certificates and keys are. Let's add the following variables to the configuration file in the [mysqld] section:

# SSL
ssl-ca=/etc/ssl/mysql/ca-cert.pem
ssl-cert=/etc/ssl/mysql/server-cert.pem
ssl-key=/etc/ssl/mysql/server-key.pem

We can also use the corresponding server startup options:

mysql --ssl-ca=/etc/ssl/mysql/ca-cert.pem --ssl-cert=/etc/ssl/mysql/server-cert.pem --ssl-key=/etc/ssl/mysql/server-key.pem

We can also add similar variables for the client in the [client] section of the configuration file, or use the corresponding client startup options:

# SSL 
ssl-cert=/etc/mysql-ssl/client-cert.pem 
ssl-key=/etc/mysql-ssl/client-key.pem

This allows the client to use an SSL connection with the server.

We will also require an account to use SSL. If the account tries to connect without encryption, the connection will be rejected even if the username and password it provides are correct. To do this, we can again use the GRANT statement:

MariaDB [(none)]> GRANT USAGE ON *.* TO u1 REQUIRE SSL; 
Query OK, 0 rows affected (0.00 sec) 

This example simply forces the user u1 to connect using an SSL encryption without any further requirements. It is also possible for the user to require a higher security level. One or more options can be used in a single GRANT statement, after the REQUIRE keyword, optionally separated by AND. These options are stated as follows:

The following example shows the usage of the preceding options:

MariaDB [(none)]> GRANT USAGE ON *.* TO u1@localhost;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> GRANT USAGE ON *.* TO 'u1'@'%' REQUIRE ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Norman Bates/[email protected]' AND CIPHER 'RSA-SHA';
Query OK, 0 rows affected (0.01 sec) 

In this example, the user u1 can connect from a localhost without encryption; but if the user wants to connect from anywhere else, he/she must use an SSL connection with a certificate released by the specified authority, using the Remote Secure Access (RSA) authentication or the Secure Hash Algorithm (SHA) hashing.