Sécurité granulaire

Salim Chelabi

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

- Titre : Sécurité granulaire

- Auteur : Salim Chelabi

- Parution : 16 juin 2007

- Licence : 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.

Sommaire

- 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