RLS : Securité au niveau de ligne dans Oracle

Date de publication : le 26 Septembre 2005

Dans cet Article

I. Introduction au RLS
II. Utilisation du RLS
II-A. Le paramètre update_check
II-B. Les regles RLS statiques
III. RLS dans Oracle 10g
IV. Dépannage

I. Introduction au RLS

RLS ou VPD consistent à personnaliser la façon de voir une table par plusieurs utilisateurs en limitant l'accès sur certains lignes de colonnes d'une table.

On commence par un exemple simple, en créant une table SALAIRES sous l'utilisateur DVP :

CREATE TABLE salaires (nom VARCHAR2(10), sal NUMBER(7,2));

SQL> SELECT * FROM salaires;

NOM                         SAL
-------------------- ----------
bouyao                    10000
jaouad                    20000
SheikYerbouti             90000
Fred_D                    80000
LeoAnderson               70000

On veut limiter l'accés à l'utilisateur DVP d'acceder au données du champ SAL qui sont plus grand à 70000 dans la table SALAIRES. Si l'utilisateur DVP lance la requête suivant :

SELECT * FROM salaires ;

On veut que le RLS transforme la requête d'une manière transparente en :

SQL> SELECT * FROM salaires WHERE sal 70000;

Avant tout, on doit crée une fonction qui construit et renvois cette predicat en une chaine de caractères.

CREATE OR REPLACE FUNCTION salaires_dispo (
     p_nom_schema   IN   VARCHAR2,
     p_nom_objet    IN   VARCHAR2
)
     RETURN VARCHAR2
IS
BEGIN
     RETURN 'SAL  70000';
END;
/

On a deux arguments qui ne sont pas utilisés dans la fonction, mais ils seront nécessaire pour l'architecture du RLS.

Ici on vérifie que la fonction retourne la valeur desirée :

DECLARE
       l_return_string   VARCHAR2 (2000);
    BEGIN
       l_return_string := salaires_dispo ('X', 'X');
       DBMS_OUTPUT.put_line ('String retournee = ' || l_return_string);
    END;
/

String retournee = SAL  70000

Dans l'etape suivante, on crée une methode de securité qui va définir quand et comment Le predicat applique l'instruction.

BEGIN
     DBMS_RLS.add_policy (object_schema  => 'DVP',
         object_name          => 'SALAIRES',
         policy_name          => 'SALAIRES_POLICY',
         function_schema      => 'DVP',
         policy_function      => 'SALAIRES_DISPO',
         statement_types      => 'INSERT, UPDATE, DELETE, SELECT'
         );
END;

Donc, on a ajoute une methode nommé SALAIRES_POLICY dans la table SALAIRES qui appartient à l'utilisateur DVP. Cette methode doit appliquer le filtre de la fonction SALAIRES_DISPO qui appartient à l'utilisateur DVP chaque fois qu'un utilisateur effectue une opération INSERT, UPDATE, DELETE ou SELECT.

Sous le compte DVP :

SQL> select * from salaires;

NOM                         SAL
-------------------- ----------
bouyao                    10000
jaouad                    20000

Sous le compte SYS :

SQL> select * from dvp.salaires;

NOM                         SAL
-------------------- ----------
bouyao                    10000
jaouad                    20000
SheikYerbouti             90000
Fred_D                    80000
LeoAnderson               70000

On remarque, que l'utlisateur DVP voit que 2 lignes et que l'utlisateur SYS voit 5 lignes. Le resultat sera pareil pour les commandes UPDATE et DELETE.

Quand un utilisateur opère sur une table qui est sous le contrôle du RLS, la reqûete SQL est intercepté et re-écrite par le moteur de la base, en ajoutant le resultat de la fonction.

Ces methodes n'appartient à aucun utlisateur. N'importe quel utilisateur ayant le privilège EXECUTE sur le package DBMS_RLS, peut créer ou supprimer une methode. Si une personne accorde le privilège EXECUTE à PUBLIC du package DBMS_RLS, alors il faut le retirer sur-le-champ.

II. Utilisation du RLS

II-A. Le paramètre update_check

Regardons l'exemple suivante :

SQL> UPDATE salaires SET sal=40000;

2 ligne(s) mise(s) à jour.

SQL> UPDATE salaires SET sal=30000;

2 ligne(s) mise(s) à jour.

SQL>

Seulement deux lignes sont modifiés.

SQL> UPDATE salaires SET sal=75000;

2 ligne(s) mise(s) à jour.

SQL> UPDATE salaires SET sal=30000;

0 ligne(s) mise(s) à jour.

On remarque qu'après la première mise a jours, toutes les colonnes de la table deviennent invisibles. Ce qui pose une situation de confusion : car le fait de lancer une requête peut modifier l'accès sur les lignes. Pour contrer ce comportement, on utilise le paramètre update_check.

Sous le compte SYS on remet les salaires qu'on a modifiés

SQL> connect /as sysdba
Connecté.
SQL> UPDATE dvp.salaires SET sal=10000 WHERE nom='bouyao';
1 ligne mise à jour.

SQL> UPDATE dvp.salaires SET sal=20000 WHERE nom='jaouad';
1 ligne mise à jour.

SQL> commit;
Validation effectuÚe.
SQL>

Et sous le compte DVP on'aura :

SQL> select * from salaires;

NOM                         SAL
-------------------- ----------
bouyao                    10000
jaouad                    20000

SQL>

On supprime la regle. Attention il faut avoir le privilège EXECUTE ON DBMS_RLS pour effectuer cette opération.

SQL> BEGIN
  2  DBMS_RLS.drop_policy (
  3  object_name => 'DVP',
  4  object_schema => 'DVP',
  5  policy_name => 'SALAIRES_POLICY');
  6  END;
  7      /

Mainetant on ajoute le paramètre update_check en lui attribuant la valeur TRUE.

SQL>

BEGIN
     DBMS_RLS.add_policy (object_schema  => 'DVP',
         object_name          => 'SALAIRES',
         policy_name          => 'SALAIRES_POLICY',
         function_schema      => 'DVP',
         policy_function      => 'SALAIRES_DISPO',
         statement_types      => 'INSERT, UPDATE, DELETE, SELECT',
         update_check         => TRUE
         );
END;
/

Après que la regle soit placé sur la table

SQL> UPDATE salaires SET sal = 75000;
UPDATE salaires SET sal = 75000
       *
ERREUR à la ligne 1 :
ORA-28115: régle comportant une violation d'option de contrôle

SQL>

II-B. Les regles RLS statiques

Pour eviter que la fontion soit appelé à chaque fois qu'on lance une requête, on a jouté alors un nouveau paramètre static_policy et on le fixe à TRUE. Ce qui va mettre le resultat de la fonction dans la mémoire cache.

BEGIN
     DBMS_RLS.add_policy (object_schema  => 'DVP',
         object_name          => 'SALAIRES',
         policy_name          => 'SALAIRES_POLICY',
         function_schema      => 'DVP',
         policy_function      => 'SALAIRES_DISPO',
         statement_types      => 'INSERT, UPDATE, DELETE, SELECT',
         update_check         => TRUE,
         static_policy    => TRUE
         );
END;
/

La methode statique peut augmenter les performances, mais elle peut aussi introduire de nouveaux bugs. Si l'attribu dérive ou depends d'un changement de valeur, comme le temps ou l'adresse IP, alors, on devrait définir une methode dynamique au lieu d'une methode statique.

SQL> CREATE TABLE trigger_fire
      2  (
      3     val NUMBER
      4  );

    Table created.

    SQL> INSERT INTO trigger_fire
      2  VALUES
      3  (1);

    1 row created.

    SQL> COMMIT;

    Commit complete.
    
    SQL> CREATE OR REPLACE FUNCTION authorized_emps (
      2     p_schema_name   IN   VARCHAR2,
      3     p_object_name   IN   VARCHAR2
      4  )
      5     RETURN VARCHAR2
      6  IS
      7     l_return_val   VARCHAR2 (2000);
      8     PRAGMA AUTONOMOUS_TRANSACTION;
      9  BEGIN
     10     l_return_val := 'SAL inf= ' || TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) * 100;
     11
     12     UPDATE trigger_fire
     13        SET val = val + 1;
     14
     15     COMMIT;
     16     RETURN l_return_val;
     17 END;
     18  /

III. RLS dans Oracle 10g

IV. Dépannage

Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.