* A simple role-based row level security example
* All DBA can select from the tyu table in jyang schema
* Non-DBA will retrieve nothing from this table.



SQL> select * from tyu;

        C1
----------
         0
         1
         2

SQL> 
SQL> create or replace function rls_func(p1 varchar2, p2 varchar2)
  2  return varchar2 as
  3  l_where varchar2(30);
  4  cnt number;
  5  begin
  6  select count(1) into cnt from user_role_privs
  7    where username=sys_context('USERENV','SESSION_USER')
  8    and granted_role='DBA';
  9  if cnt=1 then
 10     l_where:='';
 11  else
 12     l_where:='1=2';
 13  end if;
 14  return l_where;
 15  end;
 16  /

Function created.

SQL> 
SQL> begin
  2  dbms_rls.add_policy(object_schema => 'JYANG',
  3                      object_name   => 'TYU',
  4                      policy_name   => 'TYU_POLICY',
  5                      function_schema =>'JYANG',
  6                      policy_function => 'RLS_FUNC',
  7                      statement_types  => 'SELECT');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from tyu;

        C1
----------
         0
         1
         2

SQL> grant select on tyu to scott;

Grant succeeded.

SQL> conn scott/tiger@athena
Connected.
SQL> select * from jyang.tyu;

no rows selected

SQL>