La gestion des privilèges

Voici une liste des différents privilèges que SQL permet et les objets sur lesquels ces privilèges portent :

Notons qu'il n'est pas possible de définir des droits sur la création, la modification ou la suppression des éléments de schema (base de données), ceci étant défini lors de la création du schema.

C'est l'ordre SQL GRANT qui permet d'attribuer un privilège à différents utilisateurs sur différents objets.

Voici la syntaxe de l'ordre SQL GRANT :

GRANT <privileges>
   TO <gratifié> [ { , <gratifié> }... ]
       [ WITH GRANT OPTION ]

   <privileges> ::=
      <privilège> [ { , <privilège> }... ]

    <privilege> ::=
        ALL PRIVILEGES ON <objet table>
      | <privilège d'action>
      | <privilege d'usage>
      | <privilege de référence>

      <privilège d'action> ::=
         <action> [ { , <action> }... ] ON <objet table>

       <privilege d'usage> ::=
         USAGE ON <objet d'usage>

      <privilege de référence> ::=
         REFERENCES [ ( <liste de colonne> ) ] <nom de table>

         <action> ::=
              SELECT
            | DELETE
            | INSERT [ ( <liste de colonne> ) ]
            | UPDATE [ ( <liste de colonne> ) ]

         <objet table> ::=
            [ TABLE ] <nom de table ou de vue>

         <objet d'usage> ::=
            DOMAIN <nom de domaine>
            | COLLATION <nom de collation>
            | CHARACTER SET <nom de jeu de caractères>
            | TRANSLATION <nom de translation>

   <gratifié> ::=
       PUBLIC
     | <utilisateur>

La clause WITH GRANT OPTION, est utilisée pour autoriser la transmission des droits. La clause ALL PRIVILEGES n'a d'intérêt que dans le cadre de la transmission des droits.

Voici maintenant une batterie d'exemples afin de mieux comprendre comment utiliser cet ordre… Pour nos exemples, nous avons considéré que les utilisateurs DUPONT, DURAND, DUBOIS, DUVAL, DULAC et DUFOUR étaient créés dans la base de données. Celui qui lance les ordres (sauf indication contraire) est l'utilisateur DUHAMEL.

Exemple 3

GRANT SELECT
      ON T_CHAMBRE
      TO DUBOIS

Autorise DUBOIS à lancer des ordres SQL SELECT sur la table T_CHAMBRE. Notez l'absence du mot TABLE.

Exemple 4

GRANT INSERT, UPDATE, DELETE
      ON TABLE T_CHAMBRE
      TO DUVAL, DUBOIS

Autorise DUVAL et DUBOIS à modifier les données par tous les ordres SQL de mise à jour (INSERT, UPDATE, DELETE), mais pas à les lire !

Exemple 5

GRANT SELECT
      ON TABLE T_CMAMBRE
      TO DUFOUR WITH GRANT OPTION

Autorise DUFOUR à lancer des ordres SQL SELECT sur la table T_CHAMBRE, mais aussi à transmettre à tout autre utilisateur les droits qu'il a acquis dans cet ordre.

Exemple 6

GRANT SELECT, INSERT, DELETE
      ON TABLE T_CHAMBRE
      TO DURAND WITH GRANT OPTION

Autorise DURAND à lancer des ordres SQL SELECT, INSERT, DELETE sur la table T_CHAMBRE.

Exemple 7

GRANT SELECT, UPDATE
      ON TABLE T_CHAMBRE
      TO PUBLIC

Autorise tous les utilisateurs présents et à venir à lancer des ordres SQL SELECT et UPDATE sur la table T_CHAMBRE.

Exemple 8 : DURAND lance l'ordre suivant :

GRANT ALL PRIVILEGES
      ON TABLE T_CHAMBRE
      TO DUBOIS

Ce qui autorise DUBOIS à lancer sur la table T_CHAMBRE, les mêmes ordres SQL, que ceux autorisés à DURAND (SELECT, INSERT, DELETE).

On parle alors d'héritage de droits c'est-à-dire que l'utilisateur doté de ces droits peut à nouveau les céder à un ou plusieurs autres utilisateurs.

Exemple 9 : DURAND lance l'ordre suivant :

GRANT UPDATE
      ON TABLE T_CHAMBRE
      TO DUBOIS

Cet ordre va provoquer une erreur, car DURAND n'est pas autorisé à lancer des ordres UPDATE sur la table T_CHAMBRE et ne peut donc transmettre un droit qu'il n'a pas !

Est-il possible de gérer des privilèges plus fins que sur l'intégralité de la table ou de vue ? En particulier, peut-on gérer des privilèges au niveau de certaines colonnes d'une table ?
La réponse est OUI, mais il faut utiliser un peu d'astuce…

2-3-1. Privilèges INSERT et UPDATE sur colonne▲

On peut employer l'ordre GRANT pour ce faire :

Exemple 10

 

Sélectionnez

GRANT UPDATE (CHB_POSTE_TEL, CHB_COUCHAGE)
      ON TABLE T_CHAMBRE
      TO DULAC

Cet ordre permet à DULAC de modifier uniquement les colonnes « poste téléphonique » et « nombre de places de couchage » de la table T_CHAMBRE.

Plus curieux, on peut définir les colonnes utilisables pour un ordre d'insertion pour un utilisateur :

Exemple 11

 

Sélectionnez

GRANT INSERT (CHB_NUMERO, CHB_ETAGE, CHB_BAIN, CHB_DOUCHE, CHB_WC)
      ON TABLE T_CHAMBRE
      TO DULAC

Cet ordre permet à DULAC d'insérer une nouvelle ligne dans la table, uniquement en spécifiant les colonnes listées. Le problème est que dans cette liste ne figure pas la colonne clef… Autrement dit, DULAC ne pourra jamais rien insérer du tout, sauf si la clef est calculée par un déclencheur avant insertion.

NOTA : dans le cas de l'attribution de privilèges d'insertion sur colonne, il est indispensable de faire figurer toutes les colonnes NOT NULL n'ayant ni clause de valeur par défaut, ni remplissage par un trigger avant insertion. Sans cela cette autorisation est illusoire !

2-3-2. Privilèges SELECT sur colonne▲

Ce type de privilège n'est pas géré directement par un ordre SQL.
En effet, il n'est pas possible d'écrire :

Exemple 12

 

Sélectionnez

GRANT SELECT (CHB_NUMERO, CHB_ETAGE, CHB_BAIN, CHB_DOUCHE, CHB_WC)
      ON TABLE T_CHAMBRE
      TO DULAC

Cet ordre n'est pas légal au niveau de SQL.

Mais un ordre GRANT peut porter sur une vue !
Nous voilà donc sauvés : créer une vue pour gérer les privilèges de sélection de l'utilisateur DULAC..

Exemple 13

Sélectionnez CREATE VIEW V_CHAMBRE AS SELECT CHB_NUMERO, CHB_ETAGE, CHB_BAIN, CHB_DOUCHE, CHB_WC FROM T_CHAMBRE

Sélectionnez GRANT SELECT ON V_CHAMBRE TO DULAC

Et le tour est joué !

Mais quel est donc l'intérêt de gérer des privilèges sur des domaines, des collations, des translations, ou des jeux de caractères ?

NOTA : pour les lecteurs qui ne seraient pas familiarisés avec ces objets d'une base de données, voici les références.
Pour les domaines :

• le SQL de A à Z, 1re partie, 6.6 les domaines
• le SQL de A à Z, 7e partie, 5.4 Définir des domaines

Pour les jeux de caractères, collations et translations, lire :

• le SQL de A à Z, 7e partie, 4 Une question de caractères

Pour comprendre l'intérêt des privilèges d'usage intéressons-nous au domaine en rappelant que le domaine est constitué d'un type de données assorties d'autant de règles que l'on veut (contraintes) et même aucune si on le désire. En outre, le domaine est utilisé en remplacement d'un type de donnée SQL basique, partout où on en a besoin, dans la définition des tables, comme dans l'utilisation de la fonction CAST par exemple.
Voici la création d'un domaine permettant de spécifier un pourcentage :

Exemple 14

CREATE DOMAIN DMN_POURCENT FLOAT
       CHECK (VALUE BETWEEN 0 AND 100)

Le problème réside dans le fait qu'une vue peut avoir été créée avec un transtypage de domaine…

Exemple 15

SELECT CHB_ID, CHB_COUCHAGE,
       CAST(100 * (CHB_COUCHAGE
                   / (SELECT SUM(CHB_COUCHAGE)
                      FROM   T_CHAMBRE)) AS DMN_POURCENT) AS POURCENT_OCCUPATION
FROM   T_CHAMBRE

Dans ce cas que va-t-il se passer lors de la suppression du domaine ?

Si la suppression du domaine se fait avec la clause RESTRICT, le fait que cette vue utilise un transtypage provoquera une erreur et interdira la suppression effective du domaine.

Si la suppression du domaine se fait à l'aide de la clause CASCADE, alors la vue est supprimée !

C'est pourquoi on aura tout intérêt à introduire des privilèges d'usage pour les domaines, collations, translations et jeux de caractères, mais de façon parcimonieuse à certains utilisateurs capables d'instancier des vues par exemple. Cela permettra de limiter les dégâts…

Bien entendu ce qui se passe dans notre exemple avec les domaines est similaire à ce qui peut se passer avec les collations, translations et jeux de caractères.

Lorsque l'on crée des tables en liaisons les unes aux autres, on utilise très souvent le mécanisme d'intégrité référentiel afin de gérer les clefs étrangères.

Voyons ce qui se passe si, dans notre base exemple, nous attribuons les droits ainsi :

Exemple 16

GRANT SELECT, INSERT, UPDATE, DELETE
      ON TABLE T_CHAMBRE
      TO DUMONT

GRANT SELECT, INSERT, UPDATE, DELETE
      ON TABLE T_PLANNING
      TO DUMONT

GRANT SELECT, INSERT, UPDATE, DELETE
      ON TABLE TJ_CHB_PLN_CLI
      TO DUMONT

Dumont pourra sélectionner et supprimer sans problèmes dans toutes les tables. Il pourra mettre à jour les données et insérer sans aucun problème dans les tables T_CHAMBRE et T_PLANNING.
En revanche il se heurtera parfois à un refus de la base de données pour la mise à jour de la table de jointure TJ_CHB_PLN_CLI. Pire , il lui sera impossible d'insérer des données dans cette dernière table…
Quelle en est la raison ?

Regardons comment a été créée cette table de jointure :

CREATE TABLE TJ_CHB_PLN_CLI
(   CHB_ID               INTEGER               NOT NULL,
    PLN_JOUR             DATE                  not null,
    CLI_ID               INTEGER               not null,
    CHB_PLN_CLI_NB_PERS  SMALLINT              not null,
    CHB_PLN_CLI_RESERVE  NUMERIC(1)            not null        default 0,
    CHB_PLN_CLI_OCCUPE   NUMERIC(1)            not null        default 1,
    CONSTRAINT PK_TJ_CHB_PLN_CLI    PRIMARY KEY  (CHB_ID, PLN_JOUR) ,
    CONSTRAINT FK_CHB_ID            REFERENCES   T_CHAMBRE (CHB_ID) ,
    CONSTRAINT FK_PLN_JOUR          REFERENCES   T_PLANNING (PLN_JOUR) ,
    CONSTRAINT FK_CLI_ID            REFERENCES   T_CLIENT CLI_ID)
)

Elle utilise trois tables en référence : T_CHAMBRE, T_PLANNING, T_CLIENT. Or notre utilisateur DUMONT n'a aucun privilège sur la table T_CLIENT. Il lui sera donc impossible lors de l'insertion, comme lors de la mise à jour de préciser une valeur pour cette colonne sans qu'il se voie automatiquement infliger un refus du serveur.

Or donc, pour pouvoir définir une valeur pour la colonne CLI_ID lors de l'exécution des ordres UPDATE et INSERT, notre utilisateur DUMONT, doit avoir un privilège supplémentaire défini comme suit :

Exemple 17

GRANT REFERENCES (CLI_ID)
      ON TABLE T_CLIENT
      TO DUMONT

NOTA : le privilège de référence ne porte pas exclusivement sur les contraintes d'intégrité, mais sur toute contrainte faisant référence à une colonne d'une table externe.

Exemple 18

REVOKE SELECT
       ON T_CHAMBRE
       FROM DUBOIS

Supprime le privilège de selection de la table T_CHAMBRE attribué à DUBOIS dans l'exemple 1.

Exemple 19

REVOKE INSERT, DELETE
       ON TABLE T_CHAMBRE
       FROM DUVAL, DUBOIS

Supprime les privilèges d'insertion et de suppression de la table T_CHAMBRE attribuées à DUVAL et DUBOIS dans l'exemple 2, mais pas celui de mise à jour (UPDATE).

Exemple 20

REVOKE GRANT OPTION FOR SELECT
       ON TABLE T_CMAMBRE
       FROM DUFOUR

Supprime la possibilité pour DUFOUR de transmettre le privilège de sélection sur la table T_CHAMBRE.

Il existe cependant quelques pièges dans l'utilisation du mécanisme de révocation. Nous allons en montrer quelques-uns à l'aide de différents exemples. Rappelons simplement que celui qui lance les ordres (sauf indication contraire) est l'utilisateur DUHAMEL.

Contrairement aux droits « système » les privilèges sont cumulatifs. On peut ainsi obtenir plusieurs fois le même privilège sur le même objet en provenance de différents utilisateurs. Le privilège sera totalement retiré lorsque tous les utilisateurs ayant donné ce privilège l'auront retiré.

Exemple 21

GRANT SELECT
      ON T_CLIENT
      TO DUCROS WITH GRANT OPTION

GRANT SELECT
      ON T_CLIENT
      TO DUGLAND

C'est maintenant DUCROS qui est l'utilisateur qui va lancer l'ordre suivant :

GRANT SELECT
      ON T_CLIENT
      TO DUGLAND

Enfin, DUHAMEL reprend la main pour révoquer ainsi :

REVOKE SELECT
       ON T_CLIENT
       FROM DUGLAND

DUGLAND peut-il sélectionner des lignes de la table T_CLIENT ? La réponse est OUI, parce qu'il possède encore un droit de sélection venant de DUCROS !

Voici une autre problématique. Le super utilisateur PUBLIC ne vise personne en particulier ni en général. On ne peut donc retirer un privilège particulier à un utilisateur donné même si l'on a attribué des privilèges à « PUBLIC ».

Exemple 22

GRANT SELECT
      ON T_CLIENT
      TO PUBLIC

REVOKE INSERT, DELETE
       ON T_CLIENT
       FROM DUMOULIN

Ce dernier ordre SQL va retourner un message d'erreur et ne sera pas exécuté parce que DUMOULIN n'a jamais reçu les privilèges INSERT et DELETE sur la table CLIENT, bien qu'il hérite des privilèges de PUBLIC !

En fait il faut comprendre que le modèle de gestion des privilèges dans SQL repose sur la théorie des graphes et peut devenir vite compliqué lorsque l'on veut gérer finement de multiples droits.

Ainsi il n'est pas possible d'octroyer des privilèges à plusieurs objets simultanément :

Exemple 24

GRANT INSERT, DELETE
      ON TABLE T_CHAMBRE, T_CLIENT, T_PLANNING
      FOR DUVAL, DUBOIS

Un tel ordre est syntaxiquement incorrect du fait de la présence de plusieurs tables…
La conséquence est que la gestion fine des droits de cinq utilisateurs sur une base de données comportant une centaine de tables se traduit en au moins une centaine d'ordres à passer…

Il n'est pas possible d'appliquer un privilège de déniement à un objet. Cela n'est pas défini par la norme SQL.

Exemple 25

DENY DELETE
     TO DUVAL, DUBOIS

N'existe pas en SQL. Ce serait pourtant bien pratique de passer par un tel mécanisme…

Nous allons voir cependant que la norme SQL:1999 (SQL 3) propose l'ajout du concept de ROLE afin de pallier certains de ces défauts.