The following is a step-by-step guide on how to set up MySQL / MariaDB for SSL connectivity.
Creation of Certificates and Keys
Using OpenSSL, we will need to create three SSL certificates and keys : 1) Certificate Authority; 2) Server; and 3) Client. The certificates and keys will form the heart of our configuration.
As outlined in (1), we create the certificates and keys using the following procedure : (Note: take care that the CN value in the Server and Client 'must be different from the CN value in the Certificate Authority.)
# 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 3600 \ -key ca-key.pem -out ca.pem # Create server certificate, remove passphrase, and sign it # server-cert.pem = public key, server-key.pem = private key shell> openssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout server-key.pem -out server-req.pem shell> openssl rsa -in server-key.pem -out server-key.pem shell> openssl x509 -req -in server-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 \ -out server-cert.pem # Create client certificate, remove passphrase, and sign it # client-cert.pem = public key, client-key.pem = private key shell> openssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout client-key.pem -out client-req.pem shell> openssl rsa -in client-key.pem -out client-key.pem shell> openssl x509 -req -in client-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 \ -out client-cert.pem
Finally, verify certificates so that they will authenticate when used.
shell> openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK
You now have a set of files which can be used with MySQL / MariaDB in the following way :
- ca.pem - The 'Certificate Authority' certificate. Use this as the argument to
--ssl-cafor both the Server and Client. (If CA is used, it must be the same for both sides.)
- server-cert.pem, server-key.pem - The 'Server' certificate and key. Use these as the arguments to
--ssl-keyon the server side.
- client-cert.pem, client-key.pem - The 'Client' certificate and key. User these as the arguments to
--ssl-keyon the client side.
Configuring a MySQL Account for SSL Access
Setting up a MySQL Account for SSL access uses the same Grant syntax, except for one key difference - the 'Require' clause. The Require clause tells MySQL that this account will only authenticate via an encryption algorithm. An example is below :
grant select,insert,update,delete on db.* to ssluser@localhost identified by 'ssluser' require SSL;
Grants 'select', 'insert', 'update' & 'delete' access on database 'db' to database user 'ssluser' with password of 'ssluser', but only authenticate via SSL.
Configuring MySQL Server and MySQL Client
MySQL Server uses a configuration file to determine key startup parameters on startup. On a Windows machine, this would generally be your 'mysql.ini' or 'my.ini' file. On a Linux machine, generally this would be your 'my.cnf' file. It is all dependent on whether you installed a compiled solution, or you customised the install process.
Within the configuration file, there are two sections that are outlined :
- [MYSQLD] - This is the section related to the MySQL server
- [MYSQL] - This is the section related to the MySQL client
To set up SSL access on MySQL server, add in the following lines underneath the [MYSQLD] section
[MYSQLD] ssl-ca = <path_to_certificates>/ca.pem ssl-cert = <path_to_certificates>/server-cert.pem ssl-key = <path_to_certificates>/server-key.pem
To set up MySQL Client for SSL access, add the following lines under the [MYSQL] section.
[MYSQL] ssl-ca = <path_to_certificates>/ca.pem ssl-cert = <path_to_certificates>/client-cert.pem ssl-key = <path_to_certificates>/client-key.pem
To make the changes active, restart the MySQL service.
(1) Creating SSL Files Using OpenSSL - http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html
About the author
Tom Thorp is an IT Consultant living in Miami on Queensland's Gold Coast. With over 30+ years working in the IT industry, Tom's experience is a broad canvas. The IT services Tom provides to his clients, includes :