By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.
To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_
, but internally, MariaDB only supports its secure successors.
In order to secure connections between the server and client, you need to ensure that your server was compiled with TLS support. See Secure Connections Overview to determine how to check whether a server was compiled with TLS support.
You also need an X509 certificate, a private key, and the Certificate Authority (CA) chain to verify the X509 certificate for the server. If you want to use two-way TLS, then you will also an X509 certificate, a private key, and the Certificate Authority (CA) chain to verify the X509 certificate for the client. If you want to use self-signed certificates that are created with OpenSSL, then see Certificate Creation with OpenSSL for information on how to create those.
In order to enable TLS on a MariaDB server that was compiled with TLS support, there are a number of system variables that you need to set, such as:
ssl_cert
system variable. ssl_key
system variable. ssl_ca
or the ssl_capath
system variables. ssl_cipher
system variable. For example, to set these variables for the server, add the system variables to a relevant server option group in an option file:
[mariadb] ... ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem ssl_key = /etc/my.cnf.d/certificates/server-key.pem ssl_ca = /etc/my.cnf.d/certificates/ca.pem
And then restart the server to make the changes persistent.
Once the server is back up, you can check that TLS is enabled by checking the value of the have_ssl
system variable. For example:
SHOW VARIABLES LIKE 'have_ssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+
The FLUSH SSL
command was first added in MariaDB 10.4.
In MariaDB 10.4 and later, the FLUSH SSL
command can be used to dynamically reinitialize the server's TLS context.
See FLUSH SSL
for more information.
Different clients and utilities may use different methods to enable TLS.
For many of the standard clients and utilities that come bundled with MariaDB, you can enable two-way TLS by adding the same options that were set for the server to a relevant client option group in an option file. For example:
[client-mariadb] ... ssl_cert = /etc/my.cnf.d/certificates/client-cert.pem ssl_key = /etc/my.cnf.d/certificates/client-key.pem ssl_ca = /etc/my.cnf.d/certificates/ca.pem
The specific options that you would need to set would depend on whether you want one-way TLS or two-way TLS, and whether you want to verify the server certificate.
The same options may also enable TLS on non-standard clients and utilities that are linked with either libmysqlclient or MariaDB Connector/C.
Two-way TLS means that both the client and server provide a private key and an X509 certificate. It is called "two-way" TLS because both the client and server can be authenticated. For example, to specify these options in a relevant client option group in an option file, you could set the following:
[client-mariadb] ... ssl_cert = /etc/my.cnf.d/certificates/client-cert.pem ssl_key = /etc/my.cnf.d/certificates/client-key.pem ssl_ca = /etc/my.cnf.d/certificates/ca.pem ssl-verify-server-cert
Or if you wanted to specify them on the command-line with the mysql
client, then you could execute something like this:
$ mysql -u myuser -p -h myserver.mydomain.com \ --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \ --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \ --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \ --ssl-verify-server-cert
Two-way SSL is required for an account if the REQUIRE X509
, REQUIRE SUBJECT
, and/or REQUIRE ISSUER
clauses are specified for the account.
One-way TLS means that only the server provides a private key and an X509 certificate. When TLS is used without a client certificate, it is called "one-way" TLS, because only the server can be authenticated, so authentication is only possible in one direction. However, encryption is still possible in both directions. Server certificate verification means that the client verifies that the certificate belongs to the server. For example, to specify these options in a a relevant client option group in an option file, you could set the following:
[client-mariadb] ... ssl_ca = /etc/my.cnf.d/certificates/ca.pem ssl-verify-server-cert
Or if you wanted to specify them on the command-line with the mysql
client, then you could execute something like this:
$ mysql -u myuser -p -h myserver.mydomain.com \ --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \ --ssl-verify-server-cert
One-way TLS means that only the server provides a private key and an X509 certificate. When TLS is used without a client certificate, it is called "one-way" TLS, because only the server can be authenticated, so authentication is only possible in one direction. However, encryption is still possible in both directions. For example, to specify these options in a a relevant client option group in an option file, you could set the following:
[client-mariadb] ... ssl
Or if you wanted to specify them on the command-line with the mysql
client, then you could execute something like this:
$ mysql -u myuser -p -h myserver.mydomain.com \ --ssl
See the documentation on MariaDB Connector/C's TLS Options for information on how to enable TLS for clients that use MariaDB Connector/C.
See the documentation on MariaDB Connector/ODBC's TLS-Related Connection Parameters for information on how to enable TLS for clients that use MariaDB Connector/ODBC.
See the documentation on Using TLS/SSL with MariaDB Connector/J for information on how to enable TLS for clients that use MariaDB Connector/J.
You can verify that a connection is using TLS by checking the connection's Ssl_cipher
status variable. If it is non-empty, then the connection is using TLS. For example:
SHOW SESSION STATUS LIKE 'Ssl_cipher'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | Ssl_cipher | DHE-RSA-AES256-GCM-SHA384 | +---------------+---------------------------+ 1 row in set (0.00 sec)
You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER
, ALTER USER
, or GRANT
statements. For example:
REQUIRE SSL
clause. ALTER USER 'alice'@'%' REQUIRE SSL;
REQUIRE CIPHER
clause. ALTER USER 'alice'@'%' REQUIRE CIPHER 'ECDH-RSA-AES256-SHA384';
REQUIRE X509
clause. ALTER USER 'alice'@'%' REQUIRE X509;
REQUIRE SUBJECT
clause. ALTER USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland';
REQUIRE ISSUER
clause. ALTER USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/[email protected]';
A user account can have different definitions depending on what host the user account is logging in from. Therefore, it is possible to have different TLS requirements for the same username for different hosts. For example:
CREATE USER 'alice'@'localhost' REQUIRE NONE; CREATE USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/[email protected]' AND CIPHER 'ECDHE-ECDSA-AES256-SHA384';
In the above example, the alice
user account does not require TLS when logging in from localhost. However, when the alice
user account logs in from any other host, they must use TLS with the given cipher, and they must provide a valid client certificate with the given subject that must have been signed by the given issuer.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/securing-connections-for-client-and-server/