Team LiB
Previous Section Next Section

Setting Up Secure Connections

On Unix, MySQL provides support for secure, encrypted connections over SSL.[2] 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.

[2] It is possible to use SSL in MySQL on Windows as well, but it is more difficult to compile the distribution. Also, currently there are licensing issues that prevent MySQL AB from distributing precompiled SSL-enabled MySQL distributions.

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.

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. (Often this means you will need to compile MySQL yourself.)

  • 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 own certificate and key files.

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:

  • 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 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

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

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 client library itself must support SSL connections to the server. It must be from MySQL 4 or later, and it must have been compiled with OpenSSL support.

  • The API must also be recent enough to use the SSL capabilities of the client library. For DBI scripts, you must use DBD::mysql 2.1013 or later. For PHP, you must use the mysqli extension that is available in PHP 5. The regular mysql extension doesn't support SSL connections.

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.

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

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

    Team LiB
    Previous Section Next Section