Introduction to Oracle Virtual Private Database (VPD) 12c

Before we get our hands on Virtual Private Databases (VPDs), let's try to understand if there is a real use case for VPD? Organizations have data of different sensitivity levels. For example, highly confidential trade secrets to classified publicly available news articles. From the point of view of databases, organizations needs to store data of different sensitivity levels in database tables and allow different users to access different set of data. For example, a manager is allowed to access records for all employees working under him/her whereas an engineer is allowed to access only his employment record. Oracle DAC privileges (or the privileges in any other major database system for that matter) either allow or deny access to the complete database objects such as tables, and views. There is no way to grant partial access to tables or views. What can we do about it?

One possible solution is to segregate data into different tables based on access control requirements. For the employee table example above, for each manager, one employee table with all of his/her subordinates could be created. That's a little too many tables! A better idea would be to create one table and multiple views on the same table for each manager. Even such an approach could be difficult to manage with so many objects and grants. Further, such approaches are not transparent to users as users need to know which table/view they can access in order to view employee records. Is there a magical way to do this segregation overcoming the above challenges? VPD is the answer! A bunch of smart people at Oracle got together and implemented a way to transparently present different records to different users based on context information. VPD has been around since Oracle 8i. Over time, now 12c, VPD has matured and evolved to support many powerful features. In this post, we are going to look at the very basic features of VPD as our goal is to get an idea of what VPD does. Once you have a hang of it, it is just a matter of referring to the user guide to absorb more advanced features of VPD.

So, what exactly VPD does? The basic idea behind VPD is really simple. It modifies user queries to add a predicate to show different set of records to different users. The query modification is transparent to users and based on the context information. The following diagram shows a very simple example.

Figure: A simple VPD example

As you can see from the above diagram, the context sensitive access policy is as follows:
"An employee can access her records and her subordinates' records if she is a manager"

Notice that VPD is transparent to database users. All employees execute the same query "select * from hr.emp". However, based on the access control policy, different users see different set of records. VPD does this by adding the predicate "upper(user) = SYS_CONTEXT('USERENV', 'CURRENT_USER') OR upper(manager) = SYS_CONTEXT('USERENV', 'CURRENT_USER'), where SYS_CONTEXT('USERENV', 'CURRENT_USER') returns the current database user.

Simple Code Example:
The following CDB setting is used to illustrate a dynamic VPD policy in action. (There are a few types of VPD polices such static, shared-static, and shared context-sensitive. These different types decide when to re-evaluate VPD polices as they use different caching strategies. For dynamic VPD policies, no caching strategy is used and policies are evaluated every time the a related statement is parsed or executed.)

Figure: CDB with a single PDB

Create the necessary users and grant privileges.
SQL> CONN sys/password@cdb1_pdb1 AS SYSDBA
Connected.

SQL> CREATE USER bob IDENTIFIED BY bob;
User created.

SQL> CREATE USER anne IDENTIFIED BY anne;
User created.

SQL> CREATE USER tim IDENTIFIED BY tim;
User created.

SQL> CREATE USER hr IDENTIFIED BY hr;
User created.

SQL> CREATE USER vpdadmin IDENTIFIED BY vpdadmin;
User created.

SQL> 
SQL> GRANT CREATE SESSION TO hr, bob, anne, tim, vpdadmin;
Grant succeeded.

SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO hr;
Grant succeeded.

Notice that one needs execute privilege on DBMS_RLS package in order to manage VPD policies.
SQL> GRANT EXECUTE ON DBMS_RLS TO vpdadmin;
Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO vpdadmin;
Grant succeeded.

SQL> 
SQL> CONN hr/hr@cdb1_pdb1
Connected.
SQL> CREATE TABLE emp (ename VARCHAR2(50), title VARCHAR2(50), manager VARCHAR2(50), deptid NUMBER, salary NUMBER);
Table created.

SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('bob', 'manager', NULL, 1, 10000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('alice', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('tim', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('eve', 'analyst', 'bob', 1, 7500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('anne', 'manager', NULL, 2, 10500);
1 row created.

SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('denis', 'engineer', 'anne', 2, 9000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('jack', 'engineer', 'anne', 2, 9500);
1 row created.

SQL> 
SQL> GRANT SELECT ON emp TO bob, anne, tim;
Grant succeeded.

Now vpdadmin user creates the VPD policy; first creates the policy function and then uses it to create the policy.
SQL> 
SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1
Connected.
SQL> -- Should have two input arguments and a return value - all of them are
SQL> -- of type VARCHAR2
SQL> -- Return value should be a predicate (that fits in the WHERE clause).
SQL> CREATE OR REPLACE FUNCTION get_emp_pred
  2    (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
  3    BEGIN
  4      RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') OR upper(manager) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  5    END;
  6  /
Function created.


SQL> -- create VPD policy
SQL> -- Note: for INSERT statements, you need to specify update_check => true as well
SQL> BEGIN
  2    SYS.DBMS_RLS.ADD_POLICY(
  3      object_schema   => 'hr',
  4      object_name     => 'emp',
  5      policy_name     => 'emp_vpd1',
  6      function_schema => 'vpdadmin',
  7      policy_function => 'get_emp_pred',
  8      statement_types => 'select'
  9    );
 10  END;
 11  /
PL/SQL procedure successfully completed.

SQL> 
SQL> column ename format a10;
SQL> column title format a10;
SQL> column manager format a10;
SQL> column deptid format 99;
SQL> column salary format 999999;
SQL> 
SQL> CONN bob/bob@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

bob        manager                    1   10000

alice      engineer   bob             1    8000

tim        engineer   bob             1    8000

eve        analyst    bob             1    7500

SQL> 

SQL> CONN tim/tim@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

tim        engineer   bob             1    8000

SQL> 

SQL> CONN anne/anne@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

anne       manager                    2   10500

denis      engineer   anne            2    9000

jack       engineer   anne            2    9500

Now we are going to clean up.

SQL> 

SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1

Connected.

SQL> BEGIN

  2    SYS.DBMS_RLS.DROP_POLICY(

  3      object_schema => 'hr',

  4      object_name   => 'emp',

  5      policy_name   => 'emp_vpd1'

  6    );

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> 

SQL> CONN sys/password@cdb1_pdb1 as SYSDBA

Connected.

SQL> DROP USER bob CASCADE;

User dropped.

SQL> DROP USER anne CASCADE;

User dropped.

SQL> DROP USER tim CASCADE;

User dropped.

SQL> DROP USER hr CASCADE;

User dropped.

SQL> DROP USER vpdadmin CASCADE;

User dropped.

Today we looked at a very basic example of VPD. In a real world application, a user defined context is utilized to construct the VPD predicate. In addition to row level control, VPD allows column level control  as well.