Exercices corrigés type E6 et E7 SLAM : procédures et triggers

Un déclencheur s'exécute dans le SGBD à chaque modification de la table compte, quel que soit l'outil qui la modifie : la page d'administration, un autre écran, un script, phpMyAdmin ou un client SQL. Une trace écrite dans le code PHP ne couvrirait que la page d'administration : précisément, le rapport pointe un technicien qui accéderait directement à la base. La règle est écrite une seule fois et ne peut pas être oubliée par un développeur. Enfin, le compte de l'application n'a besoin d'aucun droit d'écriture sur journal_securite : le déclencheur l'alimente avec les droits de son propriétaire, et un attaquant passé par l'application ne peut pas effacer ses traces.

DROP TRIGGER IF EXISTS trg_compte_journal;   -- s'il existe depuis le chapitre 9
DELIMITER //
CREATE TRIGGER trg_compte_journal
AFTER UPDATE ON compte
FOR EACH ROW
BEGIN
  IF OLD.role <> NEW.role THEN
    INSERT INTO journal_securite (evenement, id_compte, detail, auteur)
    VALUES ('role_modifie', NEW.id_compte, CONCAT(OLD.role, ' -> ', NEW.role),
            COALESCE(@utilisateur_appli, USER()));
  END IF;
END //
DELIMITER ;

AFTER : on ne trace qu'un changement réellement enregistré. Le test OLD.role <> NEW.role évite une trace à chaque modification d'un autre champ (un mot de passe, l'état actif). role est NOT NULL : <> suffit. USER(), et non CURRENT_USER(), qui donnerait le propriétaire du déclencheur.

DELIMITER //
CREATE TRIGGER trg_compte_controle_admin
BEFORE UPDATE ON compte
FOR EACH ROW
BEGIN
  IF NEW.role = 'admin' AND OLD.role <> 'admin'
     AND NOT EXISTS (SELECT 1 FROM compte
                     WHERE login = @utilisateur_appli AND role = 'admin' AND actif) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Seul un administrateur peut attribuer le rôle admin';
  END IF;
END //
DELIMITER ;

Essais : avec @utilisateur_appli = 'clambert' (technicienne), UPDATE compte SET role = 'admin' WHERE id_compte = 5 est refusé ; avec 'amercier' (administrateur), la promotion d'Hélène Garnier passe et le déclencheur de la question 2 la trace. BEFORE, parce qu'il faut refuser avant l'écriture. Lire la table compte dans un déclencheur de compte est permis ; seule sa modification est interdite (erreur 1442).

Depuis un client SQL sans la variable, @utilisateur_appli vaut NULL, la sous-requête ne trouve rien : la promotion est refusée, même pour root. C'est voulu (l'administrateur passe par l'application), mais la protection a une limite : la variable est déclarative, et quelqu'un qui se connecte directement avec un compte autorisé à modifier compte peut écrire SET @utilisateur_appli = 'amercier'. Elle complète donc les droits MySQL, elle ne les remplace pas : seul le compte de l'application et les administrateurs de la base doivent avoir UPDATE sur compte, et les techniciens n'ont aucun accès direct.

-- (a)
SELECT id_compte, COUNT(*) AS changements
FROM journal_securite
WHERE evenement = 'role_modifie'
  AND date_evenement >= NOW() - INTERVAL 7 DAY
GROUP BY id_compte
HAVING COUNT(*) > 1;

-- (b) l'auteur est un compte MySQL (« utilisateur@hôte »), pas un login de l'application
SELECT id_journal, id_compte, detail, auteur
FROM journal_securite
WHERE evenement = 'role_modifie'
  AND auteur LIKE '%@%';

(a) renvoie le compte 1 avec 2 changements : Hélène Garnier est passée administratrice puis est revenue responsable, un aller-retour qui mérite une question. (b) renvoie la ligne demandeur -> technicien du compte 2, d'auteur root@localhost : un changement fait directement dans la base, à vérifier. Le filtre sur la date est dans le WHERE, le seuil sur le nombre dans le HAVING.

REVOKE ALL PRIVILEGES ON parc.* FROM 'helpdesk'@'192.168.21.20';

GRANT SELECT ON parc.ticket       TO 'helpdesk'@'192.168.21.20';
GRANT SELECT ON parc.intervention TO 'helpdesk'@'192.168.21.20';
GRANT SELECT ON parc.poste        TO 'helpdesk'@'192.168.21.20';
GRANT SELECT ON parc.employe      TO 'helpdesk'@'192.168.21.20';
GRANT SELECT, UPDATE ON parc.compte TO 'helpdesk'@'192.168.21.20';
GRANT UPDATE ON parc.ticket       TO 'helpdesk'@'192.168.21.20';
GRANT INSERT ON parc.intervention TO 'helpdesk'@'192.168.21.20';

Oui : un déclencheur s'exécute avec les droits de son propriétaire (DEFINER), pas avec ceux du compte qui modifie la table. Il suffit que le propriétaire ait INSERT sur journal_securite et le privilège TRIGGER sur compte (chapitre 11). Le compte helpdesk ne peut ni lire, ni modifier, ni effacer les traces.

La durée est fixée par le responsable du traitement selon la finalité (pouvoir enquêter sur un incident) : pour des journaux de ce type, la recommandation de la CNIL de 2021 donne un repère de six mois à un an (quoi journaliser et combien de temps). Elle s'écrit dans le registre des traitements (conformité RGPD) et dans la politique de journalisation. Dans MySQL, un événement planifié l'applique :

CREATE EVENT ev_purge_journal_securite
  ON SCHEDULE EVERY 1 DAY STARTS '2026-11-09 03:30:00'
  DO DELETE FROM journal_securite WHERE date_evenement < NOW() - INTERVAL 1 YEAR;

Vérifie que le planificateur tourne (event_scheduler à ON, valeur par défaut de MySQL 8.4) et surveille la colonne last_executed d'information_schema.events.

(1) Le déclencheur. La règle doit s'appliquer « quel que soit l'outil » : c'est un déclencheur, pas un contrôle dans l'application.

DELIMITER //
CREATE TRIGGER trg_intervention_ticket_ouvert
BEFORE INSERT ON intervention
FOR EACH ROW
BEGIN
  DECLARE v_statut VARCHAR(10);
  SELECT statut INTO v_statut FROM ticket WHERE id_ticket = NEW.id_ticket;
  IF v_statut = 'resolu' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Ticket résolu : rouvre-le avant d''ajouter une intervention';
  END IF;
END //
DELIMITER ;

(2) La procédure, qui gère sa transaction :

DELIMITER //
CREATE PROCEDURE rouvrir_ticket(IN p_id_ticket INT, IN p_motif VARCHAR(200))
COMMENT 'Gère sa propre transaction. Erreur 1644 : ticket inconnu ou non résolu.'
BEGIN
  DECLARE v_statut VARCHAR(10);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SELECT statut INTO v_statut FROM ticket WHERE id_ticket = p_id_ticket FOR UPDATE;
  IF v_statut IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ticket inconnu';
  ELSEIF v_statut <> 'resolu' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Seul un ticket résolu peut être rouvert';
  END IF;

  UPDATE ticket
  SET statut = 'en_cours', date_cloture = NULL,
      titre = LEFT(CONCAT(titre, ' (rouvert)'), 100)
  WHERE id_ticket = p_id_ticket;

  INSERT INTO journal_securite (evenement, id_compte, detail, auteur)
  VALUES ('ticket_rouvert', 0, LEFT(CONCAT('ticket ', p_id_ticket, ' : ', p_motif), 255),
          COALESCE(@utilisateur_appli, USER()));
  COMMIT;
END //
DELIMITER ;

LEFT(…, 100) protège la colonne titre (VARCHAR(100)) : en mode strict, un titre trop long ferait échouer l'UPDATE (erreur 1406). Si le déclencheur de clôture du chapitre 8 existe, il efface déjà la date ; l'écrire ici rend la procédure juste même sans lui.

(3) Le script de test, sur la base fraîche :

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
VALUES (10, 11, '2026-11-03 09:00:00', 20);
-- ERROR 1644 (45000): Ticket résolu : rouvre-le avant d'ajouter une intervention

CALL rouvrir_ticket(10, 'Écran noir revenu');            -- réussit
CALL rouvrir_ticket(10, 'deux fois');
-- ERROR 1644 (45000): Seul un ticket résolu peut être rouvert
CALL rouvrir_ticket(99, 'x');
-- ERROR 1644 (45000): Ticket inconnu

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
VALUES (10, 11, '2026-11-03 09:00:00', 20);              -- acceptée

SELECT titre, statut, date_cloture FROM ticket WHERE id_ticket = 10;
-- Écran noir au démarrage (rouvert) | en_cours | NULL

(4) L'annulation : DROP TRIGGER IF EXISTS trg_intervention_ticket_ouvert; DROP PROCEDURE IF EXISTS rouvrir_ticket;. Les tickets déjà rouverts restent rouverts : annuler le code n'annule pas les données.

À l'entretien, prépare deux réponses : pourquoi un déclencheur pour l'interdiction (la règle vaut pour tous les outils) mais une procédure pour la réouverture (une opération en plusieurs étapes, appelée volontairement, avec un motif) ; et pourquoi FOR UPDATE (deux techniciens qui rouvrent en même temps ne créent pas deux traces).

1. Il manque FOR EACH ROW, obligatoire en MySQL : … ON poste FOR EACH ROW BEGIN … END.
2. Les variables se déclarent avant les curseurs (erreur 1337) : DECLARE v_id INT; d'abord.
3. NEW ne se modifie que dans un trigger BEFORE (erreur 1362). Il faut BEFORE UPDATE.
4. = NULL n'est jamais vrai : la comparaison vaut NULL. Il faut IF v_licences IS NULL THEN.
5. Un trigger ne peut pas modifier sa propre table (erreur 1442 à l'exécution). Pour fixer la date de la ligne insérée, un trigger BEFORE INSERT avec SET NEW.date_installation = CURDATE();.
6. Le gestionnaire annule mais ne relance pas l'erreur : l'appelant croit que l'opération a réussi. Il faut BEGIN ROLLBACK; RESIGNAL; END.

1. L'INSERT déclenche trg_intervention_prise_en_charge, qui passe le ticket 12 de ouvert à en_cours. Cet UPDATE déclenche à son tour trg_ticket_cloture (le statut n'est pas résolu : date_cloture reste NULL) et trg_ticket_audit_statut (trace ouvert → en_cours).
2. Le trigger de l'intervention s'exécute, mais son UPDATE … WHERE statut = 'ouvert' ne touche aucune ligne : le ticket est déjà en cours. Aucun trigger de ticket ne s'exécute, aucune trace.
3. trg_ticket_cloture renseigne date_cloture avec l'heure courante ; trg_ticket_audit_statut trace en_cours → resolu.
4. Les deux triggers de ticket s'exécutent, mais ne font rien : le statut ne change pas.
5. Le ticket est déjà résolu : trg_ticket_cloture ne fait rien (ni première branche, l'ancien statut étant déjà resolu, ni seconde), et la date fournie, le 1er janvier 2026, est antérieure à l'ouverture du ticket (21 octobre 2026). La contrainte ck_ticket_dates refuse l'instruction : ERROR 3819 (HY000): Check constraint 'ck_ticket_dates' is violated.

audit_ticket contient 2 lignes : ouvert → en_cours et en_cours → resolu. Le ticket 12 finit resolu, en priorité haute, avec la date de clôture de l'instruction (3).