Setting Up Secure Connections

On UNIX, MySQL 4 provides support for secure, encrypted connections over SSL. By default, an SSL-enabled MySQL installation allows a client to ask for secure connections on an optional basis. (The tradeoff is that a normal unencrypted connection has higher performance, whereas an encrypted connection is secure but somewhat slower due to the additional computational burden that encryption imposes.) It's also possible for administrators to specify using a GRANT statement that a given account is required to connect securely.

Note that there is little point in using SSL for connections to the local host that are made using a UNIX socket file, a named pipe, or to the IP address 127.0.0.1 (the network loopback interface). The real benefit of SSL comes when the information that you're transmitting goes over a network that may be susceptible to snooping.

To take advantage of SSL support for encrypted connections between the server and client programs, use the following general procedure:

• Make sure the user table contains the SSL-related columns.

• Make sure the server and client programs have been compiled with OpenSSL support.

• Start the server with options that tell it where to find its certificate and key files; these are necessary to set up secure connections.

• To connect securely with a client program, invoke it with options that tell it where to find your certificate and key files.

The following discussion describes this process in more detail.

To use SSL for secure connections, the user table of the mysql database must contain the SSL columns described in the "Grant Table SSL-Related Columns" section in this chapter. If you have installed MySQL using version 4.0.0 or later, your user table should contain these columns already. If you have upgraded to MySQL 4.x from an earlier version, the columns may not be present, in which case you should run the mysql_fix_privilege_tables script to update the tables.

Your MySQL distribution must be built with OpenSSL included. Either get a binary distribution that has OpenSSL compiled in or build MySQL from source. In the latter case, you must have OpenSSL installed; if you need to get it, visit www.openssl.org. Then build MySQL by running configure with the --with-vio and --with-openssl options. After you start your OpenSSL-enabled server, verify that it supports SSL by connecting with mysql and issuing the following query:

 mysql> SHOW VARIABLES LIKE 'have_openssl';  +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl  | YES   | +---------------+-------+ 

If you don't see YES, SSL support was not enabled correctly.

When your MySQL installation has been enabled to support SSL, the server and its clients can communicate securely. Each end of a connection uses three files to set up secure communications. Briefly summarized, these files are:

• A Certificate Authority (CA) certificate. A CA is a trusted third party; its certificate is used to verify the authenticity of the client and server certificates. It's common to purchase a CA certificate from a commercial entity, but you can generate your own.

• A certificate file that authenticates one side of the connection to the other.

• A key file, used to encrypt and decrypt traffic over the connection.

The server's certificate and key files must be installed first. The ssl directory of the sampdb distribution contains some boilerplate files that you can use for this:

• ca-cert.pem The Certificate Authority certificate

• server-cert.pem The server's certificate

• server-key.pem The server's public key

Copy these files to your server's data directory and then add some lines to the [mysqld] group of an option file that the server reads when it starts up, such as /etc/my.cnf on UNIX. The options should indicate the pathnames to the certificate and key files. For example, if the data directory is /usr/local/mysql/data, the options will be listed as follows:

 [mysqld]  ssl-ca=/usr/local/mysql/data/ca-cert.pem ssl-cert=/usr/local/mysql/data/server-cert.pem ssl-key=/usr/local/mysql/data/server-key.pem 

You can put the certificate and key files elsewhere if you like, but the location should be one that only the server has access to. After modifying the option file, restart the server.

At this point, client programs can still connect to the server only over unencrypted connections. To set up a client program to use secure connections, it's necessary to use certificate and key files on the client side as well. The ssl directory of the sampdb distribution contains files for this. You can use the same CA certificate file (ca-cert.pem), and the client certificate and key files are named client-cert.pem and client-key.pem. Copy these files to some directory under your own account and then add some lines to the .my.cnf file in your home directory to let the client program know where they are. Suppose I want to use encrypted connections for mysql. To do this, I'd copy the SSL files to my home directory, /u/paul, and then put the following lines in my .my.cnf file:

 [mysql]  ssl-ca=/u/paul/ca-cert.pem ssl-cert=/u/paul/client-cert.pem ssl-key=/u/paul/client-key.pem 

You can set up your own account similarly. (It's also a good precaution to make sure your certificate and key files are accessible only to yourself.) After modifying .my.cnf to indicate where the SSL files are located, invoke mysql and issue a \s or STATUS command. The SSL line in the output should indicate that the connection is encrypted:

 mysql> \s  -------------- ./mysql  Ver 12.10 Distrib 4.1.0-alpha, for apple-darwin5.5 (powerpc) Connection id:          1 Current database: Current user:           root@localhost SSL:                    Cipher in use is EDH-RSA-DES-CBC3-SHA ... 

You can also issue the following query to see what the SSL-related server status variables are set to:

 SHOW STATUS LIKE 'Ssl%';  

The presence of the SSL-related options in the [mysql] group causes mysql to use SSL connections by default. If you comment out those lines or remove them from your option file, mysql will use a regular non-encrypted connection. It's also possible to ignore the options by invoking mysql like this:

 % mysql --skip-ssl  

The SSL options in the [mysql] group can be copied to other program-specific groups as well if you want to use SSL for other programs. However, note that if you put the options in the general [client] group, that will cause any client program that doesn't understand about SSL to fail so that may not be such a good idea.

As an alternative to listing SSL options in the option file, you can specify them on the command line. For example, in my home directory I might invoke mysql like this (type the command all on one line):

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

However, that becomes burdensome if you have to do it often.

The discussion thus far describes how any account can use SSL on an optional basis. You can also disallow unencrypted connections for an account and require it to use SSL. This can be done both for new accounts and for existing accounts.

To set up a new account, use a GRANT statement as you normally would but add a REQUIRE clause that specifies the constraints that connections must satisfy. Suppose you want to set up a user named laura who will be connecting to the server on cobra.snake.net from the host rat.snake.net to access the finance database. If you want to require only that connections be encrypted, use the following statement:

 GRANT ALL ON finance.* TO 'laura'@'rat.snake.net'  IDENTIFIED BY 'moneymoneymoney' REQUIRE SSL; 

For more security, use REQUIRE X509 instead. Then laura must supply a valid X509 client certificate when connecting. (This will be the file named by the ssl-cert option.) As long as the certificate is valid, its contents don't otherwise matter. To specify more constrained requirements, use some combination of CIPHER, ISSUER, and SUBJECT in the REQUIRE clause. CIPHER indicates the type of encryption cipher on which you want the connection to be based. ISSUER or SUBJECT indicate that the client certificate must have been issued by a particular source or for a particular recipient. These clauses narrow the scope of otherwise-valid certificates to include only those with specific content. The following GRANT statement requires a particular issuer in the client certificate and specifies the use of EXP1024-RC4-SHA encryption:

 GRANT ALL ON finance.* TO 'laura'@'rat.snake.net'  IDENTIFIED BY 'moneymoneymoney' REQUIRE ISSUER '/C=US/ST=WI/L=Madison/O=sampdb/OU=CA/CN=sampdb' CIPHER 'EXP1024-RC4-SHA'; 

To modify an existing account to require SSL connections, use a GRANT USAGE statement of the following form, where require_options specifies the SSL characteristics you want to enforce:

 GRANT USAGE ON *.* TO 'user_name'@'host_name' REQUIRE require_options;  

GRANT USAGE ON *.* leaves the account's privileges unchanged, so the statement modifies only its SSL-related attributes.

If an account currently is set to require SSL and you want to rescind the requirement, use GRANT USAGE in conjunction with REQUIRE NONE:

 GRANT USAGE ON *.* TO 'user_name'@'host_name' REQUIRE NONE;  

The certificate and key files in the sampdb distribution suffice to allow you to establish encrypted connections. However, they're publicly available, so connections thus established cannot truly be said to be secure. After you use these files to verify that SSL is working properly, you should replace them with ones that you generate yourself. For instructions on making your own certificate and key files, see the ssl/README file in the sampdb distribution. You may also want to consider purchasing a commercial certificate.