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:
NONE
: This option states that SSL can be used, but is not requiredSSL
: This option states that an SSL encryption is required without any requirements about its characteristicsX509
: This option states that a valid X509 certificate is requiredISSUER 'str'
: This option states that a valid X509 certificate is required which is released by the specified authoritySUBJECT 'str'
: This option states that a valid X509 certificate is required with the specified subjectCIPHER 'str'
: This option states that a valid X509 certificate is required, and the connection must use one of the specified encryption methodsThe 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.