Image
Body
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 more than 30 years working in the IT industry, he has extensive experience. The IT services provided to clients include:
Website development and hosting,
Database Administration, Server Administration (Windows, Linux, Apple), PBX 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:.
|
|
