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.