Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Oracle VPD Security

Mark Rittman

Access to subject areas in the data warehouse is usually secured first by the use of Oracle roles, which allow us to put together groups of SELECT access rights to fact and dimension tables within the warehouse and grant these to groups of users. Role-based security is excellent for controlling access to database objects, but often data warehouse implementers need to further restrict access at a row level, for example to restrict queries to a subset of products or customers depending on the department or branch the user belongs to. In the past, row-level security was usually implemented through making users access the warehouse tables through views, including a WHERE clause in the view to restrict the data returned to only certain rows;  however, making users access data through views can impact performance, can confuse the cost-based optimizer and is cumbersome to administer. To address these shortcomings, from Oracle 8i and onwards a new database feature known as Virtual Private Databases can now be used instead.

Virtual Private Databases, explained in this excellent article by Don Burleson, are an Oracle Enterprise Edition feature that transparently adds predicates to user statements to limit down their access in a way that is transparent to the user, and the application. For example, if a user issues the statement

SELECT year, prod_category, sum(sales)
FROM sales_mv
WHERE year = '2002';

then the VPD feature might modify that query as follows;

SELECT year, prod_category, sum(sales)
FROM sales_fact
WHERE year = '2002'
AND prod_category in ('VIDEOS','RADIOS');

As this modification is carried out by the Oracle server directly against the users' query, and does not involve views, control tables, synonyms and the like, the VPD-modified query will fully utilize Oracle's query optimization features, such as materialized views, indexes, partitioning and parallelism. Using the DBMS_RLS package, Virtual Private Database policies can be created, dropped, enabled, disabled and refreshed and a framework can be set up to enabled fine-grained access control to the data warehouse tables. For further information about Virtual Private Databases, including details of the forthcoming enhancements due with Oracle 10g, take a look at this paper written by George Lumpkin over at the Oracleworld website.

In summary then, it's clear that by using the Single Sign-On feature within 9iAS, Oracle Advanced Security within Oracle 9i, and the Virtual Private Databases feature within Oracle 9i, a simple, seamless and complete data warehouse security architecture can be put together that makes life simple for users, and doesn't adversely affect query performance and response times.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational