Use SSL to securely link to the MySQL database

1. Use SSL Secure Connection

To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL and your version of MySQL must be built with SSL support.

To make it easier to use secure connections, MySQL is bundled with yaSSL as of MySQL 5.0.10. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license .) yaSSL support initially was available only for a few platforms, but now it is available on all platforms supported by MySQL AB.

To get secure connections to work with MySQL and SSL, you must do the following:

1. If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, and you are going to use OpenSSL rather than the bundled yaSSL library, install OpenSSL if it has not already been installed. we have tested MySQL with OpenSSL 0.9.6. to obtain OpenSSL and visit http://www.openssl.org.

2. If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, configure a MySQL source distribution to use SSL. When you configure MySQL, invokeConfigureScript with the appropriate option to select the SSL library that you want to use.

   For yaSSL:

   shell> ./configure --with-yassl 

   For OpenSSL:

   shell> ./configure --with-openssl 

   Before MySQL 5.0, it was also neccessary to use--with-vio, But that option is no longer required.

   Note that yaSSL support on UNIX platforms requires that either/dev/urandomOr/dev/randomBe available to retrieve true random numbers. For additional information (especially regarding yaSSL on Solaris versions prior to 2.8 and HP-UX)

3. Make sure that you have upgraded your grant tables to include the SSL-related columns inmysql.userTable. This is necessary if your grant tables date from a version of MySQL older than 4.0.

4. To check whether a server binary is compiled with SSL support, invoke it with--sslOption. An error will occur if the server does not support SSL:

   shell> mysqld --ssl --help
   060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'
   

   To check whether a runningMysqldServer supports SSL, examine the value ofhave_opensslSystem variable:

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

   If the value isYES, The server supports SSL connections. If the value isDISABLED, The server supports SSL connections but was not started with the appropriate--ssl-xxxOptions (described later in this section). If the value isYES, The server supports SSL connections.

To start the MySQL server so that it allows clients to connect via SSL, use the options that identify the key and certificate files the server needs when establishing a secure connection:

Shell>mysqld --ssl-ca=cacert.pem /
--ssl-cert=server-cert.pem /
--ssl-key=server-key.pem

In general, the MySQL server is automatically started upon startup. To support SSL, modify the configuration file/etc/MySQL/My. CNF,
Set SSL-Ca, SSL-cert, SSL-key. Then/etc/init. d/MySQL restart

• --ssl-caIdentifies the Certificate Authority (CA) certificate.

• --ssl-certIdentifies the server public key. This can be sent to the client and authenticated against the CA certificate that it has.

• --ssl-keyIdentifies the server private key.

To establish a secure connection to a MySQL server with SSL support, the options that a client must specify depend on the SSL requirements of the user account that the client uses.

If the account has no special SSL requirements or was created usingGRANTStatement that includes desREQUIRE SSLOption, a client can connect securely by using just--ssl-caOption:

shell> mysql --ssl-ca=cacert.pem 

To require that a client certificate also be specified, create the account usingREQUIRE X509Option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=cacert.pem /
--ssl-cert=client-cert.pem /
--ssl-key=client-key.pem 

In other words, the options are similar to those used for the server. Note that the certificate authority certificate has to be the same.

A client can determine whether the current connection with the server uses SSL by checking the value ofSsl_cipherStatus variable. The valueSsl_cipherIs non-empty if SSL is used, and empty otherwise. For example:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

ForMySQLClient, you can useSTATUSOr/sCommand and checkSSLLine:

mysql> /s
...
SSL:                    Not in use
...

Or:

mysql> /s
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

To establish a secure connection from within an application program, usemysql_ssl_set()C API function to set the appropriate certificate options before callingmysql_real_connect().

2. Set different security connection types for database accounts

There are a number of different possibilities for limiting connection types for a given account:

• REQUIRE NONEIndicates that the account has no SSL or X509 requirements. This is the default if no SSL-relatedREQUIREOptions are specified. unencrypted connections are allowed if the username and password are valid. however, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files. that is, the client need not specify any SSL commmand options, in which case the connection will be unencrypted. to use an encrypted connection, the client must specify either--ssl-caOption, or all three of--ssl-ca,--ssl-key, And--ssl-certOptions.

• TheREQUIRE SSLOption tells the server to allow only SSL-encrypted connections for the account.

  GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    IDENTIFIED BY 'goodsecret' REQUIRE SSL;
  

  To connect, the client must specify--ssl-caOption, and may additionally specify--ssl-keyAnd--ssl-certOptions.

• REQUIRE X509Means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. the only requirement is that it shoshould be possible to verify its signature with one of the CA certificates.

  GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    IDENTIFIED BY 'goodsecret' REQUIRE X509;
  

  To connect, the client must specify--ssl-ca,--ssl-key, And--ssl-certOptions. This is also trueISSUERAndSUBJECTBecause thoseREQUIREOptions implyX509.

• REQUIRE ISSUER 'issuer'Places the restriction on connection attempts that the client must present a valid X509 Certificate issued by CA'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, soSSLOption is unnecessary in this case.

  GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    IDENTIFIED BY 'goodsecret'
    REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
      O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
  

  Note that'issuer'Value shoshould be entered as a single string.

• REQUIRE SUBJECT 'subject'Places the restriction on connection attempts that the client must present a valid X509 Certificate containing the subjectsubject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection.

  GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    IDENTIFIED BY 'goodsecret'
    REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
      O=MySQL demo client certificate/
      CN=Tonu Samuel/Email=tonu@example.com';
  

  Note that'subject'Value shoshould be entered as a single string.

• REQUIRE CIPHER 'cipher'Is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used. using this option, you can ask that a specific cipher method is used to allow a connection.

  GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    IDENTIFIED BY 'goodsecret'
    REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
  

TheSUBJECT,ISSUER, AndCIPHEROptions can be combined inREQUIREClause like this:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

TheANDKeyword is optionalREQUIREOptions.

3. Create an SSL Certificate for MySQL

This section demonstrates how to set up SSL Certificate and key files for use by MySQL servers and clients. the first example shows a simplified procedure such as you might use from the command line. the second shows a script that contains more detail. both examples useOpenSSLCommand that is part of OpenSSL.

The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts byOpenSSLCommands. For testing, you can press enter to all prompts. For production use, you shoshould provide non-empty responses.

# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 /
-key ca-key.pem > ca-cert.pem

# Create server certificate
shell> openssl req -newkey rsa:2048 -days 1000 /
-nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 1000 /
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificate
shell> openssl req -newkey rsa:2048 -days 1000 /
-nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 /
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem