Fine Grained Access Control with DBMS_RLS using UPDATE_CHECK=>true

Fine Grained Access Control aka Virtual Private Database (VPD) has been there since Oracle 8 and got enhanced in each subsequent version. One minor New Feature of 11.2 was the addition of the parameter UPDATE_CHECK to the DBMS_RLS.ADD_POLICY procedure. During the OCM Preparation Workshop that I deliver presently, one of the attendees asked me what this parameter is actually doing – the doc is not so clear about it – which is why I came up with this simplified example. Hope you find it useful also :-)

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table emp (ename varchar2(5),salary number);

Table created.

SQL> insert into emp values ('SCOTT',3000);

1 row created.

SQL> insert into emp values ('KING',9000);

1 row created.

SQL> commit;

Commit complete.

SQL> grant create session to scott identified by tiger;

Grant succeeded.

SQL> grant select,update on adam.emp to scott;

Grant succeeded.

The user SCOTT is not supposed to see the salary of the other employees and VPD is an elegant way to achieve that. The following technique will silently attach a WHERE-condition to statements hitting the table emp:

SQL> connect adam/adam
Connected.
SQL> create or replace function whoisit(schema varchar2, tab varchar2) return varchar2 
as
begin 
return '''' || sys_context('userenv','session_user') || ''' = ename ';     
end; 
/    

Function created.

SQL> begin
 dbms_rls.add_policy
  (object_schema=>'ADAM',
   object_name=>'EMP',
   policy_name=>'EMP_POLICY',
   function_schema=>'ADAM',
   policy_function=>'WHOISIT',
   update_check=>true);
end;
/    

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000

Although there are two rows in the table, SCOTT sees only his own salary! So far, this has been the same in earlier versions already. Now to the effect of update_check:

SQL> update adam.emp set ename='KING';
update adam.emp set ename='KING'
            *
ERROR at line 1:
ORA-28115: policy with check option violation

Without that parameter setting, the update would succeed – and the row would vanish for the user SCOTT as if the update would have deleted the row. Imagine the confusion of the user about that weird behavior :-)

Talking about weird, by the way:

SQL> connect adam/adam
Connected.
SQL> select * from adam.emp;

no rows selected

SQL> connect / as sysdba
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000
KING	    9000

SQL> grant exempt access policy to adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000
KING	    9000

That was funny, wasn’t it?

Conclusion: The new parameter UPDATE_CHECK in the DBMS_RLS.ADD_POLICY procedure restricts updates that would else lead to the updated rows to fall out of the allowed visibility for that user. Check out the old behavior by just omitting that parameter. Because: Don’t believe it, test it :-)

About these ads

, , , ,

  1. #1 by Anibal on August 7, 2013 - 23:35

    Hi,

    Is it possible to check against RLS when the fired statement is in create, insert, update or delete mode?
    Have a table which contains all tables that a user can access by select, insert, update or delete.

    Thx.

  2. #2 by Uwe Hesse on August 13, 2013 - 09:50

    Anibal, the procedure can not be used for DDL like CREATE. See here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_rls.htm#i998159

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,805 other followers

%d bloggers like this: