Virtual Private Databases (VPD) is an Enterprise Edition feature related to security. It restricts the scope of Data Manipulation Language to a subset of the table rows by transparently adding a where clause before executing them. It is also called Row-Level Security (RLS). Where the policy is enabled, it is like having the selected DML (SELECT, INSERT, UPDATE, DELETE) operate on a transcient view. And the predicates for this view can be dynamic and even query tables that the user cannot see.

In this post I'll show how to use it not as a strict restriction, but as a safeguard for users with powerful roles. A user has all DML granted. He usually queries and modify the tables through the application. But there may be some exceptional changes to do that were not forseen and were not implemented in the application. For this, the user has access to the production tables through SQL Developer. This is for users who knows what they do, with full knowledge of the data model. But human error can always happen and a mistake in a where clause can be critical. In order to reduce the risk, we can limit the scope of rows that are modified with Row Level Security. With its default role, the user can modify only the rows that are related to his area.

I run the example on SCOTT schema. The user will have any DML granted, but the VPD will prevent any modification of employees (EMP table) that are not in the 'SALES' department (DEPT). The restriction is based on the role which gives the DML privileges.

Define the VPD 

I create a role DEMO_SALES and a user DEMO_SALES_USER with this role granted.

With this role, the user can read EMP and DEPT from the SCOTT schema, and do any DML on EMP

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.

SQL> create role DEMO_SALES;

Role created.

SQL> grant select, insert, delete, update on scott.EMP to DEMO_SALES;

Grant succeeded.

SQL> grant select on scott.DEPT to DEMO_SALES;

Grant succeeded.

SQL> grant connect,demo_sales to DEMO_SALES_USER identified by demo_sales_user;

Grant succeeded.

I want to prevent that users with this role can accidently modify EMP rows belonging to other department than SALES

In order to create the VPD policy, SCOTT needs to execute DBMS_RLS (warning: this allows to disable or drop on any schema in the database)

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL Format Cleared
SQL> grant execute on dbms_rls to scott;

Grant succeeded.

SCOTT creates the VPD policy to view only SALES employees for INSERT, DELETE and UPDATE statements

SQL> connect scott/tiger@//localhost/PDB1
Connected.
SQL Format Cleared
SQL> select * from SCOTT.DEPT;

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK     
        20 RESEARCH       DALLAS       
        30 SALES          CHICAGO      
        40 OPERATIONS     BOSTON       

SQL> create function SCOTT.DEMO_VPD_FUNCTION(object_schema IN VARCHAR2, object_name VARCHAR2) return varchar2 as
  2  begin
  3   return q'[DEPTNO in (select DEPTNO from DEPT where DNAME='SALES') or 'DEMO_SALES' not in (select role from session_roles)]';
  4  end;
  5  /

Function created.
SQL> show errors
SQL> exec dbms_rls.add_policy(object_schema=>null,object_name=>'EMP'-
>  ,policy_name=>'DEMO_POLICY', function_schema=>null,policy_function=>'DEMO_VPD_FUNCTION'-
>  ,statement_types=>'INSERT,UPDATE,DELETE',update_check=>true,enable=>true);

PL/SQL procedure successfully completed.

Test the VPD 

I connect as DEMO_SALES_USER which has the DEMO_SALES role by default

SQL> connect DEMO_SALES_USER/demo_sales_user@//localhost/PDB1
Connected.
SQL Format Cleared
SQL> select role from session_roles;

ROLE
--------------------------------------------------------------------------------------------------------------------------------
CONNECT
DEMO_SALES

I can see all rows from EMP

SQL> select * from SCOTT.DEPT join SCOTT.EMP using(DEPTNO) order by DNAME;

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ----------
        10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER         7839 09-JUN-81       2450           
        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            17-NOV-81       5000           
        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           7782 23-JAN-82       1300           
        20 RESEARCH       DALLAS              7566 JONES      MANAGER         7839 02-APR-81       2975           
        20 RESEARCH       DALLAS              7902 FORD       ANALYST         7566 03-DEC-81       3000           
        20 RESEARCH       DALLAS              7876 ADAMS      CLERK           7788 23-MAY-87       1100           
        20 RESEARCH       DALLAS              7369 SMITH      CLERK           7902 17-DEC-80        800           
        20 RESEARCH       DALLAS              7788 SCOTT      ANALYST         7566 19-APR-87       3000           
        30 SALES          CHICAGO             7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30 SALES          CHICAGO             7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30 SALES          CHICAGO             7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30 SALES          CHICAGO             7900 JAMES      CLERK           7698 03-DEC-81        950           
        30 SALES          CHICAGO             7698 BLAKE      MANAGER         7839 01-MAY-81       2850           
        30 SALES          CHICAGO             7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400

14 rows selected. 

Trying to update all salaries, only those from department SALES are actually modified

SQL> update SCOTT.EMP set sal=sal+10000;

6 rows updated.

No errors. This is like a view: the DML operates on the subset of rows defined by the VPD.

Inserting for departement SALES is allowed

SQL> insert into SCOTT.EMP(EMPNO,ENAME,DEPTNO,SAL) values (8001,'Sales',30,10000);

1 row created.

Inserting for other departements raises an error

SQL> insert into SCOTT.EMP(EMPNO,ENAME,DEPTNO,SAL) values (8002,'No Sales',20,10000);

insert into SCOTT.EMP(EMPNO,ENAME,DEPTNO,SAL) values (8002,'No Sales',20,10000)
                  *
ERROR at line 1:
ORA-28115: policy with check option violation

This goes beyond viewing only a subset of rows. Because the VPD was created with update_check=>true the modifications that create rows outside of the view raises an error.

A delete removes only the rows from SALES departement

SQL> delete from SCOTT.EMP;

7 rows deleted.

Basically all other department were protected from any DML mistake

SQL> select * from SCOTT.DEPT join SCOTT.EMP using(DEPTNO) order by DNAME;

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ----------
        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           7782 23-JAN-82       1300           
        10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER         7839 09-JUN-81       2450           
        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            17-NOV-81       5000           
        20 RESEARCH       DALLAS              7902 FORD       ANALYST         7566 03-DEC-81       3000           
        20 RESEARCH       DALLAS              7876 ADAMS      CLERK           7788 23-MAY-87       1100           
        20 RESEARCH       DALLAS              7566 JONES      MANAGER         7839 02-APR-81       2975           
        20 RESEARCH       DALLAS              7369 SMITH      CLERK           7902 17-DEC-80        800           
        20 RESEARCH       DALLAS              7788 SCOTT      ANALYST         7566 19-APR-87       3000           

8 rows selected. 

If we grant DML through another role or direct grant, the protection is still there

SQL> connect scott/tiger@//localhost/PDB1
Connected.
SQL Format Cleared
SQL> grant all on SCOTT.EMP to DEMO_SALES_USER;

Grant succeeded.

SQL> grant all on SCOTT.DEPT to DEMO_SALES_USER;

Grant succeeded.

SQL> connect DEMO_SALES_USER/demo_sales_user@//localhost/PDB1
Connected.
SQL Format Cleared
SQL> delete from SCOTT.EMP;

0 rows deleted.

As I have defined it, bypassing this restriction requires to disable the role from the session

SQL> set role none;

Role succeeded.

SQL> select role from session_roles;
no rows selected
SQL> delete from SCOTT.EMP;

8 rows deleted.

Here all rows were deleted because the safeguard role was disabled, and another privilege was used to run the DML.

SQL> select * from SCOTT.EMP ;
no rows selected
SQL> exit

In summary, this solution is not there to block the modifications on non-allowed department. The user has all privileges on purpose, because he may need them. The idea is just to add a safeguard to be sure that the part of data which is not in the normal scope of modifications is not altered by mistake. 

The comments are closed on this post, but do not hesitate to give feedback, comment or questions on Twitter (@FranckPachot)