MySQL

SSL

• Vérifier votre version de Openssl avant de poursuivre :

openssl version OpenSSL 1.0.1c 10 May 2012

La version 1.0.1c n'est pas supporté par MySQL. Il sera nécessaire d'installer une version antérieure pour poursuivre.

Autorité de certification

Création de la clé privée

• On se place dans le dossier /etc/ssl/private qui contient les clés et certificats privées :

cd /etc/ssl/private

• On génère la clé privée :

sudo openssl genrsa -out ca.key 2048 -des3

Cette clé va nous permettre de signer nos propres certificats.

Création du certificat auto-signé

• On lance la création de certificat auto-signé :

openssl req -new -x509 -nodes -days 365 -key ca.key -out ca.crt You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. - - - -- Country Name (2 letter code) [AU]:FR State or Province Name (full name) [Some-State]:Paris Locality Name (eg, city) []:Paris Organization Name (eg, company) [Internet Widgits Pty Ltd]:NCad Network Organizational Unit Name (eg, section) []:Autorité de certification Common Name (e.g. server FQDN or YOUR name) []:Murdoc Certificats pour GLX Email Address []:tech@ncad.fr

Certificat serveur

• On génère la clé privée :

sudo openssl genrsa -out mysql.key 2048

• On génère un fichier de demande de signature de certificat :

sudo openssl req -new -key mysql.key -out mysql.csr You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. - - - - - Country Name (2 letter code) [AU]:FR State or Province Name (full name) [Some-State]:Paris Locality Name (eg, city) []:Paris Organization Name (eg, company) [Internet Widgits Pty Ltd]:NCad Network Organizational Unit Name (eg, section) []:Intranet Goulouxiou Common Name (e.g. server FQDN or YOUR name) []:10.0.0.47 Email Address []:tech@ncad.fr Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:N'importe quoi. An optional company name []:NCad Network

• On signe notre certificat à l'aide du certificat auto-signé de notre autorité de certification (précédemment créée) :

openssl x509 -req -in mysql.csr -days 365 -CA ca.crt -CAkey ca.key -set_serial 01 -out mysql.crt Signature ok subject=/C=FR/ST=Paris/L=Paris/O=NCad Network/OU=Intranet Goulouxiou/CN=10.0.0.47/emailAddress=tech@ncad.fr Getting CA Private Key

Configuration du serveur MySQL

• On déplace les certificats crées dans /etc/ssl/private vers /etc/mysql/ssl

sudo mkdir -p /etc/mysql/ssl cp /etc/ssl/private/ca.* /etc/mysql/ssl cp /etc/ssl/private/mysql.* /etc/mysql/ssl

• On applique les permissions et droits mysql sur les certificats :

sudo chown mysql:mysql /etc/mysql/ssl -R

• On édite le fichier /etc/apparmor.d/usr.sbin.mysqld et on y insère les éléments suivants :

/etc/mysql/ssl/*.pem r,
/etc/mysql/ssl/*.crt r,
/etc/mysql/ssl/*.key r,

• On édite le fichier de configuration /etc/mysql/my.cnf et on y dé-commente et adapte les lignes suivantes dans la section [mysqld] :

ssl-ca=/etc/mysq/ssl/ca.crt
ssl-cert=/etc/mysql/ssl/mysql.crt
ssl-key=/etc/mysql/ssl/mysql.key

Validation

mysql> show variables like "%ssl%";
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /etc/mysq/ssl/ca.crt     |
| ssl_capath    |                          |
| ssl_cert      | /etc/mysql/ssl/mysql.crt |
| ssl_cipher    |                          |
| ssl_key       | /etc/mysql/ssl/mysql.key |
+---------------+--------------------------+
7 rows in set (0.02 sec)

mysql> show status like 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.01 sec)