8.3.1 Configuring MySQL to Use Encrypted Connections (original) (raw)

8.3.1 Configuring MySQL to Use Encrypted Connections

Several configuration parameters are available to indicate whether to use encrypted connections, and to specify the appropriate certificate and key files. This section provides general guidance about configuring the server and clients for encrypted connections:

Encrypted connections also can be used in other contexts, as discussed in these additional sections:

Instructions for creating any required certificate and key files are available in Section 8.3.3, “Creating SSL and RSA Certificates and Keys”.

Server-Side Startup Configuration for Encrypted Connections

To require that clients connect using encrypted connections, enable therequire_secure_transport system variable. See Configuring Encrypted Connections as Mandatory.

These system variables on the server side specify the certificate and key files the server uses when permitting clients to establish encrypted connections:

For example, to enable the server for encrypted connections, start it with these lines in the my.cnf file, changing the file names as necessary:

[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem

To specify in addition that clients are required to use encrypted connections, enable therequire_secure_transport system variable:

[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
require_secure_transport=ON

Each certificate and key system variable names a file in PEM format. Should you need to create the required certificate and key files, see Section 8.3.3, “Creating SSL and RSA Certificates and Keys”. MySQL servers compiled using OpenSSL can generate missing certificate and key files automatically at startup. SeeSection 8.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”. Alternatively, if you have a MySQL source distribution, you can test your setup using the demonstration certificate and key files in its mysql-test/std_data directory.

The server performs certificate and key file autodiscovery. If no explicit encrypted-connection options are given to configure encrypted connections, the server attempts to enable encrypted-connection support automatically at startup:

If the server automatically enables encrypted connection support, it writes a note to the error log. If the server discovers that the CA certificate is self-signed, it writes a warning to the error log. (The certificate is self-signed if created automatically by the server.)

MySQL also provides these system variables for server-side encrypted-connection control:

If the server cannot create a valid TLS context from the system variables for server-side encrypted-connection control, the server executes without support for encrypted connections.

Server-Side Runtime Configuration and Monitoring for Encrypted Connections

The tls_ _`xxx`_ andssl_ _`xxx`_ system variables are dynamic and can be set at runtime, not just at startup. If changed withSET GLOBAL, the new values apply only until server restart. If changed withSET PERSIST, the new values also carry over to subsequent server restarts. See Section 15.7.6.1, “SET Syntax for Variable Assignment”. However, runtime changes to these variables do not immediately affect the TLS context for new connections, as explained later in this section.

Along with the change that enables runtime changes to the TLS context-related system variables, the server enables runtime updates to the actual TLS context used for new connections. This capability may be useful, for example, to avoid restarting a MySQL server that has been running so long that its SSL certificate has expired.

To create the initial TLS context, the server uses the values that the context-related system variables have at startup. To expose the context values, the server also initializes a set of corresponding status variables. The following table shows the system variables that define the TLS context and the corresponding status variables that expose the currently active context values.

Table 8.12 System and Status Variables for Server Main Connection Interface TLS Context

Those active TLS context values are also exposed as properties in the Performance Schematls_channel_status table, along with the properties for any other active TLS contexts.

To reconfigure the TLS context at runtime, use this procedure:

  1. Set each TLS context-related system variable that should be changed to its new value.
  2. Execute ALTER INSTANCE RELOAD TLS. This statement reconfigures the active TLS context from the current values of the TLS context-related system variables. It also sets the context-related status variables to reflect the new active context values. The statement requires theCONNECTION_ADMIN privilege.
  3. New connections established after execution ofALTER INSTANCE RELOAD TLS use the new TLS context. Existing connections remain unaffected. If existing connections should be terminated, use theKILL statement.

The members of each pair of system and status variables may have different values temporarily due to the way the reconfiguration procedure works:

In some cases, ALTER INSTANCE RELOAD TLS by itself may suffice to reconfigure the TLS context, without changing any system variables. Suppose that the certificate in the file named byssl_cert has expired. It is sufficient to replace the existing file contents with a nonexpired certificate and execute ALTER INSTANCE RELOAD TLS to cause the new file contents to be read and used for new connections.

The server implements independent connection-encryption configuration for the administrative connection interface. SeeAdministrative Interface Support for Encrypted Connections. In addition, ALTER INSTANCE RELOAD TLS is extended with a FOR CHANNEL clause that enables specifying the channel (interface) for which to reload the TLS context. See Section 15.1.5, “ALTER INSTANCE Statement”. There are no status variables to expose the administrative interface TLS context, but the Performance Schematls_channel_status table exposes TLS properties for both the main and administrative interfaces. SeeSection 29.12.22.9, “The tls_channel_status Table”.

Updating the main interface TLS context has these effects:

By default, the RELOAD TLS action rolls back with an error and has no effect if the configuration values do not permit creation of the new TLS context. The previous context values continue to be used for new connections. If the optionalNO ROLLBACK ON ERROR clause is given and the new context cannot be created, rollback does not occur. Instead, a warning is generated and encryption is disabled for new connections on the interface to which the statement applies.

Options that enable or disable encrypted connections on a connection interface have an effect only at startup. For example, the --tls-version and--admin-tls-version options affect only at startup whether the main and administrative interfaces support those TLS versions. Such options are ignored and have no effect on the operation ofALTER INSTANCE RELOAD TLS at runtime. For example, you can settls_version='' to start the server with encrypted connections disabled on the main interface, then reconfigure TLS and executeALTER INSTANCE RELOAD TLS to enable encrypted connections at runtime.

Client-Side Configuration for Encrypted Connections

For a complete list of client options related to establishment of encrypted connections, seeCommand Options for Encrypted Connections.

By default, MySQL client programs attempt to establish an encrypted connection if the server supports encrypted connections, with further control available through the--ssl-mode option:

Important

The default setting,--ssl-mode=PREFERRED, produces an encrypted connection if the other default settings are unchanged. However, to help prevent sophisticated man-in-the-middle attacks, it is important for the client to verify the server’s identity. The settings--ssl-mode=VERIFY_CA and--ssl-mode=VERIFY_IDENTITY are a better choice than the default setting to help prevent this type of attack. VERIFY_CA makes the client check that the server’s certificate is valid.VERIFY_IDENTITY makes the client check that the server’s certificate is valid, and also makes the client check that the host name the client is using matches the identity in the server’s certificate. To implement one of these settings, you must first ensure that the CA certificate for the server is reliably available to all the clients that use it in your environment, otherwise availability issues will result. For this reason, they are not the default setting.

Attempts to establish an unencrypted connection fail if therequire_secure_transport system variable is enabled on the server side to cause the server to require encrypted connections. SeeConfiguring Encrypted Connections as Mandatory.

The following options on the client side identify the certificate and key files clients use when establishing encrypted connections to the server. They are similar to thessl_ca,ssl_cert, andssl_key system variables used on the server side, but--ssl-cert and--ssl-key identify the client public and private key:

For additional security relative to that provided by the default encryption, clients can supply a CA certificate matching the one used by the server and enable host name identity verification. In this way, the server and client place their trust in the same CA certificate and the client verifies that the host to which it connected is the one intended:

MySQL also provides these options for client-side encrypted-connection control:

Depending on the encryption requirements of the MySQL account used by a client, the client may be required to specify certain options to connect using encryption to the MySQL server.

Suppose that you want to connect using an account that has no special encryption requirements or that was created using aCREATE USER statement that included the REQUIRE SSL clause. Assuming that the server supports encrypted connections, a client can connect using encryption with no--ssl-mode option or with an explicit --ssl-mode=PREFERRED option:

mysql

Or:

mysql --ssl-mode=PREFERRED

For an account created with a REQUIRE SSL clause, the connection attempt fails if an encrypted connection cannot be established. For an account with no special encryption requirements, the attempt falls back to an unencrypted connection if an encrypted connection cannot be established. To prevent fallback and fail if an encrypted connection cannot be obtained, connect like this:

mysql --ssl-mode=REQUIRED

If the account has more stringent security requirements, other options must be specified to establish an encrypted connection:

mysql --ssl-ca=ca.pem  
      --ssl-cert=client-cert.pem  
      --ssl-key=client-key.pem

For additional information about the REQUIRE clause, see Section 15.7.1.3, “CREATE USER Statement”.

MySQL servers can generate client certificate and key files that clients can use to connect to MySQL server instances. SeeSection 8.3.3, “Creating SSL and RSA Certificates and Keys”.

Important

If a client connecting to a MySQL server instance uses an SSL certificate with the extendedKeyUsage extension (an X.509 v3 extension), the extended key usage must include client authentication (clientAuth). If the SSL certificate is only specified for server authentication (serverAuth) and other non-client certificate purposes, certificate verification fails and the client connection to the MySQL server instance fails. There is no extendedKeyUsage extension in SSL certificates generated by MySQL Server (as described inSection 8.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”), and SSL certificates created using theopenssl command following the instructions in Section 8.3.3.2, “Creating SSL Certificates and Keys Using openssl”. If you use your own client certificate created in another way, ensure any extendedKeyUsage extension includes client authentication.

To prevent use of encryption and override other--ssl-_`xxx`_ options, invoke the client program with--ssl-mode=DISABLED:

mysql --ssl-mode=DISABLED

To determine whether the current connection with the server uses encryption, check the session value of theSsl_cipher status variable. If the value is empty, the connection is not encrypted. Otherwise, the connection is encrypted and the value indicates the encryption cipher. For example:

mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+

For the mysql client, an alternative is to use the STATUS or \s command and check the SSL line:

mysql> \s
...
SSL: Not in use
...

Or:

mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
...

Configuring Certificate Validation Enforcement

The--tls-certificates-enforced-validation option enables validation of the server public key certificate file, Certificate Authority (CA) certificate files, and certificate revocation-list files at server startup:

mysqld --tls-certificates-enforced-validation

If set to ON, the server stops execution of the startup in case of invalid certificates. The server informs DBAs by providing valid debug messages, error messages, or both depending on the status of the certificates. This capability may be useful, for example, to avoid restarting a MySQL server that has been running so long that its SSL certificate has expired.

Similarly, when you execute the ALTER INSTANCE RELOAD TLS statement to change the TLS context at runtime, the new server and CA certificate files are not used if validation fails. The server continues to use the old certificates in this case. For more information about changing the TLS context dynamically, seeServer-Side Runtime Configuration and Monitoring for Encrypted Connections.

Validating CA Certificates

For a connection using the server main interface:

For a connection using the server administrative interface:

Validating the Server Certificate

For a connection using the server main interface:

For a connection using the server administrative interface:

Configuring Encrypted Connections as Mandatory

For some MySQL deployments it may be not only desirable but mandatory to use encrypted connections (for example, to satisfy regulatory requirements). This section discusses configuration settings that enable you to do this. These levels of control are available:

To require that clients connect using encrypted connections, enable therequire_secure_transport system variable. For example, put these lines in the servermy.cnf file:

[mysqld]
require_secure_transport=ON

Alternatively, to set and persist the value at runtime, use this statement:

SET PERSIST require_secure_transport=ON;

SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

With require_secure_transport enabled, client connections to the server are required to use some form of secure transport, and the server permits only TCP/IP connections that use SSL, or connections that use a socket file (on Unix) or shared memory (on Windows). The server rejects nonsecure connection attempts, which fail with anER_SECURE_TRANSPORT_REQUIRED error.

To invoke a client program such that it requires an encrypted connection whether or not the server requires encryption, use an--ssl-mode option value ofREQUIRED, VERIFY_CA, orVERIFY_IDENTITY. For example:

mysql --ssl-mode=REQUIRED
mysqldump --ssl-mode=VERIFY_CA
mysqladmin --ssl-mode=VERIFY_IDENTITY

To configure a MySQL account to be usable only over encrypted connections, include a REQUIRE clause in theCREATE USER statement that creates the account, specifying in that clause the encryption characteristics you require. For example, to require an encrypted connection and the use of a valid X.509 certificate, use REQUIRE X509:

CREATE USER 'jeffrey'@'localhost' REQUIRE X509;

For additional information about the REQUIRE clause, see Section 15.7.1.3, “CREATE USER Statement”.

To modify existing accounts that have no encryption requirements, use the ALTER USER statement.