VPD in Oracle

What Is Oracle Virtual Private Database?

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.
Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.
When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.
For example, suppose a user performs the following query:

SELECT * FROM OE.ORDERS;

The Oracle Virtual Private Database policy dynamically appends the statement with a WHERE clause. For example:

SELECT * FROM OE.ORDERS 
 WHERE SALES_REP_ID = 159;

In this example, the user can only view orders by Sales Representative 159.
If you want to filter the user based on the session information of that user, such as the ID of the user, you can create the WHERE clause to use an application context. For example:

SELECT * FROM OE.ORDERS 
 WHERE SALES_REP_ID = SYS_CONTEXT('USERENV','SESSION_USER'); 

Note:

Oracle Virtual Private Database does not support filtering for DDLs, such as TRUNCATE or ALTER TABLE statements.

Basing Security Policies on Database Objects Rather Than Applications

Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:

Security. Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data. 

Simplicity. You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications. 

Flexibility. You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE statements. For example, you might want to enable Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

Controlling How Oracle Database Evaluates Policy Functions

Running policy functions multiple times can affect performance. You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates. The following options are available:

Evaluate the policy once for each query (static policies).

Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).

Evaluate the policy each time it is run (dynamic policies). 

Configuring an Oracle Virtual Private Database Policy

About Oracle Virtual Private Database Policies

After you create a function that defines the actions of the Oracle Virtual Private Database WHERE clause, you must associate this function with the database table to which the VPD action applies. You can do this by configuring an Oracle Virtual Private Database policy. The policy itself is a mechanism for managing the Virtual Private Database function. The policy also enables you to add fine-grained access control, such as specifying the types of SQL statements or particular table columns the policy affects. When a user tries to access the data in this database object, the policy goes into effect automatically.
This section describes commonly used ways of attaching policies to tables, views, and synonyms. To manage an Oracle Virtual Private Database policy, you use the DBMS_RLS package, which is described in detail in

 lists the procedures in the DBMS_RLS package.

Table 7-1 DBMS_RLS Procedures

Procedure	Description
For Handling Individual Policies
DBMS_RLS.ADD_POLICY	Adds a policy to a table, view, or synonym
DBMS_RLS.ENABLE_POLICY	Enables (or disables) a policy you previously added to a table, view, or synonym
DBMS_RLS.REFRESH_POLICY	Invalidates cursors associated with nonstatic policies
DBMS_RLS.DROP_POLICY	To drop a policy from a table, view, or synonym
For Handling Grouped Policies
DBMS_RLS.CREATE_POLICY_GROUP	Creates a policy group
DBMS_RLS.DELETE_POLICY_GROUP	Drops a policy group
DBMS_RLS.ADD_GROUPED_POLICY	Adds a policy to the specified policy group
DBMS_RLS.ENABLE_GROUPED_POLICY	Enables a policy within a group
DBMS_RLS.REFRESH_GROUPED_POLICY	Parses again the SQL statements associated with a refreshed policy
DBMS_RLS.DISABLE_GROUPED_POLICY	Disables a policy within a group
DBMS_RLS.DROP_GROUPED_POLICY	Drops a policy that is a member of the specified group
For Handling Application Contexts
DBMS_RLS.ADD_POLICY_CONTEXT	Adds the context for the active application
DBMS_RLS.DROP_POLICY_CONTEXT	Drops the context for the application

 

Attaching a Policy to a Database Table, View, or Synonym

To attach a policy to a table, view, or synonym, you use the DBMS_RLS.ADD_POLICY procedure. You must specify the table, view, or synonym to which you are adding a policy, and a name for the policy. You can also specify other information, such as the types of statements the policy controls (SELECT, INSERT, UPDATE, DELETE, CREATE INDEX, or ALTER INDEX).
shows how to use DBMS_RLS.ADD_POLICY to attach an Oracle Virtual Private Database policy called secure_update to the HR.EMPLOYEES table. The function attached to the policy is check_updates.

Example 7-1 Attaching a Simple Oracle Virtual Private Database Policy to a Table

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'check_updates',
...

If the function was created inside a package, include the package name. For example:

 policy_function => 'pkg.check_updates',
...

Note:

Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'check_updates',
  statement_types => 'SELECT,INDEX');
END;

BEGIN
  DBMS_RLS.ADD_POLICY ( object_schema    => 'PRD'  -- sehema 
                      , object_name      => 'USERS' -- view name
                      , policy_name      => 'USERS'  -- view name
                      , function_schema  => 'PRD'  -- sehema 
                      , policy_function  => '' -- function name 
                      , statement_types  => 'select, insert, update, delete'  
                      );  
-- operation END;
 

Views

When working with views you have several options to hide some of the data. In the article on  I have told you about the way to hide columns from the table. By adding a predicate (where clause) to the view, you can restrict the rows a user sees. This would imply creating different views for different (groups of) users. If you have an interface built to interact with the data, that would also mean you have to maintain multiple versions of these interfaces. Not a big problem if you only have a small number (like two or three) interfaces to maintain, but if the number of interfaces grows, chances are that you will forget to implement changes in one or more of them.
Another way of dealing with different (groups of) users is to implement Fine Grained Access Control (or Virtual Private Databases or Row Level Security).

Row Level Security

RLS or VPD or FGAC is one of the features of the Oracle database with lots of different names. Commonly it’s referred to as Virtual Private Database (VPD) but it is implemented as Row Level Security (RLS) hence the names of the supplied package DBMS_RLS. The marketing term for this feature was (or used to be) Fine Grained Accesss Control.
What RLS does is transparently add a predicate to every query issued against a table that has a policy defined on it. The package provides a couple of programs to (amongst others) add, remove, enable and disable policies. The actual implementation of the policy should be done in your own package.

Steps to take

First you create a (packaged) function that will result in the predicate to be added. This function has a specific signature. It has two in parameters which are the schema name and the object (table, view or synonym) name of the object. It must return a where predicate as a result. Attaching the function to the object you want to protect provides the names of the schema and object to which the policy will apply:

FUNCTION my_policy(
  schema_in IN VARCHAR2,
  object_in IN VARCHAR2
 )
RETURN VARCHAR2 

In this function you execute anything you would normally be able to execute in a PL/SQL function. Be aware that this function is run every time you execute DML on the object, so if this one runs for a relatively long time, your queries will run considerably longer.
In the implementation of the function you construct the predicate you want attached to the object:

IS
  l_return_value VARCHAR2(32767);
BEGIN
  l_return_value := '1=1';
 RETURN l_return_value;
END my_policy;

After you have created the function, you must attach it to the object. You do this by executing a procedure in the DBMS_RLS package: DBMS_RLS.ADD_POLICY:

 BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'SCOTT',
    object_name      => 'EMP',
    policy_name      => 'EMP_POLICY',
    function_schema  => 'SCOTT',
    policy_function  => 'MY_POLICY',
    statement_types  => 'SELECT, INSERT, UPDATE, DELETE'
   );
 END;

object_schema	Specifies the schema containing the object (table, view or synonym) you want to protect, in our case that is SCOTT. If no object_schema is specified, the current log-on user schema is assumed.
object_name	Specifies the object within the schema to protect, in our case EMP.
policy_name	Gives this policy a name so it can be referenced with the other programs in the DBMS_RLS package. It must be unique for the same object.
function_schema	Specifies the schema in which the policy function was created. In our case it is also SCOTT but it can be any schema with the appropriate privileges. If NULL then the current schema is assumed.
policy_function	Specifies the function to enforce the policy. In our case that is MY_POLICY. If the function is defined within package, then the name of the package must be present.
statement_types	Specifies the operations when this policy applies. You can select one or more of the options INDEX, SELECT, INSERT, UPDATE and DELETE as a comma separated list. The default is to apply to all types except INDEX.

The function created does not restrict the visibility of the data at all, since the returned predicate is 1=1 which is always true. We want to return data more selectively, for example based on who is currently connected. One way of doing this is by using the USERENV application context.
Our function could look something like this:

IS 

  l_return_value VARCHAR2(32767);
BEGIN
 IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SCOTT' THEN
    l_return_value := '1=1';
 ELSE
    l_return_value := '1=0';
 END IF;
 RETURN l_return_value;
END my_policy; 

This will effectively hide all the data from anyone but SCOTT.
You can also create your own application context and check the value in the function. To achieve this we first need to create an application context:

CREATE OR REPLACE CONTEXT my_context USING my_context_pkg;

Then we create the package to actually set to context:

CREATE OR REPLACE PACKAGE my_context_pkg IS
 PROCEDURE set_max_dept;
END my_context_pkg;

CREATE OR REPLACE PACKAGE BODY my_context_pkg IS
 PROCEDURE set_max_dept
 AS
    l_max_deptno NUMBER;
 BEGIN
   CASE SYS_CONTEXT('USERENV'', 'SESSION_USER')
     WHEN 'SCOTT' THEN l_max_deptno := 9999;
     WHEN 'HR' THEN l_max_deptno := 10;
     WHEN 'OE' THEN l_max_deptno := 20;
     ELSE l_max_deptno := 0;
   END CASE;
   DBMS_SESSION.SET_CONTEXT('my_context', 'max_deptno', l_max_deptno);
 END set_max_dept;
END my_context_pkg;

Now we need to create a logon trigger to set the context (run this a SYS or a user with enough privileges):

CREATE TRIGGER tr_set_max_dept AFTER LOGON ON DATABASE
  BEGIN
    scott.my_context_pkg.set_max_dept;
  END;

In our function we can now check the application context and return the appropriate predicate:

 IS 

 l_return_value VARCHAR2 (32767);
 BEGIN
  CASE SYS_CONTEXT('my_context', 'max_deptno')
    WHEN 10 THEN l_return_value := 'DEPTNO <= 10';
    WHEN 20 THEN l_return_value := 'DEPTNO <= 20';
    WHEN 30 THEN l_return_value := 'DEPTNO <= 30';
    WHEN 40 THEN l_return_value := 'DEPTNO <= 40';
    WHEN 9999 THEN l_return_value := '1=1';
    ELSE l_return_value := '1=0';
  END IF;
  RETURN l_return_value;
 END my_policy;

This function can be written shorter and more flexible to the following:

 IS 

 l_return_value VARCHAR2 (32767);
 BEGIN
   l_return_value := q'[DEPTNO <= SYS_CONTEXT('my_context', 'max_deptno')]';
   RETURN l_return_value;
 END my_policy;

I have used alternative quoting in the predicate to get rid of the problem of doubling the quotes to get a quote in a string. You can read more about this in the oracle docs or view a simple example at

To test our policy we just created, grant access to the table to a couple of users and check their results:

GRANT ALL ON SCOTT.EMP TO HR;
GRANT ALL ON SCOTT.EMP TO OE;

Logon as HR:

CONNECT HR/<PASSWORD>

Remember we set the context to the max DEPTNO for HR to 10, so our SELECT should result in only these EMPs:

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO	ENAME	DEPTNO
7839	KING	10
7782	CLARK	10
7934	MILLER	10

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    99 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |    99 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DEPTNO"<=10)

Now we logon as OE

CONNECT OE/<PASSWORD>

We set the context to the max DEPTNO for OE to 20, so our SELECT should result in only the EMPs from these DEPTs:

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO	ENAME	DEPTNO
7839	KING	10
7782	CLARK	10
7566	JONES	20
7788	SCOTT	20
7902	FORD	20
7369	SMITH	20
7876	ADAMS	20
7934	MILLER	10

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   264 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     8 |   264 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"<=20)

Now we logon as SCOTT:

CONNECT SCOTT/<PASSWORD>

In the context function we set the max DEPTNO for SCOTT to 9999, effectively showing all rows.

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO	ENAME	DEPTNO
7839	KING	10
7698	BLAKE	30
7782	CLARK	10
7566	JONES	20
7788	SCOTT	20
7902	FORD	20
7369	SMITH	20
7499	ALLEN	30
7521	WARD	30
7654	MARTIN	30
7844	TURNER	30
7876	ADAMS	20
7900	JAMES	30
7934	MILLER	10

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   462 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

In Oracle 12c a new function, EXPAND_SQL_TEXT, has been added to the DBMS_UTILITY packages, which makes seeing what SQL is actually executed pretty easy.

variable x clob

begin
  dbms_utility.expand_sql_text
    ( input_sql_text => 'select e.EMPNO, e.ENAME, e.DEPTNO from scott.EMP e',
      output_sql_text => :x );
end;
/
print x
X
--------------------------------------------------------------------------------
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."DEPTNO" "DEPTNO" FROM  
(SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB"
,"A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL"
,"A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" 
FROM "SCOTT"."EMP" "A2" WHERE 1=1) "A1"

A table or view can have multiple policy functions defined. All predicates are AND-ed together. Using the EXPAND_SQL_TEXT program can help in debugging. If you have multiple policy functions defined, it might be a good idea to add a comment indicating which function resulted in a certain predicate.
If you are granted the EXEMPT ACCESS POLICY system privilege then all policies are by-passed (ignored). SYS has this privilege by default. Foreign Keys and Unique Keys also by-passed the Policy functions. If you have enough time, you could learn about the existence of records you are not allowed to see.
If you have an error in your policy function you get the ORA-28110: Policy function or package has error. This means no rows can be selected from the table. This also happens when you change an object where this program is, in any way, depending on. The policy function is not automatically recompiled. Fix the error or recompile the package to re-activate select on the table.

What Is Oracle Virtual Private Database?

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.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To implement Oracle Virtual Private Database, you must create a function to generate the dynamic WHERE clause, and a policy to attach this function to the objects that you want to protect.
I will explain that definition using example below.

Sample Case

we have department (10,20,30) in EMP table and want
1-user "user10" to select employees in department 10 only.
2-user "user20" to select employees in department 20only.
3-user "user30" to select employees in department 30 only.

Step 1 : Create Policy Group
This allows you to manage your policies, you can assign a policy to a policy group.
We can use create policy group from Toad

Or run the below code
BEGIN
  SYS.DBMS_RLS.CREATE_POLICY_GROUP
  (
   object_schema => 'SCOTT'
   ,object_name => 'EMP'
   ,policy_group => 'EMP_POLICIES'
  );
END;

Step 2 : Create Policy Function 
Policy function will return varchar2 and that is predicate that is added to where clause at table.
Function signature must be as done at function example.
This can be in a package or a stand-alone function.
<textarea rows="3" cols="25">
CREATE OR REPLACE FUNCTION auth_emps (
   schema_var   IN   VARCHAR2,                                      --required
   table_var    IN   VARCHAR2                                       --required
)
   RETURN VARCHAR2
IS
   return_val   VARCHAR2 (400);
BEGIN
   return_val :=
      CASE USER
         WHEN 'USER10'
            THEN 'DEPTNO = 10'
         WHEN 'USER20'
            THEN 'DEPTNO = 20'
         WHEN 'USER30'
            THEN 'DEPTNO = 30'
         ELSE NULL
      END;
   RETURN return_val;
END auth_emps;
</textarea>

Step 3 : Create the policy
We can create policy using Toad

You can decide which columns can be fetched at  result set using Security Relevant Columns tab

or you can do that step using the below code
BEGIN
  SYS.DBMS_RLS.ADD_GROUPED_POLICY
    (
      Object_schema         => 'SCOTT'
     ,Object_name           => 'EMP'
     ,policy_group           => 'EMP_POLICIES'
     ,policy_name           => 'SCOTT_EMPS'
     ,function_schema       => 'SCOTT'
     ,policy_function       => 'AUTH_EMPS'
     ,statement_types       => 'SELECT '
     ,policy_type           => dbms_rls.dynamic
     ,long_predicate        => FALSE
     ,sec_relevant_cols     => 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO'
     ,sec_relevant_cols_opt => NULL
     ,update_check          => FALSE
     ,enable                => TRUE
    );
END;

Step 4 : Test
Let's now connect using user10 the result set will be only employees that in Department 10 like the below

Let's now connect using user20 the result set will be only employees that in Department 20 like the below

 Let's now connect using user30 the result set will be only employees that in Department 30 like the below

At policy we didn't specify predicate for scott so if we log in by scott and query we will retrieve all employees like the following

At that post I used Toad many times to make process easy and if you aren't interested with Toad, You can do everything at VPD using sys.DBMS_RLS package.
I planned that article to be shortly, so for further details you can read that