How to Use SSL for secure MySQL server connections
MySQL enables encryption on a per-connection basis. Depending on the requirements of individual applications, you can choose a normal unencrypted connection or a secure encrypted SSL connection. Secure connections are based on the OpenSSL API and are available through the MySQL C API. Replication uses the C API, so secure connections can be used between master and slave servers.
X509 makes it possible to identify someone on the Internet. It is most commonly used in ecommerce applications. In basic terms, there should be a trusted Certificate Authority (CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a private key). A certificate owner can provide the certificate to another party as proof of identity. A certificate contains its owner’s public key and other details, signed by the trusted CA. Any data encrypted with this public key can be decrypted only by using the corresponding private key, which is held by the owner of the certificate.
For example, when you visit a secure (HTTPS) e-commerce site, the site provides your browser with its certificate. Your browser validates the certificate against its list of trusted CAs, and uses the public key it contains to create encrypted session information that can only be decrypted by the originating server. The browser and server can then communicate securely.
Using SSL with the MySQL Server
SSL usage requirements:
- The system must support yaSSL (which comes bundled with MySQL) or OpenSSL.
- The MySQL version being used must be built with SSL support.
To get secure connections to work with MySQL and SSL, you must first do the following:
- Load OpenSSL (if you are not using a precompiled MySQL).
- Configure MySQL with SSL support.
- Make sure that the user table in the mysql database includes the SSL-related columns (ssl_* and x509_*).
- Check whether a server binary is compiled with SSL support, using the –ssl option.
To make it easier to use secure connections, MySQL is bundled with yaSSL. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license.) To obtain OpenSSL, go to http://www.openssl.org. Building MySQL using OpenSSL requires a shared OpenSSL library; otherwise, linker errors occur.
To configure a MySQL source distribution to use SSL, invoke CMake:
shell> cmake . -DWITH_SSL=bundled
This configures the distribution to use the bundled yaSSL library. To use the system SSL library instead, specify the option as -DWITH_SSL=system. If your user table does not have the SSL-related columns (beginning with ssl_* and x509_*), it must be upgraded using the mysql_upgrade program. When checking whether a server binary is compiled with SSL support, an error occurs if the server does not support SSL:
shell] mysqld --ssl --help 060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'
Starting the MySQL Server with SSL
Enable the mysqld server clients to connect using SSL, with the following options:
- –ssl-ca: Identifies the Certificate Authority (CA) certificate to be used (required for encryption)
- –ssl-key: Identifies the server public key
- –ssl-cert: Identifies the server private key
shell> mysqld --ssl-ca=ca-cert.pem \
--ssl-cert=server-cert.pem \
--ssl-key=server-key.pem
–ssl-cert can be sent to the client and authenticated against the CA certificate that it is using.
Requiring SSL-Encrypted Connections
To establish a secure connection to a MySQL server with SSL support, the client options that you must specify depend on the SSL requirements of the user account that the client uses. MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the username and password. To specify SSL-related options for a MySQL account, use the REQUIRE clause of the GRANT statement.
Use the REQUIRE SSL option of the GRANT statement to permit only SSL-encrypted connections for an account as shown below:
GRANT ALL PRIVILEGES ON test.*
TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret' REQUIRE SSL;
There are a number of ways to limit connection types for a given account. For the purposes of this post, only three options are described:
- REQUIRE NONE: Indicates that the account has no SSL or X509 requirements. This is the default if no SSL-related REQUIRE options are specified. Unencrypted connections are permitted if the username and password are valid. However, a client can also request an encrypted connection by specifying an option, if the client has the proper certificate and key files. That is, the client need not specify any SSL command options, in which case the connection is unencrypted.
- REQUIRE SSL: Tells the server to permit only SSL-encrypted connections for the account
- REQUIRE X509: The client must have a valid certificate, but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates.
Start the mysql client with the –ssl-ca option to acquire an encrypted connection. (Optional) You can also specify the –ssl-key and –ssl-cert options for an X509 connection:
shell> mysql --ssl-ca=ca-cert.pem \
--ssl-cert=server-cert.pem \
--ssl-key=server-key.pem
Checking SSL Status
Check whether a running mysqld server supports SSL, using the value of the have_ssl system variable:
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
If the value of have_ssl is YES, the server supports SSL connections. If the value is DISABLED, the server supports SSL connections but the appropriate –ssl-* options have not been provided at startup.
Check whether the current server connection uses SSL, using the value of the ssl_cipher status variable:
mysql> SHOW STATUS LIKE 'ssl_cipher';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+
For the mysql client, you can use the STATUS or \s command and check the SSL line:
mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
Advantages and Disadvantages of Using SSL
| Advantages | Disadvantages |
|---|---|
| Added security for applications in need | CPU-intensive |
| Can be enabled on a per-connection basis | Slows down client/server protocol |
| Can be used with replication | Can delay other SQL tasks |
Secure Remote Connection to MySQL
MySQL supports SSH (secure shell) connection to a remote MySQL server. This requires:
- An SSH client on the client machine
- Port forwarding through an SSH tunnel from the client to the server
- A client application on the machine with the SSH client
When you complete the setup, you have a local port that hosts an SSH connection to MySQL, encrypted using SSH.