Business Intelligence – Oracle

OBI EE 10.1.3.3 and VPD

Posted by Venkatakrishnan J on August 29, 2007

I had one of the customers the other day asking for a step by step documentation for implementing VPD using OBI EE. I thought i would compile one here since this was the first time i myself was implementing VPD in OBI EE. There are many ways to implement VPD or role-based row level access from within OBI EE. They are

1.   Setting the context when a connection is established and then using that context for implementing VPD.
2.   Using proxy authentication
3.   Using variable username and passwords in the connection pools.
4.   Varying the where clause from within BI Server.

The above 4 are some of the ways that i could think of. Let me know if you come across some more methods. In this article, we would be going through the steps for implementing VPD by setting the contexts.

If you had gone through my previous article here on implementing VPD using proxy authentication from BI Publisher, i would have blogged about a scenario of 2 users trying to access the same employee table. We would be using the same scenario here. User ANALYST can see all the records in the employee table and user EXECUTIVE can see only those records wherein Salary > 7000. The first step is to create a context. Context is something that logging applications can use to identify themselves. I have a schema called VPD_ADMIN which would be the owner of all the contexts and functions. ANALYST schema would hold the employees and the departments table.

As VPD_ADMIN:

CREATE OR REPLACE CONTEXT VPD_CONTEXT USING SET_CONTEXT_PROCEDURE;

For the sake of security, contexts cannot be set directly. They can be set only via a procedure. In my case, i have created a context called VPD_CONTEXT that would be set by the procedure SET_CONTEXT_PROCEDURE. The next step is to create the procedure that would set the context.

create or replace procedure set_context_procedure
(
v_user in varchar2
)
is
begin
dbms_session.set_context(‘VPD_CONTEXT’,’USER_NAME’,v_user);
end set_context_procedure;

Here, the VPD_CONTEXT context has one attribute called USER_NAME that would be set from within OBI EE. Once the procedure is created, the next step is to call this procedure from OBI EE. Unfortunately, OBI EE cannot execute oracle procedures directly. In order to overcome this, we will create a function that would call this procedure and then call the function as sql from within OBI EE.

create or replace function set_context_function(v_user in varchar2)
return varchar2 is
begin
set_context_procedure(v_user);
return SYS_CONTEXT(‘VPD_CONTEXT’,’USER_NAME’);
end;

The next step is to create the function for implementing VPD. We will use the same EXECUTIVE_APPLY function that we used earlier and modify it a little bit.

create or replace FUNCTION Executive_Apply (
p_schema IN VARCHAR2 DEFAULT NULL,
p_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
AS
BEGIN
if upper(SYS_CONTEXT(‘VPD_CONTEXT’,’USER_NAME’)) = ‘ANALYST’ then
RETURN ’1=1′;
else
IF upper(SYS_CONTEXT(‘VPD_CONTEXT’,’USER_NAME’)) = ‘EXECUTIVE’ THEN
return ‘salary > 7000′;
ELSE
return ’1=0′;
end if;
end if;
END;

And then, add this function to the VPD Policy.

BEGIN
DBMS_RLS.add_policy
(object_schema => ‘ANALYST’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMPLOYEE_SEL_POL’,
function_schema => ‘VPD_ADMIN’,
policy_function => ‘EXECUTIVE_APPLY’,
statement_types => ‘SELECT’);
END;

Once this is done, check whether the VPD is functioning properly.

                     vsnap1.jpg

Once this is done, the next step is to import both the employees and department tables into OBI EE.

                     vsnap2.jpg

After that, we would have to create 2 users called Analyst and Executive using the OBI EE security.

                     vsnap3.jpg

After that, in the connection pool, change the username and passwords to that of VPD_ADMIN. Also, enable the check box to use the fully qualified table names.

                     vsnap4.jpg

Double click on the connection pool and go to the Connection scripts tab. Then enter the select script.

                     vsnap5.jpg

The next step is to log into BI Answers as ANALYST and EXECUTIVE and view the create reports out of the employees table.

                     vsnap6.jpg

                     vsnap7.jpg

As you see, the data would vary based on the logged in user though we are getting the data out of the same table. But better be aware of shared cache. Modify the query whenever you feel that the cache is coming in your way. You would have to make the cache VPD aware. I have not tried this. I would update this blog as soon as i try that one out. To make it VPD aware you would have to use security sensitive repository variables. Thanks to bryan for his comments. There are also other ways of implementing VPD. I believe Mark Rittman has already blogged about the 4th one in our list here. Another interesting way of implementing VPD wherein the predicates would be changed by the BI Server rather than the database.

About these ads

7 Responses to “OBI EE 10.1.3.3 and VPD”

  1. Bryan said

    The cache can (and should!!!) be made VPD aware. This is done by attaching security sensitive variable to the cache entries. Check out the Server Admin doc page 290.

  2. Venkatakrishnan J said

    Thanks Bryan. I have updated the blog accordingly. For the benefit of other readers, Bryan is one of the co-bloggers of BI Publisher blog maintained by Tim Dexter(http://blogs.oracle.com/xmlpublisher) and is a renowned BI expert within Oracle. Thanks again Bryan.

  3. Matt said

    That works ok for USER specific VPD – however eBusiness Suite has modules containing views which work on Effective Dates, set via sessions.

    I have a function my_pkg.set_effective_date(p_date) is there an easy way to plug this into a prompt (or maybe even a calendar portlet) to set application context?

  4. Venkatakrishnan J said

    Matt – Are you using Fusion Intelligence or BI Applications on Ebusiness Suite?

  5. Matt said

    Standalone OBIEE with 11.5 eBus suite. Not 12i Fusion.

  6. haki said

    can you tell more about how to make the cache vpd aware and implement cache sensitive variables ?

    the main problem when intergrating obiee with vpd is the cache issues. secured data is being cached and later populated to users without being enforced in the database.

    how can you overcome that without dissabeling (relevant) tables caching ?

    btw, i initialized my security inforcemnt using a select statment from some “apply_policy” function in a session variable declaration. i dont have the “connection script” tab in my connection pool (i’m using version 10.1.3 aswell).

    what is the difference ?

    thanks !

  7. Fernando said

    I’m tring to set the context when a connection is established, implementing Virtual Private Database (VPD).

    OBI server version 10.1.3.3.0

    O.S Windows 2003 server

    DB Oracle 10g release 2.

    Writing the following statement in the connection scrip tab (*execute before query option*) seems not to be working:

    SELECT SET_CONTEXT_FUNCTION (‘:USER’))

    I have also tried replacing :USER with a hardcoded value, same results.

    Strange as it sounds, the query is listed in the log file AFTER the report SQL statement. I imagine that’s why the policy is not applied in this case.

    When I change the script to *execute after query option*, query cannot be seen listed in the log file.

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

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: