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 NORMALRemerciments▲
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