DEMO de la sécurité au niveau LIGNE avec Oracle 10g
(A.K.A Row Level Security / RLS / Fine Grained Access Control / VPDs)

Le but de cette demo est de mettre en place une strategie sur la table
SCOTT.EMP, qui s’appuie sur un contexte utilisateur, défini de manière automatique..
Un user ne devra voir que la (les) ligne’s) qui le concerne dans la table,
même en faisant un ‘SELECT * FROM emp’.
ex : KING ne voit que la ligne de KING, CLARK la ligne de CLARK.
On créera un user Oracle ‘CLARK’ présent dans la table des employés,
pour tester les filtres appliqués au user connecté.

—————————-
– 0) Environnement de TEST
—————————-
– en tant que SYS

— créer un user spécifique pour mettre en place la sécurié
CREATE USER sec IDENTIFIED BY nbvcxw;
– les privileges minimum necessaire pour DEV1
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
CREATE ANY CONTEXT TO sec;
GRANT EXECUTE ON DBMS_RLS TO sec;
GRANT EXECUTE ON DBMS_SESSION TO sec;
– rem on peut remplacer les 2 precedents par un
– GRANT EXECUTE_CATALOG_ROLE TO dev1 (moins ciblé / sécurisé)
CREATE USER clark IDENTIFIED BY clark;
GRANT CREATE SESSION TO clark;
GRANT SELECT ON scott.emp TO PUBLIC;
GRANT EXECUTE ON sec.pack_contexte_emp TO PUBLIC; (pour debug)
GRANT EXECUTE ON filtre_emp TO PUBLIC; (pour debug)

—————————
– 1) déclarer le contexte
—————————

CONNECT sec/nbvcxw
CREATE OR REPLACE CONTEXT contexte_employe
USING pack_contexte_emp;

——————————————————————————–
– 2) definir le contexte applicatif (code du package et attributs du contexte)
——————————————————————————–

– ici c’est le no d’emp et le niveau (chef ou nom)
– qui determineront les privileges d’acces ? l’applicatif

– les specs du package :

CREATE OR REPLACE PACKAGE pack_contexte_emp
AS
PROCEDURE def_contexte;
END;

– le corps du package :

CREATE OR REPLACE PACKAGE BODY pack_contexte_emp
IS
PROCEDURE def_contexte
IS
v_empno NUMBER;
nb_subordonnes NUMBER;
BEGIN

– on recupere le no de l’employe et sa fonction (chef ou non)
– et on definit les attributs du contexte en consequence
– le ‘NAMESPACE’ ‘contexte_employe’ a donc 2 attributs ici
– on ne se sert que du premier dans cet exemple…

– d’abord le no d’emp
– en utilisant le ‘SESSION_USER’ de SYS_CONTEXT
– equivallent a un SELECT username FROM dual
– rem : si le user n’apparait pas dans la table –> no data found (a trapper)

SELECT empno INTO v_empno FROM scott.emp
WHERE ename = SYS_CONTEXT(’USERENV’, ‘SESSION_USER’);

DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘no_emp’, v_empno);

– puis son niveau de responsabilite : chef ou non
– si oui son no d’emp est le no de manager de quelqu’un…

SELECT COUNT(*) INTO nb_subordonnes
FROM scott.emp
WHERE mgr= v_empno;
IF (nb_subordonnes <> 0) THEN
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘chef’);
ELSE
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘employe’);
END IF;

END def_contexte;
END pack_contexte_emp;

– test

select SYS_CONTEXT(’USERENV’, ‘SESSION_USER’) from DUAL
exec sec.pack_contexte_emp.def_contexte

———————————————-
– 3) definir les fonctions limitant les acces
———————————————-

– la ou les fonctions vont retourner une chaine
– qui sera ajoutee a la clause WHERE (predicat supplementaire)
– on utilise le contexte ‘employe’ pour identifier le user

CREATE OR REPLACE PACKAGE filtre_emp
AS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY filtre_emp
IS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2
IS
predicat VARCHAR2(1000);
BEGIN
predicat := ‘empno=SYS_CONTEXT(”contexte_employe”,”no_emp”)’;
RETURN predicat;
END;
END;

– test

SELECT SYS_CONTEXT(’contexte_employe’,'no_emp’) FROM dual
SELECT sec.filtre_emp.vue_emp(’SCOTT’,'EMP’) FROM dual

————————————————————
– 4) creer stratégie d’acces (policy) attachée a la table
————————————————————

DBMS_RLS.ADD_POLICY (proprietaire_objet,nom_objet,nom strategie,proprietaire strategie,fonction_filtre, SQL_concerne) :
execute DBMS_RLS.ADD_POLICY (’scott’, ‘emp’, ‘policy_emp’,’sec’, ‘filtre_emp.vue_emp’, ‘SELECT’);

(si necessaire
DBMS_RLS.DROP_POLICY (nom_owner, nom_table, nom_police) :
execute DBMS_RLS.DROP_POLICY (’scott’, ‘emp’, ‘policy_emp’); )

–test

select * from sys.rls$ where PFSCHMA=’DEV1′;
OBJ# GNAME PNAME STMT_TYPE CHECK_OPT ENABLE_FLAG PFSCHMA PPNAME PFNAME PTYPE
52606 SYS_DEFAULT POLICY_EMP 513 0 1 DEV1 FILTRE_EMP VUE_EMP

———————————————————————
– 5) activer le contexte, avant l’acces aux données des utilisateurs
———————————————————————
– manuellement (ou géré par l’applicatif)
connect clark/clark
exec sec.pack_contexte_emp.def_contexte;

– ou automatiquement par un trigger, en tant que DBA !!

CREATE OR REPLACE TRIGGER dev1.active_contexte_emp
AFTER LOGON ON DATABASE
BEGIN
sec.pack_contexte_emp.def_contexte;
END;

——————-
– 6) Test VPD
——————-

SQL> connect clark/clark
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —– ——– —- ——– —- —- ——
7782 CLARK MANAGER 7839 09/06/81 2450 10

SQL> connect system/xdcfvgh
SQL> select * from emp;
aucune ligne sélectionnée.

et sans la VPD :

SQL> execute DBMS_RLS.DROP_POLICY (’dev1′, ‘emp’, ‘policy_emp’);
ou moins violent :
SQL> grant exempt access policy to DEV1;
SQL> connect dev1/dev1
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——– ———- —– ——
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10
1111 DEV1 Engineer 7782 01/01/07 2000 10

15 ligne(s) selectionnee(s).

L’erreur ORA-28112

l’utilisation des policies déclenche parfois une erreur ORA-28112 , même si les polices sont correctes et les packages aussi.
Voir [1] le musée des erreurs - erreur ORA-28112 ” Failed to execute policy function ”

——-
Notes
——-

1) Pourquoi un compte SEC spécifique pour les objets nécessaire ? la VPD ?
- c’est + sécurisé, les données ne sont pas mélangées avec les règles d’accès
- c’est + facile a debugger : on peut soustraire ce compte aux règles des VPDs
- on évite les pbs d’accès récursif de la VPD

2) les parametres des fonctions de filtre sont OBLIGATOIRES !

3) Les DBAs soont normalement soumis aux stratégies comme tous les autres users.
Il est néanmoins possible d’outrepasser les VPDs
(pour le deboggage, pour l’administration, ou pour eviter les pbs de récursion)
SQL> grant exempt access policy to nom_user_cible;

4) une ORA-28112 = echec d’execution de la fonction de regle, lors du SELECT
==> la fonction est OK, mais il y a une exception qui n’est pas trappée par la fonction.
voir le fichier trace généré par l’erreur dans le répertoire USER_DUMP_DEST
pour résoudre le problème

5) vues du referentiel utiles
%_POLICIES, V$VPD_POLICY, RLS$, v$SQL

6) ne pas confondre les policies générales et les policies utilisées pour l’audit (Fine Grain Auditing).
Les packages, procédures et vues du dictionnaire ne sont pas les mêmes !
exemple : les packages DBMS_RLS (généraux) et DBMS_FGA (audit)

7) il n’existe pas de DISABLE_POLICY en 9i !!! mais un DBMS_RLS.ENABLE_POLICY avec ENABLE=….FALSE !!!