Using Row Level Security

The objective is to make sure that one somebody issue a Query, he/she retrieves rows for which he/she has privilege for.

Normally, this is done using views or application logic.

Since views are created using Standard SQL language, it can be difficult to embed logic if the criteria are a bit complex.  Application Logic can enforce filtration is the query is execute through the application itself, but if executed directly through SQL*Plus or any other interface.

Oracle Introduces DBMS_RLS  (row level Security) to simplify this task and solve the problem by allowing you to write filtration logic and associate it with a table, effectively, enforcing the filtration on the database level.


I have the following table; each row contains an field called username as shown below

It is required to set a security policy which allows AMMAR to only view rows with username = AMMAR, same rule applies to SAJDI and SYSTEM

To accomplish this task, we need to create a function (Policy function) and a Procedure (to set the context) in addition to creating a security policy.

Let us start with creating a policy function.  The purpose of this function is to return a Where clause that filters required rows. It does not matter what you call the function , but you have to stick with the signature given below (schema_name, and object_name).

The return value is a varchar that actually return a where condition. It does not matter what the name of the return variable is, it only have to be of type VARCHAR2

create or replace function DEPT_PREDICATE( schema_name in varchar2,object_name in varchar2)

    return varchar2
    lv_predicate varchar2(1000):='';
    lv_predicate:='USERNAME=SYS_CONTEXT(''test'', ''username'')';
    return lv_predicate;


To understand what the function does, just imagine that the SYS_CONTEXT function shall return 'AMMAR'.  Therefore, the lv_predicate variable shall be



What we are after is then, when a user, executes a query against the EMP table, we want the following to take place automatically


1) A SYS_CONTEXT to automatically return a value which in our case is the username

2) The Query to automatically associate the security function with the EMP table in the Query mode


We shall start with (2). To do the association, you need to issue the following statement from SYSTEM user


    object_schema => 'scott',       -- The schema that for which EMP belongs

    object_name => 'emp',           -- The object you want to associate the sec. function with

    policy_name => 'EMP_CL_POLICY', -- a Name for your choice for this policy

    function_schema => 'scott',       --the schema where the function is created

    policy_function => 'dept_predicate '   -- the name of the function

    Statement_Types => 'Select');

The above statement instructs Oracle to execute the dept_predicate function whenever a SELECT statement is issued against the EMP table and then add the predicate that result from the execution of the dept_predicate function to the WHERE clause of the SELECT statement

Now, we will handle the first (1) above, which will allow us to associate the a NAME with SYS_CONTEXT

Frist, Create a CONTEXT that is associated with a procedure


here, we created a context called TEST and is associated with set_detpno.

The set_deptno is a procedure which is responsible for setting a context to a session.

SQL>create or replace PROCEDURE set_deptno(p1 varchar2) AS
    DBMS_SESSION.set_context(namespace => 'test',ATTRIBUTE => 'username',
VALUE => p1);

When the above procedure is executed from a session, the session shall acquire a context called TEST, with a context value of the parameter P1

(NOTE: you cannot call DBMS_SESSION.set_context as a standalone procedure, it has to be called through the procedure name with which TEST context is created (ie the CREATE CONTEXT associated the TEST context with set_deptno, so you have to call the set_deptno procedure in order to execute the set.context)

Let us demonstrate


Context created.

SQL> create or replace PROCEDURE set_deptno(p1 varchar2) AS
3 DBMS_SESSION.set_context
4 (namespace => 'test',
5 ATTRIBUTE => 'username',
6 VALUE => p1);
7 END;
8 /

Procedure created.

SQL> create or replace function DEPT_PREDICATE(schema_name in varchar2,object_name in varchar2)
2 return varchar2
3 is
4 lv_predicate varchar2(1000):='';
5 begin
6 lv_predicate:='USERNAME=SYS_CONTEXT(''test'', ''username'')';
7 return lv_predicate;
8 end;
9 /

Function created.

2 dbms_rls.add_policy(
3 object_schema => 'scott'
4 ,object_name => 'emp'
5 ,policy_name => 'EMP_CL_POLICY'
6 ,function_schema => 'scott'
7 ,policy_function => 'dept_predicate ');
8 END;
9 /

PL/SQL procedure successfully completed.

1)  Set the context to Amman

SQL> begin
2 set_deptno('AMMAR');
3 end;
4 /

PL/SQL procedure successfully completed.

2) test the context setting

SQL> select sys_context('TEST','USERNAME') from dual;


3) Issue a query against EMP

  4) Change the context again

1 begin
2 set_deptno('SAJDI');
3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select sys_context('TEST','USERNAME') from dual;


5) Query Again