Sécurité granulaire

Date de publication : 16/06/2007

Comment gérer l'accès partiel aux données d'une table ?

I. Introduction
II. Les composants de la sécurité granulaire
II-A. Le contexte et son package
II-A-1. Le contexte (context)
II-A-2. Le package de contexte
II-B. Les politiques ou règles (policies) et son package
II-B-1. Politique ou règle (policy)
II-B-2. Package de sécurité
II-C. Trigger on_logon
III. Exemple
Remerciments

I. Introduction

La sécurité granulaire (Fine Grained Access Control) nous permet de gérer l'accès d'un sous-ensemble
d'enregistrements d'une table (l'accès partiel aux données d'une table).

La sécurité granulaire figure aussi sous d'autres noms dans la documentation d'oracle, dont on peut citer :
- La sécurité de niveau rangée (RLS).
- La politique ou règle de sécurité (policy) de bases de données privées virtuelles (VPD).

Exemple :
Dans une table dossier (no_dos,dt_dos,type_dos(secret,normal))
- Les dossiers secrets sont accessibles uniquement par les managers(les usagers qui ont le rôle sec_manager).
- Les employés ont seulement le droit de voir les dossiers normaux(les usagers qui ont le rôle sec_employe).

II. Les composants de la sécurité granulaire

La sécurité granulaire est constituée de trois éléments :

II-A. Le contexte et son package

II-A-1. Le contexte (context)

Le contexte est un regroupement de variables, sa synthaxe est la suivante :
Create or replace context nom_context using nom_proc_ou_pkg

SQL> create or replace context scott_dossier using pkg_dossier_context;

II-A-2. Le package de contexte

Un contexte est toujours lié à un package et c'est le seul qui a le droit d'initialiser les variables de ce contexte.

Remarque:
Si on utilise un package ou une procédure pour initialiser les variables de context
autre que le package ou la procédure déclaré au moment de la création du context,
il nous retourne l'erreur suivante :
Ora-01031 : insufficient privileges
Ora-06512 : at « SYS.DBMS_SESSION », at line ..
ora-65512 : at line 2

II-B. Les politiques ou règles (policies) et son package

II-B-1. Politique ou règle (policy)

Nous permet d'attacher la sécurité à une table, et déterminer les opérations DML(select, insert, update, delete) concernées par la sécurité pour cette même table.
Quand on élimine une table, la politique ou règle (policy) de cette table disparaît automatiquement. On peut avoir plusieurs politiques ou règles (polices) par table.

SQL> begin
  2       dbms_rls.add_policy(
  3                 object_schema => 'SCOTT',
  4                 object_name => 'DOSSIERS',
  5                 policy_name => 'SCOTT_DOSSIERS_POLICY',
  6                 function_schema => 'SCOTT',
  7                 policy_function => 'pkg_dossier_sec.dossier_predicate',
  8                 statement_types => 'select, insert, update, delete',
  9                 update_check => TRUE,
 10                enable => TRUE,
 11                static_policy => FALSE);
 12    end;
 13  /
PL/SQL procedure successfully completed.

Remarque:
Le statement_types => 'select, insert, update, delete' qui nous permet
de déterminer les opérations DML qui sont concernées par la sécurité.

II-B-2. Package de sécurité

Permet d'ajouter une clause de restriction à la requête originale, on l'appelle prédicat.

II-C. Trigger on_logon

Nous permet d'initialiser les variables du contexte, il se déclenche juste après la création d'une session Oracle.
- S'exécute à chaque connexion.
- Les variables d'un contexte sont disponibles et peuvent être utilisées dans un module, une procédure...etc
- Attention car la soumission des rapports déclenche une nouvelle session.

create or replace trigger scott_logon_trigger
     after logon on database
     declare
     NB VARCHAR2(30) ;
     begin
        select granted_role
           into nb
           from dba_role_privs    where grantee='SCOTT' and            
           granted_role='SEC_MANAGER' ;
      pkg_dossier_ context.set_manager;
     exception
      when no_data_found then
      pkg_dossier_context.set_employe;
      end;
   /

La politique ou règle (policy) est le plus important composant à la sécurité granulaire.
Les autres éléments rendent cette sécurité plus efficace et le système n'est pas ralenti.

III. Exemple

--Se connecter avec le user  scott :

-- Création de la table dossiers

SQL> connect scott/tiger@oratest

Connected.

creation de la table dossiers

CREATE TABLE DOSSIER(
  NO_DOS    NUMBER(6),
  DT_DOS    DATE,
  TYPE_DOS  VARCHAR2(50) CHECK (TYPE_DOS IN ('SECRET','NORMAL'))
);

--Insertions dans la table dossiers

SQL> insert into dossier values(1,trunc(sysdate),'SECRET');
1 row created.

SQL> insert into dossier values(2,trunc(sysdate),'SECRET');
1 row created.

SQL> insert into dossier values(3,trunc(sysdate),'SECRET');
1 row created.

SQL> insert into dossier values(4,trunc(sysdate),'NORMAL');
1 row created.

SQL> insert into dossier values(5,trunc(sysdate),'NORMAL');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dossier;

    NO_DOS DT_DOS    TYPE_DOS
---------- --------- ------------------------------
         1 22-MAY-07 SECRET
         2 22-MAY-07 SECRET
         3 22-MAY-07 SECRET
         4 22-MAY-07 NORMAL
         5 22-MAY-07 NORMAL

-- Se connecter avec le user sys :

-- Attribution des grants

grant execute on dbms_rls to scott;

grant execute on dbms_session to scott;

grant ADMINISTER DATABASE TRIGGER  to scott;

grant alter session to scott ;


--Se connecter avec le user system :  

--Création et attribution des rôles

Create role sec_employe ;

Create role sec_manager;

Grant select, update,insert, delete on scott.dossier to sec_employe ;

Grant select, update,insert, delete on scott.dossier to sec_manager;

Grant sec_manager to scott;

Grant create any context to scott;

grant create table to scott;

grant create procedure to scott;

-Se connecter avec le user  scott :

-- Création du package de context 

SQL> create or replace package pkg_dossier_context
  2   is
  3     procedure set_manager;
  4     procedure set_employe;
  5    end;

Package created.

 SQL> create or replace package body pkg_dossier_context
  2       as
  3   procedure set_manager
  4      is
  5    begin
  6       dbms_session.set_context('scott_dossier','app_role','manager');
  7    end;
  8           --
  9    procedure set_employe
 10       is
 11     begin
 12       dbms_session.set_context('scott_dossier','app_role','employe');
 13     end;
 14   end;


Package body created.

-- Création du context 

SQL> create or replace context scott_dossier using pkg_dossier_context;

Context created.


 -- Création du package de sécurité
 
SQL> create or replace package pkg_dossier_sec
  2    as
  3       function dossier_predicate(schema_name in varchar2, object_name in varchar2)
  4         return varchar2;
  5   end;

Package created.

SQL> create or replace package body pkg_dossier_sec
  2     as
  3        function dossier_predicate(schema_name in varchar2,object_name in varchar2)
  4         return varchar2
  5         is
  6                 lv_predicate varchar2(1000):='';
  7         begin
  8                 if sys_context('scott_dossier','app_role') = 'manager' then
  9                         lv_predicate:='type_dos=''SECRET'''; -- a le droit de voir uniquement 
							       --	les dossiers de type SECRET
 10                elsif sys_context('scott_dossier','app_role') = 'employe' then
 11                        lv_predicate:='type_dos=''NORMAL'''; -- a le droit de voir uniquement 
							   --les dossiers de type NORMAL
 12                 else
 13                        lv_predicate:='1=2'; -- block access
 14                end if;
 15               return lv_predicate;
 16        end;
 17     end;

Package body created.

-- Ajouter la policy (politique)

SQL> begin
 2        dbms_rls.add_policy(
 3                  object_schema => 'SCOTT',
 4                  object_name => 'DOSSIER',
 5                  policy_name => 'SCOTT_DOSSIER_POLICY',
 6                  function_schema => 'SCOTT',
 7                  policy_function => 'pkg_dossier_sec.dossier_predicate',
 8                   statement_types => 'select, insert, update, delete',
 9                   update_check => TRUE,
10                  enable => TRUE,
11                  static_policy => FALSE);
12   end;

PL/SQL procedure successfully completed.

-- Création du trigger on logon

SQL>  create or replace trigger scott_logon_trigger
  2     after logon on database
  3    declare
  4     NB VARCHAR2(30) ;
  5   begin
  6       select granted_role
  7        into nb
  8        from dba_role_privs 
  9        where grantee='SCOTT' and granted_role='SEC_MANAGER' ;
 10        pkg_dossier_context.set_manager;
 11      exception
 12         when no_data_found then
 13         pkg_dossier_context.set_employe;
 14   end;

Trigger created.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM DOSSIER;

    NO_DOS DT_DOS    TYPE_DOS
---------- --------- ------------------------------
         1 22-MAY-07 SECRET
         2 22-MAY-07 SECRET
         3 22-MAY-07 SECRET

Explication :

 Scott a le rôle sec_manager donc il a le droit de voir uniquement  les dossiers  de type SECRET, 
 une clause de restriction  se rajoute à ma requête initiale, 
 elle devient :SELECT * FROM DOSSIERS  WHERE  type_dos='SECRET' ;

-- Se connecter avec le user system :   

SQL> revoke sec_manager from scott;

Revoke succeeded.

SQL> grant sec_employe to scott;

Grant succeeded.

--Se connecter avec le user  scott :

SQL> connect scott/tiger@oratest;

Connected.

SQL> select * from dossier;

    NO_DOS DT_DOS    TYPE_DOS
---------- --------- ------------------------------
         4 22-MAY-07 NORMAL
         5 22-MAY-07 NORMAL

--Pour enlever le policy 

SQL>  begin
  2          dbms_rls.drop_policy(
  3           object_schema => 'SCOTT',
  4           object_name => 'DOSSIER',
  5           policy_name => 'SCOTT_DOSSIER_POLICY');
  6   end;

PL/SQL procedure successfully completed.


SQL> select * from dossier;

    NO_DOS DT_DOS    TYPE_DOS
---------- --------- ------------------------------
         1 22-MAY-07 SECRET
         2 22-MAY-07 SECRET
         3 22-MAY-07 SECRET
         4 22-MAY-07 NORMAL
         5 22-MAY-07 NORMAL

Remerciments

Chaleureux remerciements à M. Sheik Yerbouti, M. Richer Morin(DBA), M. Richard Taillefer(DBA), Mlle Denise Samkocwa,
M. Ernest DEGBOE, Developpez.com et l'équipe SGBD

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.