Implementing Row Level Security (RLS)

We have implemented Row Level Security using the Virtual Private database Policy (VPD).What VPD will do is to add where conditions on user SQLs based on the policies defined.

For example, in the simplest manner, suppose you want to restrict the users to access the records created only by themselves in the table "TEST_TAB". You can define and apply a policy "POLICY1" as "CREATED_BY = USER" for the table "TEST_TAB".

The steps in implementing VPD is as follows:

1. Create a function which will return the where condition to be applied. The argumnets to this function should be schema and object name.

function policy1 (p_schema varchar2,p_object varchar2) return varchar2


2. Add the policy using DBMS_RLS.ADD_POLICY
Below is a simple example to add a policy on a table RETAILDM.TEST_RLS.

First We will see the data in RETAILDM.TEST_RLS.It has two records created by RETAILDM and one record created by another user.

SQL>; show user
USER is "RETAILDM"

SQL>; select *  
2  from test_rls;
C1                   CREATED_BY<
--------------------------------
XXXXX                RETAILDM
YYYYY                USER2
ZZZZZ                RETAILDM

Now, we have to define the Policy function to return the "where' condition.

SQL>; create or replace function test_rls_fn(p_schema varchar2,p_object varchar2)  
2          return varchar2 is  
3      lc_where varchar2(500);  
4  begin  
5      lc_where := ' created_by = user';  
6      return lc_where;
7  end;
8  /
Function created.

next step is to add the policy using DBMS_RLS.ADD_POLICY. The arguments are

object schema, object name, policy name, policy function schema and policy function name.

SQL>; exec dbms_rls.ADD_POLICY('RETAILDM','TEST_RLS','TEST_RLS_POLICY','RETAILDM','TEST_RLS_FN');
PL/SQL procedure successfully completed.

Now, if we select data from TEST_RLS, we will get only those records created by RETAILDM.

SQL>; select *  
2  from test_rls;
C1                   CREATED_BY
-------------------- ----------
XXXXX                RETAILDM
ZZZZZ                RETAILDM

If you check the explain plan, the where condition applied can be spotted out.

SQL>; explain plan for  
2  select *  
3  from test_rls;
Explained

SQL>; select *
2        from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 636636236

------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------
0 SELECT STATEMENT 2 38 3 (0) 00:00:01 *
1 TABLE ACCESS FULL TEST_RLS 2 38 3 (0) 00:00:01
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("CREATED_BY"=USER@!)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

Tags: RLS ORACLE, Row level security ORACLE