Intro to Virtual Private Databases (VPDs)

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Dec 14, 2017

Intro to Virtual Private Databases (VPDs)

Oracle VPD enables you to create security policies or group policies to control database access at the row and column level. It allows multiple users to access a single schema while preventing them from accessing data which is not relevant to them. VPD uses Fine-Grained Access Control to limit visibility of the data to the specific users. 

Introduction:
Introduced in Oracle8i, a Virtual Private Database (VPD) is the most popular security feature of Oracle Database Enterprise Edition. It is used when the standard object privileges and associated database roles are insufficient to meet the application security requirements.

Oracle VPD enables you to create security policies or group policies to control database access at the row and column level. It allows multiple users to access a single schema while preventing them from accessing data which is not relevant to them. VPD uses Fine-Grained Access Control to limit visibility of the data to the specific users. This is also referred to as the Row Level Security (RLS) and Fine Grained Access Control (FGAC). 
Generally, we leverage data access control in application accessing the data. Oracle VPD security policies provide a mechanism to secure data at the database level itself. The ability to secure data at a granular database object level is a very powerful feature of VPD. 

By principle, Oracle Virtual Private Database adds a dynamic WHERE clause to an SQL statement that is issued against the table, view, or is a synonym of an applied Oracle Virtual Private Database security policy.

We attach security policies directly to the database tables, views, or synonyms. Oracle Virtual Private Database enforces security to a fine level of granularity directly on these objects.

As a result, the policies are automatically applied whenever a user accesses data from these objects. There is no way to bypass the VPD security policy added for these objects. 

When a user accesses the VPD enforced object (table, view, or synonym), based on the VPD predicate function, Oracle engine dynamically modifies the SQL statement of the user. 

An additional WHERE clause condition is added as returned by the policy function of the object being accessed. Oracle engine modifies the statement dynamically as returned by the predicate function of the VPD policy. Oracle Virtual Private Database policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements. 

Oracle Virtual Private Database policy uses the DBMS_RLS package for VPD enforcement, which is described in detail below. The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement VPD. DBMS_RLS is available with the Enterprise Edition only.

Procedure

Description

ADD_POLICY

Adds a fine-grained access control policy to a table, view, or synonym

ENABLE_POLICY

Enables or disables a fine-grained access control policy

REFRESH_POLICY

Causes all the cached statements associated with the policy to be reparsed

DROP_POLICY

Drops a fine-grained access control policy from a table, view, or synonym

CREATE_POLICY_GROUP

Creates a policy group

DELETE_POLICY_GROUP

Deletes a policy group

ADD_GROUPED_POLICY

Adds a policy associated with a policy group

ENABLE_GROUPED_POLICY

Enables or disables a row-level group security policy

REFRESH_GROUPED_POLICY

Reparses the SQL statements associated with a refreshed policy

DROP_GROUPED_POLICY

Drops a policy associated with a policy group

DISABLE_GROUPED_POLICY

Disables a row-level group security policy

ADD_POLICY_CONTEXT

Adds the context for the active application

DROP_POLICY_CONTEXT

Drops a driving context from the object so that it will have one less driving context

Let’s understand the real power of a VPD through some basic examples.

Example 1: Row level VPD -

We want to secure the HR table “PER_PHONES” data. We want to add a VPD policy to this object so that no database users can see the data except the APPS user.

1) Create VPD policy function –
Create the VPD Policy function in the database where you want to implement VPD.

vpd policy function

2) Create the VPD policy-

Once the policy function is created, link it to the policy.

vpd create policy function

3) Query the table “PER_PHONES” with “APPS” user –

vpd query

4) Query the table “PER_PHONES” with users other than “APPS” user


vpd query table

Here VPD is applied to table “PER_PHONES” for both the users “APPS” and other when they issued a select on the table. The dynamic predicate returned by the policy function result in the data being displayed differently.

Example 2: Column Level VPD – 

We want to conceal some of the columns data for HR table “PER_ALL_PEOPLE_F”.

We need a VPD policy so that no database user other than “APPS” can see the data for below columns- 

  • DATE_OF_BIRTH
  • NATIONAL_IDENTIFIER
  • MARITAL_STATUS

The other columns data apart from the one mentioned above is available to all the users.

  • Create VPD Policy Function –

 create vpd policy function

  • Create the VPD Policy- 

Once the policy function is created, link it to the VPD policy

oracle policy function complete

sec_relevant_cols

Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to the synonyms. Specify a list of commas- or space-separated valid column names of the policy-protected object. Default is all the user-defined columns for the object.

sec_relevant_cols_opt

Use with sec_relevant_cols to display all rows for column-level VPD filtered queries (SELECT only), where sensitive columns appear as NULL. Default is set to NULL, which allows the filtering defined with sec_relevant_cols to take effect. Set to dbms_rls. ALL_ROWS to display all rows, but with sensitive column values, which are filtered by sec_relevant_cols, displayed as NULL.


  • Query the table “PER_ALL_PEOPLE_F” with “APPS” user –

oracle database table query

 

“APPS” user can see all the column data for the table. 

  • Query the table “PER_ALL_PEOPLE_F” with users other than “APPS” user –

 vpd query oracle

Other database users cannot see these columns data as the fields are masked for them as per the policy function. 

Conclusion:
VPD enables you to control access to table columns and rows by database users and non-database users (application or end-users). VPD policy groups and driving application context allows for selective hiding of certain application table columns for different application users. The policy function should not have complex logic as that might cause some performance issue. 

These examples are tested on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.

Learn more about Rackspace’s database services:

Database ServicesFor any questions on the topic above click below. You can also leave a comment in the field below:

Ask Harish