There are two aspects where
Fine grained auditing and fine grained access control. Two difference features!
The first one is about auditing column display under special conditions or not (doc is here).
The second is about limiting the display of rows depending on some rules (doc is here).
Fine grained auditing
To do that, I create a user secu with execute privilege on dbms_fga to control the auditing feature.
SQL>Create user secu identified by secu;
SQL>grant connect to secu;
SQL>grant select any table to secu;
SQL>grant select on dba_fga_audit_trail to secu;
SQL>grant execute on dbms_fga to secu;
SQL>connect seci/secu
The goal is to audit the display of the columns SAL and COMM.
SQL>exec dbms_fga.add_policy (‘SCOTT’,’EMP’,’MyPolicy’,”,’SAL,COMM’);
SQL>select count (*) from dba_fga_audit_trail;
0
SQL>
SQL>connect emp/emp
SQL>select empno from emp;
…
No audit.
SQL>select sal from emp;
SQL>select comm from emp;
SQL>Select count (*) from emp where sal > 6000;
SQL>connect secu/secu
SQL>select sql_text from dba_fga_audit_trail
SQL_TEXT
——————————————————————————–
select count (*) from emp where sal>6000
select sal from emp
select comm from emp
Event if the result of the count () is 0 the sql statement is audited because of the usage of the sal column.
Fine grained access control
The goal here is to restrict access to certain row for certain users.
I am using the same secu user created previously for FGA implementation.
The package that handle the feature is called dbms_rls (row level security).
SQL>grant execute on dbms_rls to secu;
User secu must be exempted of the defined policies.
SQL>grant exempt access policy to secu;
The most easiest way to implement it is the following:
Imagine we have an oracle account for each employee and the employees are all defined into the emp table.
Therefore we can fix the display of the content of the emp table to the logged user, exept for those who are exempted such as secu user.
Let’s create a simple function into the SCOTT schema:
Warning: parameters are mandatory or you will get:ORA-28112: failed to execute policy function
Create or replace function scott.GetCurrentUser (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return ‘ename = sys_context(”USERENV”, ”CURRENT_USER”)’;
end;
/
SQL>Exec DBMS_RLS.drop_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’);
SQL>Exec DBMS_RLS.ADD_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’,’SCOTT’, ‘GetCurrentUser’,’SELECT’);
Test:
SQL>Connect scott/tiger
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Ok we could make a view with a filter on ename with sys_context but doing this using VPD makes the feature client application independent!
We could have a much more complex condition covered within the function such as query that check business conditions and so on…
Sure we will not do the check every time the user runs a query on emp table but only ones when he logon and the store the condition in application context.
Here is a short example using application context:
Create context mycontext using policy_pkg;
CREATE OR REPLACE PACKAGE policy_pkg IS
FUNCTION limit_emp (obj_schema varchar2, obj_name varchar2) RETURN VARCHAR2;
Procedure Init_limit_emp;
END;
/
CREATE OR REPLACE PACKAGE BODY policy_pkg IS
FUNCTION limit_emp (obj_schema varchar2, obj_name varchar2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘ename = sys_context(”USERENV”, ”CURRENT_USER”)’;
END;
Procedure Init_limit_emp IS
v_comm NUMBER;
BEGIN
select comm into v_comm from scott.emp where ename=sys_context(‘USERENV’, ‘CURRENT_USER’);
if v_comm is not null then
DBMS_SESSION.SET_CONTEXT(‘mycontext’, ‘cansee’, ‘1’);
else
DBMS_SESSION.SET_CONTEXT(‘mycontext’, ‘cansee’, ‘0’);
end if;
exception
when no_data_found then
DBMS_SESSION.SET_CONTEXT(‘mycontext’, ‘cansee’, ‘0’);
END;
END;
/
create synonym scott.policy_pkg for policy_pkg;
grant execute on policy_pkg to scott;
Exec DBMS_RLS.drop_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’);
Exec DBMS_RLS.ADD_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’,”, ‘policy_pkg.limit_emp’,’SELECT’);
From Scott:
You can invoke the init procedure to initialize the local context mycontext cansee variable.
Will have value 1 if the comm is not null and 0 if null.
The init procedure could be called within a logon trigger…
Warning: If you are using shared server, you have to create global context as you do not have a dedicated memory structure within the PGA that hold context variables.
Create context mycontext using policy_pkg accessed globally;
If you are using RAC, then you have to limit the usage of this feature to a dedicated instance by defining a singleton service and using that service to connect to you RAC database.
Have fun!
jko
