Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

OBIEE and Virtual Private Database (VPD)

What is VPD?

Virtual Private Database is Oracle’s fine grain access control (FGAC) feature that was introduced in Oracle 8i. It helps control data level security on the database side by applying policies, thus data level security in the applications that read from the database is not necessary. The advantage is that if there are multiple applications accessing data from a database, it is not necessary to implement data level security in all those applications.

How does VPD work?

Policies are created in the database that would append a predicate (a WHERE clause) to the query in runtime. Consider a simple example – there exists a policy which would return only the rows attached to a particular user id on the table Orders. If a user “Kumar” were to query data from Orders table, Kumar would enter the following command:

Select * from Orders;

The policy that dictates what information a user can see would append a predicate to the query as follows:

Select * from Orders

where user_name = ‘KUMAR’;

This mechanism of appending the predicate is entirely transparent to the user.

Click here to read more about VPD on Oracle’s OTN.

 

Configuring VPD in OBIEE

To use the VPD feature in the Database and OBIEE along with its caching capabilities it is important to configure VPD in OBIEE. Failing to configure VPD in OBIEE while caching is enabled (in OBIEE), the request would bypass VPD policies by accessing data from cache and data level security will not be effectively handled by the database’s VPD. Thus, the users will see incorrect results.

To configure VPD in OBIEE, first enable the VPD option in the database’s general tab as shown:

 

Then enable the “Security Sensitive” option in the security variable:

 

 

Normal OBIEE Cache Behavior

To be simple and brief, if caching is enabled, a query that is being run for the first time would create a cache. Subsequent requests that is similar to the query or its subset would hit the cache to retrieve the results. This is true even if the users are different.

Example:

Logged on as Kumar Kambam

 

Running a request…

… generates the following Query log

The cache is created….

 

Now any user that issues a similar request or a subset of the request will hit the cache.

Logged on as Power User1

Running a similar request, generates the following log. Notice that OBIEE server found a matching query in the cache that is created by Kumar.Kambam for the query issued by Poweruser1.

 

 

 

 

OBIEE Cache Behavior with VPD configured

When VPD option is configured in OBIEE, cache is created for each user even though a matching query exists in the cache. This ensures that the data retrieved for a user is not retrieved from the cache created by a different user, thereby ensuring the enforcement of VPD policies. In other words, if Kumar.Kambam were to run a query, the cache is created by the data visibility rules enforced by the VPD for Kumar.Kambam. If Poweruser1 runs a similar request it should bypass the query cache and hit the database to retrieve the data along the policies of the VPD for Poweruser1; if it were to hit the cache created by Kumar.Kambam, the results for Kumar Kambam will be presented to Poweruser1.

 

After configuring VPD, logged on as Kumar Kambam

Running a query for the first time…

…the following log is generated

The cache is created

Running the same query again, the following log is generated…

OBIEE found a matching query in the cache and uses it.

 

Now log on as Power User1

By running the same request, the following log is generated…

A new cache entry is created even though a similar request has been issued by a different user and a cache has been created for it

The subsequent requests by Poweruser1 that is similar to the query will hit its own cache. This ensures that a user will only see his/her data.

 

 

 

 

Advertisements

December 29, 2008 - Posted by | OBIEE Security | , , , , , , , ,

11 Comments »

  1. Hi Kunmar,

    Great article! I will reference it on my blog!

    regards

    John
    http://obiee101.blogspot.com/

    Comment by johnminkjan | January 1, 2009

  2. Gr8 job..!!!
    keep it up..

    Comment by Kishore Guggilla | January 2, 2009

  3. Hi Kumar,
    Good article.

    Regards

    Naresh

    Comment by Naresh Meda | January 2, 2009

  4. Kumar,
    I’m wondering if you might shed some light on my observation that a user’s request only will only get a cache hit on that same user’s cache? All of our cache on the bi server is at a user level. I can run the same exact request by another user and no cache hit will occur. We are using external authentication. Every user is uniquely identified in OBIEE. Our database connection pools are defined with shared login with a hardcoded user id, i.e. all physical sql on oracle is run as one particular oracle user.

    Thanks,
    John Tesson

    Comment by John T. | January 2, 2009

  5. Unless you have data level security set on user level than on group level, it is bound to happen. If the authorization is set on group level, then the cache hit occurs when a member of a same group issues a similar request.

    How is your authorization/ data level security being handled?

    Kumar Kambam
    http://www.biconsultinggroup.com
    https://obieeblog.wordpress.com
    http://oraclebiblog.blogspot.com

    Comment by Kumar Kambam | January 2, 2009

  6. Kumar,
    Thanks for this blog article, well explained.
    -Al

    Comment by Al Kannan | January 4, 2009

  7. Thanks Alot Kumar….Good Article!!

    Comment by Sridhar Mandala | January 5, 2009

  8. I observed the same behavior as indicated by John Tesson on 1/2/09 i.e. a user’s request only will only get a cache hit on that same user’s cache.

    We are not using VPD. As per the Admin guide –
    “Initializing Cache Entries for User Ids
    To initialize cache entries for user Ids, the Connection Pool needs to be set up for shared login ..If the shared login is disabled and a user specific database login is used, cache will be shared.”

    Does the above explain the behavior I see or am I missing something ?

    Thanks

    Comment by msp | February 16, 2009

  9. Looks good.. Thanks… Two questions though.

    1) Looks like VPD and sharing cache are mutually exclusive. Are there ways to have a hybrid approach work. e.g. We’d like to seed the cache for some high use tables and let all users share the results, but with other tables, we’d like to use VPD. Is this possible?

    2) To utilize VPD, we set an Oracle (non-OBI) session variable called CLIENT_INFO and our VPD policies use that to restrict row access based upon things like user-location…etc.
    Do you know of a way to set a session variable in ORACLE via OBI??

    All comments are appreciated!

    Thanks.
    Joel

    Comment by Joel Saunders | May 20, 2009

  10. Hi,
    Excellent article.

    I’m also keen to know if there is a hybrid approach, as similar to Joel Saunders request, we only have one table that is VPD’d.

    Thanks,
    Gareth

    Comment by Gareth Roberts | July 21, 2009

  11. Oops, forgot to tick notify me via email…

    Comment by Gareth Roberts | July 21, 2009


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: