Oracle Virtual Private Database (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 the visibility of the data to specific users. In this post, I will be showing you how a VPD is used with its advantages
As said earlier, Virtual Private Database (VPD) is a database security feature that is built into an Oracle database server, as opposed to being part of an application that is accessing the data. The user is only allowed to see the data they have been given permission to see.
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.
VPD was introduced in Oracle8i, which made the Virtual Private Database (VPD) the most popular security feature of Oracle Database Enterprise Edition. It is mostly 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, data access control is leveraged in an 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.
Oracle Virtual Private Database policy uses the DBMS_RLS package for VPD enforcement. The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement VPD. It should be noted that DBMS_RLS is only available with the Enterprise Edition of Oracle database. Below are the procedures available for the DBMS_RLS package
| 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 re-parsed |
| 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 | Re-parses 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 |
It’s now time for practicals…
