OBIEE and Virtual Private Database (VPD)d

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.

Data Level Security using Virtual Private Database

In this article we are going to see the implementation part of VPD concept in OBIEE. Before understanding the working of VPD, let us see the meaning of this concept:

Virtual private database graciously use the FGAC (fine grain access feature) which was implemented by Oracle 8i.It helps us with Data Level Security on the database side by applying policies. The advantage is that if there are multiple applications accessing data from the database then it is not necessary to implement data level security in all those applications.
It can be used as in combination of “application context” feature to enforce sophisticated row and/or column level security for privacy and regulatory compliance.

No matter how users connect to the protected table (via an web application) web interface or SQL* plus) the results are the same. No application problem persists since the access polices are attached to the table, and cannot be bypassed.

Working of VPD:
As discussed above in simple words the polices that are created in the database will append a predicate (where clause) to the query at run time.
Eg: there exists a query that would return data according to the user.

If a user would enter a query then he would enter the following:

Select * from table

Internally according to the policies the following query will be generated.

Select * from table
where user_name='XYZ'

this mechanism is transparent to the users.

Configuration of VPD in OBIEE
To use the VPD feature in the database and in OBIEE along with its caching capabilities it is necessary to configure VPD in OBIEE.

Failing to configure VPD feature when the cache is enabled (in obiee)the request would by pass all the VPD policies by accessing the data from the cache and the data level security will not be handled properly. The user will not see the results as expected.

In order to understand the topic in a much better way I have divided this article I two parts.
First we will see how the cache behaves normally and then in the second part we will see OBIEE cache behaviour with VPD installed

Normal OBEE cache behavior
Pr-requisite:Make sure that your cache is enabled so subsequent requests that are similar to the query or the subset will hit the cache and understanding the concept will be easier..

Step I:
Login as Administrator and create request(Any request).

Step II.
Now via the admin tool, in the repository check the cache section.
(Tools-Cache)

Since the cache is enabled you will see that an entry is made in the cache with respect to the request created in the presentation services.

Step III
Now Login as another user and send the same request
Here I Have logged in as User MARY WEST

Step IV
Create the same/similar request .In this case I have the same request that I had created when logged in as Administrator so it would be easy to understand.(Refer Step I).

Step V
Now again via the admin tool check the cache section/Repeat step II
You will see that the entry that was seen last time still persists. This means that the request that I have created  when logged in as Mary West has hit the cache.

Note: You can also check the NQQuery log to cross check if the query has hit the cache or not.

OBIEE Cache behavior with VPD installed

Step I
First in the repository you need to right click the database object and in the propreties you check the VPD check box as shown below.

Step II
In the repository Tool-Variables I create a system session variable and enable the Security Sensitive option.

Before going to the presentation services I purge the previous cache entries so that it will make things easier to understand.

Step III
Now in the presentaiton services I again log in as administrator and create a  request.

Step IV
Now via the admin tool in the repository check the cache section.
(Tools-Cache)

An entry will be made in the cache for the user administrator.

tip:You can also check the query log to see if the request is inserted into the cache

Step IV
Now log in as a different user(I logged in as Mary West) and fire the same or the subset of the request sent earlier when the user Administrator had logged in.

In the repositorysection check the entry made in the cache .

You will notice that even if the cache was enabled and the same/subset of the request created by the user Mary West(in my case) a separate entry is made according to the user Mary West.

The subsequent requests by the user Mary West that is similar to the query will hit its own cache.

This ensures that the user will see only his data.

--