Virtual Private Database

Oracle has a security feature introduced in Oracle 8i called the Virtual Private Database (VPD). It allows you to specify policies at the row and column level. Under the hood, it adds a WHERE clause to SQL statements to limit what data you can see. Normally there is no way to bypass this security.

The VPD restrictions apply to select/insert/update/index/delete statements. They do no affect DDL. The security will have an impact on security. There are some types of VPD you can use to lessen the performance hit:

• Static policy - applied once per query
• Context sensitive - applied when context changes
• Dynamic policy - reevaluated every run

VPD runs with definers rights. You write a function that generates the WHERE clause to restrict access. This function must take a schema and object names as inputs. It must return a VARCHAR2 which is the WHERE clause addition. This special function cannot access the table it is providing security for.

You manage the VPD security with calls to the DBMS_RLS package. RLS stands for row level security. There is also a column masking type of VPD. That style will return all rows int he object. But sensitive columns will be returned as NULL values. The column masking technique only applies to SELECT statements.

VPD policies can be managed in groups. One should not perform a SELECT FOR UPDATE on an object managed with VPD. There can be multiple polices on an object. There WHERE clauses implementing the VPD will be joined via AND.

Earlier I said that VPD cannot be bypassed. VPD is not applied in these circumstances:

• DIRECT path exports
• Operations on SYS owned objects
• Operations run by SYS
• Operations run AS SYSDBA
• The user has the EXEMPT ACCESS POLICY privilege

VPD goes by other names. It is also known as Fine Grained Access Control. And it is also sometimes referred to as Row Level Security. Thus the DMMS_RLS package.