SSL

maximum de connection et maximum de connection utilisateur

max_user_connections : Le nombre maximum de connexions simultanées autorisées sur un compte utilisateur MySQL donné. Une valeur de 0 (valeur par défaut) signifie «aucune limite».Cette variable a une valeur globale qui peut être définie au démarrage ou à l'exécution du serveur. Il a également une valeur de session en lecture seule qui indique la limite effective de connexion simultanée qui s'applique au compte associé à la session en cours.

max_connections Le nombre maximum autorisé de connexions client simultanées

Par défaut dans l'installation :

 
mysql> select @@max_user_connections ;
+------------------------+
| @@max_user_connections |
+------------------------+
| 0 |
+------------------------+
1 row in set (0,00 sec)
​
mysql> select @@max_connections ;
+-------------------+
| @@max_connections |
+-------------------+
| 151 |
+-------------------+
1 row in set (0,00 sec)

Un bon conseil est de fixer max_user_connections à 50 à 75% de vos paramètres max_connections. Vous définissez cette valeur dans la section mysqld de votre my.cnf:

 
max_connections = 400
max_user_connections=200

maximum de connection pour un utilisateur

Le settings précédent concerne une mise en place assez globale du nombre de connection.
Il est possible de signifier des limits plus fine en terme de temps et de ressources
Il existe différents types de limites pouvant être utilisés:

MAX_QUERIES_PER_HOUR Limite le compte à X requêtes par heure.
MAX_UPDATES_PER_HOUR Limite le compte à X relevés UPDATE par heure.
MAX_CONNECTIONS_PER_HOUR Limite le compte à un total de X connexions par heure.
MAX_USER_CONNECTIONS Limite le compte à un total de X connexions simultanées pour le compte.

Par exemple, on limite le nombre de connection de myuser à 5

 
mysql> ALTER USER 'myuser'@'localhost' WITH MAX_USER_CONNECTIONS 5;
​

LOCK et Unlock Account

Account lock et Account unlock permette de vérrouiller ou pas un utilisateur

 
ALTER USER 'myuser'@'localhost' ACCOUNT LOCK;
Query OK, 0 rows affected (0,09 sec)
​
mysql> \q
Bye
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./bin/mysql -u myuser -h localhost -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'myuser'@'localhost'. Account is locked.
​

Mise en place de SSL

Pour l'instant la connection entre le client et le serveur est faite en claire.

 
status
--------------
./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
​
Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.13 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 29 min 39 sec
​
Threads: 2 Questions: 22 Slow queries: 0 Opens: 136 Flush tables: 2 Open tables: 106 Queries per second avg: 0.012
--------------

Création de l'autorité de certification

Exécutez les commandes suivantes pour créer les clés de l'autorité de certification (CA):

 
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ mkdir ssl_keys
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ openssl genrsa 2048 > ./ssl_keys/ca-key.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
.........................................................................+++++
...............................+++++
e is 65537 (0x010001)
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ openssl req -sha1 -new -x509 -nodes -days 3650 -key ./ssl_keys/ca-key.pem > ./ssl_keys/ca-cert.pem

Création de la clef serveur et du certificat serveur

Exécutez les commandes suivantes pour créer la clé SSL et le certificat du serveur:

 
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout ./ssl_keys/server-key.pem > ./ssl_keys/server-req.pem
openssl x509 -sha1 -req -in ./ssl_keys/server-req.pem -days 3650 -CA ./ssl_keys/ca-cert.pem -CAkey ./ssl_keys/ca-key.pem -set_serial 01 > ./ssl_keys/server-cert.pem
openssl rsa -in ./ssl_keys/server-key.pem -out ./ssl_keys/server-key.pem

Création de la clef serveur et du certificat client

Exécutez les commandes suivantes pour créer la clé SSL et le certificat du client:

 
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout ./ssl_keys/client-key.pem > ./ssl_keys/client-req.pem
openssl x509 -sha1 -req -in ./ssl_keys/client-req.pem -days 3650 -CA ./ssl_keys/ca-cert.pem -CAkey ./ssl_keys/ca-key.pem -set_serial 01 > ./ssl_keys/client-cert.pem
openssl rsa -in ./ssl_keys/client-key.pem -out ./ssl_keys/client-key.pem

Sortie de OpenSSL

Pour avoir de bon certifiact, il est important de selectionner des CN différents pour les CA, server et client

pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./ssl.sh
Generating RSA private key, 2048 bit long modulus (2 primes)
...............................................+++++
..............................+++++
e is 65537 (0x010001)
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:CA
Email Address []:
Ignoring -days; not generating a certificate
Generating a RSA private key
............+++++
......................................+++++
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem'
-----
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:server
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = server
Getting CA Private Key
writing RSA key
Ignoring -days; not generating a certificate
Generating a RSA private key
.................................+++++
.....................+++++
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-key.pem'
-----
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:client
Email Address []:

 
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./ssl.sh
Generating RSA private key, 2048 bit long modulus (2 primes)
...............................................+++++
..............................+++++
e is 65537 (0x010001)
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:CA
Email Address []:
Ignoring -days; not generating a certificate
Generating a RSA private key
............+++++
......................................+++++
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem'
-----
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:server
Email Address []:
​
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = server
Getting CA Private Key
writing RSA key
Ignoring -days; not generating a certificate
Generating a RSA private key
.................................+++++
.....................+++++
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-key.pem'
-----
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:client
Email Address []:
​
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = client
Getting CA Private Key
writing RSA key

Modification de MySQL

Il faut indiquer à MySQL ou se trouve les différentes clefs et certificat:

 
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
table_open_cache=500
secure_file_priv=/tmp
max_connections = 400
max_user_connections=200
ssl-ca=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem
ssl-cert=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-cert.pem
ssl-key=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem
ssl-cipher=DHE-RSA-AES256-SHA
​
[client]
ssl-cert=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-cert.pem
ssl-key=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-key.pem

Après redemarrage, le serveur signale que le certificat est auto signé

 
2019-01-05T08:44:05.815408Z 0 [Warning] [MY-010068] [Server] CA certificate
/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem is self signed.

et les parametre SSL sont bien chargé

 
SHOW VARIABLES LIKE '%ssl%';
+--------------------+----------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------+----------------------------------------------------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| ssl_ca | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem |
+--------------------+----------------------------------------------------------------------------------+
17 rows in set (0,01 sec)

Test

Nous allons nous connecter en SSL sur le serveur en demandant explicitement a utiliser la connection TCP (ce qui force l'utilisation de SSL)

pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./bin/mysql --defaults-file=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/my.cnf -u root -h localhost -p --protocol tcp
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id: 8
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.13 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Uptime: 20 sec

 
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./bin/mysql --defaults-file=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/my.cnf -u root -h localhost -p --protocol tcp
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL
​
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> status
--------------
./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
​
Connection id: 8
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.13 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Uptime: 20 sec

Installation de SSL pour MariaDB

Créez un répertoire nommé ssl dans le répertoire /etc/mysql/

 
$ cd /etc/mysql
$ sudo mkdir ssl
$ cd ssl

 
La valeur du nom commun utilisée pour les certificats/clés du serveur et du client doit être différente de la valeur du nom commun utilisée pour le certificat CA. Pour éviter tout problème, je les règle comme suit. Sinon, vous obtiendrez une erreur d'échec de la vérification de la certification. Par conséquent, définissez-le comme suit :
Nom commun de l'AC : administrateur MariaDB
Nom commun du serveur : serveur MariaDB
Nom commun du client : client MariaDB

Tapez la commande suivante pour créer une nouvelle autorité de certification:

Revision #2
Created 8 June 2020 18:42:12 by ggpilou2
Updated 10 November 2021 06:22:56 by ggpilou2