Habilitations, droits et SQL de sécurité

Les questions d'habilitations demandent de vérifier qu'un utilisateur, ou une application, n'a que les droits dont il a besoin, puis de corriger ces droits en SQL ou dans le code. Elles s'accompagnent presque toujours de SQL de sécurité : un compte MySQL à restreindre, une vue qui masque des colonnes, un déclencheur à corriger, une colonne à chiffrer, une requête d'audit sur un journal. Ce chapitre traite ces six situations avec la syntaxe de MySQL 8.4 et des réponses modèles.

Le principe du moindre privilège

Le moindre privilège veut qu'un utilisateur, un programme ou un compte technique ne dispose que des droits strictement nécessaires à sa fonction. Il limite les dégâts d'une erreur ou d'une attaque : une injection SQL menée à travers un compte qui n'a que SELECT sur trois tables ne peut ni supprimer une base, ni créer un compte.

Il se décline à trois niveaux, que les sujets interrogent tous :

Modéliser des habilitations

Les sujets demandent souvent de faire évoluer un schéma de données pour gérer des droits. Trois modèles reviennent.

Fonction et droits (2025, cas Casterman). Chaque utilisateur a une fonction ; chaque fonction possède des droits ; un droit associe une table et une opération.

Fonction (id, libelle)
Utilisateur (id, identifiant, empreinteMdp, idFonction)
    idFonction : clé étrangère en référence à id de Fonction
Droit (idFonction, nomTable, operation)
    clé primaire : (idFonction, nomTable, operation)
    idFonction : clé étrangère en référence à id de Fonction

Profils et habilitations ponctuelles (2025, cas Kirassur). Un profil regroupe des actions ; un utilisateur reçoit des profils sur une période, et des habilitations ponctuelles sur une action, en complément.

Rôles sur un contrat (2026, cas NDV, organisation « miroir »). Pour chaque contrat, des rôles génériques (chef de projet, DPD, responsable des API…) sont tenus par une personne de l'agence et par son homologue chez le client. La question A3.1 demandait d'intégrer ce besoin dans le schéma de la base. Une proposition de correction :

Role (id, libelle)
    clé primaire : id

Affectation (idContrat, idRole, matriculeCollaborateur, idContactExterne)
    clé primaire : (idContrat, idRole)
    idContrat : clé étrangère en référence à id de Contrat
    idRole : clé étrangère en référence à id de Role
    matriculeCollaborateur : clé étrangère en référence à matricule de Collaborateur
    idContactExterne : clé étrangère en référence à id de ContactExterne

La clé primaire (idContrat, idRole) garantit un seul binôme par rôle et par contrat ; rien n'empêche une même personne d'occuper plusieurs rôles, ce que le sujet autorisait. La contrainte du sujet (le contact doit travailler pour l'organisation cliente du contrat) reste à vérifier, par exemple par un déclencheur. Les colonnes idReferent et idResponsable de Contrat peuvent être conservées ou remplacées par le rôle « chef de projet » ; les deux choix se défendent s'ils sont justifiés.

Comptes et privilèges MySQL

Un compte MySQL est identifié par un nom et un hôte : 'appli'@'localhost' et 'appli'@'%' sont deux comptes différents. L'hôte % signifie « depuis n'importe quelle adresse » : pour un compte applicatif dont le serveur web tourne sur la même machine que la base, c'est une ouverture inutile.

Les privilèges se donnent à un niveau précis : global (*.*), base (ndv.*), table (ndv.contrat) ou colonne. Un REVOKE doit viser le même niveau que le GRANT : retirer ON ndv.* ne retire pas un privilège accordé ON *.*.

Corriger les droits d'un compte applicatif

Voici une situation du type de la question A1.2 du sujet 2026. Le compte web_crm, utilisé par le site de l'intranet hébergé sur le même serveur que la base crm, devrait seulement lire, ajouter, modifier et supprimer des lignes, et seulement depuis le serveur local. SHOW GRANTS affiche :

GRANT USAGE ON *.* TO `web_crm`@`%`
GRANT ALL PRIVILEGES ON `crm`.* TO `web_crm`@`%` WITH GRANT OPTION

Et le journal des connexions contient, entre autres, ces lignes :

2027-02-11 22:41:07 [CONNEXION] [web_crm]@[91.204.18.77] Connect web_crm@91.204.18.77 on crm
2027-02-11 22:41:52 [REQUETE]   [web_crm]@[91.204.18.77] CREATE USER 'support'@'%' IDENTIFIED BY ...
2027-02-11 22:42:10 [REQUETE]   [web_crm]@[91.204.18.77] GRANT ALL ON crm.* TO 'support'@'%'

Ce que prouvent les journaux. Le compte se connecte depuis 91.204.18.77, une adresse qui n'est pas celle du serveur local (localhost ou 127.0.0.1). Il exécute CREATE USER et GRANT, des opérations d'administration qui dépassent la lecture et la modification de données.

Ce que prouvent les habilitations. L'hôte % autorise la connexion depuis n'importe quelle adresse. ALL PRIVILEGES donne tous les droits sur la base, y compris DROP, ALTER et CREATE, et WITH GRANT OPTION permet de transmettre ces droits à d'autres comptes.

Les requêtes de correction, dans l'ordre :

-- 1. Limiter la connexion au serveur local (les privilèges suivent le compte renommé)
RENAME USER 'web_crm'@'%' TO 'web_crm'@'localhost';
-- 2. Retirer tous les privilèges sur la base, puis le droit de les transmettre
REVOKE ALL PRIVILEGES ON crm.* FROM 'web_crm'@'localhost';
REVOKE GRANT OPTION ON crm.* FROM 'web_crm'@'localhost';
-- 3. Donner seulement les privilèges nécessaires
GRANT SELECT, INSERT, UPDATE, DELETE ON crm.* TO 'web_crm'@'localhost';
-- 4. Supprimer le compte créé par l'attaquant
DROP USER 'support'@'%';

La dernière requête n'était pas demandée explicitement dans ce type de question, mais un compte créé lors d'une intrusion doit être supprimé : le mentionner montre que tu as lu le journal jusqu'au bout. Il faudrait aussi changer le mot de passe de web_crm, puisqu'il est connu de l'attaquant (ALTER USER 'web_crm'@'localhost' IDENTIFIED BY '…';), puis mettre à jour la configuration de l'application.

Restreindre les données avec une vue

Une vue est une requête SELECT enregistrée sous un nom. Donner SELECT sur la vue, et rien sur les tables, limite ce qu'un compte voit : certaines colonnes, certaines lignes.

-- Le service commercial voit les contacts sans leur téléphone personnel
CREATE VIEW v_contacts_commerciaux AS
SELECT ce.id, ce.nom, ce.prenom, o.denomination
FROM ContactExterne ce
JOIN OrganisationCliente o ON o.id = ce.idOrganisation;

GRANT SELECT ON ndv.v_contacts_commerciaux TO 'commercial'@'localhost';

Par défaut, une vue MySQL s'exécute avec les droits de son créateur (SQL SECURITY DEFINER) : le compte commercial n'a besoin d'aucun droit sur les tables sous-jacentes. Une vue peut aussi unifier plusieurs sources avec UNION, à condition que chaque SELECT renvoie le même nombre de colonnes, de types compatibles. Le chapitre Vues SQL du cours SQL détaille la syntaxe.

Lire et corriger un déclencheur

Un déclencheur (trigger) est un traitement que le SGBD exécute automatiquement avant ou après un INSERT, un UPDATE ou un DELETE sur une table. Les sujets l'utilisent pour trois usages de sécurité.

Pourquoi un déclencheur plutôt qu'un contrôle dans le code ? Parce qu'il s'exécute quelle que soit l'application qui modifie la table : le site, une API, un outil interne, un script. Il ne peut pas être oublié par un développeur ni contourné par un client qui appelle la base directement. C'est l'argument attendu quand un sujet demande de justifier ce choix.

La lecture d'un déclencheur bogué suit toujours la même méthode : dérouler le code ligne par ligne sur un exemple chiffré. Le sujet 2025 donnait un déclencheur censé garder les cinq derniers mots de passe ; la table d'historique restait vide. En déroulant avec un historique vide : l'ancien mot de passe est inséré (une ligne), le nombre de lignes est compté (1), puis la ligne la plus ancienne est supprimée sans condition : on supprime celle qu'on vient d'insérer. La correction tient en deux lignes : entourer le DELETE d'un IF v_nb > 5 THEN … END IF;. Le corrigé du sujet Casterman déroule le code complet.

Le cours Procédures stockées et triggers reprend la syntaxe complète.

Chiffrer une colonne

Pour protéger une donnée qu'il faut pouvoir relire (une adresse électronique, un IBAN), on la chiffre : c'est réversible avec la clé. Pour un mot de passe, qu'on n'a jamais besoin de relire, on calcule une empreinte (hachage) : c'est irréversible. Ne confonds jamais les deux.

MySQL fournit AES_ENCRYPT(texte, cle), qui renvoie une chaîne binaire, et AES_DECRYPT(binaire, cle). La colonne qui reçoit la valeur chiffrée doit donc être de type binaire, VARBINARY ou BLOB. Le sujet 2026 demandait de chiffrer les adresses électroniques des contacts existants, avec une colonne VARBINARY(200). Une proposition de correction pour la question A2.4 :

-- 1. Passer la colonne en binaire (la conversion depuis VARCHAR est sans perte)
ALTER TABLE ContactExterne MODIFY adresseMel VARBINARY(200);
-- 2. Chiffrer les adresses déjà présentes
UPDATE ContactExterne SET adresseMel = AES_ENCRYPT(adresseMel, 'lhh4&abt2!');

Ensuite, chaque lecture doit déchiffrer, avec la clé passée en paramètre et jamais écrite en dur dans le code. Pour la question A2.5, la requête préparée de getContact() devient :

$sql = "SELECT ce.nom, ce.prenom,
               AES_DECRYPT(ce.adresseMel, :cle) AS adresseMel,
               ce.telephone
        FROM Contrat c
        JOIN ContactExterne ce ON c.idReferent = ce.id
        WHERE c.id = :id";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':cle', $_ENV['AES_key'], PDO::PARAM_STR);
$stmt->bindValue(':id', $idContrat, PDO::PARAM_INT);

L'alias AS adresseMel garde le nom de clé attendu par le reste du code. Sans lui, la colonne porterait le nom de l'expression calculée (et même, avec la configuration par défaut de PDO, la clé en clair) : $contact['adresseMel'] n'existerait plus et l'affichage resterait vide.

Requêtes d'audit sur un journal

Les sujets demandent d'exploiter une table de journal pour détecter un comportement anormal. Trois outils suffisent : le filtre (WHERE), le regroupement (GROUP BY) et le filtre sur les groupes (HAVING).

-- Les utilisateurs qui ont fait plus de 5 ajouts dans BandeDessinee depuis 8 h ce matin
SELECT idUtilisateur, COUNT(*) AS nbAjouts
FROM Log
WHERE nomTable = 'BandeDessinee'
  AND operation = 'insert'
  AND dateHeure >= CONCAT(CURRENT_DATE(), ' 08:00:00')
GROUP BY idUtilisateur
HAVING COUNT(*) > 5;

WHERE élimine les lignes avant le regroupement, HAVING élimine les groupes après le calcul de COUNT(*). Une condition sur un agrégat écrite dans le WHERE provoque une erreur : c'est le piège classique. En 2025 (question C2.3), la requête d'un collègue oubliait carrément le COUNT, le GROUP BY et le HAVING : il fallait expliquer qu'elle listait des ajouts au lieu de compter par utilisateur, puis la corriger.

Pour les dates, utilise les fonctions que le dossier documentaire fournit. En 2025, il décrivait une fonction datestampdiff(unite, date1, date2) ; la fonction réelle de MySQL s'appelle TIMESTAMPDIFF, avec les mêmes paramètres. Sur ta copie, écris celle du document. Les autres fonctions utiles de MySQL : NOW(), CURRENT_DATE(), DATE(colonne), DATE_SUB(NOW(), INTERVAL 30 MINUTE).

Pour réviser ces clauses, le chapitre GROUP BY, HAVING et COUNT et le chapitre GRANT, REVOKE et rôles du cours SQL contiennent de nombreux exercices.

Garantir la cohérence avec une transaction

Une transaction regroupe plusieurs requêtes qui doivent réussir ensemble ou échouer ensemble : soit toutes sont validées (COMMIT), soit aucune n'est appliquée (ROLLBACK). C'est la propriété d'atomicité. En sécurité, elle protège l'intégrité : une opération interrompue au milieu (erreur, coupure, requête refusée) ne laisse pas la base dans un état incohérent.

START TRANSACTION;
UPDATE Utilisateur SET idRole = 3 WHERE id = 42;
INSERT INTO HistoHabilitation (idUtilisateur, ancienRole, nouveauRole, dateHeure)
VALUES (42, 2, 3, NOW());
COMMIT;   -- ou ROLLBACK; si une requête a échoué

Sans transaction, si l'INSERT échoue, le rôle est changé sans trace : la traçabilité promise est perdue. Avec la transaction, le changement de rôle et sa trace forment un tout. Les transactions fonctionnent sur les tables InnoDB, le moteur par défaut de MySQL.

En PHP, PDO fournit trois méthodes. Depuis PHP 8, PDO lève une exception en cas d'erreur SQL, ce qui permet d'annuler dans le catch :

try {
    $pdo->beginTransaction();
    $pdo->prepare("UPDATE Utilisateur SET idRole = :role WHERE id = :id")
        ->execute([':role' => 3, ':id' => 42]);
    $pdo->prepare("INSERT INTO HistoHabilitation (idUtilisateur, ancienRole, nouveauRole, dateHeure)
                   VALUES (:id, :ancien, :nouveau, NOW())")
        ->execute([':id' => 42, ':ancien' => 2, ':nouveau' => 3]);
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();          // aucune des deux requêtes n'est appliquée
    error_log($e->getMessage());
}

Le sujet 2023 (cas Lama Zoo, question A.6) demandait « l'intérêt d'utiliser des transactions » pour créer ou supprimer un rôle applicatif, qui impose « deux opérations indissociables » : le compte du SGBD et la ligne de la table RoleApplicatif. Réponse attendue : la transaction garantit que les deux opérations sont faites ensemble ou pas du tout ; sans elle, une erreur au milieu laisserait un compte de base sans rôle enregistré, donc des droits que l'application de gestion des habilitations ne voit plus, ou l'inverse. Le code de deleteRole() à compléter suivait le modèle de createRole() :

$req = $this->monPdo->prepare('DROP USER :idUser ;');
$req->bindParam(':idUser', $id, PDO::PARAM_STR);
$resultat = $req->execute();

$req = $this->monPdo->prepare('DELETE FROM RoleApplicatif
                               WHERE idAppli = :idAppli AND idRoleAppli = :idRole ;');
$req->bindParam(':idAppli', $idAppli, PDO::PARAM_STR);
$req->bindParam(':idRole', $id, PDO::PARAM_STR);
$resultat = $resultat + $req->execute();

Le chapitre Transactions SQL : COMMIT et ROLLBACK du cours SQL détaille les propriétés ACID et l'isolation.

Pour aller plus loin. La construction d'une matrice d'habilitations et sa tenue dans le temps sont détaillées dans le chapitre Matrice d'habilitations du cours Authentification, et le chiffrement d'une colonne dans le chapitre Chiffrement symétrique du cours Cryptographie.

Exercices

GRANT USAGE ON *.* TO `reporting`@`%`
GRANT SELECT, INSERT, UPDATE ON `adherents`.* TO `reporting`@`%`

RENAME USER 'reporting'@'%' TO 'reporting'@'10.20.0.15';
REVOKE SELECT, INSERT, UPDATE ON adherents.* FROM 'reporting'@'10.20.0.15';
GRANT SELECT ON adherents.v_stats_mensuelles TO 'reporting'@'10.20.0.15';

DELIMITER //
CREATE TRIGGER controleReservation
BEFORE INSERT ON Reservation
FOR EACH ROW
BEGIN
    IF NEW.dateFin < NEW.dateDebut THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'La date de fin précède la date de début';
    END IF;
    IF DATEDIFF(NEW.dateFin, NEW.dateDebut) > 14 THEN
        SET NEW.statut = 'A_VALIDER';
    END IF;
END //
DELIMITER ;