Re: configuring oracle RLS to work with roles instead of users

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 6 Jun 2003 22:24:02 -0700
Message-ID: <130ba93a.0306062124.513c362d@posting.google.com>

doron_almog_at_msn.com (Doron) wrote in message news:<995517bc.0306061226.27c6e5c_at_posting.google.com>...
> hi,
> has anyone configured oracle RLS to work with roles instead of users? if so how?

RLS is not restricted to user level at all. It all depends on how you write the function that implements the policy. You can pretty much set up any kind of logic you want in the function. Here is a simple example. I want only users with SELECT_CATALOG_ROLE role privilege to be able to see data in jyang.tyu table...

SQL> select * from tyu;

        C1

---

         0
         1
         2

SQL>
SQL> begin
  2 dbms_rls.drop_policy(object_schema => 'JYANG',

  3                      object_name   => 'TYU',
  4                      policy_name   => 'TYU_POLICY');

  5 end;
  6 /

PL/SQL procedure successfully completed.

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='SELECT_CATALOG_ROLE';   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_at_athena
Connected.
SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF

OS_

------------------------------ ------------------------------ --- ---
---
SCOTT                          CONNECT                        NO  YES
NO
SCOTT                          PLUSTRACE                      NO  YES
NO
SCOTT                          RESOURCE                       NO  YES

NO

SQL> select * from jyang.tyu;

no rows selected

SQL>

• Jusung Yang

Received on Sat Jun 07 2003 - 00:24:02 CDT