Making Secure Connections to MySQL

MySQL supports the use of SSL and the X509 standard, allowing you to make an encrypted connection to a MySQL server.

By default, a connection to a MySQL server is unencrypted, meaning that data is transferred in plain text or binary, and theoretically someone can “sniff” packets of information passing between client and server across the public Internet. If a hacker can do this, he can also modify the data before sending it on, or send it on multiple times.

Using SSL, you can send data in a way that makes it practically unreadable to anyone other than the intended recipient. Any change in the data post-encryption, and the recipient will be aware that the data has been compromised since leaving the sender and cannot be trusted.

The recipient's public key is used by the sender to encrypt the data when it's sent, and the recipient has a private key that he uses to decrypt the message.

SSL encryption can optionally use an X509 Certificate, issued by a certificate authority. This is a trusted public body that ascertains a system owner's real identity before granting a certificate.

Any receiving machine will send an SSL public key to any host trying to send it encrypted data. The public key means “encrypt your data like this.”

A host sending data must encrypt the data using the public key, but the data is safe from prying eyes on the Internet because it can be decrypted only by using the SSL private key, which is kept secret and in a secure place on the receiving system.

In MySQL, you declare that a user must connect using SSL and/or X509 by using a REQUIRE clause in the GRANT statement. This defines whether that user and host combination must connect using just SSL, and whether that user must hold an X509 Certificate. It may specify the Certificate Issuer, the Certificate “Subject,” or that a specific cipher method has to be used when connecting.

To use SSL and X509 on MySQL, you must first prepare your server for it.

Preparing Your Server for Secure Connections

To use secure connections, you will need to do the following:

If you're ready to do these things, let's go through it step-by-step.

Installing MySQL with openssl

You can download openssl, the SSL library that MySQL requires, from http://www.openssl.org/. At the time of writing, the latest version is 0.9.6g.

Locate and download the file openssl-0.9.6g.tar.gz, become root using su, and move the file to wherever you like to keep your source files, such as /usr/src or /usr/local/src. Unzip and extract the archive using tar.

You should read the instructions in INSTALL, but in essence, configure using the directory locations shown (MySQL likes these) and run make:

# ./config --prefix=/usr/local --openssldir=/usr/local/openssl

# make

# make test

# make install
							

If everything goes well, openssl should be installed on your machine. Now change to the MySQL source directory and configure with the options --with-vio and --with-openssl. For example:

# ./configure 
# --prefix=/usr/local/mysql 
# --enable-assembler 
# --with-mysqld 
# --with-ldflags=-all-static 
# --with-vio 
# --with-openssl
							

This compilation should work on most Unix systems, but if not, you will need to read the MySQL and OpenSSL documentation and possibly related Web sites. Compilation problems usually arise when libraries are located in different places from where the compiler expects.

You can refresh your memory on the configure command by referring to Day 2, “Installing MySQL.” Now run make:

# make
							

If compilation goes without error, shut down your MySQL server. Then install the new binary as follows:

# make install
							

Updating the Grant Tables to the New Format

Now you'll need to update the user grant table to the new format that allows for encryption fields. If your MySQL version is currently 3.x, your user table may look similar to this (in the mysql monitor):

mysql> DESC user;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Password        | char(16) binary |      |     |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Reload_priv     | enum('N','Y')   |      |     | N       |       |
| Shutdown_priv   | enum('N','Y')   |      |     | N       |       |
| Process_priv    | enum('N','Y')   |      |     | N       |       |
| File_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

If so, you'll need to run a script called mysql_fix_privilege_tables to change the tables to the required format. (If you installed from source, it will be in your scripts directory, or the bin directory if you installed the binary.)

Run it like this (with the response shown):

# ./scripts/mysql_fix_privilege_tables rootpass

This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func table to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREAGATE FUNCTION or want to use the more secure passwords in 3.23

If you get Access denied errors, you should run this script again
and give the MySQL root user password as a argument!
Converting all privilege tables to MyISAM format

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!

...

There will probably be a lot more output, but you can ignore any Duplicate column name errors.

Now go back into the mysql console and look at the effect on your mysql.user table:

mysql> DESC user;
+---------------------+---------------------------------+----+---+-------+----+
|Field                |Type                             |Null|Key|Default|Extr|
+---------------------+---------------------------------+----+---+-------+----+
|Host                 |varchar(60) binary               |    |PRI|       |    |
|User                 |varchar(16) binary               |    |PRI|       |    |
|password             |varchar(16)                      |    |   |       |    |
|Select_priv          |enum('N','Y')                    |    |   |N      |    |
|Insert_priv          |enum('N','Y')                    |    |   |N      |    |
|Update_priv          |enum('N','Y')                    |    |   |N      |    |
|Delete_priv          |enum('N','Y')                    |    |   |N      |    |
|Create_priv          |enum('N','Y')                    |    |   |N      |    |
|Drop_priv            |enum('N','Y')                    |    |   |N      |    |
|Reload_priv          |enum('N','Y')                    |    |   |N      |    |
|Shutdown_priv        |enum('N','Y')                    |    |   |N      |    |
|Process_priv         |enum('N','Y')                    |    |   |N      |    |
|File_priv            |enum('N','Y')                    |    |   |N      |    |
|Grant_priv           |enum('N','Y')                    |    |   |N      |    |
|References_priv      |enum('N','Y')                    |    |   |N      |    |
|Index_priv           |enum('N','Y')                    |    |   |N      |    |
|Alter_priv           |enum('N','Y')                    |    |   |N      |    |
|Show_db_priv         |enum('N','Y')                    |    |   |N      |    |
|Super_priv           |enum('N','Y')                    |    |   |N      |    |
|Create_tmp_table_priv|enum('N','Y')                    |    |   |N      |    |
|Lock_tables_priv     |enum('N','Y')                    |    |   |N      |    |
|Execute_priv         |enum('N','Y')                    |    |   |N      |    |
|Repl_slave_priv      |enum('N','Y')                    |    |   |N      |    |
|Repl_client_priv     |enum('N','Y')                    |    |   |N      |    |
|ssl_type             |enum('','ANY','X509','SPECIFIED')|    |   |       |    |
|ssl_cipher           |blob                             |    |   |       |    |
|x509_issuer          |blob                             |    |   |       |    |
|x509_subject         |blob                             |    |   |       |    |
|max_questions        |int(11) unsigned                 |    |   |0      |    |
|max_updates          |int(11) unsigned                 |    |   |0      |    |
|max_connections      |int(11) unsigned                 |    |   |0      |    |
+---------------------+---------------------------------+----+---+-------+----+
31 rows in set (0.00 sec)

Your server should now be ready to accept secure connections. To test this, do the following:

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

If the Value shows YES, MySQL is prepared. If it's NO, go back and check that you followed every step of the procedure. Failure to convert the grant tables, or even to link the openssl library properly can prevent it from succeeding.

Setting Up SSL/X509 Users

Turn your attention now to the REQUIRE clause of the GRANT statement, including the three lines that follow REQUIRE:

GRANT privileges [(column_list)]
  ON database_name.table_name
  TO username@hostname [IDENTIFIED BY 'password']
  [REQUIRE [SSL | X509]
    [CIPHER cipher [AND]]
    [ISSUER issuer [AND]]
    [SUBJECT subject]]

For SSL connections without an X509 Certificate, you just need to specify the user with

REQUIRE SSL

So for example:

GRANT ALL ON *.*
  TO root@localhost IDENTIFIED BY "rootpass"
  REQUIRE SSL

You can specify X509 instead, requiring a valid certificate, but with no particular requirements placed on the certificate, like this:

GRANT ALL ON *.*
  TO root@localhost IDENTIFIED BY "rootpass"
  REQUIRE X509

If you want to require the certificate to be from a particular authority, for it to have a specific subject, or for a particular cipher method to be used (avoiding older ciphers whose codes are considered crackable these days), you can do this using the keywords given in the preceding syntax. Use the keyword AND to enforce multiple conditions.

The SSL Client

After you set a user grant table entry to require an encrypted connection from a given user and host, the client making that connection must connect in the way specified, or the connection will fail.

Depending on what kind of client you are using, this will be implemented in various ways. But let's look at building an example program using the C API.

You may recall our small C client program from Day 13, “Using the C API.” We used it to connect to the MySQL server, taking the username from the identity of the logged-in Linux user and requesting his password from the console when the program was run.

main.c was the connection script, which used mysql_real_connect() to make the database connection using a number of parameters including username, password, hostname, and so on. At the time, for simplicity, we left the last argument in the list, db_flags, at 0.

We can use db_flags to indicate that an SSL connection is to be made. You simply have to set its value to CLIENT_SSL.

To implement this, add this line to your main.c listing, in the section that defines constants:

#define def_db_flags    CLIENT_SSL

Also modify the connection routine in main.c to look like this:

mysql = db_connect (
         def_db_host,
         def_db_user,
         db_pass,
         def_db_name,
         def_db_port,
         def_unix_socket,
         def_db_flags);

The last line contains def_db_flags where it used to be 0. It is now effectively CLIENT SSL.

Make these changes to your main.c and recompile using make. Your client will now use SSL for all its connections to MySQL.

Although the example here is simple, it shows you the basics of running MySQL with applications using secure connections. For a database containing information that must be well protected, this may be the only viable way to allow access to it from across the public Internet.