Jun 11 2007
Mysql Traffic Encryption with OpenSSL
This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. Freeradius is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from the laboratory Infogard has realised a very good analyse available on the web at http://www.untruth.org/~josh/security/radius/. This document relates possible attacks while sending login and password for authentication.
We won’t detail Radius protocol issues, but try to avoid some security problems when using a database system as a backend. Access to the Radius database must be secure of course, but the data transport from the Freeradius server also. This is really convenient when both servers are distant, which can be frequent with networks of big size. We propose to encrypt Mysql traffic with OpenSSL, the wildly used SSL engine, in these conditions.
OpenSSL Installation
OpenSSL can be downloaded from the official site. This test has been realised with the last stable version available today, 0.9.8e. It can be installed as follow:
./config –prefix=/usr/local/openssl shared zlib
make
make test
make install
“zlib” activates support for compression/decompression and “shared” for shared libraries. It’s important to note compiling Mysql will fail on a Linux plateform without the “shared” option.
New libraries must be included in the path then. Add path /usr/local/openssl/lib (or any other where you’ve installed OpenSSL) in the /etc/ld.so.conf file under Linux. Run command line ldconfig to make the new path active.
OpenSSL activation on the server
Mysql libraries are usually provided with YaSSL, to replace OpenSSL, limited by its license. (Re)compilation is then necessary:
./configure –enable-openssl=/usr/local/openssl
make
make install
To check wether Mysql server supports SSL after service restart, we need to examine the value of the system variable have_openssl like this:
mysql> SHOW VARIABLES LIKE 'have_openssl'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | +---------------+----------+
If the value is “DISABLED”, the server supports SSL connections but wasn’t started with the correct options. Certificates needed for encryption must be created.
cd /usr/local/mysql
mkdir openssl && cd openssl
mkdir certs && cd certs
CA certificate generation
openssl genrsa -out ca-key.pem 2048
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem
Answer questions with appropriate data.
Openssl commands generate a 2048 bit key and a certificate valid for a thousand day period.
Server certificate generation
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.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
Client certificate generation
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem -out client-req.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
Certificates related data must be given as parameters while launching the service. They can also be specified in Mysql configuration file /etc/my.cnf in section [mysqld].
ssl-ca=/usr/local/mysql/openssl/certs/cacert.pem
ssl-cert=/usr/local/mysql/openssl/certs/server-cert.pem
ssl-key=/usr/local/mysql/openssl/certs/server-key.pem
Having restarted Mysql, SSL encryption should be available:
mysql> SHOW VARIABLES LIKE 'have_openssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | +---------------+-------+
As for now, the server accepts secure connections. It’s still possible to connect in clear mode as the choice is made on a per connection basis. It is possible to force a user to connect only in secure mode. This option resides in the user’s parameters and can be changed with the GRANT command.
OpenSSL activation on the client
There are several ways to connect to the server in SSL mode. Certificates are either given in option while launching Mysql client, or can be read from the configuration file /etc/my.cnf (or any other as we can modify the default file like this: mysql –defaults-file=my.cnf). Consequently, add these lines in the [client] section:
ssl-ca=/usr/local/mysql/openssl/certs/cacert.pem
ssl-cert=/usr/local/mysql/openssl/certs/client-cert.pem
ssl-key=/usr/local/mysql/openssl/certs/client-key.pem
After being connected, the cipher used to encrypt data can be displayed:
SHOW STATUS LIKE 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+
In the case where Freeradius is the client, the number of SSL connections should rise with the same number of connections created in the pool, as defined in file sql.conf.
show status like 'Ssl_accepts'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Ssl_accepts | 52 | +---------------+-------+
Performance Impact
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
These tests have been realised with a machine on which were running both Radius and Mysql services. They give however a good appreciation of encryption impact on performance that is around 30% slower.