MySQL 5.7 server binaries compiled with the OpenSSL library now make it easy to set up SSL/TLS and RSA artifacts, and to enable them within MySQL. Two new read-only global options have been introduced through this work:
--auto-generate-certs: Enables automatic generation and detection of SSL artifacts at server start-up.
--sha256-password-auto-generate-rsa-keys: Enables automatic generation of an RSA key pair.
These options govern automatic generation and detection of SSL/TLS artifacts and RSA key pairs respectively. Auto generated files are placed inside the data directory, and both options now default to ON.
For the sha256_password authentication plugin, the private key and public key file locations already default to the data directory and hence, automatic detection of these files was already in place. Due to this existing functionality, the sole function of
--sha256-password-auto-generate-rsa-keys is related to automatic key generation.
Using encrypted connections in communications with the server protects one’s data from the eyes of malicious entities while in transit. This is especially important when the server and clients are connected through open and/or insecure networks. While MySQL does provide a definitive guide to help users set up certificates and keys, one still needs to take the following steps in order to enable SSL/TLS manually within the MySQL server:
- Use the steps provided in the documentation to generate the certificates
- Move these certificates and keys to a secure location
- Update the MySQL server configuration file to specify the location of these certificates
- Start the MySQL server in order to use the new SSL artifacts
The case is similar when it comes to RSA keys. While the documentation helps you in generating an RSA key pair, using the newly generated key still requires steps similar to those mentioned above.
Our aim is to make MySQL secure by default. At the same time, we also want to make sure that it is easy to setup this secure environment with very little user intervention. These new options are a step towards this goal. These new server options default to
ON, and hence in the absence of existing SSL/TLS artifacts and/or an RSA key pair, automatic generation of them will take place resulting in the MySQL server automatically having the capability to create secure connections immediately. This will be convenient for users who wish to create secure connections to the MySQL server without going through the trouble of generating SSL/TLS artifacts and/or RSA key pairs by themselves and then configuring the server to use them.
Note that the purpose of this functionality is to encourage users to use secure methods when connecting to the server by making the initial secure configuration easy. For better security, it is strongly recommended that users later switch to a valid set of certificates signed by a recognized certificate authority as soon as possible, rather than continuing to use the auto generated certificates indefinitely.
Auto-enabling SSL support
--auto-generate-certs kicks in if none of the ssl command line options (except
--ssl of course!) are specified. It works in following manner:
- Step 1: Check whether any of the ssl command line options except
--sslare specified, if so, the server will skip automatic generation and try to use the supplied options.
123...2014-09-23T06:56:07.353216Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified....
- Step 2: Check for existing SSL/TLS artifacts in the data directory. If they exist then the automatic creation process is skipped with a message similar to following:
123...2014-09-23T06:56:45.146238Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory....
Note that we check for the presence of ca.pem, server-cert.pem, and server-key.pem files as these three files are essential for enabling SSL support within the MySQL server.
- Step 3: If the certificate files are not present in the data directory then the new certificate files—ca.pem, server-cert.pem, and server-key.pem—are generated and placed within the data directory.
Upon successful automatic generation of these files, the MySQL server will log a message similar to following:
123...2014-09-23T06:58:03.184170Z 0 [Note] Auto generated SSL certificates are placed in data directory....
From this set of generated files, ca.pem, server-cert.pem, and server-key.pem are used for the
--ssl-ca, --ssl-cert and --ssl-key options respectively. These auto generated files allow SSL/TLS support to be automatically enabled within the MySQL server from the get-go.
mysql> show variables like '%ssl%';
| Variable_name | Value |
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
9 rows in set (0.01 sec)
mysql> show status like 'Ssl_cipher';
| Variable_name | Value |
| Ssl_cipher | DHE-RSA-AES256-SHA |
1 row in set (0.00 sec)
Furthermore, an extra set of X509 certificates and private keys are generated, which can be used as the client certificate and key.
Some of the properties of the automatically generated certificates and keys are:
- The RSA key is 2048 bits.
- The certificates are signed using the sha256 algorithm.
- The certificates are valid for 10 years.
- The subject line of the certificates contain only the common name (CN) field.
- The naming convention for the generated CN is:
Where MySQL_Server_Version is fixed at compile time. TYPE can be one of the CA, Server and Client. e.g. CN=MySQL_Server_X.Y.Z_Auto_Generated_Server_Certificate
- The new CA certificate is self-signed and other certificates are signed by this new auto generated CA certificate and private key.
Auto-enabling RSA support for the sha256_password authentication plugin
Much like auto-enabling SSL/TLS support,
--sha256-password-auto-generate-rsa-keys is responsible for automatic generation of the RSA key pair. When the client tries to connect to the server using the sha256_password authentication plugin, a password is never sent in cleartext. By default, the sha256_password plugin attempts to use an SSL connection. If MySQL is built with OpenSSL, an additional option of using RSA encryption is also available to the client. The MySQL server exposes
--sha256_password_public_key_path, which can be used to point to an RSA private key and public key respectively at server startup.
--sha256-password-auto-generate-rsa-keys option works in following manner:
- Step 1: Check if a non-default value for either
--sha256_password_public_key_pathis used. If so, the server will skip automatic generation and try to obtain keys from the specified location(s).
123...2014-09-23T06:59:22.776254Z 0 [Note] Skipping generation of RSA key pair as options related to RSA keys are specified....
- Step 2: If the default location is used for both of these options, then check if the private_key.pem and public_key.pem files are present in the data directory. If these files are found, then auto generation is skipped.
123...2014-09-23T06:56:45.160448Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory....
- Step 3: Otherwise we generate the private_key.pem and public_key.pem files with a key length of 2048 bits. These files are then placed within the data directory and are picked up automatically by the server.
123...2014-09-23T06:58:03.363858Z 0 [Note] Auto generated RSA key files are placed in data directory....
Again, these keys are then automatically picked up by the MySQL server thus enabling RSA support for the sha256_password authentication plugin from the get-go.
mysql> show variables like 'sha256_password%';
| Variable_name | Value |
| sha256_password_auto_generate_rsa_keys | ON |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_public_key_path | public_key.pem |
3 rows in set (0.00 sec)
mysql> show status like '%public_key%'\G
*************************** 1. row ***************************
Value: -----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----
1 row in set (0.00 sec)
As always, a big thank you for using MySQL and we look forward to your input on these new features! Please let us know if you have any questions, or if you encounter any problems. You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.