The Ultimate Guide to High Availability with MariaDB

Securing Connections for Client and Server

You are viewing an old version of this article. View the current version here.

Contents

  1. Configuration
  2. TLS Requirements
    1. Requiring TLS by Resource
    2. Requiring TLS by Account

By default, MariaDB transmits data between the Server and clients unencrypted. While this may be acceptable when the Server and Client are running on the same host, it does pose security risks when they operate on separate machines.

In order to secure connections between the Server and Client, you need to use a server compiled with TLS support. Check the have_ssl system variable to determine whether TLS support is available. You also need to prepare a Certificate Authority and certificate files for both the Server and the Client. If you want to use a self-signed certificate with OpenSSL, see Certificate Creation with OpenSSL.

Configuration

Securing both the Server and the Client requires setting the ssl_ca, ssl_key, and ssl_cert system variables. Setting them implies the --ssl option, which enables TLS support for the Server.

First set these variables for the Server:

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

Then, set these variables for the Client:

[mysql]
ssl-ca = /path/to/ca-cert.pem
ssl-key = /path/to/client-key.pem
ssl-cert = /path/to/client-cert.pem

Restart the server to make the changes persistent. You can check that TLS is now enabled using the have_ssl system variable.

SHOW VARIABLES LIKE 'have_ssl';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+

TLS Requirements

Enabling TLS support on MariaDB with the certificates, keys and Certificate Authority files makes it available, but it requires a few additional steps to use it. MariaDB supports several layers of TLS usage that can be defined either at a resource-level or an account-level.

MariaDB supports six levels of TLS requirements:

- REQUIRE NONE: TLS isn't used. - REQUIRE SSL: Requires that the account use TLS, but does not require a valid X509 certificate. - REQUIRE X509: Requires that the account to use TLS with a valid X509 certificate. - REQUIRE ISSUER: Requires that the account to use TLS with a valid X509 certificate from the given issuer. - REQUIRE SUBJECT: Requires that the account to use TLS with a valid X509 certificate with the given subject. - REQUIRE CIPHER: Requires that the account to use TLS with a valid X509 certificate using the given cipher.

Requiring TLS by Resource

MariaDB does not require symmetric TLS usage at the same level on every table, database and user on the Server. You may find situations where data in certain tables is more sensitive than in others. For instance, you might give a user generic access to a database when they log in from localhost, but when they log in from anywhere else they need a valid X509 certificate to read from a few specific tables that contain sensitive data. Or, you might let them read from the table, but require certificates for writes or other operations.

In order to set TLS requirements for an account on certain resources you need to use a GRANT statement.

GRANT USAGE ON accounts.* TO 'someone'@'localhost' REQUIRE NONE;

GRANT USAGE ON accounts.contacts TO 'someone'@'%'
REQUIRE SUBJECT '/CN=www.mydom.com/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 'SHA-DES-CBC3-EDH-RSA';

The user logging in from localhost has complete access to the accounts database. But, when the user logs in from an untrusted location, they must provide a valid X509 certification with the given subject, issuer and cipher to access the accounts.contacts table.

Requiring TLS by Account

In addition to requiring TLS validation for particular resources, you can also set requirements generally at an account-level. For instance, you might use this with users that require general access to sensitive data who log in from multiple or unpredictable hosts.

You can set account-level TLS requirements in the CREATE USER statement. In more recent releases of MariaDB, you can modify an existing account by adding the requirements through an ALTER USER statement.

ALTER USER 'someone'@'%' REQUIRE X509

This modifies the user to require always a valid X509 certificate. If the user does not have a valid certificate, MariaDB rejects the connection.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.