Setting Up Secure Connections
On Unix, MySQL 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, shared memory, 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.
The following discussion describes this process in more detail.
Before you can use SSL for secure connections, the user table of the mysql database must contain the SSL columns described in "Grant Table SSL-Related Columns." 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. See the section "Dealing with Changes to Grant Table Structure," in Chapter 11.
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 (http://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.
After 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:
Copy these three 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, 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 can be listed like this:
[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
At this point, you have enabled the server to allow encrypted connections, but client programs still can 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). The client certificate and key files are named client-cert.pem and client-key.pem. Copy these three 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 that I want to use encrypted connections for the mysql program. To do this, I can copy the SSL files to my home directory, /home/paul, and then put the following lines in my .my.cnf file:
[mysql] ssl-ca=/home/paul/ca-cert.pem ssl-cert=/home/paul/client-cert.pem ssl-key=/home/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> status; -------------- mysql Ver 14.7 Distrib 4.1.9 for intel-linux (i686) Connection id: 5 Current database: Current user: sampadm@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, it may not be a good idea to put the options in the general [client] group. That will cause any client program to fail that doesn't understand how to use SSL. (If you want to put the options there anyway, use the loose- prefix so that non-SSL-aware programs will skip over them.)
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 certificate and key files in the sampdb distribution suffice to allow you to establish encrypted connections. However, they're publicly available (anyone can get the distribution), 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.txt file in the sampdb distribution. You may also want to consider purchasing a commercial certificate.
If you are using a client API such as Perl DBI or PHP, SSL capabilities depend not only on the API but on the MySQL client library that is linked into it:
The discussion thus far describes how any account can use SSL on an optional basis. You can also set up any account to disallow unencrypted connections 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 that 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. To require only that connections be encrypted, use this 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 indicate more specific requirements, use some combination of CIPHER, ISSUER, and SUBJECT in the REQUIRE clause. CIPHER indicates the type of encryption method you want the connection to use. 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, and modifies only SSL-related account attributes.
GRANT USAGE ON *.* TO 'user_name'@'host_name' REQUIRE NONE;