Pickleball spielen

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 🙂