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.
|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.
Once the policy function is created, link it to the policy.
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-
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 220.127.116.11.0 - 64bit. Learn more about Rackspace’s database services.