Business Intelligence – Oracle

Archive for August 29th, 2007

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.

Advertisements

Posted in All Posts, OBI EE Plus | 7 Comments »