SQL : contrôle de données

Quatrième partie sur le langage SQL : les requêtes de gestion du contrôle d'accès aux données.

I. Présentation

1) Intérêt des permissions

Les bases de données peuvent être utilisées par des utilisateurs différents, notamment lorsqu'elles sont partagées en réseau. Ces différents utilisateurs peuvent parfois y accéder simultanément et chaque utilisateur a des besoins différents. Il est ainsi possible d'associer des permissions appropriées à chaque profil d'utilisateur.

Un utilisateur qui créé un élément (table, vue, index,...) a la possibilité d'accorder ou de retirer des droits sur cet élément. Il existe également un administrateur de la base de données qui a tous les droits sur tous les éléments, et qui doit aussi gérer les privilèges.

2) Les clauses du controle de données

• Clause GRANT : Accorder des droits à un (ou plusieurs) utilisateurs.
• Clause REVOKE : Révoquer des droits à un (ou plusieurs) utilisateurs.

II. Les privilèges

• SELECT : droit d'interrogation d'une table
• INSERT [Liste de champs] : droit d'ajout d'enregistrements à une table (avec la possibilité de restrictions sur les champs complétés).
• UPDATE [Liste de champs] : droit de mise à jour des enregistrements d'une table (avec la possibilité de restriction sur les champs mis à jour).
• DELETE : droit de suppression d'enregistrements d'une table.
• CREATE, ALTER, DROP : droits sur la structure de la base de données.
• GRANT et REVOKE : droits sur la gestion des droits.

III. Attribution des droits

1) Utilisation de GRANT

GRANT Privilège
ON table.champ
TO utilisateur1
[IDENTIFIED BY "motdepasse"]
[WITH GRANT OPTION];

• WITH GRANT OPTION : définit si l'utilisateur peut lui-même accorder à un autre utilisateur les permissions qu'on lui attribue sur les éléments (facultatif)
• IDENTIFIED BY : définit le mot de passe de l'utilisateur (facultatif)
• PUBLIC : accorder les privilèges à tous les utilisateurs.
• ALL PRIVILEGES : à utiliser après le GRANT pour accorder tous les privilèges aux utilisateurs présents dans la liste.
• Possibilité de donner les droits à tous les champs d'une table en mettant une * après le nom de la table (table.*)
• Possibilité de donner les droits sur plusieurs tables/champs en même temps en indiquant à la suite de l'instruction ON la liste des objets où appliquer les droits (séparés par des virgules).
• Possibilité de donner les droits à plusieurs utilisateurs en même temps en indiquant à la suite de l'instruction TO la liste des utilisateurs à qui donner les droits (séparés par des virgules).
• Possibilité de définir la machine d'où doit se connecter l'utilisateur en ajoutant après le nom d'utilisateur @ suivi de localhost, % (toutes les machines) ou l'adresse IP autorisée.

2) Exemples

GRANT UPDATE(CotisationReglee)
ON Membre
TO Édouard, Johan, Vincent;

GRANT INSERT, SELECT
On Membre
TO PUBLIC;

GRANT ALL
ON Membre
TO Lucie
WITH GRANT OPTION;

3) Depuis la version 8 de MySQL

Avant la version 8 de MySQL, il suffisait de d'utiliser GRANT avec IDENTIFIED BY pour créer un utilisateur. Ce n'est plus possible avec MySQL 8, qui utilise de plus un autre système de cryptage pour les mots de passe. Si vous souhaitez créer un utilisateur puis lui donner les droits, il faut utiliser la commande CREATE USER avant la commande GRANT.

CREATE USER 'utilisateur'@'localhost' IDENTIFED WITH mysql_native_password BY 'motdepasse';

Puis

GRANT ALL ON *.* TO 'utilisateur'@'localhost' WITH GRANT OPTION;

IV. Révocation des permissions

1) Utilisation de REVOKE

REVOKE [GRANT OPTION FOR] privilege
ON table.champ
FROM utilisateur ;

• PUBLIC et ALL sont utilisables avec REVOKE.
• GRANT OPTION FOR : Ne retire pas les permissions aux utilisateurs, mais retire le droit d'accorder ces permissions à d'autres utilisateurs.
• Révoquer un droit à un utilisateur entraine généralement la révocation de ce droit aux autres utilisateurs à qui il l'a accordé (sauf si ces utilisateurs ont reçu ce droit de plusieurs utilisateurs).
• REVOKE fonctionne différemment selon les SGBDR.
• Comme pour GRANT, il y a la possibilité d'utiliser REVOKE pour plusieurs droits et plusieurs utilisateurs en les notant à la suite, séparés par des virgules.

2) Exemples

REVOKE UPDATE(CotisationReglee)
ON Membre
FROM Édouard, Johan, Vincent;

REVOKE ALL
ON Membre
FROM PUBLIC;

REVOKE GRANT OPTION FOR DELETE
ON Membre
FROM Lucie;