Encrypt Mysql traffic between Client – Server

Posted on Posted in Mysql, seguridad

In this post I’ll explain how encrypt mysql connections using SSL certificates between mysql client and server (not applications). By default mysql are not used encryption, to activate it you only need to compile with SSL option.

Install OpenSSL

To generate the certifications and keys we’ll need openssl with shared libraries and zlib package.

Zlib

Download and install zlib package:

yum install zlib.x86_64 zlib-devel.x86_64

OpenSSL

Download the software

wget http://www.openssl.org/source/openssl-1.0.1c.tar.gz

Extract and isntall:

tar xvfz openssl-1.0.1c.tar.gz
./config --prefix=/usr/local/etc/openssl shared zlib
make
make test
make install

“zlib” activates support for compression/decompression and “shared” for shared libraries.

ld.so.conf

New libraries must be included in the path then. Add path /usr/local/etc/openssl/lib (or any other where you’ve installed OpenSSL) in the /etc/ld.so.conf file under Linux.

vim /etc/ld.so.conf
.....................................
include ld.so.conf.d/*.conf
/usr/local/etc/openssl/lib
.....................................

Run command line ldconfig to make the new path active.

ldconfig

Mysql Installation

Pre-requisites

To install mysql we need this packages:

yum install gcc-c++
yum install ncurses-devel
wget http://dl.atrpms.net/el5-x86_64/atrpms/stable/cmake-2.6.4-7.el5.x86_64.rpm
rpm -ivh cmake-2.6.4-7.el5.x86_64.rpm

Installation

Download from official website

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.24.tar.gz/from/ftp://mirrors.ircam.fr/pub/mysql/

Now compile activating the SSL support

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/etc/mysql -DWITH_SSL=bundled
make
make install

The type of SSL support to include, if any:

  • no: No SSL support. This is the default.
  • yes: Use the system SSL library if present, else the library bundled with the distribution.
  • bundled: Use the SSL library bundled with the distribution.
  • system: Use the system SSL library.

If it is the first time that you install mysql on this server, continues with the mysql normal installation (temporarily in spanish), after that, we’ll  to create the SSL infraestructure.

Activate SSL

After install Mysql we’ll see that ssl is not activated

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

Create Certificates

Create the directory containing the certificates

[root@localhost mysql]# /etc/init.d/mysql stop
cd /usr/local/etc/mysql/
mkdir openssl && cd openssl
mkdir certs && cd certs
  • Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 \
-key ca-key.pem -out ca-cert.pem
  • Create server certificate, remove passphrase, and sign it
openssl req -newkey rsa:2048 -days 1000 \
-nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Modify the file permissions to secure your files and allow MySQL to access them

chown -R mysql certs
chmod -R 700 certs
ls -ltr
total 20
-rwx------ 1 mysql root 1675 may 28 00:23 ca-key.pem
-rwx------ 1 mysql root 1261 may 28 00:23 ca-cert.pem
-rwx------ 1 mysql root 976 may 28 00:24 server-req.pem
-rwx------ 1 mysql root 1675 may 28 00:24 server-key.pem
-rwx------ 1 mysql root 1135 may 28 00:24 server-cert.pem

Start mysql with SSL

Edit the /etc/my.cnf and add the three certificates in the mysqld paragraph

[mysqld]

#CRYPT

ssl-capath=/usr/local/etc/mysql/openssl/certs
ssl-ca=/usr/local/etc/mysql/openssl/certs/ca-cert.pem
ssl-cert=/usr/local/etc/mysql/openssl/certs/server-cert.pem
ssl-key=/usr/local/etc/mysql/openssl/certs/server-key.pem

Start mysql server and check:

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------------------------------------------+
| Variable_name | Value                                               |
+---------------+-----------------------------------------------------+
| have_openssl  | YES                                                 |
| have_ssl      | YES                                                 |
| ssl_ca        | /usr/local/etc/mysql/openssl/certs/ca-cert.pem     |
| ssl_capath    | /usr/local/etc/mysql/openssl/certs                                                    |
| ssl_cert      | /usr/local/etc/mysql/openssl/certs/server-cert.pem |
| ssl_cipher    |                                                     |
| ssl_key       | /usr/local/etc/mysql/openssl/certs/server-key.pem  |
+---------------+-----------------------------------------------------+
7 rows in set (0,00 sec)
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0,00 sec)

SSL Client

To force a client use SSL, create it with the option require SSL

mysql> grant all privileges on larry.* to 'larry'@'localhost' identified by 'password' require SSL;

If you try to connect an error appear:

mysql -h localhost -u larry -p
Enter password:
ERROR 1045 (28000): Access denied for user 'larry'@'localhost' (using password: YES)

This meaning that the client don’t try to use a SSL connection with the server.

Client certificate

Create client certificate, remove passphrase, and sign it. All of it in the certs directory /usr/local/etc/mysql/openssl/certs/

openssl req -newkey rsa:2048 -days 1000 \
-nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Modify the file permissions to secure your files and allow MySQL to access them

chown -R mysql cert
chmod -R 700 cert

To activate edit /etc/my.cnf and add the three files

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

#CRYPT

ssl-capath=/usr/local/etc/mysql/openssl/certs
ssl-ca=/usr/local/etc/mysql/openssl/certs/ca-cert.pem
ssl-cert=/usr/local/etc/mysql/openssl/certs/client-cert.pem
ssl-key=/usr/local/etc/mysql/openssl/certs/client-key.pem

Ok now check again 😉

Performance

Data encryption has of course an impact on performance as the operation is greedy in CPU and network usage. Here is a graphical visualisation of the number of transactions processed per second with and without encryption

References:

http://dev.mysql.com/doc/refman/5.5/en/secure-using-ssl.html
http://dev.mysql.com/doc/refman/5.5/en/secure-create-certs.html
http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html

Leave a Reply

Your email address will not be published. Required fields are marked *