Virtual Private Databases (VPDs)

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

From the point of view of databases, organizations need to store data of different sensitivity levels in database tables and allow different users to access different sets of data.

For example, suppose we have a grades table, and we want to add a policy that “Every Student can only see his/her grade” but the Teacher can see all the user’s grades for whom they are teacher.”

So, if we want to do this without VPD then possible options are.

Let the applications handle it with Parameterized Views
- CREATE VIEW StudentGrades AS SELECT * FROM Grades WHERE student = $username
  (Here $username is a variable that is given at runtime.)
- Problem: Applications need different queries for students and lecturers
  - For students, need to query StudentGrades
  - For lecturers, need to query Grades instead
Let the application handle the query (e.g., a grade viewing service)
- Check user identities and issue queries like SELECT * FROM Grades WHERE student = `Bob’
- Problem: Application code can see everything on Grades
  If the application is hijacked, the whole table can be compromised.

ORACLE VPD is the solution

It is also referred to as Oracle Row-Level Security (RLS) or Fine-Grained Access Control (FGAC). VPD modifies user queries to add a predicate to show different sets of records to different users. The query modification is transparent to users and based on the context information.

As Bob, Alice, Cath, Mandy, and Rosy are students they can see their own records while Kath can see records of 3 students and Hemant should see records of 2 students, as they are the teachers.

Let’s see how to do this. On my system, I have installed Oracle 19c, so first let’s open the command prompt and type.

Write command “sqlplus /nolog” which provides access to utilize sqlplus. The “/nolog” parameter means “start SQL plus, but do not log into a database.”

Then connect to sys account of the pluggable database. (Please note if you are new to Oracle that at the core we have a Container Database (CDB) while on top of that, we can create multiple pluggable databases (PDB). [One PDB is created by default by Oracle during installation over the CDB]. Bottom line we will be doing all the below steps on the PDB.
- SQL> CONN sys/oracle@ORCLPDB AS SYSDBA
  (*Note: Here ORCLPDB is the container database)

Let’s create all the users.
- Students (for test we just create two) :
  - SQL>CREATE USER bob IDENTIFIED BY bob;
  - SQL>CREATE USER alice IDENTIFIED BY alice;
- Teachers
  - SQL>CREATE USER hemant IDENTIFIED BY hemant;
  - SQL>CREATE USER kath IDENTIFIED BY kath;
We will create a user VPDAdmin who will be creating the vpd function and policy.
- SQL>CREATE USER vpdadmin IDENTIFIED BY vpdadmin
We will create a user studentadmin who will create and own the studentmarks table and insert records.
- SQL>CREATE USER studentadmin IDENTIFIED BY studentadmin;
- SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO studentadmin;
Assign permission create session to all the users which will allow them to connect to database
- SQL>GRANT CREATE SESSION TO bob, alice, studentadmin, hemant, kath, vpdadmin;
To manage VPD policies VPDAdmin account needs access to DBMS_RLS package
- SQL> GRANT EXECUTE ON DBMS_RLS TO vpdadmin;
Also, the VPDAdmin has to create a procedure hence permission needs to be provided.
- SQL> GRANT CREATE PROCEDURE TO vpdadmin;
Now log in as studentadmin user
- CONN studentadmin / studentadmin@ORCLPDB
- CREATE TABLE Studentmarks (name VARCHAR2(50), physics NUMBER, maths NUMBER, arts NUMBER, teacher VARCHAR2(50));
Insert records into students
- INSERT INTO Studentmarks (name, physics, maths, arts, teacher) VALUES (‘Bob’,70,80,30,’Hemant’);
- INSERT INTO Studentmarks (name, physics, maths, arts, teacher) VALUES (‘Alice’,50,90,78,’Hemant’);
- INSERT INTO Studentmarks (name, physics, maths, arts, teacher) VALUES (‘Cath’,80,85,75,’Kath’);
- INSERT INTO Studentmarks (name, physics, maths, arts, teacher) VALUES (‘Mandy’,90,80,85,’Kath’);
- INSERT INTO Studentmarks (name, physics, maths, arts, teacher) VALUES (‘Rosy’,95,80,85,’Kath’);
Assign select operation on this table to bob, alice, hemant, kath,
- GRANT SELECT ON Studentmarks TO bob, alice, hemant, kath, vpdadmin;
Now login as vpdadmin to create a function with the predicate query which will be dynamically added to the users query
- SQL> CONN vpdadmin/vpdadmin@ORCLPDB
- SQL> CREATE OR REPLACE FUNCTION checkMarks (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
  RETURN ‘upper(name) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(teacher) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
  END;
  /
Next setup the VPD policy to attach the above function and the table with the type of operation allowed.
- BEGIN
  SYS.DBMS_RLS.ADD_POLICY(
  object_schema => ‘studentadmin’,
  object_name => ‘Studentmarks’,
  policy_name => ‘student_vpd’,
  function_schema => ‘vpdadmin’,
  policy_function => ‘checkMarks’,
  statement_types => ‘select’
  );
  END;
  /
To show the data in nice formatting
- column name format a10;
  column physics format 999;
  column maths format 999;
  column arts format 999;
  column teacher format a10;
Log in as SYSDBA who is the owner of the table to see what will be seen on the select query
- CONN sys/oracle@ORCLPDB AS SYSDBA
- SELECT * FROM studentadmin.Studentmarks;

(*So we noticed that VPD has no effect on SYSDBA.)

Now let us login as the table owner studentadmin
- CONN studentadmin / studentadmin@ORCLPDB
- SELECT * FROM studentadmin.Studentmarks;
  (*So here even if we are table owners but still no records are displayed as neither ‘studentadmin’ is teacher nor a student)

Now let us login as the teacher Kath
- CONN kath / kath@ORCLPDB
- SELECT * FROM studentadmin.Studentmarks;
  (*So here ‘Kath’ is a teacher hence she can see all the students reporting her)

Now let us login as the student Bob
- CONN bob/bob@ORCLPDB
- SELECT * FROM studentadmin.Studentmarks;
  (*So here ‘Bob’ is a student hence he can only see his record)

Reference:
http://mohamednabeel.blogspot.com/2014/08/oracle-virtual-private-database-vpd.html
https://oracle-base.com/articles/8i/virtual-private-databases