Sécurité : DEFINER, INVOKER, droit EXECUTE et SQL dynamique

Une procédure, une fonction, un trigger ou un événement s'exécute avec les droits d'un compte : par défaut, ceux de son propriétaire, désigné par la clause DEFINER. C'est ce qui permet de donner à une application le seul droit EXECUTE sur quelques procédures, sans aucun droit sur les tables : elle ne peut faire que ce que les procédures prévoient. Mal maîtrisé, le même mécanisme devient une porte dérobée. Ce chapitre montre comment appliquer le moindre privilège aux routines, éviter l'injection SQL dans le SQL dynamique et surveiller les propriétaires des objets.

DEFINER et SQL SECURITY

Chaque programme stocké a un propriétaire (DEFINER) : par défaut, le compte qui l'a créé. Une procédure ou une fonction a en plus une caractéristique SQL SECURITY qui dit avec quels droits elle s'exécute :

Les triggers et les événements n'ont pas de SQL SECURITY : ils s'exécutent toujours avec les droits de leur propriétaire. Les vues suivent la même règle que les routines (vues et droits).

CREATE DEFINER = 'routines_parc'@'localhost' PROCEDURE cloturer_ticket (…)
SQL SECURITY DEFINER
BEGIN … END

Indiquer un autre compte que le sien dans DEFINER demande le privilège SET_ANY_DEFINER (MySQL 8.2 et plus ; auparavant SET_USER_ID, ou SUPER ; sur MariaDB, SET USER). Sans lui, tu ne peux écrire que DEFINER = CURRENT_USER, ce qui revient à omettre la clause.

Donner le seul droit EXECUTE à l'application

L'application d'assistance tourne sur un serveur du VLAN des applications de Sorbier Logistique, à l'adresse 192.168.21.20. Elle doit pouvoir clôturer des tickets, mais pas modifier un ticket n'importe comment. Le schéma de droits :

1. un compte propriétaire des routines, qui a les droits sur les tables et ne peut pas se connecter ;
2. les procédures créées avec ce propriétaire, en SQL SECURITY DEFINER ;
3. un compte applicatif qui n'a que EXECUTE sur ces procédures, et la lecture de ce qu'il affiche.

-- 1. Le propriétaire : ACCOUNT LOCK interdit toute connexion avec ce compte
CREATE USER 'routines_parc'@'localhost' IDENTIFIED BY 'Mot-de-passe-long-du-proprietaire-2026' ACCOUNT LOCK;
GRANT SELECT, INSERT, UPDATE ON parc.ticket       TO 'routines_parc'@'localhost';
GRANT SELECT, INSERT         ON parc.intervention TO 'routines_parc'@'localhost';

-- 2. La procédure du chapitre 7, recréée avec ce propriétaire
DROP PROCEDURE IF EXISTS cloturer_ticket;
DELIMITER //
CREATE DEFINER = 'routines_parc'@'localhost' PROCEDURE cloturer_ticket(
  IN p_id_ticket INT, IN p_id_technicien INT, IN p_duree_min SMALLINT, IN p_compte_rendu VARCHAR(255)
)
SQL SECURITY DEFINER
BEGIN
  -- … corps identique au chapitre 7 …
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE parc.cloturer_ticket TO 'routines_parc'@'localhost';

-- 3. Le compte de l'application
CREATE USER 'helpdesk'@'192.168.21.20' IDENTIFIED BY 'Phrase-de-passe-du-helpdesk-2026';
GRANT EXECUTE ON PROCEDURE parc.cloturer_ticket TO 'helpdesk'@'192.168.21.20';
GRANT SELECT ON parc.ticket TO 'helpdesk'@'192.168.21.20';

Connecté avec helpdesk, sur la base remise à zéro au début du chapitre (le ticket 6 est encore en_cours) :

UPDATE ticket SET statut = 'resolu' WHERE id_ticket = 6;
-- ERROR 1142 (42000): UPDATE command denied to user 'helpdesk'@'192.168.21.20' for table 'ticket'

CALL cloturer_ticket(6, 12, 25, 'Ventilateur remplacé');   -- réussit
SELECT statut FROM ticket WHERE id_ticket = 6;             -- resolu

L'application ne peut clôturer un ticket que par la procédure : avec une intervention, une date, après les contrôles. Une injection SQL dans son code ne lui permettrait ni UPDATE libre, ni DELETE, ni lecture des comptes.

Deux détails que les débutants découvrent à leurs dépens :

• le propriétaire doit lui-même avoir EXECUTE sur sa procédure, sinon l'appel échoue avec l'erreur 1370 (execute command denied to user 'routines_parc'@'localhost' for routine 'parc.cloturer_ticket'). Le droit automatique d'exécution va au compte qui a tapé le CREATE, pas au DEFINER désigné ;
• le propriétaire a besoin de chaque droit utilisé dans le corps : s'il manque INSERT sur intervention, l'appel échoue sur l'erreur 1142… au nom de routines_parc.

Le propriétaire des triggers

Un trigger s'exécute avec les droits de son propriétaire. Pour qu'il s'exécute, ce propriétaire doit avoir le privilège TRIGGER sur la table, et les droits que demande son corps. Avec le trigger d'audit du chapitre 9 créé au nom de routines_parc :

GRANT TRIGGER ON parc.ticket       TO 'routines_parc'@'localhost';
GRANT INSERT  ON parc.audit_ticket TO 'routines_parc'@'localhost';

DROP TRIGGER IF EXISTS trg_ticket_audit_statut;
DELIMITER //
CREATE DEFINER = 'routines_parc'@'localhost' 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 ;

Quand helpdesk clôture un ticket par la procédure, le trigger écrit dans audit_ticket une ligne dont modifie_par vaut helpdesk@192.168.21.20 (USER() donne l'auteur, pas le propriétaire). Et pourtant helpdesk ne peut ni lire ni effacer cette table : SELECT * FROM audit_ticket lui renvoie l'erreur 1142. C'est exactement ce qu'on attend d'une table de traces.

Sans le privilège TRIGGER du propriétaire, toute modification de la table échoue, même par la procédure : ERROR 1142 (42000): TRIGGER command denied to user 'routines_parc'@'localhost' for table 'ticket'. Un trigger mal installé bloque l'application entière : teste-le avec le compte applicatif, pas seulement avec root.

Quand choisir SQL SECURITY INVOKER

SQL SECURITY INVOKER fait vérifier les droits de l'appelant. Une procédure de rapport écrite pour les responsables, en INVOKER, n'affiche à chacun que ce que ses propres droits lui permettent de lire :

DELIMITER //
CREATE DEFINER = 'routines_parc'@'localhost' PROCEDURE rapport_interventions()
SQL SECURITY INVOKER
BEGIN
  SELECT COUNT(*) AS interventions FROM intervention;
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE parc.rapport_interventions TO 'helpdesk'@'192.168.21.20';

Appelée par helpdesk, qui n'a aucun droit sur intervention, elle échoue : ERROR 1142 (42000): SELECT command denied to user 'helpdesk'@'192.168.21.20' for table 'intervention'. La documentation de MySQL recommande INVOKER quand c'est possible : une routine INVOKER ne peut jamais servir à dépasser les droits de qui l'appelle. Garde DEFINER pour les procédures dont le but est justement de donner un accès encadré (cloturer_ticket), avec un propriétaire qui n'a que les droits nécessaires, jamais root.

Les privilèges des programmes stockés

Sur MariaDB, SET_ANY_DEFINER s'appelle SET USER, et ALLOW_NONEXISTENT_DEFINER n'existe pas.

Le droit EXECUTE se donne au niveau le plus fin possible : GRANT EXECUTE ON PROCEDURE parc.cloturer_ticket plutôt que GRANT EXECUTE ON parc.*, qui donnerait accès à toutes les routines de la base, y compris celles qu'on ajoutera plus tard. Les droits automatiques du créateur peuvent être désactivés avec la variable automatic_sp_privileges.

Les objets orphelins

Un objet orphelin a pour propriétaire un compte qui n'existe plus. MySQL 8.4 refuse de supprimer ou de renommer un compte propriétaire d'un objet, sauf pour un compte qui a ALLOW_NONEXISTENT_DEFINER, ce qui est le cas de root : le DROP USER réussit alors avec un simple avertissement.

-- Le compte 'jetable'@'localhost' est le propriétaire de la procédure p_orpheline
DROP USER 'jetable'@'localhost';
-- Warning 4005: User 'jetable'@'localhost' is referenced as a definer account in a stored routine.
CALL p_orpheline();
-- ERROR 1449 (HY000): The user specified as a definer ('jetable'@'localhost') does not exist

La procédure ne fonctionne plus. Pire, si quelqu'un recrée un jour un compte du même nom, il en hérite. Avant de supprimer un compte, cherche ce qu'il possède :

SELECT 'routine' AS type, routine_name AS nom, definer FROM information_schema.routines
WHERE routine_schema = 'parc'
UNION ALL
SELECT 'trigger', trigger_name, definer FROM information_schema.triggers
WHERE trigger_schema = 'parc'
UNION ALL
SELECT 'événement', event_name, definer FROM information_schema.events
WHERE event_schema = 'parc'
ORDER BY definer, type, nom;

C'est aussi la requête d'un audit de sécurité : chaque propriétaire doit être un compte prévu pour cela, et aucun objet ne doit appartenir à un compte personnel ou à un ancien salarié. Le cas typique : un stagiaire crée des procédures avec son compte, part, et l'administrateur supprime son compte.

Le SQL dynamique et l'injection

Le SQL dynamique construit une requête sous forme de chaîne, puis l'exécute avec PREPARE et EXECUTE. C'est la seule façon de faire varier un nom de table ou de colonne, et la porte d'entrée de l'injection SQL dans une procédure. Une procédure de recherche mal écrite :

-- À NE PAS FAIRE : la saisie est collée dans la requête
DELIMITER //
CREATE PROCEDURE chercher_tickets_faible(IN p_mot VARCHAR(50))
BEGIN
  SET @sql = CONCAT('SELECT id_ticket, titre FROM ticket WHERE titre LIKE ''%', p_mot, '%''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

CALL chercher_tickets_faible('VPN');           -- fonctionne
CALL chercher_tickets_faible('l''entrepôt');
-- ERROR 1064 (42000): You have an error in your SQL syntax; … near 'entrepôt%'' at line 1

Le second appel échoue parce que l'apostrophe de « l'entrepôt » ferme la chaîne SQL au milieu du mot. C'est le symptôme d'une injection possible : si une apostrophe casse la requête, une saisie construite exprès peut la réécrire, exactement comme dans du code PHP (le principe de l'injection SQL). Et comme la procédure s'exécute avec les droits de son propriétaire, l'attaquant obtient ces droits-là, souvent plus larges que ceux de l'application.

La version sûre utilise un marqueur ? pour la valeur, et une liste blanche pour ce qui ne peut pas être un marqueur (le nom de la colonne de tri) :

DELIMITER //
CREATE PROCEDURE chercher_tickets(IN p_mot VARCHAR(50), IN p_tri VARCHAR(20))
SQL SECURITY INVOKER
BEGIN
  DECLARE v_colonne VARCHAR(20);

  -- Liste blanche : seules trois colonnes de tri sont possibles
  SET v_colonne = CASE p_tri
                    WHEN 'date'     THEN 'date_ouverture'
                    WHEN 'priorite' THEN 'priorite'
                    ELSE 'id_ticket'
                  END;

  SET @motif = CONCAT('%', p_mot, '%');
  SET @sql = CONCAT('SELECT id_ticket, titre FROM ticket WHERE titre LIKE ? ORDER BY ', v_colonne);
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING @motif;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

CALL chercher_tickets('l''entrepôt', 'date');   -- ticket 13, sans erreur

• La valeur passe par ? et USING : le serveur l'utilise comme une donnée, apostrophes comprises.
• Ce qui est collé dans la chaîne (v_colonne) ne peut valoir qu'une des trois valeurs écrites dans le code.
• En MySQL, EXECUTE … USING n'accepte que des variables de session (@motif) : USING p_mot provoque une erreur de syntaxe. De même, PREPARE stmt FROM prend une chaîne littérale ou une variable de session, pas une variable locale. MariaDB accepte des expressions quelconques dans USING.
• Le SQL dynamique est interdit dans les fonctions et les triggers (erreur 1336) : seules les procédures en font.

Exercices

CREATE USER 'reporting'@'192.168.21.30' IDENTIFIED BY 'Phrase-de-passe-du-reporting-2026';
GRANT EXECUTE ON PROCEDURE parc.calculer_stat_mois TO 'reporting'@'192.168.21.30';
GRANT SELECT ON parc.stat_mensuelle TO 'reporting'@'192.168.21.30';

-- Le propriétaire (procédure en SQL SECURITY DEFINER)
GRANT SELECT ON parc.ticket                       TO 'routines_parc'@'localhost';
GRANT SELECT ON parc.intervention                 TO 'routines_parc'@'localhost';
GRANT INSERT, DELETE ON parc.stat_mensuelle       TO 'routines_parc'@'localhost';
GRANT EXECUTE ON PROCEDURE parc.calculer_stat_mois TO 'routines_parc'@'localhost';

DELIMITER //
CREATE PROCEDURE lister_postes(IN p_tri VARCHAR(30))
BEGIN
  SET @sql = CONCAT('SELECT num_inventaire, marque, modele FROM poste ORDER BY ', p_tri);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS lister_postes;
DELIMITER //
CREATE PROCEDURE lister_postes(IN p_tri VARCHAR(30))
SQL SECURITY INVOKER
BEGIN
  DECLARE v_colonne VARCHAR(30);
  SET v_colonne = CASE p_tri
                    WHEN 'marque'     THEN 'marque'
                    WHEN 'date_achat' THEN 'date_achat'
                    ELSE 'num_inventaire'
                  END;
  SET @sql = CONCAT('SELECT num_inventaire, marque, modele FROM poste ORDER BY ', v_colonne);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;