Home » RDBMS Server » Security » Doubt in DBMS_RLS!
Doubt in DBMS_RLS! [message #504893] Thu, 28 April 2011 06:59 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
I have tried to implement RLS policy of oracle.
I have two Schema X1 & X1_DBA.

I have created the emp table in X1_DBA
create table emp(empid number,ename varchar2(10),deptno number)

and inserted some rows into the Table. i have created the below function in X1_DBA schema & Given Select Privilege to X1.
CREATE OR REPLACE FUNCTION no_dept10(
      p_schema  IN VARCHAR2,
      p_object  IN VARCHAR2)
      RETURN VARCHAR2
    AS
    BEGIN
      RETURN 'deptno != 10';
    END;

Grant select on emp to X1;

When i Add the Policy in X1_DBA.schema i am getting the Error as Table does not exist
SQL> BEGIN
  2        DBMS_RLS.add_policy
  3           (object_schema    => 'X1',
  4            object_name      => 'EMP',
  5            policy_name      => 'quickstart',
  6            policy_function  => 'no_dept10');
  7      END;
  8  /

BEGIN
      DBMS_RLS.add_policy
         (object_schema    => 'X1',
          object_name      => 'EMP',
          policy_name      => 'quickstart',
          policy_function  => 'no_dept10');
    END;

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_RLS", line 20
ORA-06512: at line 2

Could any one please tell me why i am Getting the Error?


Re: Doubt in DBMS_RLS! [message #504901 is a reply to message #504893] Thu, 28 April 2011 07:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are trying to add the policy to x1.emp, not x1_dba.emp.
Re: Doubt in DBMS_RLS! [message #504908 is a reply to message #504901] Thu, 28 April 2011 07:15 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Yes. i want to Restrict Deptno 10 for X1 user. do i have to create a emp table in X1 Schema also? If i use X1_DBA as object schema then if i select emp table from X1_DBA it is restricting deptno 10.
But my concern is i want to restrict deptno 10 only for X1 schema.
Re: Doubt in DBMS_RLS! [message #504911 is a reply to message #504908] Thu, 28 April 2011 07:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your policy function needs to check who the user is, and generate an appropriate predicate.
I'm not going to give any more advice, because you didn't say "thank you".
Re: Doubt in DBMS_RLS! [message #505069 is a reply to message #504911] Fri, 29 April 2011 02:27 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
ok... Thanks for your Reply. I will modify the policy & check.
Re: Doubt in DBMS_RLS! [message #505075 is a reply to message #505069] Fri, 29 April 2011 03:09 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I have changed my Policy Function as
CREATE OR REPLACE FUNCTION no_dept10(
      p_schema  IN VARCHAR2,
      p_object  IN VARCHAR2)
      RETURN VARCHAR2
    AS
    BEGIN
    if p_schema = 'X1' then
       return 'deptno != 10';
    else
       return null;
    end if;
    END;


But when i login to X1 schema & run the Query
select * from X1_DBA.emp 


its listing all the rows including deptno=10. Could you please tell me how can i give deptno!=10 condition only to X1 Schema.?
Thanks in Advance.
Re: Doubt in DBMS_RLS! [message #505253 is a reply to message #505075] Sat, 30 April 2011 21:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The p_schema and object_schema are the schema that the emp table is in (x1_dba). The user is the user running the query (x1) that you want to apply the restriction to.

X1_DBA@orcl_11gR2> create or replace function x1_dba.no_dept10
  2    (p_schema in varchar2,
  3  	p_object in varchar2)
  4    return	    varchar2
  5  as
  6  begin
  7    if user = 'X1' then
  8  	 return 'deptno != 10';
  9    else
 10  	 return null;
 11    end if;
 12  end;
 13  /

Function created.

X1_DBA@orcl_11gR2> show errors
No errors.
X1_DBA@orcl_11gR2> begin
  2    dbms_rls.add_policy
  3  	 (object_schema   => 'X1_DBA',
  4  	  object_name	  => 'EMP',
  5  	  policy_name	  => 'quickstart',
  6  	  policy_function => 'no_dept10');
  7  end;
  8  /

PL/SQL procedure successfully completed.

X1_DBA@orcl_11gR2> select * from x1_dba.emp
  2  /

     EMPID ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14 rows selected.

X1_DBA@orcl_11gR2> grant select on x1_dba.emp to x1
  2  /

Grant succeeded.

X1_DBA@orcl_11gR2> connect x1/x1
Connected.
X1@orcl_11gR2> select * from x1_dba.emp
  2  /

     EMPID ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7788 SCOTT              20
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20

11 rows selected.

X1@orcl_11gR2> 

Re: Doubt in DBMS_RLS! [message #505314 is a reply to message #505253] Mon, 02 May 2011 06:19 Go to previous message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
Thanks for your reply. It is working now.
Previous Topic: Oracle Label Security - Insert Operation
Next Topic: how to implement row level and column level vpd simultaneously
Goto Forum:
  


Current Time: Thu Mar 28 06:25:59 CDT 2024