Oracle Virtual Private Database (VPD) is an awesome tool you can use to secure your data. It allows you to restrict data on row and/or column level for each user or role individually. It works for all tables, views, and synonyms where VPD was applied by appending the WHERE clause. It is actually awesome because there is no way for anybody to bypass this security layer 🙂
Whenever a user issues a query (SELECT, UPDATE, INSERT, DELETE, and CREATE/ALTER INDEX), directly or indirectly, Oracle Virtual Private Database automatically updates the query with a predefined WHERE clause.
Let’s demonstrate it in the following example:
-- user A issues SELECT query on an employees table
-- this user is allowed to see only his own direct reports and no one else
SELECT *
FROM oracle_world.employee;
-- Oracle Virtual Private Database is applied to this table
-- therefore the original query is automatically transformed to this
SELECT *
FROM oracle_world.employee
WHERE superior_employee = 10001 -- requestors ID
-- or you can use sys_context (application context) variables as well
SELECT *
FROM oracle_world.employee
WHERE superior_employee = SYS_CONTEXT('USERENV','OS_USER');
Benefits
Oracle Virtual Private Database is really a cool feature and its main advantage is a very simple, flexible, and secure implementation. Usually, all DBAs handle the security on the application level, which might, of course, lead to several issues and holes. With Oracle VPD you can be sure that all security rules will not be bypassed, either by a coincidence or on purpose, since the rules are implemented directly to the object.
Another great thing is you can configure all statements individually. Having said that, you can define one rule for SELECT, another for INSERT, different for UPDATE, and something totally else for DELETE. So you can easily let someone SELECT everything in the table but they can be limited to UPDATE only certain records without the possibility to INSERT. That’s awesome 🙂
Drawbacks
Each coin has two sides and Oracle VPD is no exception. You need to be careful not to “overdo” the logic and keep it simple or you can run into performance issues. On top of that, since the system is adding WHERE clause, the corresponding objects must be optimized accordingly (proper indexes, partitions, ….)
Policy Evaluation
There are three ways the VPD’s policy can be evaluated:
- Static – once per query
- Context-aware – only when the application context within the policy changes
- Dynamic – every runtime
I highly suggest sticking with context-aware policies. They allow you to have better and dynamic control over the access to your protected objects.
Furthermore, you can also create functions and assigned them to a policy. There are certain requirements for such functions such as:
- Must return VARCHAR2 data type
- Must generate a valid WHERE clause
- Must be a pure function
- Must take schema name and object name as input arguments
List of procedures (policies)
Ok, let’s cut the theory and start with something for enjoyable 🙂 To be able to use VPD, you have to have DMBS_RLS package in the database and, of course, you have to be allowed to use it 🙂 Here is the list of procedures you can use.
Individual handling
| Procedure | Description |
|---|---|
| DBMS_RLS.ADD_POLICY | Adds the policy to an object |
| DBMS_RLS.DROP_POLICY | Removes the policy from an object |
| DBMS_RLS.ENABLE_POLICY | Enables/Disables added policy |
| DBMS_RLS.ALTER_POLICY | Alters an existing policy to add or remove attributes |
| DBMS_RLS.REFRESH_POLICY | Invalidates cursors (non-static policies only) |
Group handling
| Procedure | Description |
|---|---|
| DBMS_RLS.CREATE_POLICY_GROUP | Creates a policy group |
| DBMS_RLS.DELETE_POLICY_GROUP | Deletes the whole policy group |
| DBMS_RLS.ADD_GROUPED_POLICY | Adds a policy (to a specified group) |
| DBMS_RLS.DROP_GROUPED_POLICY | Deletes a policy (from a specified group) |
| DBMS_RLS.ENABLE_GROUPED_POLICY | Enables a policy (within a group) |
| DBMS_RLS.DISABLE_GROUPED_POLICY | Disable a policy (within a group) |
| DBMS_RLS.ALTER_GROUPED_POLICY | Alters a policy group |
| DBMS_RLS.REFRESH_GROUPED_POLICY | Re-parses again all SQL statements associate with the affected policy |
Application Context handling
| Procedure | Description |
|---|---|
| DBMS_RLS.CREATE_POLICY_GROUP | Creates a policy group |
| DBMS_RLS.DELETE_POLICY_GROUP | Deletes the whole policy group |
| DBMS_RLS.ADD_GROUPED_POLICY | Adds a policy (to a specified group) |
| DBMS_RLS.DROP_GROUPED_POLICY | Deletes a policy (from a specified group) |
| DBMS_RLS.ENABLE_GROUPED_POLICY | Enables a policy (within a group) |
| DBMS_RLS.DISABLE_GROUPED_POLICY | Disable a policy (within a group) |
| DBMS_RLS.ALTER_GROUPED_POLICY | Alters a policy group |
| DBMS_RLS.REFRESH_GROUPED_POLICY | Re-parses again all SQL statements associate with the affected policy |
Examples
Let’s check some examples of usage.
Example1: Restrict access to contact columns (email, phone) for everybody except for the application account (=WEBAPP)
-- FUNCTION to limit the records CREATE OR REPLACE FUNCTION f_restrict_contact (i_schema IN VARCHAR2, i_objname IN VARCHAR2) RETURN VARCHAR2 AS where_limit VARCHAR2 (200); BEGIN IF user = 'WEBAPP' THEN where_limit := '1=1'; ELSE where_limit := '1=2'; END IF; RETURN (where_limit); END f_restrict_contact; / -- add policy BEGIN  DBMS_RLS.ADD_POLICY (  object_schema     => 'oracle_world',  object_name       => 'customer',  policy_name       => 'restrict_contact',  policy_function   => 'f_restrict_contact', --policy_function => 'pckg_restriction.f_restrict_contact', -- in case your function is part of a package  sec_relevant_cols => 'email,phone', sec_relevant_cols_opt => dbms_rls.all_rows ); END; / -- when the following query is issued SELECT name, phone, email FROM oracle_world.customer; -- this will be the output for WEBAPP NAME      PHONE EMAIL ----------- ------------- -------------------------- John Doe   234 878 9093  john.doe@oracle-world.com  Jane Black 125 819 1103 jane.black@oracle-world.com Jimmy Red 103 220 0203 jimmy.red@oracle-world.com Maria Creed 430 003 1100 maria.creed@oracle-world.com Donny Dark 329 810 0182 donny.dark@oracle-world.com -- this will be the output for any other user NAME       PHONE EMAIL ----------- ------------- -------------------------- John Doe     Jane Black Jimmy Red Maria Creed Donny Dark --drop the policy BEGIN DBMS_RLS.DROP_POLICY ( object_schema  => 'oracle_world', object_name    => 'customer', policy_name    => 'restrict_contact' ); END;
If you don’t want to show the restricted rows at all, remove the “sec_relevant_cols_opt => dbms_rls.all_rows” from the ADD_POLICY. This only makes sure it will show all columns with those restricted being NULL.
As you can see, those lines with different id_department will not be displayed; cool, right? 🙂
There, of course, other ways you can use the functions.
Example2: Limit access to data for a specific role.
-- FUNCTION to limit the records CREATE OR REPLACE FUNCTION f_restrict_contact (i_schema IN VARCHAR2, i_objname IN VARCHAR2) RETURN VARCHAR2 AS where_limit VARCHAR2 (200) DEFAULT NULL; l_has_role CHAR (1); BEGIN SELECT DECODE(count(*), 0, 'N', 'Y') INTO l_has_role FROM dba_role_privs WHERE 1=1 AND grantee = user AND granted_role = 'ROLE_MANAGER'; IF l_has_role = 'N' THEN where_limit := '1=2'; ELSE NULL END IF; RETURN (where_limit); END f_restrict_contact; /
You can get very creative with the roles, restrictions, group policies, and the whatnot. Oracle Virtual Private database can be very flexible and allows you to set extremely granular restrictions; just keep in mind the performance 🙂
If you are keen to learn more, dive into the official documentation where every single detail is described.