Virtual Private Database

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.

VPD uses application contexts to provide row-level security and fine-grained access control based on a company's security policies. Application contexts are simply key value pairs that are created in a defined namespace. VPD was first introduced in Oracle8i.

Virtual Private Database (VPD), a feature of Oracle Database 11g Enterprise Edition, was introduced in Oracle8i and is one of the most popular security features in the database. VPD is used when the standard object privileges and associated database roles are insufficient to meet application security requirements. VPD policies can be simple or complex depending on your security requirements. VPD can be used in combination with the "application context" feature to enforce sophisticated row and/or column level security requirements for privacy and regulatory compliance. A simple VPD example might restrict access to data during business hours and a more complex VPD example might read an application context during a login trigger and enforce row level security against the ORDERS table.

No matter how users connect to the protected table (via an application, a Web interface or SQL*Plus), the result is the same. There is no "application security problem" anymore, since the access policy is attached to the table, and cannot be bypassed.
Benefits of Using Oracle Virtual Private Database Policies

Oracle Virtual Private Database policies provide the following benefits:

• Basing Security Policies on Database Objects Rather Than Applications
• Controlling How Oracle Database Evaluates Policy Functions

Basing Security Policies on Database Objects Rather Than Applications

Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:

Security. Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data.

Simplicity. You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications.

Flexibility. You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE statements. For example, you might want to enable Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

Controlling How Oracle Database Evaluates Policy Functions

Running policy functions multiple times can affect performance. You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates. The following options are available:

• Evaluate the policy once for each query (static policies).
• Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).
• Evaluate the policy each time it is run (dynamic policies).

Which Privileges Are Used to Run Oracle Virtual Private Database Policy Functions?

For greater security, the Oracle Virtual Private Database policy function runs as if it had been declared with definer's rights. Do not declare it as invoker's rights because this can confuse yourself and other users who maintain the code.

Using Oracle Virtual Private Database with an Application Context

You can use application contexts with Oracle Virtual Private Database policies. When you create an application context, it securely caches user information. Only the designated application package can set the cached environment. It cannot be changed by the user or outside the package. In addition, because the data is cached, performance is increased. Chapter 6, "Using Application Contexts to Retrieve User Information" describes application contexts in detail.

For example, suppose you want to base access to the ORDERS_TAB table on the customer ID number. Rather than querying the customer ID number for a logged-in user each time you need it, you could store the number in the application context. Then, the customer number is available in the session when you need it.

Application contexts are especially helpful if your security policy is based on multiple security attributes. For example, if a policy function bases a WHERE predicate on four attributes (such as employee number, cost center, position, spending limit), then multiple subqueries must execute to retrieve this information. Instead, if this data is available through an application context, then performance is much faster.

You can use an application context to return the correct security policy, enforced through a predicate. For example, consider an order entry application that enforces the following rules: customers only see their own orders, and clerks see all orders for all customers. These are two different policies. You could define an application context with a position attribute, and this attribute could be accessed within the policy function to return the correct predicate, depending on the value of the attribute. Thus, you can enable a user in the clerk position to retrieve all orders, but a user in the customer position can see only those records associated with that particular user.