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-certand--ssl-keyon the server side. - client-cert.pem, client-key.pem - The 'Client' certificate and key. User these as the arguments to
--ssl-certand--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 :
Website development and hosting
Database Administration Server Administration (Windows, Linux, Apple) PABX Hosting and Administration Helpdesk Support (end-user & technical). |
If you like any of my content, consider a donation via Crypto by clicking on one of the payment methods :
|
|
