Implement fine-grained access control

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;



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

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
return ‘ename = sys_context(”USERENV”, ”CURRENT_USER”)’;

SQL>Exec DBMS_RLS.drop_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’);
SQL>Exec DBMS_RLS.ADD_POLICY (‘SCOTT’,’EMP’,’MyRLSPolicy’,’SCOTT’, ‘GetCurrentUser’,’SELECT’);


SQL>Connect scott/tiger

SQL>select * from emp;

———- ———- ——— ———- ——— ———- ———- ———-
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;

FUNCTION limit_emp (obj_schema varchar2, obj_name varchar2) RETURN VARCHAR2;
Procedure Init_limit_emp;

FUNCTION limit_emp (obj_schema varchar2, obj_name varchar2) RETURN VARCHAR2 IS

RETURN ‘ename = sys_context(”USERENV”, ”CURRENT_USER”)’;

Procedure Init_limit_emp IS
v_comm NUMBER;
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’);
DBMS_SESSION.SET_CONTEXT(‘mycontext’, ‘cansee’, ‘0’);
end if;

when no_data_found then
DBMS_SESSION.SET_CONTEXT(‘mycontext’, ‘cansee’, ‘0’);

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!







The following two tabs change content below.


I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

1 thought on “Implement fine-grained access control

  1. Gustavo

    Hey Jacques, I would like to have a help from you.
    I am implementing an Oracle AVDF audit, and am looking to get the user name and / or OS UserName.
    At the moment I have 3 banks being audited, in one of the banks where an audit is being done directly on the bank’s commands (where each user needs to connect to the bank to perform actions), it’s all right, the tool returns the path parameters right.
    In the others, I have applications WEB being audited and the tool returns the service account and not the username that is connected
    Is there any way around this problem?



Leave a Reply

Your email address will not be published. Required fields are marked *