10. 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.

10.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 11) 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.

10.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é.

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.

DEFAULT ROLE ... peut être fixé à la création ou à la modification d'un compte (voir §10.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 (§10.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 §10.6).

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 (%, _).

10.3 Privilèges : GRANT et REVOKE#

Les trois 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).

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.%';

Ce qui n'est pas pris en charge#

Les privilèges par colonne (GRANT SELECT (col1, col2) ON ...) et les privilèges par routine (GRANT EXECUTE ON FUNCTION ... / ON PROCEDURE ...) ne sont pas pris en charge dans cette version (erreur 1235). Les privilèges s'accordent donc au minimum au niveau d'une table entière.

10.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();

10.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 disponibles pour le compte 'rapport'@'10.0.0.%'
SELECT * FROM information_schema.APPLICABLE_ROLES WHERE USER = 'rapport' AND HOST = '10.0.0.%';

-- Rôles actifs dans la session en cours
SELECT * FROM information_schema.ENABLED_ROLES;

Un compte ordinaire ne voit, dans ces tables comme dans information_schema.USERS, que ce qui le concerne ; authentication_string y vaut toujours NULL, et toute tentative d'écriture y est refusée (erreur 1044) — ce ne sont pas des tables mais une vue calculée du coffre des comptes.

10.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 :

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é.

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.

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).

10.7 Le compte root et bonnes pratiques#

Un compte root est créé automatiquement avec le dossier de données, doté de tous les privilèges 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é.

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

• Changer le mot de passe du compte root dès l'installation (ALTER USER 'root'@'%' 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 (§10.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 11, « Administration du serveur ».

Voir aussi#

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