15. Comptes et privilèges

MIRAJ contrôle qui peut se connecter et ce que chaque connexion a le droit de faire par un système de comptes, de privilèges et de rôles proche de celui des serveurs SQL les plus répandus. Ce chapitre couvre l'authentification, la gestion des comptes, les privilèges à leurs trois niveaux, les rôles, et les tables information_schema utiles pour auditer les droits.

15.1 Authentification#

À la connexion, le mot de passe d'un compte ne circule jamais en clair sur le réseau et n'est jamais conservé en clair sur le serveur, ni même sous une forme qui permettrait de le retrouver :

• Le serveur envoie un défi aléatoire à chaque connexion.
• Le client combine ce défi avec le mot de passe saisi par l'utilisateur et renvoie seulement le résultat de ce calcul — jamais le mot de passe lui-même.
• Le serveur ne garde, pour chaque compte, qu'un vérificateur dérivé du mot de passe (empreinte à deux niveaux) : il permet de vérifier une tentative de connexion, mais ne permet pas de reconstituer le mot de passe d'origine.

En pratique, cela signifie :

• une capture du trafic réseau ne révèle pas les mots de passe des comptes ;
• une copie du dossier de données (ou du coffre des comptes, voir chapitre 16) ne révèle pas non plus les mots de passe en clair ;
• il n'existe donc aucun moyen de « retrouver » le mot de passe d'un compte oublié : seul ALTER USER ... IDENTIFIED BY ou SET PASSWORD permet d'en fixer un nouveau.

Le détail cryptographique exact (algorithmes, format du défi) relève de la sécurité interne du produit et n'est pas nécessaire pour utiliser MIRAJ ; il est documenté dans readme.txt pour qui en a besoin.

Chaque définition d'un mot de passe (CREATE USER … IDENTIFIED BY, ALTER USER … IDENTIFIED BY, SET PASSWORD, mot de passe initial de root) calcule aussi un vérificateur SCRAM-SHA-256, quel que soit le langage de l'instance : il sert aux clients PostgreSQL d'une instance en langage PostgreSQL (§ 16.10.2), et un changement de langage ne bloque donc aucun compte. Un mot de passe donné par son empreinte (IDENTIFIED BY PASSWORD '*…') n'en a pas, pas plus qu'un compte défini avant cette version : pour lui en donner un, redéfinissez son mot de passe. Les vérificateurs suivent les comptes dans le coffre chiffré, dans la réplication des comptes d'un cluster et dans --reset-accounts.

15.2 Comptes : CREATE / ALTER / DROP USER#

Identité d'un compte : utilisateur + hôte#

Un compte MIRAJ n'est pas identifié par son seul nom d'utilisateur, mais par la paire 'utilisateur'@'hôte'. Le même nom d'utilisateur peut ainsi désigner des comptes différents, avec des mots de passe et des privilèges différents, selon la machine depuis laquelle la connexion est établie :

• 'app'@'localhost' — le compte app connecté uniquement depuis la machine du serveur ;
• 'app'@'192.168.1.50' — le compte app connecté depuis une adresse précise ;
• 'app'@'192.168.1.%' — motif % : tout le sous-réseau 192.168.1.* ;
• 'app'@'%' — n'importe quelle machine (l'hôte par défaut si @hôte est omis).

Les motifs % (n'importe quelle suite de caractères) et _ (un caractère quelconque) sont autorisés dans l'hôte, comme dans une clause LIKE. Quand plusieurs comptes du même nom correspondent à la connexion en cours, MIRAJ retient le plus précis : un hôte exact ou localhost l'emporte sur un motif, et % est choisi en dernier recours. localhost désigne spécifiquement la boucle locale (et non « n'importe quelle adresse locale »).

Créer, modifier, supprimer un compte#

CREATE USER 'lecteur'@'%' IDENTIFIED BY 'un-mot-de-passe-solide';

CREATE USER
    'app'@'10.0.0.%' IDENTIFIED BY 'secret1',
    'admin'@'localhost' IDENTIFIED BY 'secret2';

ALTER USER 'lecteur'@'%' IDENTIFIED BY 'nouveau-mot-de-passe';

ALTER USER 'lecteur'@'%' ACCOUNT LOCK;      -- verrouille le compte : connexion refusée
ALTER USER 'lecteur'@'%' ACCOUNT UNLOCK;    -- le déverrouille

DROP USER 'lecteur'@'%';

IDENTIFIED BY 'texte' fixe un mot de passe en clair côté client (converti aussitôt en vérificateur côté serveur) ; IDENTIFIED BY PASSWORD 'empreinte' fixe directement un vérificateur déjà calculé. IDENTIFIED {WITH | VIA} <greffon> suivi de BY 'texte', de {AS | USING} 'empreinte' ou de {AS | USING} PASSWORD('texte') est accepté pour les outils qui l'écrivent, quand le greffon est le greffon natif du protocole, sous son nom usuel ou sous le nom miraj_password (celui qu'affiche la vue des comptes) : c'est le seul greffon d'authentification. Un autre greffon (caching_sha2_password, auth_socket…) est refusé par l'erreur 1524, et plusieurs greffons (VIA a OR b) par l'erreur 1235. IDENTIFIED WITH greffon seul donne un compte sans mot de passe (retiré par ALTER USER) : la règle require_password s'applique alors (erreur 9033 pour un compte joignable depuis une autre machine).

Comptes distants sans mot de passe refusés. Tant que la variable globale require_password vaut ON (défaut, option --require-password du serveur), un compte dont l'hôte n'est pas la seule boucle locale (localhost, 127.0.0.1, ::1) doit avoir un mot de passe : CREATE USER 'app'@'%' sans IDENTIFIED BY est refusé par l'erreur 9033, de même qu'un ALTER USER ou un SET PASSWORD qui retirerait le mot de passe d'un tel compte. Un compte local sans mot de passe reste permis. SET GLOBAL require_password = OFF (privilège SUPER ou SYSTEM_VARIABLES_ADMIN) lève la règle, par exemple le temps de recharger un export d'anciens comptes.

ACCOUNT LOCK / ACCOUNT UNLOCK permet de suspendre un compte sans le supprimer ni changer son mot de passe — utile pour désactiver temporairement un compte applicatif ou celui d'un collaborateur qui quitte l'équipe, sans perdre l'historique de ses privilèges. Le verrou s'écrit aussi à la création (CREATE USER … IDENTIFIED BY '…' ACCOUNT LOCK) ou avec un changement de mot de passe (ALTER USER … IDENTIFIED BY '…' ACCOUNT LOCK). Une connexion à un compte verrouillé est refusée par l'erreur 3118 ; la console l'affiche ainsi :

connexion à [email protected]:7106 impossible : erreur SQL 1105 (HY000) : 3118 (HY000): Accès refusé pour l'utilisateur 'lk'@'localhost'. Le compte est verrouillé.

Compte déjà existant ou absent. CREATE USER d'un compte qui existe déjà, ou DROP USER d'un compte absent, échoue en 1396 et ne crée ni ne supprime aucun des comptes de la liste :

ERREUR 1396 (HY000) : L'opération CREATE USER a échoué pour 'root'@'localhost'
ERREUR 1396 (HY000) : L'opération DROP USER a échoué pour 'zz'@'%'

CREATE USER IF NOT EXISTS, ALTER USER IF EXISTS et DROP USER IF EXISTS passent alors le compte concerné avec un avertissement (3163 « … already exists », 3162 « … does not exist ») et traitent les autres ; CREATE ROLE IF NOT EXISTS et DROP ROLE IF EXISTS font de même.

DEFAULT ROLE ... peut être fixé à la création ou à la modification d'un compte (voir §15.4).

Un compte tout juste créé n'a aucun privilège sur les données : seulement USAGE, c'est-à-dire le droit de se connecter et rien de plus. Il faut lui accorder explicitement des privilèges avec GRANT (§15.3).

Changer son propre mot de passe#

SET PASSWORD = 'nouveau-mot-de-passe';                 -- le compte de la session
SET PASSWORD FOR 'lecteur'@'%' = 'nouveau-mot-de-passe'; -- un autre compte (droit CREATE USER requis)
SET PASSWORD = PASSWORD('nouveau-mot-de-passe');

Un compte ordinaire peut toujours changer son propre mot de passe ; changer celui d'un autre compte, ou plus généralement gérer les comptes (CREATE USER, ALTER USER, DROP USER), demande le privilège global CREATE USER — sinon erreur 1227 (voir §15.6).

Comptes système (SYSTEM_USER). Un compte qui détient le privilège SYSTEM_USER, directement ou par un rôle qui lui est accordé — c'est le cas du root initial —, ne peut être modifié (ALTER USER, SET PASSWORD FOR), supprimé (DROP USER), dépouillé (REVOKE … FROM) ou pourvu de privilèges et de rôles (GRANT … TO) que par un compte qui détient lui-même SYSTEM_USER ; sinon erreur 1227 nommant SYSTEM_USER. Un rôle qui porte SYSTEM_USER ne s'accorde et ne se supprime qu'aux mêmes conditions. Ainsi, un compte d'administration à qui l'on a confié CREATE USER pour gérer les comptes applicatifs ne peut pas prendre la main sur root. Chaque compte garde le droit de changer son propre mot de passe.

Identifier le compte courant#

SELECT CURRENT_USER();   -- 'utilisateur'@'hôte' du COMPTE qui s'est authentifié
SELECT USER();           -- 'utilisateur'@'hôte' tel que le client l'a demandé à la connexion

CURRENT_USER() reflète le compte réellement retenu par le serveur pour l'authentification (après résolution du motif d'hôte le plus précis) ; USER() reflète la demande initiale du client. Les deux coïncident dans l'immense majorité des cas ; ils peuvent différer quand le client se connecte sous un hôte qui correspond à un compte défini par un motif (%, _).

15.3 Privilèges : GRANT et REVOKE#

Les niveaux#

Un privilège détenu à un niveau s'applique aussi à tout ce qu'englobe ce niveau : un privilège SELECT accordé sur ventes.* s'applique à toutes les tables de la base ventes, présentes et à venir, sans qu'il soit besoin de le réaccorder à chaque nouvelle table.

Syntaxe#

GRANT privilège [, privilège ...] ON [TABLE] niveau TO compte [, compte ...] [WITH GRANT OPTION];

REVOKE [IF EXISTS] privilège [, privilège ...] ON [TABLE] niveau FROM compte [, compte ...];
REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION FROM compte [, compte ...];

ALL (ou ALL PRIVILEGES) accorde ou retire tout ce que le niveau visé admet. WITH GRANT OPTION permet en plus au bénéficiaire de retransmettre lui-même ces privilèges (ou un sous-ensemble) à d'autres comptes, au même niveau ou à un niveau qu'il englobe.

Pour accorder ou révoquer un privilège à un niveau donné, il faut soi-même détenir ce privilège à ce niveau (ou à un niveau englobant) avec l'option GRANT. GRANT n'accepte que des comptes déjà existants : il n'en crée plus (créer un compte est le rôle de CREATE USER). Au niveau d'une table (ON base.table), la table, voire la base, peut ne pas exister encore si les privilèges accordés comprennent CREATE (ou ALL) ; sinon l'erreur 1146 est rendue.

Les droits s'appliquent dès l'instruction qui les change : FLUSH PRIVILEGES, que certains outils envoient après un GRANT, est accepté sans effet (privilège RELOAD). Le privilège DELETE HISTORY (tables temporelles, chapitre 12) s'accorde, se retire et s'affiche comme les autres et fait partie de ALL PRIVILEGES ; DELETE HISTORY FROM t n'exige pourtant que DELETE sur la table (voir 15.3.1).

Exemples concrets#

Compte applicatif en lecture seule sur une base :

CREATE USER 'rapport'@'10.0.0.%' IDENTIFIED BY 'mot-de-passe-1';
GRANT SELECT ON gestion.* TO 'rapport'@'10.0.0.%';

Compte applicatif qui lit et écrit dans une base, sans pouvoir modifier son schéma :

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'mot-de-passe-2';
GRANT SELECT, INSERT, UPDATE, DELETE ON gestion.* TO 'app'@'10.0.0.%';

Compte limité à une seule table sensible :

GRANT SELECT, UPDATE ON gestion.parametres TO 'support'@'localhost';

Compte administrateur d'une base, qui peut à son tour déléguer :

CREATE USER 'dba_gestion'@'localhost' IDENTIFIED BY 'mot-de-passe-3';
GRANT ALL ON gestion.* TO 'dba_gestion'@'localhost' WITH GRANT OPTION;

Retirer un privilège devenu inutile :

REVOKE INSERT, UPDATE, DELETE ON gestion.* FROM 'rapport'@'10.0.0.%';

Privilèges par colonne#

SELECT, INSERT, UPDATE et REFERENCES s'accordent aussi sur une partie des colonnes d'une table ou d'une vue : le nom du privilège est suivi de la liste des colonnes.

-- Le service client lit l'identité des clients et corrige leur adresse, jamais leur solde
GRANT SELECT (id, nom, ville), UPDATE (ville) ON gestion.clients TO 'support'@'localhost';
SHOW GRANTS FOR 'support'@'localhost';

+-------------------------------------------------------------------------------------------------------+
| Grants for support@localhost                                                                          |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support`@`localhost`                                                           |
| GRANT SELECT (`id`, `nom`, `ville`), UPDATE (`ville`) ON `gestion`.`clients` TO `support`@`localhost` |
+-------------------------------------------------------------------------------------------------------+

-- Privilèges de table et de colonne dans la même instruction
GRANT INSERT (id, nom), DELETE ON gestion.prospects TO 'support'@'localhost';
REVOKE UPDATE (ville) ON gestion.clients FROM 'support'@'localhost';
SHOW GRANTS FOR 'support'@'localhost';

+--------------------------------------------------------------------------------------+
| Grants for support@localhost                                                         |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support`@`localhost`                                          |
| GRANT SELECT (`id`, `nom`, `ville`) ON `gestion`.`clients` TO `support`@`localhost`  |
| GRANT INSERT (`id`, `nom`), DELETE ON `gestion`.`prospects` TO `support`@`localhost` |
+--------------------------------------------------------------------------------------+

Chaque colonne utilisée par une instruction est contrôlée, là où le compte n'a le privilège que sur des colonnes :

Une vue, une procédure, une fonction ou un déclencheur SQL SECURITY DEFINER est contrôlé colonne par colonne sous l'identité de son définisseur : une vue qui lit une colonne refusée à son définisseur rend 1356. Une colonne refusée donne l'erreur 1143 :

ERREUR 1143 (42000) : Commande SELECT refusée à l'utilisateur 'support'@'localhost' pour la colonne 'solde' de la table 'clients'

Privilèges par routine#

EXECUTE et ALTER ROUTINE s'accordent aussi sur une seule procédure ou une seule fonction, plutôt que sur toute sa base (GRANT EXECUTE ON base.*) :

-- Le service client appelle deux routines de gestion, et aucune autre
GRANT EXECUTE ON PROCEDURE gestion.relancer_client TO 'support'@'%';
GRANT EXECUTE ON FUNCTION gestion.solde_client TO 'support'@'%' WITH GRANT OPTION;
-- Le compte d'exploitation peut supprimer cette procédure (DROP PROCEDURE)
GRANT ALTER ROUTINE ON PROCEDURE gestion.purge TO 'exploitation'@'localhost';
REVOKE EXECUTE ON PROCEDURE gestion.relancer_client FROM 'support'@'%';

• Une procédure et une fonction de même nom sont deux niveaux distincts.
• La routine doit exister au moment du GRANT (1305) ; seuls EXECUTE, ALTER ROUTINE (et GRANT OPTION) y sont admis (1144 pour un autre privilège) ; ALL accorde les deux.
• L'appel (CALL, fonction dans une requête, à arguments constants ou ligne à ligne) est contrôlé comme pour le niveau base : 1370 (Commande execute refusée à l'utilisateur 'support'@'…' pour la routine 'gestion.solde_client') sans EXECUTE sur la routine, sa base ou au niveau global.
• ALTER ROUTINE sur la routine permet DROP PROCEDURE / DROP FUNCTION de celle-ci ; CREATE ROUTINE reste un privilège de base.
• Retransmettre un droit de routine demande l'option GRANT sur la routine (ou plus large) : 1370 (Commande grant refusée …) sinon. Un REVOKE d'un droit absent rend 1403 (Aucun droit de ce type n'est défini pour l'utilisateur 'support' sur l'hôte '%' pour la routine 'relancer_client').
• Supprimer la routine (DROP PROCEDURE / DROP FUNCTION) retire les droits accordés sur elle à tous les comptes : recréée, elle n'en porte plus.
• Un droit sur une routine rend sa base visible (USE, SHOW DATABASES).
• SHOW GRANTS les montre après les tables, procédures puis fonctions, privilèges nommés un à un (GRANT EXECUTE ON PROCEDURE `gestion`.`relancer_client` TO `support`@`%`) ; la table système miraj.procs_priv (colonnes Host, Db, User, Routine_name, Routine_type, Grantor, Proc_priv, Timestamp) et information_schema.ROUTINE_PRIVILEGES (§15.5) les énumèrent pour les outils d'administration. Grantor est vide et Timestamp NULL : MIRAJ ne garde ni l'auteur ni l'instant d'un GRANT.

Les privilèges ne s'accordent pas seulement sur des tables : voir §15.3.1 pour les séquences, les tables à versionnement système et les privilèges d'administration.

15.3.1 Séquences, historique et privilèges d'administration#

• Séquences. Une séquence se désigne comme une table (GRANT SELECT ON gestion.seq_facture TO …). INSERT permet NEXTVAL / NEXT VALUE FOR et SETVAL ; SELECT permet LASTVAL, la lecture (SELECT * FROM seq_facture) et SHOW CREATE SEQUENCE. CREATE SEQUENCE, ALTER SEQUENCE et DROP SEQUENCE demandent CREATE, ALTER et DROP sur la base. Sans droit, l'erreur est 1142 (chapitre 7, « Séquences »).
• Tables à versionnement système. L'historique d'une table (sa table compagnon, chapitre 12) se lit et s'écrit avec les droits de la table elle-même : SELECT … FOR SYSTEM_TIME … demande SELECT, DELETE HISTORY FROM t demande DELETE sur t. Le privilège DELETE HISTORY s'accorde, se retire et s'affiche comme les autres, fait partie de ALL PRIVILEGES, mais n'est pas exigé en plus.
• SHUTDOWN : l'instruction SHUTDOWN (arrêt du serveur, chapitre 16) demande le privilège SHUTDOWN.
• BACKUP_ADMIN : BACKUP DATABASE, et avec CREATE (plus DROP avec REPLACE) RESTORE DATABASE (chapitre 19) ; il autorise aussi à régler journal_archive* par SET GLOBAL.
• EXPLAIN ANALYZE / ANALYZE exécutent l'instruction : ils exigent les droits de l'instruction analysée, et ANALYZE UPDATE|DELETE modifie vraiment les lignes (chapitre 9, 9.21).

15.3.2 Liste des privilèges#

SHOW PRIVILEGES liste les privilèges que le serveur connaît. Ceux de table s'accordent aussi au niveau base ou global ; ceux d'administration seulement au niveau global (ON *.*), sinon erreur 1144 (GRANT SUPER ON gestion.* …).

ALL [PRIVILEGES] au niveau global donne tous les privilèges écrits en mots (de SELECT à DROP ROLE), mais ni GRANT OPTION, qui s'ajoute par WITH GRANT OPTION, ni ceux écrits en majuscules avec un tiret bas (ROLE_ADMIN … ENCRYPTION_KEY_ADMIN), qui s'accordent un à un. Le root initial les détient tous :

SHOW GRANTS FOR CURRENT_USER;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                       |
| GRANT ROLE_ADMIN,CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,BACKUP_ADMIN,ENCRYPTION_KEY_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

15.4 Rôles#

Un rôle regroupe un ensemble de privilèges sous un nom, pour les accorder ensuite en une seule fois à plusieurs comptes, plutôt que de répéter les mêmes GRANT compte par compte.

Créer et administrer un rôle#

CREATE ROLE 'lecture_gestion';
GRANT SELECT ON gestion.* TO 'lecture_gestion';

DROP ROLE 'lecture_gestion';

CREATE ROLE demande le privilège CREATE ROLE (ou CREATE USER) ; DROP ROLE demande DROP ROLE (ou CREATE USER). Supprimer un rôle le retire aussitôt de tous les comptes qui le portaient.

Accorder un rôle à un compte#

GRANT 'lecture_gestion' TO 'rapport'@'10.0.0.%';
GRANT 'lecture_gestion' TO 'autre_compte'@'%' WITH ADMIN OPTION;

REVOKE 'lecture_gestion' FROM 'rapport'@'10.0.0.%';

Accorder ou retirer un rôle demande le privilège ROLE_ADMIN (ou SUPER), ou l'option ADMIN détenue spécifiquement sur ce rôle (donnée par WITH ADMIN OPTION).

Activer un rôle dans la session#

Un rôle accordé à un compte n'est pas automatiquement actif à la connexion, sauf s'il a été fixé comme rôle par défaut :

SET DEFAULT ROLE 'lecture_gestion' TO 'rapport'@'10.0.0.%';   -- activé à chaque connexion
SET DEFAULT ROLE ALL TO 'rapport'@'10.0.0.%';                 -- tous les rôles accordés
SET DEFAULT ROLE NONE TO 'rapport'@'10.0.0.%';                -- aucun par défaut

-- Dans la session courante :
SET ROLE 'lecture_gestion';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'un_autre_role';
SET ROLE NONE;
SET ROLE DEFAULT;   -- revient aux rôles par défaut du compte

SET ROLE ne peut activer que des rôles déjà accordés au compte de la session. SET DEFAULT ROLE pour un compte autre que celui de la session demande le privilège CREATE USER.

Consulter les privilèges et rôles actifs#

SHOW GRANTS;                              -- pour le compte de la session (rôles actifs fondus)
SHOW GRANTS FOR 'rapport'@'10.0.0.%';     -- pour un autre compte (droit CREATE USER requis)
SHOW GRANTS FOR 'rapport'@'10.0.0.%' USING 'lecture_gestion';
SHOW CREATE USER 'rapport'@'10.0.0.%';    -- instruction qui recrée le compte (droit CREATE USER requis
                                          -- pour un autre compte) : vérificateur du mot de passe
                                          -- (IDENTIFIED BY PASSWORD '*…', jamais le mot de passe), verrou

SELECT CURRENT_ROLE();

Pour le compte rapport des exemples ci-dessus (privilège SELECT sur gestion.*, rôle lecture_gestion accordé) :

+-----------------------------------------------------+
| Grants for [email protected].%                         |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `rapport`@`10.0.0.%`          |
| GRANT SELECT ON `gestion`.* TO `rapport`@`10.0.0.%` |
| GRANT `lecture_gestion`@`%` TO `rapport`@`10.0.0.%` |
+-----------------------------------------------------+

+-----------------------------------------------------------------------------------------------------+
| CREATE USER for [email protected].%                                                                    |
+-----------------------------------------------------------------------------------------------------+
| CREATE USER 'rapport'@'10.0.0.%' IDENTIFIED BY PASSWORD '*7AED0DAAF571D30F9B8058AE21DC2D88E0707207' |
+-----------------------------------------------------------------------------------------------------+

CURRENT_ROLE() rend NONE sans rôle actif, sinon la liste des rôles actifs (`lecture_gestion`@`%`). Un rôle inconnu donne 3523 (Compte ou rôle inconnu `nor`@`%`), un rôle non accordé 3530.

15.5 Auditer les droits par information_schema#

Plusieurs tables virtuelles de information_schema donnent une vue exploitable par requête des privilèges et des rôles, construites directement à partir du coffre des comptes :

Exemples :

-- Tous les privilèges accordés sur la base gestion
SELECT * FROM information_schema.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA = 'gestion';

-- Tous les comptes qui ont un privilège global (à surveiller de près)
SELECT * FROM information_schema.USER_PRIVILEGES;

-- Rôles accordés au compte de la session, et rôles actifs
SELECT * FROM information_schema.APPLICABLE_ROLES;
SELECT * FROM information_schema.ENABLED_ROLES;

Résultat pour root@localhost à qui le rôle lecture_gestion a été accordé puis activé par SET ROLE 'lecture_gestion' :

+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+
| USER               | HOST      | GRANTEE | GRANTEE_HOST | ROLE_NAME       | ROLE_HOST | IS_GRANTABLE | IS_DEFAULT | IS_MANDATORY |
+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+
| 'root'@'localhost' | localhost | root    | localhost    | lecture_gestion | %         | NO           | NO         | NO           |
+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+

+-----------------+-----------+
| ROLE_NAME       | ROLE_HOST |
+-----------------+-----------+
| lecture_gestion | %         |
+-----------------+-----------+

Sans rôle actif, ENABLED_ROLES rend une seule ligne NULL, NULL. Pour les rôles d'un autre compte, lire SHOW GRANTS FOR compte (les rôles y figurent en GRANT rôle TO compte).

Les privilèges par colonne d'un compte :

SELECT * FROM information_schema.COLUMN_PRIVILEGES WHERE TABLE_NAME = 'clients';

+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE               | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'support'@'localhost' | def           | gestion      | clients    | id          | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | nom         | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | ville       | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | ville       | UPDATE         | NO           |
+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+

Un compte ordinaire ne voit, dans ces tables comme dans la table système des comptes miraj.user, que ce qui le concerne ; authentication_string vaut toujours NULL dans miraj.user, et toute tentative d'écriture y est refusée (erreur 1044, root compris) — ce ne sont pas des tables mais une vue calculée du coffre des comptes : les comptes et les droits ne se changent que par CREATE USER, GRANT, REVOKE, etc.

15.6 Codes d'erreur de privilège#

Chaque instruction est contrôlée avant son exécution, au niveau du privilège qu'elle demande :

Messages tels que la console les affiche :

ERREUR 1044 (42000) : Accès refusé pour l'utilisateur 'app'@'localhost' à la base de données 'shop'
ERREUR 1142 (42000) : Commande INSERT refusée à l'utilisateur 'app'@'localhost' pour la table `err`.`p`
ERREUR 1227 (42000) : Accès refusé ; il faut le privilège CREATE USER pour cette opération
ERREUR 1370 (42000) : Commande execute refusée à l'utilisateur 'app'@'localhost' pour la routine 'err.f1'

Un point important pour la sécurité applicative : quand un compte n'a aucun droit sur une table, une requête qui la vise renvoie 1142 même si la table n'existe pas — jamais l'erreur « table inconnue » (1146) qui, elle, révélerait l'absence de la table à un compte qui n'a de toute façon pas le droit de la voir.

SHOW DATABASES, SHOW TABLES et les lectures d'information_schema (SCHEMATA, TABLES, COLUMNS, ...) sont filtrées silencieusement selon les mêmes règles : un compte ne voit jamais que ce qu'il a le droit d'atteindre, sans message d'erreur pour ce qui lui est caché. information_schema.COLUMNS suit les privilèges colonne par colonne : un compte qui n'a de privilèges que sur certaines colonnes d'une table n'y voit que celles-là, et la colonne PRIVILEGES rend ceux qu'il détient sur chacune (select,update, par exemple). DESCRIBE et SHOW [FULL] COLUMNS suivent la même règle (la colonne Privileges de SHOW FULL COLUMNS nomme les droits détenus) ; seuls comptent les privilèges qui portent sur une colonne (SELECT, INSERT, UPDATE, REFERENCES) : un compte qui n'a que DELETE sur la table n'en voit aucune.

Une table n'est visible que par un privilège qui peut porter sur elle, accordé sur la table, sur sa base ou au niveau global : un privilège de base sans objet pour une table (EXECUTE, CREATE ROUTINE, EVENT, LOCK TABLES…) ou un privilège global comme PROCESS laisse la base visible mais n'en montre aucune table. information_schema.TRIGGERS et SHOW TRIGGERS ne listent que les déclencheurs des tables sur lesquelles le compte a le privilège TRIGGER, et information_schema.CHECK_CONSTRAINTS que les contraintes des tables visibles.

Les procédures, fonctions et événements planifiés s'exécutent, par défaut, avec les privilèges de leur définisseur (SQL SECURITY DEFINER) plutôt que ceux de l'appelant ; SQL SECURITY INVOKER inverse ce choix pour utiliser les privilèges de l'appelant.

Appeler une routine demande toujours EXECUTE sur la routine elle-même ou sur sa base, quel que soit le contexte de l'appel : CALL p(), fonction appelée ligne à ligne (SELECT f(id) FROM t) ou avec des arguments constants (SELECT f(1), SET @v = f(1), INSERT … VALUES (f(1)), WHERE f(1) = 0). Sans lui, l'instruction échoue en 1370 avant toute exécution de la routine (aucun effet de son corps), même si l'appelant détient par ailleurs tous les droits sur les tables que le corps lit ou écrit. Un appel à arguments constants est exécuté une seule fois, avant l'instruction : ses droits (EXECUTE, puis le corps sous l'identité du définisseur) sont contrôlés avant cette exécution, avec ceux du reste de l'instruction. Dans le corps d'une routine, d'un déclencheur ou d'une vue, EXECUTE est exigé du définisseur de ce corps (de l'appelant pour une routine ou une vue SQL SECURITY INVOKER), pas du compte dont l'instruction le déclenche.

Les déclencheurs s'exécutent toujours avec les privilèges de leur définisseur, quel que soit le compte dont l'écriture les déclenche ; une fonction appelée ligne à ligne (SELECT f(id) FROM t, WHERE f(x) > 0) suit la même règle que toute fonction (définisseur, ou appelant avec SQL SECURITY INVOKER). Leurs corps sont contrôlés avec l'instruction qui les déclenche, une seule fois pour elle et non à chaque ligne : chaque instruction du corps, y compris dans une branche que l'exécution ne parcourra pas, les procédures appelées (sous leur propre définisseur), les déclencheurs des tables que le corps écrit et les vues qu'il lit. Une fonction appelée par une vue est contrôlée de la même façon, sous l'identité du définisseur de la vue. Un droit manquant au définisseur fait échouer l'instruction déclenchante (1142, 1044, 1370 ou 1227, qui nomment le définisseur ; 1356 pour une vue dont le définisseur ne peut plus lire les tables) ; un définisseur supprimé donne 1449. Un REVOKE vaut dès l'instruction suivante. Exemple : un compte qui n'a que TRIGGER et INSERT sur shop.ventes ne peut pas créer un déclencheur qui modifie compta.ecritures (1142 à la création), et un déclencheur créé par root avec DEFINER = app échoue à chaque INSERT, même de root, tant qu'app n'a pas les droits sur compta.

Instructions interdites dans un corps de déclencheur ou de fonction appelée ligne à ligne (et dans les procédures qu'ils appellent), parce qu'elles dépendent de l'identité de la session : KILL, LOAD DATA, SELECT … INTO OUTFILE / DUMPFILE, LOCK TABLES, BACKUP / RESTORE et la gestion des comptes et des droits (GRANT, REVOKE, CREATE USER, SET ROLE…). CREATE TRIGGER les refuse d'emblée (1314, ou 1422 pour la gestion des comptes, qui valide implicitement) ; une procédure qui en contient une échoue en 1314 quand un déclencheur l'appelle. Dans un déclencheur, LOAD_FILE ne lit un fichier que si le définisseur a le privilège FILE.

La table système proc (miraj.proc) ne montre que les routines des bases sur lesquelles le compte a un droit, comme information_schema.ROUTINES. La table système procs_priv (miraj.procs_priv) ne montre à un compte qui n'a pas CREATE USER que ses propres droits par routine.

Base reprise d'un autre serveur. Déclencheurs, routines et vues gardent le définisseur inscrit à leur création (DEFINER = gestium@localhost, par exemple). Tant que ce compte n'existe pas dans le coffre des comptes de MIRAJ, toute écriture qui déclenche un tel déclencheur échoue en 1449, de même qu'une fonction appelée ligne à ligne, et la lecture d'une telle vue en 1356 ; si le compte existe sans les droits nécessaires sur les tables que ses corps lisent ou écrivent, l'instruction échoue en 1142 (1356 pour une vue). Après une migration, créez donc chaque définisseur (la colonne DEFINER de information_schema.TRIGGERS, ROUTINES et VIEWS les énumère) et accordez-lui les droits dont ses corps ont besoin, par exemple CREATE USER gestium@localhost IDENTIFIED BY '…'; GRANT ALL ON gestium.* TO gestium@localhost. Sans coffre des comptes (moteur embarqué sans comptes), aucun de ces contrôles n'a lieu.

Erreurs liées aux rôles : rôle inconnu (3523), rôle non accordé au compte concerné (3530), bénéficiaire de GRANT inexistant (1410, GRANT ne créant plus de compte comme autrefois).

15.7 Le compte root et bonnes pratiques#

Un compte root@localhost est créé automatiquement avec le dossier de données, doté de tous les privilèges (dont SYSTEM_USER) et de l'option GRANT. C'est le compte que la console (miraj-cli) et l'API utilisent par défaut en accès embarqué.

Mot de passe initial. Au premier démarrage de miraj-server sur un dossier, le coffre des comptes est créé et root reçoit un mot de passe de 24 caractères tiré au sort (lettres et chiffres, sans les caractères qui se confondent comme 0/O ou 1/l), affiché une seule fois sur la console et jamais écrit dans server.log. Pour une installation par script, donnez le vôtre par --initial-root-password ou par la variable d'environnement MIRAJ_INITIAL_ROOT_PASSWORD (ou initial_root_password dans miraj_config.xml, à retirer ensuite) : il n'est alors pas affiché. Ces réglages sont ignorés, avec un avertissement, une fois le coffre créé. --reset-accounts recrée root avec un nouveau mot de passe, tiré au sort ou donné de la même façon (voir chapitre 16). Dans un cluster, seul le primaire crée les comptes : un secondaire les reçoit de lui. Le moteur embarqué seul (bibliothèque, miraj-cli sur un dossier local) n'écrit pas de coffre à l'ouverture : son root reste sans mot de passe tant que le dossier n'a pas été ouvert par miraj-server ni un compte modifié.

Bonnes pratiques à l'ouverture d'un nouveau dossier de données :

• Remplacer le mot de passe provisoire de root dès la première connexion (ALTER USER root@localhost IDENTIFIED BY '...' ou SET PASSWORD), en particulier avant toute exposition en réseau.
• Ne pas exposer miraj-server sur une adresse non locale sans avoir d'abord créé des comptes applicatifs restreints (§15.3) : par défaut, le serveur n'écoute que sur la boucle locale, et MIRAJ signale les comptes sans mot de passe joignables à distance.
• Créer un compte dédié par usage (un par application, un par utilisateur humain) plutôt que de partager root ou un compte unique : cela permet de tracer les actions, de limiter les dégâts d'une fuite de mot de passe, et de verrouiller (ACCOUNT LOCK) un seul compte sans affecter les autres.
• Donner le minimum nécessaire : un compte applicatif de lecture n'a besoin que de SELECT sur les bases qu'il consulte réellement, jamais de privilèges globaux.

Le détail du coffre des comptes — son chiffrement, l'emplacement et la protection de sa clé, et la procédure de secours en cas de coffre inutilisable — est couvert au chapitre 16, « Administration du serveur ».

Voir aussi#

• Chapitre 14, « Transactions et concurrence », pour ce qui se passe une fois connecté et autorisé.
• Chapitre 16, « Administration du serveur », pour le coffre des comptes chiffré, sa clé et --reset-accounts.