Gestion des utilisateurs

Au sommaire :

• création, modification et suppression d'utilisateurs ;
• explication des privilèges et options des utilisateurs ;
• attribution et révocation des privilèges des utilisateurs.

Introduction

Pendant l'année, nous avons créé plusieurs base de données avec le login root.

Mais ce ne sont pas les seules bases de données existant sur votre serveur MySQL. Connectez-vous avec l'utilisateur "root" (sinon certaines bases de données vous seront cachées) et exécutez la requête suivante sous l'onglet :

SHOW DATABASES;

Database

information_schema

…

mysql

performance_schema

Nos bases sont bien là, en compagnie de quelques autres :

• information_schema : cette base de données stocke les informations sur toutes les bases de données. Les tables, les colonnes, le type des colonnes, les procédures des bases de données y sont recensés, avec leurs caractéristiques. Nous verrons cette base de données plus en détail dans le prochain chapitre.
• performance_schema : permet de stocker des informations sur les actions effectuées sur le serveur (temps d'exécution, temps d'attente dus aux verrous, etc.)
• test : il s'agit d'une base de test automatiquement créée. Si vous ne l'avez pas utilisée, elle ne contient rien.
• mysql : contient de nombreuses informations sur le serveur. Entre autres, c'est dans cette base que sont stockés les utilisateurs et leurs privilèges.

Les utilisateurs et leurs privilèges

Privilèges des utilisateurs

Lorsque l'on se connecte à MySQL, on le fait avec un utilisateur. Chaque utilisateur possède une série de privilèges relatifs aux données stockées sur le serveur : le privilège de sélectionner les données, de les modifier, de créer des objets, etc.
Ces privilèges peuvent exister à plusieurs niveaux : base de données, tables, colonnes, procédures, etc.

Par exemple, on peut créer un utilisateur et lui donner des privilèges avec les commandes suivantes :

CREATE USER 'debase'@'localhost' identified by 'secret'

GRANT ALL PRIVILEGES ON cafe.* TO 'student'@'localhost';

Cette requête a donné tous les droits sur la base de données cafe à l'utilisateur debase@'localhost'.

Stockage des utilisateurs et privilèges

Toutes ces informations sont stockées dans la base de données mysql.
Les utilisateurs sont stockés dans la table user, avec leurs privilèges globaux (c'est-à-dire valables au niveau du serveur, sur toutes les bases de données). La base mysql possède par ailleurs quatre tables permettant de stocker les privilèges des utilisateurs à différents niveaux.

• db : privilèges au niveau des bases de données.
• tables_priv : privilèges au niveau des tables.
• columns_priv : privilèges au niveau des colonnes.
• proc_priv : privilèges au niveau des routines (procédures et fonctions stockées).

Modifications

Il est tout à fait possible d'ajouter, de modifier et de supprimer des utilisateurs en utilisant des requêtes INSERT, UPDATE  ou DELETE  directement sur la table mysql.user. De même pour leurs privilèges, avec les tables mysql.db, mysql.tables_priv, mysql.columns_priv et mysql.procs_priv.

Cependant, en général, on utilise plutôt des commandes dédiées à cet usage. Ainsi, pas besoin de se soucier de la structure de ces tables, et le risque d'erreur est moins grand. Ce sont ces commandes que nous allons décortiquer dans la suite de ce chapitre.

Tout comme on peut préciser la table à laquelle appartient une colonne en préfixant le nom de la colonne par le nom de la table, nom_table.nom_colonne, il est possible de préciser à quelle base de données appartient une table, nom_bdd.nom_table, voire de préciser à la fois la table et la base de données dans laquelle se trouve une colonne : nom_bdd.nom_table.nom_colonne.

Création, modification et suppression des utilisateurs

Syntaxe pour la création et la suppression

Voici les requêtes à utiliser pour créer et supprimer un utilisateur :

-- Création

CREATE USER 'login'@'hote' [IDENTIFIED BY 'mot_de_passe'];

-- Suppression

DROP USER 'login'@'hote';

Utilisateur

L'utilisateur est donc défini par deux éléments :

• son login ;
• l'hôte à partir duquel il se connecte.

Login

Le login est un simple identifiant. Vous pouvez le choisir comme vous voulez. Il n'est pas obligatoire de l'entourer de quotes, sauf s'il contient des caractères spéciaux comme -ou @. C'est cependant conseillé.

Hôte

L'hôte est l'adresse à partir de laquelle l'utilisateur va se connecter. Si l'utilisateur se connecte à partir de la machine sur laquelle le serveur MySQL se trouve, on peut utiliser 'localhost'. Sinon, on utilise en général une adresse IP ou un nom de domaine.

Exemples

CREATE USER 'max'@'localhost' IDENTIFIED BY 'maxisthebest';

CREATE USER 'elodie'@'194.28.12.4' IDENTIFIED BY 'toto1';

CREATE USER 'gabriel'@'arb.brab.net' IDENTIFIED BY 'jenesaispas';

Il est également possible de permettre à un utilisateur de se connecter à partir de plusieurs hôtes différents (sans devoir créer un utilisateur par hôte) : en utilisant le joker %, on peut préciser des noms d'hôtes partiels ou permettre à l'utilisateur de se connecter à partir de n'importe quel hôte.

Exemples

-- thibault peut se connecter à partir de n'importe quel hôte dont l'adresse IP commence par 194.28.12.

CREATE USER 'thibault'@'194.28.12.%' IDENTIFIED BY 'basketball8';

-- joelle peut se connecter à partir de n'importe quel hôte du domaine brab.net

CREATE USER 'joelle'@'%.brab.net' IDENTIFIED BY 'singingisfun';

-- anna peut se connecter à partir de n'importe quel hôte

CREATE USER 'anna'@'%' IDENTIFIED BY 'anna21';

Comme pour le login, les quotes ne sont pas obligatoires, sauf si un caractère spécial est utilisé (comme le joker %, par exemple). Notez que, si vous ne précisez pas d'hôte, c'est comme si vous autorisiez tous les hôtes. 'hannah'@'%'  est donc équivalent à 'hannah'.

Les quotes se placent indépendamment autour du login et autour de l'hôte. N'entourez pas tout par des quotes : CREATE USER 'marie@localhost'  créera un utilisateur dont le login est 'marie@localhost', autorisé à se connecter à partir de n'importe quel hôte.

Renommer l'utilisateur

Pour modifier l'identifiant d'un utilisateur (login et/ou hôte), on peut utiliser RENAME USER ancien_utilisateur TO nouvel_utilisateur.

Exemple :on renomme max en maxime, en gardant le même hôte.

RENAME USER 'max'@'localhost' TO 'maxime'@'localhost';

Mot de passe

Le mot de passe de l'utilisateur est donné par la clause IDENTIFIED BY. Cette clause n'est pas obligatoire, auquel cas l'utilisateur peut se connecter sans donner de mot de passe. Ce n'est évidemment pas une bonne idée du point de vue de la sécurité. Évitez au maximum les utilisateurs sans mot de passe.

Lorsqu'un mot de passe est précisé, il n'est pas stocké tel quel dans la table mysql.user. Il est d'abord hashé, et c'est cette valeur hashée qui est stockée.

Modifier le mot de passe

Pour modifier le mot de passe d'un utilisateur, on peut utiliser la commande SET PASSWORD  (à condition d'avoir les privilèges nécessaires, ou d'être connecté avec l'utilisateur dont on veut changer le mot de passe).
Cependant, cette commande ne hashe pas le mot de passe automatiquement. Il faut donc utiliser la fonction PASSWORD().

Exemple

SET PASSWORD FOR 'thibault'@'194.28.12.%' = PASSWORD('basket8');

Supprimer un utilisateur

DROP USER thibault;

Il faut bien évidemment avoir les droits pour supprimer un utilisateur. Le faire sous root.

Les privilèges - introduction

Lorsque l'on crée un utilisateur avec CREATE USER, celui-ci n'a au départ aucun privilège, aucun droit.
En SQL, avoir un privilège, c'est avoir l'autorisation d'effectuer une action sur un objet.

Un utilisateur sans aucun privilège ne peut rien faire d'autre que se connecter. Il n'aura pas accès aux données, ne pourra créer aucun objet (base/table/procédure/autre) ni en utiliser.

Les différents privilèges

Il existe de nombreux privilèges dont voici une sélection des plus utilisés (à l'exception des privilèges particuliers ALL, USAGE  et GRANT OPTION  que nous verrons plus loin).

Privilèges du CRUD

Les privilèges SELECT, INSERT, UPDATE  et DELETE  permettent aux utilisateurs d'utiliser ces mêmes commandes.

Privilèges concernant les tables, les vues et les bases de données

Privilège	Action autorisée
CREATE TABLE	Création de tables
CREATE TEMPORARY TABLE	Création de tables temporaires
CREATE VIEW	Création de vues (il faut également avoir le privilège SELECTsur les colonnes sélectionnées par la vue)
ALTER	Modification de tables (avec ALTER TABLE)
DROP	Suppression de tables, vues et bases de données

Autres privilèges

Privilège	Action autorisée
CREATE ROUTINE	Création de procédures stockées (et de fonctions stockées - non couvertes dans ce cours)
ALTER ROUTINE	Modification et suppression de procédures stockées (et de fonctions stockées)
EXECUTE	Exécution de procédures stockées (et de fonctions stockées)
INDEX	Création et suppression d'index
TRIGGER	Création et suppression de triggers
LOCK TABLES	Verrouillage de tables (sur lesquelles on a le privilège SELECT)
CREATE USER	Gestion d'utilisateur (commandes CREATE USER, DROP USER, RENAME USER  et SET PASSWORD)

Les différents niveaux d'application des privilèges

Lorsque l'on accorde un privilège à un utilisateur, il faut également préciser à quoi s'applique ce privilège.

Niveau	Application du privilège
*.*	Privilège global : s'applique à toutes les bases de données, à tous les objets. Un privilège de ce niveau sera stocké dans la table mysql.user.
*	Si aucune base de données n'a été préalablement sélectionnée (avec USE nom_bdd), c'est l'équivalent de *.* (privilège stocké dans mysql.user). Sinon, le privilège s'appliquera à tous les objets de la base de données que l'on utilise (et sera stocké dans la table mysql.db).
nom_bdd.*	Privilège de base de données : s'applique à tous les objets de la base nom_bdd (stocké dans mysql.db).
nom_bdd.nom_table	Privilège de table (stocké dans mysql.tables_priv).
nom_table	Privilège de table : s'applique à la table nom_table de la base de données dans laquelle on se trouve, sélectionnée au préalable avec USE nom_bdd  (stocké dans mysql.tables_priv).
nom_bdd.nom_routine	S'applique à la procédure (ou fonction) stockée nom_bdd.nom_routine (privilège stocké dans mysql.procs_priv).

Les privilèges peuvent aussi être restreints à certaines colonnes, auquel cas ils seront stockés dans la table mysql.columns_priv. Nous verrons comment restreindre un privilège à certaines colonnes avec la commande GRANT.

Ajout et révocation de privilèges

Ajout de privilèges

Pour pouvoir ajouter un privilège à un utilisateur, il faut posséder le privilège GRANT OPTION. Pour l'instant, seul l'utilisateur "root" le possède. Étant donné qu'il s'agit d'un privilège un peu particulier, nous n'en parlerons pas tout de suite. Connectez-vous donc avec "root" pour exécuter les commandes de cette partie.

Syntaxe

La commande pour ajouter des privilèges à un utilisateur est la suivante :

GRANT privilege [(liste_colonnes)] [, privilege [(liste_colonnes)], ...]

ON [type_objet] niveau_privilege

TO utilisateur [IDENTIFIED BY mot_de_passe];

· privilege  : le privilège à accorder à l'utilisateur (SELECT, CREATE VIEW, EXECUTE…) ;

· (liste_colonnes)  : facultatif - liste des colonnes auxquelles le privilège s'applique ;

· niveau_privilege  : niveau auquel le privilège s'applique (*.*, nom_bdd.nom_table…) ;

· type_objet  : en cas de noms ambigus, il est possible de préciser à quoi se rapporte le niveau, TABLE  ou PROCEDURE.

On peut accorder plusieurs privilèges en une fois : il suffit de séparer les privilèges par une virgule. Si l'on veut restreindre tout ou partie des privilèges à certaines colonnes, la liste doit en être précisée pour chaque privilège.

Exemples

1. On crée un utilisateur 'john'@'localhost', en lui donnant les privilèges SELECT, INSERT  et DELETE  sur la table cafe.serveur, et UPDATE  sur les colonnes rueserv, cpserv, villeserv de la table cafe.serveur.

CREATE USER 'john'@'localhost' IDENTIFIED BY 'exemple2021';

GRANT SELECT,

      UPDATE (rueserv, cpserv, villeserv),

      DELETE,

      INSERT

ON cafe.serveur

TO 'john'@'localhost';

2. On accorde le privilège SELECT  à l'utilisateur 'john'@'localhost'  sur la table cafe.facture.

GRANT SELECT

ON TABLE cafe.facture -- On précise que c'est une table (facultatif)

TO 'john'@'localhost';

3. On accorde à 'john'@'localhost'  le privilège de créer et d'exécuter des procédures stockées dans la base de données cafe.

GRANT CREATE ROUTINE, EXECUTE

ON cafe.*

TO 'john'@'localhost';

Révocation de privilèges

Pour retirer un ou plusieurs privilèges à un utilisateur, on utilise la commande REVOKE.

REVOKE privilege [, privilege, ...]

ON niveau_privilege

FROM utilisateur;

Exemple

REVOKE DELETE

ON cafe.serveur

FROM 'john'@'localhost';

Privilèges particuliers

Les privilèges ALL, USAGE et GRANT OPTION

Pour terminer avec les différents privilèges, nous allons parler de trois privilèges un peu particuliers.

ALL

Le privilège ALL  (ou ALL PRIVILEGES), comme son nom l'indique, représente tous les privilèges. Accorder le privilège ALL  revient donc à accorder tous les droits à l'utilisateur. Il faut évidemment préciser le niveau auquel tous les droits sont accordés (on octroie tous les privilèges possibles sur une table, ou sur une base de données, etc.).

Un privilège fait exception : GRANT OPTION  n'est pas compris dans les privilèges représentés par ALL.

Exemple :on accorde tous les droits sur la table conso à 'john'@'localhost'.

GRANT ALL

ON cafe.conso

TO 'john'@'localhost';

USAGE

À l'inverse de ALL, le privilège USAGE  signifie "aucun privilège". L'utilisateur pourra juste se connecter à mysql.

GRANT OPTION

Nous voici donc au fameux privilège GRANT OPTION. Un utilisateur ayant ce privilège est autorisé à utiliser la commande GRANT, pour accorder des privilèges à d'autres utilisateurs.
Ce privilège n'est pas compris dans le privilège ALL. Par ailleurs, un utilisateur ne peut accorder que les privilèges qu'il possède lui-même.

On peut accorder GRANT OPTION  de deux manières :

· comme un privilège normal, après le mot GRANT  ;

• à la fin de la commande GRANT, avec la clause WITH GRANT OPTION.

Exemple : on accorde les privilèges SELECT, UPDATE, INSERT, DELETE  et GRANT OPTION  sur la base de données cafe à 'joseph'@'localhost'.

GRANT SELECT, UPDATE, INSERT, DELETE, GRANT OPTION

ON cafe.*

TO 'joseph'@'localhost' IDENTIFIED BY 'ploc4';

-- OU

GRANT SELECT, UPDATE, INSERT, DELETE

ON cafe.*

TO 'joseph'@'localhost' IDENTIFIED BY 'ploc4'

WITH GRANT OPTION;

Le privilège ALL  doit s'utiliser tout seul. Il n'est donc pas possible d'accorder GRANT OPTION  et ALL  de la manière suivante : GRANT ALL, GRANT OPTION…  Il est nécessaire dans ce cas d'utiliser WITH GRANT OPTION.

Particularité des triggers, vues et procédures stockées

Les triggers, les vues et les procédures stockées (ainsi que les fonctions stockées, non couvertes par ce cours) ont un système spécial quant à la vérification des privilèges des utilisateurs.
En effet, ces objets sont créés dans le but d'être exécutés dans le futur, et l'utilisateur créant un tel objet pourrait bien être différent de l'utilisateur se servant de l'objet. Il y a donc deux types d'utilisateurs potentiels pour ces types d'objet : celui ayant défini l'objet, et celui utilisant l'objet. Quels privilèges faut-il vérifier lorsqu'une procédure est exécutée ? Lorsque la requête SELECT  d'une vue est exécutée ?

Par défaut, ce sont les privilèges du définisseur (celui qui a défini l'objet) qui sont vérifiés. Ce qui veut dire qu'un utilisateur pourrait exécuter une procédure agissant sur des tables sur lesquelles il n'a lui-même aucun privilège, l'important étant les privilèges de l'utilisateur ayant défini la procédure. Bien entendu, un utilisateur doit toujours avoir le privilège EXECUTE  pour exécuter une procédure stockée. Les privilèges du définisseur concernent les instructions à l'intérieur de la procédure ou du trigger (ou de la requête SELECT  pour une vue).

Exemple :avec l'utilisateur etudiant, on définit une procédure faisant une requête SELECT  sur la table conso. On exécute ensuite cette procédure avec l'utilisateur john, qui n'a aucun droit sur la table conso.

Utilisateur etudiant :

USE cafe;

DELIMITER |

CREATE PROCEDURE test_definer()

BEGIN

    SELECT * FROM conso;

END |

DELIMITER ;

Utilisateur john :

USE cafe;

SELECT * FROM conso;

CALL test_definer();

L'utilisateur john n'a aucun droit sur conso. La requête SELECT  échoue donc avec le message d'erreur suivant :

ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table conso

Par contre, john a le droit d'exécuter les procédures de la base de données cafe. Il exécute donc sans problème test_definer(), qui lui affiche le contenu de conso. Les privilèges vérifiés au niveau des instructions exécutées par la procédure sont en effet ceux de l'utilisateur etudiant ayant défini celle-ci.