Module 3Protéger la base de données MySQL
Chapitre 11 sur 12
La sécurité des données, les bonnes pratiques, la sauvegarde avec mysqldump, les utilisateurs, les privilèges GRANT et REVOKE, et les rôles.
Objectifs
Avec ce chapitre, tu sauras…
GRANT, REVOKE) à différents niveaux ;Leçon 1
| Objectif | Signification | Menace typique |
|---|---|---|
| confidentialité | seules les personnes autorisées lisent les données | vol des données des clients |
| intégrité | les données ne sont modifiées que correctement et par des personnes autorisées | modification frauduleuse d'un solde |
| disponibilité | la base est accessible quand on en a besoin | panne, disque détruit, rançongiciel |
Vidéo · À regarder
FRBase de données : Gestion des utilisateurs
Algomius · YouTube
Leçon 2
| Bonne pratique | Exemple concret | |
|---|---|---|
| 1 | sécuriser les serveurs physiques | salle fermée à clé, accès réservé |
| 2 | utiliser des serveurs de base de données séparés | la base n'est pas sur la même machine que le site web |
| 3 | configurer un serveur proxy HTTPS | les données circulent chiffrées entre l'application et les utilisateurs |
| 4 |
Leçon 3
| Partie | Signification |
|---|---|
-u root | l'utilisateur MySQL |
-p | demande le mot de passe |
eshop_app_db | la base à sauvegarder |
> fichier.sql | redirige le résultat dans un fichier |
Leçon 4
Vidéo · À regarder
FRBase de données : Gestion des utilisateurs
Algomius · YouTube
| Niveau |
|---|
1Les trois objectifs de la sécurité sont…
2mysqldump produit…
3Pour restaurer sauv.sql dans la base b, on tape…
4'ali'@'%' peut se connecter…
5Un utilisateur qui vient d'être créé a…
6ON eshop_app_db.* désigne…
7Pour retirer un privilège, on utilise…
8Dans MySQL 8, pour changer un mot de passe, on écrit…
L'essentiel du chapitre
mysqldump -u root -p [--routines --events] base > fichier.sql ; restauration : CREATE DATABASE b; puis mysql -u root -p b < fichier.sql.% joker) : CREATE USER, RENAME USER, ALTER USER … IDENTIFIED BY (MySQL 8), DROP USER ; connexion mysql -h hôte -u user -p base.*.*, base.*, base.table, colonnes, routines ; GRANT … ON … TO (il faut GRANT OPTION), REVOKE … ON … FROM, SHOW GRANTS FOR.CREATE ROLE, GRANT priv ON … TO rôle, GRANT rôle TO user, SET ROLE, DEFAULT ROLE, CURRENT_ROLE(), REVOKE rôle FROM user.| mettre en place un protocole de chiffrement |
| connexions MySQL en SSL/TLS, mots de passe hachés |
| 5 | faire des sauvegardes régulières | mysqldump chaque nuit (section 3) |
| 6 | mettre à jour les applications régulièrement | correctifs de sécurité de MySQL, PHP, Laravel |
| 7 | utiliser une authentification forte | mots de passe longs, jamais root sans mot de passe |
| 8 | attribuer des rôles de sécurité à tous les utilisateurs | chaque utilisateur n'a que les droits utiles (section 4) |
| 9 | éviter les ports réseau par défaut | changer le port 3306, ne pas exposer MySQL sur Internet |
Ne rien oublier
Vidéo · À regarder
FRComment sauvegarder une base de données MySQL comme un PRO + Bonus je partage un script gratuit !
La Revue GEEK · YouTube
| Exemple |
|---|
| global (toutes les bases) | *.* | ON *.* |
| base de données | nom_bd.* | ON eshop_app_db.* |
| table | nom_bd.nom_table | ON eshop_app_db.clients |
| colonne | privilège (colonnes) | UPDATE (nom, prenom) |
| routine stockée | PROCEDURE nom_bd.nom_routine | GRANT EXECUTE ON PROCEDURE eshop_app_db.sp_client_par_ville |
Vidéo · À regarder
FRBase de données : Gestion des utilisateurs
Algomius · YouTube
Vidéo · À regarder
FRBase de données : Gestion des utilisateurs
Algomius · YouTube
La sécurité doit protéger :
Ce que tu vas voir (5 min) : les chapitres « Introduction à la sécurité des données ». Le formateur présente la sécurité des données et ce qu'il faut protéger.
Tableau: Les bonnes pratiques
mysqldump sauvegarde une ou plusieurs bases en générant un fichier texte qui contient les instructions SQL capables de recréer les bases à partir de zéro (CREATE TABLE, INSERT…).Sauvegarder
On tape la commande dans l'invite de commandes (pas dans MySQL) :
mysqldump -u root -p eshop_app_db > C:\Backup\eshop_db_bak_20200424.sql
Enter password: ****Le fichier eshop_db_bak_20200424.sql apparaît dans le dossier C:\Backup. Mettre la date dans le nom permet de garder plusieurs sauvegardes.
Créer un utilisateur
Un utilisateur est défini par deux éléments : son login et l'hôte (la machine) à partir duquel il se connecte.
CREATE USER 'login'@'hote' [IDENTIFIED BY 'mot_de_passe'];
CREATE USER 'saadi'@'localhost' IDENTIFIED BY 'saadi_anass';
CREATE USER 'bensaid'@'194.28.12.4' IDENTIFIED BY 'bensaid99';On peut autoriser un utilisateur à se connecter depuis plusieurs hôtes sans créer un utilisateur par hôte, avec le joker % :
-- Said peut se connecter depuis n'importe quel hôte dont l'adresse IP commence par 194.28.12
CREATE USER 'Said'@'194.28.12.%' IDENTIFIED BY 'baskeball18';
-- Hanane peut se connecter depuis n'importe quel hôte
CREATE USER 'Hanane'@'%' IDENTIFIED BY 'looking4sun';'saadi'@'localhost' et 'saadi'@'%' sont deux comptes différents pour MySQL.
Les exemples mettent des espaces dans les guillemets : 'saadi '@' localhost '. MySQL créerait alors un login « saadi » suivi d'un espace et un hôte « localhost » entouré d'espaces : la connexion ne marcherait jamais. Écris 'saadi'@'localhost' sans espace.
Ce que tu vas voir (14 min) : les chapitres « Gestion des utilisateurs », « Créer un utilisateur en MySQL ». Il explique la gestion des utilisateurs et crée un utilisateur MySQL (login, hôte, mot de passe).
Se connecter
Dans l'invite de commandes :
mysql -h <host> -u <user> -p <db>
mysql -h localhost -u saadi -p eshop_app_dbRenommer, changer le mot de passe, supprimer
-- renommer un utilisateur
RENAME USER 'ancien_nom'@'ancien_hote' TO 'nouveau_nom'@'nouveau_hote';
-- modifier le mot de passe (syntaxe du résumé, MySQL 5.7 et MariaDB)
SET PASSWORD FOR 'utilisateur'@'hote' = PASSWORD('nouveau_pw');
-- supprimer un utilisateur
DROP USER 'login'@'hote';La fonction PASSWORD() a été supprimée dans MySQL 8 : SET PASSWORD … = PASSWORD('…') provoque une erreur. On écrit :
ALTER USER 'saadi'@'localhost' IDENTIFIED BY 'nouveau_pw';
-- ou
SET PASSWORD FOR 'saadi'@'localhost' = 'nouveau_pw';
Les privilèges
CREATE USER n'a au départ aucun privilège : il peut se connecter, c'est tout.SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, ALTER, DROP, CREATE ROUTINE, EXECUTE, LOCK TABLES… autorisent les commandes du même nom. ALL PRIVILEGES les donne tous.Les privilèges s'appliquent à différents niveaux :
(1) Crée les rôles r_lecture (SELECT sur eshop_app_db.*) et r_vente (SELECT, INSERT, UPDATE sur commandes et ligne_commande, EXECUTE sur sp_enregistrer_vente). (2) Crée les vendeurs vendeur1 et vendeur2 (localhost) avec le rôle r_vente par défaut. (3) Connecte-toi en vendeur1, affiche CURRENT_ROLE() et essaie un DELETE FROM clients : que se passe-t-il ? (4) Retire le rôle à vendeur2.
(1) Sauvegarde la base eshop_app_db avec ses procédures, fonctions et événements dans C:\Backup\eshop_JJMMAAAA.sql. (2) Crée la base eshop_test et restaure-y la sauvegarde. (3) Vérifie que les procédures existent dans eshop_test avec SHOW PROCEDURE STATUS WHERE db = 'eshop_test';.
Une diapositive de cette partie (après la restauration) est vide dans le résumé ; son contenu n'est pas récupérable. Le tableau ci-dessus reprend les 9 bonnes pratiques de la diapositive précédente, avec un exemple pour chacune.
Chaque compte reçoit seulement les droits dont il a besoin, et rien de plus. L'application web n'a pas besoin de DROP ; le comptable n'a besoin que de SELECT sur les factures. Si un compte est piraté, les dégâts restent limités.
Par défaut, mysqldump sauvegarde les tables, les données, les vues et les déclencheurs, mais pas les procédures, fonctions et événements. Ajoute --routines --events :
mysqldump -u root -p --routines --events eshop_app_db > eshop_complet.sql
mysqldump -u root -p --all-databases > toutes_les_bases.sql
Sous Windows, si la commande est inconnue, va dans le dossier C:\Program Files\MySQL\MySQL Server 8.0\bin (ou C:\xampp\mysql\bin) ou ajoute-le au PATH.
Restaurer
Deux étapes :
CREATE DATABASE nom_bd; ;mysql -u [utilisateur] -p [nom_bd] < [nom_fichier].sql
mysql -u root -p eshop_restore < C:\Backup\eshop_db_bak_20200424.sqlAttention au sens des chevrons : > (mysqldump) écrit vers le fichier, < (mysql) lit depuis le fichier.
Ce que tu vas voir (8 min) : les chapitres « Sauvegarder une seule base de données », « Sauvegarder toutes les bases ». Le formateur sauvegarde une base avec mysqldump, puis toutes les bases du serveur.
Ce que tu vas voir (16 min) : les chapitres « Gérer les privilèges », « Privilèges spéciaux (ALL, GRANT OPTION) », « Limiter les ressources ». Il gère les privilèges avec GRANT et REVOKE, présente les privilèges spéciaux ALL et GRANT OPTION, puis la limitation des ressources.
Ajouter un privilège : GRANT
Pour donner un privilège, il faut soi-même avoir le privilège GRANT OPTION. Au début, seul root le possède.
GRANT privilege1 [(liste_colonnes)] [, privilege2 [(liste_colonnes)], ...]
ON [type_objet] niveau_privilege
TO utilisateur
[IDENTIFIED BY mot_de_passe];GRANT SELECT,
UPDATE (nom, prenom, adresse),
DELETE,
INSERT
ON eshop_app_db.clients
TO 'saadi'@'localhost';
-- accorder le privilège SELECT sur toutes les tables de toutes les bases à l'utilisateur saadi
GRANT SELECT
ON *.*
TO 'saadi'@'localhost';
SHOW GRANTS FOR 'saadi'@'localhost'; -- voir les privilèges d'un utilisateursaadi peut lire, supprimer et insérer des clients, mais ne peut modifier que les colonnes nom, prenom et adresse.
Le deuxième exemple écrit GRANT SELECT , ON *.* : la virgule avant ON provoque une erreur de syntaxe.
Dans MySQL 8, GRANT … IDENTIFIED BY est supprimé : on crée d'abord l'utilisateur avec CREATE USER … IDENTIFIED BY, puis on lui donne ses droits avec GRANT. Les privilèges sur une routine permettent la sécurité vue au chapitre 2 : GRANT EXECUTE ON PROCEDURE … autorise à appeler la procédure sans avoir de droit sur les tables qu'elle utilise.
Retirer un privilège : REVOKE
REVOKE privilege [, privilege, ...]
ON niveau_privilege
FROM utilisateur;
REVOKE INSERT
ON eshop_app_db.clients
FROM 'saadi'@'localhost';Les rôles
Au lieu de donner les mêmes droits à chaque utilisateur, on peut créer un rôle, lui donner des privilèges, puis attribuer le rôle aux utilisateurs. C'est beaucoup plus simple à gérer pour des groupes d'utilisateurs aux droits identiques (les développeurs, les comptables…).
CREATE ROLE 'nom_role';
CREATE ROLE 'webdeveloper';
-- affecter des privilèges au rôle
GRANT SELECT ON mysql.user TO 'webdeveloper';
-- ajouter un utilisateur au rôle
GRANT 'webdeveloper' TO 'rh_salmi'@'entrbc.com.net';SET ROLE 'webdeveloper';.La création d'un utilisateur, l'attribution d'un rôle et sa définition comme rôle par défaut peuvent se faire en une seule instruction (MySQL 8) :
CREATE USER 'u2'@'%'
IDENTIFIED BY 'foobar'
DEFAULT ROLE 'webdeveloper';Pour voir l'utilisateur et le rôle utilisés :
SELECT CURRENT_ROLE(), CURRENT_USER();Pour exclure un utilisateur d'un rôle :
REVOKE '<role>' FROM '<user>'@'<host>';
REVOKE 'webdeveloper' FROM 'med34'@'localhost';INDENTIFIED BY → IDENTIFIED BY ;webdeveloper, mais il est écrit webdevelopper (deux p) dans DEFAULT ROLE et dans REVOKE : MySQL ne trouve pas le rôle ;SET ROLE 'webdeveloper'. se termine par un point au lieu d'un ;.MariaDB (XAMPP, WAMP) gère aussi les rôles, mais n'accepte pas DEFAULT ROLE dans CREATE USER : on écrit GRANT 'webdeveloper' TO 'u2'@'%'; puis SET DEFAULT ROLE 'webdeveloper' FOR 'u2'@'%';.
Ce que tu vas voir (15 min) : les chapitres « Gestion des rôles », « Créer un rôle », « Utiliser un rôle ». Il crée un rôle, lui donne des privilèges, l'attribue à un utilisateur et l'active.
Crée les comptes : (1) app_web (depuis localhost) qui peut lire, ajouter et modifier dans toutes les tables de eshop_app_db, mais pas supprimer ; (2) comptable (depuis le réseau 192.168.1.%) qui peut seulement lire la table commandes ; (3) stagiaire (de partout) qui peut seulement exécuter la procédure sp_client_par_ville. Affiche les droits de chacun, puis retire à app_web le droit de modifier.