Trigger d'audit et d'historique : tracer les modifications

Un trigger d'audit enregistre automatiquement, dans une table dédiée, qui a modifié quoi et quand ; un trigger d'historique conserve les anciennes valeurs d'une ligne avant qu'elles soient écrasées. Comme le trigger s'exécute à chaque modification, quelle que soit l'application, la trace ne dépend pas de la bonne volonté des développeurs. C'est le type de trigger le plus fréquent à l'E7 SLAM, parce qu'il répond au critère de preuve (la traçabilité) de la sécurité. Ce chapitre construit l'audit des tickets, l'historique des postes, le journal de sécurité des comptes et l'historique des mots de passe.

Concevoir une table d'audit

Une table d'audit répond à quatre questions : quoi (la table, la ligne, l'ancienne et la nouvelle valeur), qui (le compte), quand (la date et l'heure), et parfois d'où (l'adresse du client). La table audit_ticket du script complémentaire :

CREATE TABLE audit_ticket (
  id_audit       INT AUTO_INCREMENT PRIMARY KEY,
  id_ticket      INT          NOT NULL,          -- volontairement sans clé étrangère
  ancien_statut  VARCHAR(10)  NOT NULL,
  nouveau_statut VARCHAR(10)  NOT NULL,
  modifie_par    VARCHAR(300) NOT NULL,
  modifie_le     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Trois choix de conception à savoir justifier :

• pas de clé étrangère vers ticket : la trace doit survivre à la suppression du ticket. Une clé étrangère interdirait de supprimer un ticket audité, ou supprimerait ses traces en cascade ;
• une table par sujet, plutôt qu'une table fourre-tout : les colonnes sont typées, les requêtes de contrôle restent simples ;
• la date par défaut (DEFAULT CURRENT_TIMESTAMP) : le trigger n'a pas à la fournir, et l'horloge est celle du serveur, pas celle d'un poste client.

Tracer les changements de statut

Les résultats de ce chapitre supposent la base remise à zéro au début du chapitre (voir L'essentiel) : le ticket 6 est encore en_cours et le ticket 7 ouvert.

DELIMITER //
CREATE TRIGGER trg_ticket_audit_statut
AFTER UPDATE ON ticket
FOR EACH ROW
BEGIN
  IF OLD.statut <> NEW.statut THEN
    INSERT INTO audit_ticket (id_ticket, ancien_statut, nouveau_statut, modifie_par)
    VALUES (OLD.id_ticket, OLD.statut, NEW.statut, USER());
  END IF;
END //
DELIMITER ;

UPDATE ticket SET statut = 'en_cours' WHERE id_ticket = 7;
UPDATE ticket SET priorite = 'haute' WHERE id_ticket = 7;     -- pas de trace : le statut ne change pas
UPDATE ticket SET statut = 'resolu', date_cloture = '2026-11-08 10:00:00'
WHERE statut = 'en_cours' AND id_ticket IN (6, 7);            -- deux lignes, deux traces

SELECT id_audit, id_ticket, ancien_statut, nouveau_statut, modifie_par FROM audit_ticket;

Le trigger est AFTER : il ne trace que les modifications qui ont réellement eu lieu. Le test OLD.statut <> NEW.statut évite de remplir la table à chaque UPDATE qui ne touche pas au statut. Le dernier UPDATE modifie deux lignes : le trigger s'exécute deux fois.

USER() ou CURRENT_USER() ?

Les deux fonctions répondent à « qui ? », mais pas de la même façon dans un trigger :

Dans une procédure ou un trigger exécuté avec les droits de son propriétaire, CURRENT_USER() ne dit donc rien de l'auteur. Si un code de sujet trace CURRENT_USER() dans une table d'audit et que la question porte sur l'imputabilité, signale la différence.

Historiser une valeur qui peut être NULL

La table historique_affectation garde la trace de chaque changement d'utilisateur d'un poste. Un poste en stock a id_employe à NULL, et c'est là que le piège apparaît :

-- Première version, fausse
IF OLD.id_employe <> NEW.id_employe THEN
  INSERT INTO historique_affectation …
END IF;

Quand le PC-0014, en stock, est affecté à Paul Martin, OLD.id_employe vaut NULL : NULL <> 14 ne vaut pas vrai mais NULL, et rien n'est tracé. Même chose quand un poste retourne en stock. Pour comparer en tenant compte de NULL, MySQL a l'opérateur <=> (« égal, NULL compris ») : NULL <=> NULL vaut 1, NULL <=> 14 vaut 0.

DELIMITER //
CREATE TRIGGER trg_poste_affectation
AFTER UPDATE ON poste
FOR EACH ROW
BEGIN
  IF NOT (OLD.id_employe <=> NEW.id_employe) THEN
    INSERT INTO historique_affectation (id_poste, ancien_employe, nouvel_employe, modifie_par)
    VALUES (OLD.id_poste, OLD.id_employe, NEW.id_employe, COALESCE(@utilisateur_appli, USER()));
  END IF;
END //
DELIMITER ;

NOT (a <=> b) est vrai dès que les deux valeurs diffèrent, NULL compris. La norme SQL écrit la même chose a IS DISTINCT FROM b, que PostgreSQL connaît et que MySQL 8.4 ne connaît pas. Le COALESCE(@utilisateur_appli, …) est expliqué juste après.

Imputer la modification à une personne

Une application web se connecte à la base avec un seul compte pour tous ses utilisateurs. USER() renvoie donc toujours helpdesk@192.168.21.20, que la modification vienne de Chloé Lambert ou d'Antoine Mercier : la trace dit quelle application, pas quelle personne. Or l'E7 attend une trace imputable à une personne.

La solution courante : l'application, après avoir authentifié l'utilisateur, écrit son identifiant dans une variable de session de la connexion, que le trigger lit.

SET @utilisateur_appli = 'clambert';           -- envoyé par l'application juste après la connexion
UPDATE poste SET id_employe = 14 WHERE id_poste = 13;
UPDATE poste SET id_employe = NULL WHERE id_poste = 13;
SET @utilisateur_appli = NULL;

UPDATE poste SET id_employe = 14 WHERE id_poste = 14;    -- depuis un client SQL, sans variable

SELECT id_poste, ancien_employe, nouvel_employe, modifie_par FROM historique_affectation;

Les deux premières lignes sont imputées à la technicienne connectée à l'application ; la dernière, faite dans un client SQL sans variable, au compte MySQL. Un UPDATE qui ne change pas l'affectation (le prix, par exemple) n'écrit rien.

Le journal de sécurité des comptes

Les changements de rôle et les désactivations de comptes de l'application sont des événements de sécurité : un rôle admin attribué en douce est un signe d'intrusion. Un trigger AFTER UPDATE sur compte les inscrit dans journal_securite :

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;
  IF OLD.actif AND NOT NEW.actif THEN
    INSERT INTO journal_securite (evenement, id_compte, detail, auteur)
    VALUES ('compte_desactive', NEW.id_compte, NULL, COALESCE(@utilisateur_appli, USER()));
  END IF;
END //
DELIMITER ;

SET @utilisateur_appli = 'amercier';
UPDATE compte SET role = 'technicien' WHERE id_compte = 2;   -- Karim Morel
UPDATE compte SET actif = FALSE WHERE id_compte = 3;         -- Sofiane Benali
SET @utilisateur_appli = NULL;

SELECT id_journal, evenement, id_compte, detail, auteur FROM journal_securite;

Un même trigger peut écrire plusieurs traces : une modification qui change à la fois le rôle et l'activation en produit deux.

L'historique des mots de passe

Une politique de mots de passe interdit souvent de réutiliser l'un des derniers mots de passe. Pour cela, il faut garder les anciennes empreintes. Le sujet de l'E7 2025 (Casterman) donnait un trigger qui devait conserver les cinq dernières, et qui en réalité vidait la table ; le corrigé le déroule ligne par ligne. Voici une version juste sur la table compte :

DELIMITER //
CREATE TRIGGER trg_compte_historique_mdp
BEFORE UPDATE ON compte
FOR EACH ROW
BEGIN
  DECLARE v_limite DATETIME(6);

  IF NEW.mot_de_passe <> OLD.mot_de_passe THEN
    -- 1. Garder l'ancienne empreinte
    INSERT INTO historique_mdp (id_compte, mot_de_passe, remplace_le)
    VALUES (OLD.id_compte, OLD.mot_de_passe, NOW(6));

    -- 2. La 5e empreinte la plus récente ; s'il y en a moins de 5, v_limite reste NULL
    SELECT remplace_le INTO v_limite
    FROM historique_mdp
    WHERE id_compte = OLD.id_compte
    ORDER BY remplace_le DESC
    LIMIT 1 OFFSET 4;

    -- 3. Supprimer les plus anciennes ; avec v_limite à NULL, la condition n'est jamais vraie
    DELETE FROM historique_mdp
    WHERE id_compte = OLD.id_compte AND remplace_le < v_limite;
  END IF;
END //
DELIMITER ;

Après sept changements de mot de passe du compte 5, la table historique_mdp contient exactement cinq empreintes pour ce compte : celles des cinq mots de passe qui ont précédé le mot de passe actuel, lequel reste dans compte. DATETIME(6) garde les microsecondes, pour que deux changements dans la même seconde ne violent pas la clé primaire (id_compte, remplace_le).

Le trigger peut modifier historique_mdp parce que ce n'est pas la table qui le déclenche (sinon, erreur 1442). Il est BEFORE, mais AFTER conviendrait aussi : il n'a pas besoin de modifier NEW.

Tracer une ligne entière en JSON

Pour garder toute la ligne supprimée sans créer une colonne par colonne d'origine, MySQL sait fabriquer un objet JSON :

DELIMITER //
CREATE TRIGGER trg_poste_suppression
BEFORE DELETE ON poste
FOR EACH ROW
BEGIN
  INSERT INTO journal_securite (evenement, id_compte, detail, auteur)
  VALUES ('poste_supprime', 0,
          JSON_OBJECT('id', OLD.id_poste, 'inventaire', OLD.num_inventaire,
                      'modele', OLD.modele, 'employe', OLD.id_employe),
          COALESCE(@utilisateur_appli, USER()));
END //
DELIMITER ;

DELETE FROM poste WHERE id_poste = 13;
SELECT detail FROM journal_securite WHERE evenement = 'poste_supprime';
-- {"id": 13, "modele": "OptiPlex 7010", "employe": null, "inventaire": "PC-0013"}

JSON_OBJECT trie les clés à sa façon ; l'ordre n'a pas d'importance pour la lecture. La colonne detail fait 255 caractères : pour une ligne plus large, prévois une colonne de type JSON. Le 0 dans id_compte signale un événement qui ne concerne aucun compte ; dans un vrai projet, une table journal_suppression dédiée serait plus propre.

Protéger et exploiter les traces

Une trace qu'on peut modifier ne prouve rien. Quatre mesures :

1. Aucun droit pour l'application sur les tables d'audit. Le trigger s'exécute avec les droits de son propriétaire (chapitre 11) : le compte helpdesk peut modifier un ticket et déclencher l'écriture de la trace sans avoir lui-même le droit d'écrire, de lire ou d'effacer dans audit_ticket.
2. Un compte de lecture seule pour qui consulte les traces : GRANT SELECT ON parc.audit_ticket TO 'r_securite';.
3. Une durée de conservation fixée et appliquée : les traces contiennent des données personnelles (qui a fait quoi), le RGPD impose de ne pas les garder indéfiniment. Un événement planifié les purge (chapitre 10).
4. Une copie hors de la base pour les traces les plus sensibles : envoyées vers un serveur de journaux ou un SIEM, elles échappent à un administrateur de la base qui voudrait effacer les siennes (centraliser les logs).

Les traces servent à détecter. Une requête de contrôle typique des sujets d'E7 : qui a modifié des rôles plus de deux fois dans les dernières 24 heures ?

SELECT auteur, COUNT(*) AS changements_de_role
FROM journal_securite
WHERE evenement = 'role_modifie'
  AND date_evenement >= NOW() - INTERVAL 1 DAY
GROUP BY auteur
HAVING COUNT(*) > 2;

Le filtre sur la période va dans le WHERE, le seuil sur le nombre dans le HAVING (GROUP BY et HAVING).

Les limites de l'audit par trigger

• Un compte qui a le privilège TRIGGER sur la table peut supprimer le trigger, faire ses modifications, puis le recréer. L'audit protège contre les utilisateurs de l'application, pas contre un administrateur de la base. Pour celui-là, on s'appuie sur des journaux du serveur hors de sa portée : le plugin d'audit de MySQL Enterprise (édition commerciale) ou le plugin server_audit de MariaDB, envoyés vers un SIEM.
• Les cascades de clés étrangères ne déclenchent pas les triggers (chapitre 8).
• TRUNCATE TABLE vide une table sans déclencher aucun trigger DELETE.
• Chaque trace alourdit les écritures : sur une table très sollicitée, trace seulement les colonnes qui comptent.

Exercices

CREATE TABLE audit_suppression (
  id_audit     INT AUTO_INCREMENT PRIMARY KEY,
  table_cible  VARCHAR(64)  NOT NULL,
  id_ligne     INT          NOT NULL,
  contenu      JSON,
  supprime_par VARCHAR(300) NOT NULL,
  supprime_le  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_intervention_audit_suppression
AFTER DELETE ON intervention
FOR EACH ROW
BEGIN
  INSERT INTO audit_suppression (table_cible, id_ligne, contenu, supprime_par)
  VALUES ('intervention', OLD.id_intervention,
          JSON_OBJECT('ticket', OLD.id_ticket, 'technicien', OLD.id_technicien,
                      'duree_min', OLD.duree_min),
          COALESCE(@utilisateur_appli, USER()));
END //
DELIMITER ;

CREATE TABLE historique_prix (
  id_historique INT AUTO_INCREMENT PRIMARY KEY,
  id_poste      INT           NOT NULL,
  ancien_prix   DECIMAL(8,2)  NOT NULL,
  nouveau_prix  DECIMAL(8,2)  NOT NULL,
  modifie_par   VARCHAR(300)  NOT NULL,
  modifie_le    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_poste_historique_prix
AFTER UPDATE ON poste
FOR EACH ROW
BEGIN
  IF OLD.prix_achat <> NEW.prix_achat THEN
    INSERT INTO historique_prix (id_poste, ancien_prix, nouveau_prix, modifie_par)
    VALUES (OLD.id_poste, OLD.prix_achat, NEW.prix_achat, COALESCE(@utilisateur_appli, USER()));
  END IF;
END //
DELIMITER ;

CREATE TRIGGER trg_mdp
BEFORE UPDATE ON compte
FOR EACH ROW
BEGIN
  DECLARE v_nb INT;
  IF OLD.mot_de_passe <> NEW.mot_de_passe THEN
    INSERT INTO historique_mdp VALUES (OLD.id_compte, OLD.mot_de_passe, NOW(6));
    SELECT COUNT(*) INTO v_nb FROM historique_mdp WHERE id_compte = OLD.id_compte;
    DELETE FROM historique_mdp
    WHERE id_compte = OLD.id_compte
    ORDER BY remplace_le
    LIMIT 1;
  END IF;
END

DELIMITER //
CREATE TRIGGER trg_mdp
BEFORE UPDATE ON compte
FOR EACH ROW
BEGIN
  DECLARE v_nb INT;
  IF OLD.mot_de_passe <> NEW.mot_de_passe THEN
    INSERT INTO historique_mdp VALUES (OLD.id_compte, OLD.mot_de_passe, NOW(6));
    SELECT COUNT(*) INTO v_nb FROM historique_mdp WHERE id_compte = OLD.id_compte;
    IF v_nb > 3 THEN
      DELETE FROM historique_mdp
      WHERE id_compte = OLD.id_compte
      ORDER BY remplace_le
      LIMIT 1;
    END IF;
  END IF;
END //
DELIMITER ;