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.

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.

 

2) Create the VPD policy-
Once the policy function is created, link it to the policy.

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


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

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 the VPD Policy-

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

 

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 –

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

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

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here