Infrastructure at your Service

Mouhamadou Diaw

Oracle Virtual Private Database

Oracle Virtual Private Database (VPD) enables you to dynamically add a WHERE clause in any SQL statement that a user executes. The WHERE clause filters the data the user is allowed to access, based on the identity of a user.
This feature restricts row and column level data access by creating a policy that enforces a WHERE clause for all SQL statements that query the database. The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect. You create and manage the VPD policy at the database table or view level, which means that you do not modify the applications that access the database.
In a multitenant environment, each Virtual Private Database policy applies only to the current pluggable database (PDB).
In this blog we are going to use this functionality in a pluggable database 12.1
We are going to setup a policy for the table emp of scott so that each user should be able to see only his own data. We present below contents of table emp of scott

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

SQL> select * from scott.emp;

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

14 rows selected.

The first step is to create two users jones and miller for example and let’s give them the required privileges

SQL> create user jones  identified by root default tablespace users ;

User created.

SQL> grant select on scott.emp to jones;

Grant succeeded.

SQL>


SQL> create user miller identified by root default tablespace users ;

User created.

SQL> grant select on scott.emp to miller;

Grant succeeded.

SQL> grant create session to jones;

Grant succeeded.

SQL> grant create session to miller;

Grant succeeded.

SQL>

Once users created, we have to create the function we will use to create the VPD policy

CREATE OR REPLACE FUNCTION emp_policy
     (user_name IN VARCHAR2,tab_name in varchar2)
    RETURN VARCHAR2 IS
      BEGIN
       RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
      END;
      /

Function created.

With this function Oracle will add for each query on the table emp the following clause

Where  upper(ename) = SYS_CONTEXT(’USERENV’, ‘CURRENT_USER’)

when the VPD policy is set
Now let’s create the VPD policy

SQL> show user
USER is "SYSTEM"
SQL>
BEGIN
      SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'scott',
        object_name     => 'emp',
        policy_name     => 'emp_vpd_policy',
        function_schema => 'system',
        policy_function => 'emp_policy',
        statement_types => 'select'
     );
  END;
 11    /

PL/SQL procedure successfully completed.

And then we can test that each user can see only his own data

With user miller

[oracle@em13c wallet]$ rlwrap sqlplus miller/root@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:18 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 26 2018 17:34:57 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

With user jones

[oracle@em13c wallet]$ rlwrap sqlplus jones/root@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:25 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 26 2018 17:34:33 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

SQL>

But there is a problem now, because VPD is enforced by default for all users except those connected with SYSDBA privilege. This mean that if we connect for example with SYSTEM, we will not see any data

SQL> show user
USER is "SYSTEM"
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> select * from scott.emp;

no rows selected

SQL>

To exempt SYSTEM to VPD we have to grant him the EXEMPT ACCESS POLICY

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> grant EXEMPT ACCESS POLICY to system;

Grant succeeded.

SQL>

And then login again whith SYSTEM

SQL> show user
USER is "SYSTEM"
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

14 rows selected.
SQL>
 

Leave a Reply

Mouhamadou Diaw
Mouhamadou Diaw

Consultant