Le langage SQL : tables, contraintes et droits
Partie 4 sur 4
8Gérer les droits : GRANT et REVOKE
Plusieurs personnes travaillent en même temps sur une base, et elles n’ont pas les mêmes besoins : certaines doivent modifier des données, d’autres seulement les consulter, d’autres encore administrer la structure. On définit donc des permissions pour chaque personne, après lui avoir attribué un mot de passe. Ces permissions (on dit aussi droits ou privilèges) se règlent avec deux instructions : GRANT (« accorder ») et REVOKE (« révoquer », c’est-à-dire retirer).
8.1Les privilèges
Les privilèges de base correspondent aux instructions de manipulation : SELECT (lire), INSERT (ajouter), UPDATE (modifier), DELETE (supprimer). La norme en ajoute d’autres : REFERENCES (le droit de créer une clé étrangère vers la table), EXECUTE (exécuter une procédure), USAGE, et, selon les produits, les droits de créer des tables ou de se connecter. Le principe est que le droit se donne sur un objet précis : telle table, telle vue, voire telle colonne.
8.2Accorder : GRANT
La forme générale est la suivante :
GRANT liste_de_privileges ON objet TO liste_d_utilisateurs [WITH GRANT OPTION];
Quelques exemples concrets :
GRANT SELECT ON client TO alice; GRANT SELECT, INSERT ON commande TO bruno, chloe; GRANT UPDATE (nom, prenom) ON etudiant TO jerome WITH GRANT OPTION; GRANT SELECT ON vue_clients_paris TO PUBLIC;
- On peut donner plusieurs droits à plusieurs personnes en une seule instruction.
- En précisant des noms de colonnes entre parenthèses, on limite le privilège à ces colonnes (le troisième exemple :
jeromene peut modifier que le nom et le prénom, pas les autres colonnes). - Le mot clé
PUBLICà la place d’une liste de personnes désigne tout le monde, y compris les comptes créés plus tard ;ALLà la place d’une liste de privilèges désigne tous les privilèges. Ces deux raccourcis sont pratiques... et dangereux :GRANT ALL ON ... TO PUBLICouvre la porte à tout le monde (voir « Le coin cyber »). - L’option
WITH GRANT OPTIONpermet à la personne qui reçoit le droit de le transmettre à son tour.
8.3Qui peut donner des droits ?
Retenez ceci : la seule personne pouvant accorder ou retirer des droits sur un élément (table, vue, index) est celle qui l’a créé, son propriétaire. Elle peut déléguer ce pouvoir, auquel cas la personne qui reçoit cet « honneur » pourra à son tour le transmettre. En pratique, les systèmes ajoutent des comptes d’administration qui peuvent tout régler, et, dans certains, des droits plus fins sur la gestion des droits eux-mêmes. Cette chaîne de délégation est le sujet de la figure 3.
8.4Retirer : REVOKE
La forme générale est symétrique :
REVOKE [GRANT OPTION FOR] liste_de_privileges ON objet FROM liste_d_utilisateurs;
L’option GRANT OPTION FOR ne retire que le droit de transmettre, pas le droit lui-même. Les mots clés PUBLIC et ALL fonctionnent comme pour GRANT, ainsi que les colonnes entre parenthèses.
Deux difficultés se posent à juste titre, qui sont celles de tout système de délégation :
- Quand on retire un droit à une personne, il faut le retirer aussi à celles à qui elle l’avait transmis. D’où une cascade : retirer à Alice son droit de transmettre revient à retirer à Bruno ce qu’elle lui avait donné. Les produits proposent des mots clés (
CASCADEouRESTRICT) pour dire s’il faut propager ou refuser le retrait s’il a des conséquences. - Une personne peut avoir reçu le même droit de plusieurs personnes à la fois. Dans ce cas, le retirer à l’une ne suffit pas : l’autre source subsiste. Le système doit retenir l’origine de chaque droit pour savoir quand il disparaît vraiment.
La conclusion reste valable : la mise en œuvre de REVOKE varie d’un produit à l’autre, il faut lire la documentation, et vérifier après coup que le droit a bien disparu (en essayant de l’utiliser avec le compte concerné).
8.5Les rôles : donner des droits aux fonctions, pas aux personnes
Les rôles sont devenus la bonne façon de gérer les droits. Un rôle est un ensemble nommé de privilèges (« lecteur », « comptabilité », « support »). On donne les droits au rôle, puis le rôle aux personnes :
CREATE ROLE lecteur; GRANT SELECT ON client, commande, produit TO lecteur; GRANT lecteur TO alice, bruno, chloe;
Quand quelqu’un change de poste ou quitte l’entreprise, on lui retire ou on lui donne un rôle : un seul geste, au lieu d’une liste de droits à reconstituer de mémoire. C’est plus simple, plus sûr, et surtout auditable : on peut dire à tout moment qui a le rôle « comptabilité ».
8.6Le moindre privilège
La règle qui guide tout est le principe du moindre privilège : chaque personne et chaque programme reçoit seulement les droits dont il a besoin, rien de plus. Le compte qu’utilise un site web pour lire et écrire ses données n’a pas besoin de pouvoir supprimer des tables. L’équipe d’analyse n’a besoin que de lire, et seulement sur des vues. Ainsi, si un compte est piraté ou si un programme contient une faille, les dégâts restent limités à ce que ce compte pouvait faire. Les autres bonnes habitudes : des comptes nominatifs (pas un compte « admin » partagé), des mots de passe forts et uniques (voir l’authentification), la revue régulière des droits, et le retrait immédiat des droits des personnes qui partent.
Pour s’exercer
Exercice 6 : la cascade des droits
Le propriétaire donne à Alice SELECT sur client avec WITH GRANT OPTION. Alice donne SELECT à Bruno. Plus tard, le propriétaire retire le droit à Alice. Bruno peut-il encore lire ? Et si Bruno avait reçu le même droit de Chloé, elle-même titulaire du droit ?
Voir la réponse
Dans le premier cas, Bruno perd son droit : il le tenait d’Alice, qui n’a plus rien à transmettre ; c’est le retrait en cascade (avec le mot clé CASCADE dans les produits qui l’exigent). Dans le second cas, il le conserve : il l’a aussi reçu de Chloé, et son droit ne disparaît que lorsque toutes les sources sont retirées. C’est le problème des « plusieurs origines ». D’où l’intérêt des rôles, qui évitent de longues chaînes.
Exercice 7 : supprimer sans risque
Vous devez supprimer la table ancien_client en production. Décrivez les précautions.
Voir la réponse
(1) Vérifier avec les équipes que plus aucun programme ni vue ne s’en sert (la base le dit en partie : une vue ou une contrainte qui en dépend bloquerait l’opération). (2) Faire une sauvegarde et tester sa restauration. (3) Commencer par renommer la table, puis attendre quelques semaines. (4) Faire l’opération avec un script relu par une deuxième personne, et un compte qui n’a ce droit que pour l’occasion. (5) Si la base le permet, l’enrober dans une transaction pour pouvoir annuler. (6) Noter l’opération dans l’historique des migrations.
9Faire évoluer une base sans accident
9.1Les instructions de définition et les transactions
Une transaction est un groupe d’instructions traité comme un tout : tout réussit, ou rien n’est changé. Les instructions de manipulation y sont soumises, et on peut les annuler avec ROLLBACK tant qu’on n’a pas validé (COMMIT). Pour les instructions de définition, la situation varie : PostgreSQL permet d’annuler la création ou la modification d’une table comme n’importe quelle écriture ; Oracle et MySQL valident automatiquement presque toutes ces instructions, sans retour possible. Raison de plus pour essayer d’abord sur une copie.
9.2Des scripts de migration, versionnés
La bonne pratique actuelle est de ne jamais modifier la structure à la main dans la base de production. Chaque changement est écrit dans un petit script daté et numéroté (« 042 : ajout de la colonne téléphone »), conservé avec le code dans un gestionnaire de versions, relu, puis appliqué par un outil qui note ceux déjà passés. Ce sont des migrations. Elles permettent de reconstruire une base à l’identique à partir de zéro, d’avoir l’historique de tous les changements, et d’appliquer en production exactement ce qui a été essayé en test. C’est un cas particulier de ce que l’on appelle l’automatisation de la livraison (voir DevOps et intégration continue). Autres règles : écrire les changements en deux temps compatibles (d’abord ajouter, ensuite retirer) pour ne pas interrompre le service, et mesurer sur une copie de taille réelle le temps que prend une opération sur une grosse table.
9.3SQL et ses variantes, aujourd’hui
La norme SQL continue d’évoluer : la dernière version est SQL:2023, adoptée en juin 2023, qui ajoute notamment un type pour le format JSON (le format d’échange de données très répandu sur le Web) et des requêtes sur des graphes (relations entre éléments, comme un réseau de personnes), tandis que les grands produits n’en implémentent que des parties. Pour l’administration quotidienne, les instructions vues ici sont stables depuis plus de trente ans, ce qui fait leur valeur : ce que vous apprenez ici servira encore longtemps. Les bases dites « NoSQL » (voir cet article) reposent sur d’autres principes, mais posent les mêmes questions de structure, de règles et de droits.
10Erreurs courantes et évolutions
- Le découpage en LDD, LMD et LCD reste juste et utile pour s’y retrouver.
- Plusieurs exemples classiques sont fautifs. Les textes entre guillemets doubles (la norme demande des apostrophes), la borne d’âge « moins de 100 » et le type
charpour un nom ne sont plus à recopier. - « ON DELETE » et « ON UPDATE » ne sont pas des triggers : ce sont des actions référentielles liées à la clé étrangère. Les vrais déclencheurs sont des programmes à part.
- CHECK est enfin appliqué partout : MySQL, qui l’ignorait, le fait respecter depuis 2019.
- Les assertions restent sur le papier : prévues par la norme, pratiquement absentes des produits.
- La syntaxe de modification varie :
MODIFY,ALTER COLUMN ... TYPE,RENAME. Le conseil de lire la documentation est encore plus vrai. - Les rôles sont devenus la façon normale d’attribuer des droits ; ils étaient à peine évoqués.
- Les vues matérialisées, les migrations versionnées et le moindre privilège sont les ajouts de pratique les plus marquants.
- Le risque lié à l’injection SQL conditionne aujourd’hui la manière de donner les droits (voir « Le coin cyber »).