Comptes, rôles et droits : GRANT (LCD)

Le LCD, langage de contrôle des données, regroupe les instructions SQL qui gèrent les droits : CREATE USER crée un compte MySQL, GRANT lui donne des privilèges, REVOKE les lui retire.

Chaque connexion au serveur se fait avec un compte, et chaque compte n'a que les privilèges qu'on lui a donnés. C'est la première ligne de défense de la base : une application piratée ne peut faire que ce que son compte a le droit de faire. Les sujets d'E7 SLAM demandent régulièrement de créer un compte et de lui attribuer les droits strictement nécessaires.

Créer un compte

Un compte MySQL est un couple utilisateur et hôte : 'app_parc'@'localhost' et 'app_parc'@'10.0.1.20' sont deux comptes différents, avec leurs propres mots de passe et leurs propres droits. L'hôte dit d'où la connexion est autorisée.

-- L'application web du helpdesk, qui tourne sur le serveur 10.0.1.20
CREATE USER 'app_parc'@'10.0.1.20' IDENTIFIED BY 'Mot-de-passe-long-et-unique-2026';

-- Un compte d'administration utilisable seulement depuis le serveur lui-même
CREATE USER 'admin_parc'@'localhost' IDENTIFIED BY 'Autre-mot-de-passe-solide!';

La gestion courante des comptes :

ALTER USER 'admin_parc'@'localhost' IDENTIFIED BY 'Nouveau-mot-de-passe-2026!';  -- changer le mot de passe
ALTER USER 'admin_parc'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;              -- expiration
ALTER USER 'app_parc'@'10.0.1.20' ACCOUNT LOCK;                                   -- bloquer
ALTER USER 'app_parc'@'10.0.1.20' ACCOUNT UNLOCK;                                 -- débloquer
RENAME USER 'app_parc'@'10.0.1.20' TO 'app_helpdesk'@'10.0.1.20';
DROP USER 'app_helpdesk'@'10.0.1.20';

SELECT user, host FROM mysql.user; liste les comptes du serveur. Depuis MySQL 8.0, les mots de passe sont vérifiés par défaut par le module caching_sha2_password. En 8.4, l'ancien mysql_native_password est désactivé par défaut (il faut démarrer le serveur avec --mysql-native-password=ON pour le réactiver), et MySQL 9 le supprime. Un très vieux client ou pilote qui ne connaît que ce dernier doit être mis à jour.

GRANT et REVOKE

GRANT donne des privilèges, REVOKE les retire. Ils s'appliquent à plusieurs niveaux :

-- Toute une base
GRANT SELECT ON parc.* TO 'app_parc'@'10.0.1.20';

-- Une table
GRANT INSERT, UPDATE ON parc.ticket TO 'app_parc'@'10.0.1.20';
GRANT INSERT ON parc.intervention TO 'app_parc'@'10.0.1.20';

-- Certaines colonnes seulement, pour un compte créé d'abord
CREATE USER 'stagiaire'@'localhost' IDENTIFIED BY 'Phrase-de-passe-du-stagiaire-2026';
GRANT SELECT (nom, prenom, email) ON parc.employe TO 'stagiaire'@'localhost';
GRANT UPDATE (telephone) ON parc.employe TO 'stagiaire'@'localhost';

-- Une procédure stockée (quand la procédure existe)
-- GRANT EXECUTE ON PROCEDURE parc.cloturer_ticket TO 'app_parc'@'10.0.1.20';

-- Toute la base : le compte des évolutions de structure (voir le tableau plus bas)
GRANT ALL PRIVILEGES ON parc.* TO 'admin_parc'@'localhost';

-- Tout le serveur (*.*) : réservé à root et aux administrateurs de bases
-- GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost';

-- Retirer un privilège
REVOKE UPDATE ON parc.ticket FROM 'app_parc'@'10.0.1.20';

GRANT et REVOKE rechargent les droits aussitôt, sans FLUSH PRIVILEGES (qui ne sert qu'après une modification directe des tables système). Une session déjà ouverte voit un changement de droits sur une table ou une colonne dès sa requête suivante ; un droit sur une base, au prochain USE ; un droit global ou un nouveau mot de passe, seulement à la prochaine connexion. Depuis MySQL 8.0, GRANT ne crée plus de compte : le compte doit exister, sinon MySQL lève l'erreur 1410.

Les rôles

Un rôle est un ensemble nommé de privilèges. On donne les privilèges au rôle, puis le rôle aux personnes : quand une technicienne arrive ou part, une seule instruction suffit. MySQL a des rôles depuis la 8.0.

CREATE ROLE 'r_technicien', 'r_lecture';

GRANT SELECT ON parc.* TO 'r_technicien';
GRANT INSERT, UPDATE ON parc.ticket TO 'r_technicien';
GRANT INSERT, UPDATE ON parc.intervention TO 'r_technicien';
GRANT INSERT, DELETE ON parc.installation TO 'r_technicien';
GRANT UPDATE (id_employe) ON parc.poste TO 'r_technicien';

GRANT SELECT ON parc.v_charge_technicien TO 'r_lecture';
GRANT SELECT ON parc.v_ticket_detail TO 'r_lecture';

CREATE USER 'chloe'@'10.0.1.%' IDENTIFIED BY 'Phrase-de-passe-de-Chloe-2026';
GRANT 'r_technicien' TO 'chloe'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'chloe'@'10.0.1.%';

Un rôle accordé n'est pas actif par défaut : sans le SET DEFAULT ROLE, Chloé se connecte sans aucun privilège et doit taper SET ROLE 'r_technicien';. SELECT CURRENT_ROLE(); affiche les rôles actifs de la session. Le serveur peut aussi activer tous les rôles accordés à la connexion (variable activate_all_roles_on_login, désactivée par défaut).

Le principe du moindre privilège

Chaque compte reçoit exactement les privilèges dont il a besoin, et rien de plus. Pour la base parc :

Vérifier les droits

SHOW GRANTS FOR 'app_parc'@'10.0.1.20';

GRANT USAGE ON *.* TO `app_parc`@`10.0.1.20`
GRANT SELECT ON `parc`.* TO `app_parc`@`10.0.1.20`
GRANT INSERT, UPDATE ON `parc`.`ticket` TO `app_parc`@`10.0.1.20`
GRANT INSERT ON `parc`.`intervention` TO `app_parc`@`10.0.1.20`

USAGE signifie « aucun privilège » : c'est la ligne que tout compte possède. SHOW GRANTS; sans FOR affiche les droits de ta propre session. Pour un compte qui a des rôles, SHOW GRANTS FOR 'chloe'@'10.0.1.%' USING 'r_technicien'; détaille aussi les privilèges du rôle (une clause propre à MySQL ; MariaDB n'a pas USING).

Quand un compte dépasse ses droits, MySQL répond par l'erreur 1142 :

ERROR 1142 (42000): DELETE command denied to user 'app_parc'@'10.0.1.20' for table 'ticket'

Vues, procédures et droits

Les vues et les procédures stockées affinent les droits au-delà de la colonne. Par défaut, elles s'exécutent avec les droits de leur créateur (SQL SECURITY DEFINER) : un compte qui a SELECT sur v_annuaire lit la vue sans avoir aucun droit sur employe. Il ne voit donc jamais les dates d'embauche, la hiérarchie ni l'IBAN chiffré, quelle que soit la requête qu'il écrit.

CREATE USER 'intranet'@'10.0.1.30' IDENTIFIED BY 'Mot-de-passe-de-l-intranet-2026';
GRANT SELECT ON parc.v_annuaire TO 'intranet'@'10.0.1.30';

De la même façon, au lieu de donner UPDATE sur ticket à l'application, tu peux ne lui donner que EXECUTE sur une procédure cloturer_ticket : elle ne peut alors clôturer un ticket que de la manière prévue, avec l'intervention et la date. SQL SECURITY INVOKER fait au contraire vérifier les droits de l'utilisateur qui appelle.

Traduire une matrice d'habilitations

Les sujets donnent souvent les besoins sous forme d'un tableau : les profils en lignes, les tables en colonnes, et dans chaque case les opérations permises (le chapitre Matrice d'habilitations du cours Authentification explique comment on la construit et la tient à jour). Il suffit de le traduire case par case. Par exemple :

CREATE ROLE 'r_responsable', 'r_accueil';

GRANT SELECT ON parc.ticket       TO 'r_responsable';
GRANT SELECT ON parc.intervention TO 'r_responsable';
GRANT SELECT ON parc.poste        TO 'r_responsable';
GRANT SELECT ON parc.v_annuaire   TO 'r_responsable';

GRANT SELECT, INSERT ON parc.ticket TO 'r_accueil';
GRANT SELECT ON parc.v_annuaire     TO 'r_accueil';

Une case « Aucun » ne demande aucune instruction : par défaut, un compte n'a aucun droit.

Exercices

CREATE USER 'reporting'@'10.0.5.20' IDENTIFIED BY 'Phrase-de-passe-du-reporting-2026';
GRANT SELECT ON parc.v_charge_technicien TO 'reporting'@'10.0.5.20';
GRANT SELECT ON parc.v_ticket_detail     TO 'reporting'@'10.0.5.20';

GRANT SELECT, INSERT, UPDATE ON parc.poste        TO 'gestion_parc'@'localhost';
GRANT SELECT, INSERT, DELETE ON parc.installation TO 'gestion_parc'@'localhost';
GRANT SELECT ON parc.logiciel                     TO 'gestion_parc'@'localhost';
GRANT SELECT (nom, prenom, id_service) ON parc.employe TO 'gestion_parc'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO `app_parc`@`%`

REVOKE ALL PRIVILEGES ON *.* FROM 'app_parc'@'%';

GRANT SELECT ON parc.*                 TO 'app_parc'@'%';
GRANT INSERT, UPDATE ON parc.ticket    TO 'app_parc'@'%';
GRANT INSERT ON parc.intervention      TO 'app_parc'@'%';

CREATE USER 'moussa'@'10.0.1.%' IDENTIFIED BY 'Phrase-de-passe-de-Moussa-2026';
GRANT 'r_technicien' TO 'moussa'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'moussa'@'10.0.1.%';

DROP USER 'chloe'@'10.0.1.%';