Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Oracle VPD and Oracle BI EE (Part 1)

Posted by Dylan Wan on January 4, 2008

Oracle database allows you secure the access to the table rows using the virtual private database feature.  It is one of the enabler for the EBS organization based security.

Oracle BI EE also provide the data security in the BI server based on repository setup.  However, can we also use the database feature together with our BI deployment?

Oracle VPD is one of my favorite topic.  I studied and tested it before it was introduced into the RDBMS.  It is one of many database features that were driven from the business application development requirement.

I will compare the two data security technologies and discuss how we can VPD in OBIEE.   This part 1 of the article will compare the features and relate the terms used.  It helps people understand one of the technology to learn the other.
The term “data security” used by OBIEE is similar  to the “row level security” used in Oracle RDBMS.  In the initial release, the Oracle RDBMS feature is also called the feature “Fined Grain Access Control”.  Now most people call it VPD.

The idea are very similar.  When a user query the rows from a  table, a security predicate should be attached to the query to further filter the data returned from the query.   In VPD, the predicate is returned from a “policy function”, which is a PL/SQL function that follows a standard policy function signature.  It takes the schema and object as arguments and returns the predicate.   In BIEE server, the predicate is defined as “Business Model Filter”.

The predicate needs to be associated to a selected list of objects that will be secured.  In VPD, this is done by calling a built-in package to register the policy function with the database tables or views.  In BI server, the “Business Model Filter” cannot be reused, so you directly define the filter for each BI object, which can be a “Logical Table” or a “Presentation Table”.

This is one of the difference.  In BI server, you cannot define the filter directly to a “Physical Table” so data in a given physical table may be secured when it is accessed from a given logical table, but it is not secured from another logical table.

In BI server, the data security is actually attached to a logical table under a given security group or a user.  The BI server fetch the filter that directly assigned to the user or indirectly assigned to the user via any group.  The overhead of the filter will be only applicable to those users.   In VPD, you can generate different predicate based on the users since you can add condition based on the user context into the PL/SQL policy function.  However the overhead of executing the policy function will happen to all users once you attach the policy function to a database table or a view.

VPD feature is often used together with the “application context” feature.  It is required if you want to use the contextual information, not the user itself in the policy function or in the predicate.  For example, if the data is secured by the organization the user is assigned to, the organization context can be set as an application context, the predicate can then be simply “org_id = sys_context(‘mynamespace’,’owning_org_id’).  The application context can be set by calling the pre-built RDBMS package dbms_session.set_context.

In BI EE, again, the idea is very similar.  You can define the session variables.  the session variable can be initialized during initializing the BI user login session.  You can define “Initialization block” and associate them with the session variable.  The beauty of BI serve’s design is that the session can be initialized from a separate connection pool.  That means that you can store a security context in a separate database from the database which the data are secured.

One special feature  is that Oracle BI EE server supports the row-wise initialization.  You can use it to get a list of values for a given variable.  Using it together with the VALUEOF phase, you can cache the access list in the BI server.  For example, if a user can access multiple organizations from a given session.  You can still write the Business Model Fliter as “Fact-TRX”.”ORGID” = VALUEOF(NQ_SESSION.”ORG_LIST”).  The ORG_LIST can be list of multiple organizations.

In VPD, you do not get this feature.  You have the freedom to write the PL/SQL function logic.  You can combine VPD with other database feature to accomplish the same capability.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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