Ask Oracle: How do I set up a Virtual Private Database?

Answer:
Within a single database, the virtual private database (VPD) enables data access control by user through dynamically rewriting the users SQL statements to include and additional WHERE clause predicate. A security policy function checks the user's application context to determine the correct predicate clause for that user. The high-level steps to create a VPD are:

1. Create the user's application context
2. Create a logon trigger that assigns that context to the user
3. Create security policy functions
4. Apply the security policies to the required tables

The above steps are carried out programmatically using PL/SQL. There is a fully worked example of creating a Virtual Private Database (including a download of the scripts) on the Oracle Technology Network (OTN).

• Go to: http://otn.oracle.com/obe/start/index.html
• Select Oracle9i Release 9.2.0.2 from the Oracle Database category
• Select Build a Secure Internet Data Center
• Take option 1 Creating a Virtual Private Database

Depending on your needs, you may also want to check out Oracle Label Security which provides a functional, out-of-the-box VPD policy and a label-based access control framework so you can specify labels (sensitive, highly sensitive, unclassified for example) for users and data. You can also create one or more custom security policies to be used for label access decisions.

These policies can be implemented without any knowledge of a programming language. Oracle label security provides an efficient way to implement fine-grained security policies using data labeling technology. For a fully worked example, instead of option 1 in the last step above, take option 2 Creating Label Based Access Control.

Do you have a question for Richard or the Oracle development team that you need answered? Post your questions to builder@zdnet.com.au.

Richard Rendell is the Senior Director of Australian Product Development for Oracle Australia.