MySQL

SSL

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 insère 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)