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 🙂