Business Intelligence – Oracle

BI Publisher 10.1.3.2 and VPD

Posted by Venkatakrishnan J on July 23, 2007

I was going through an interesting post by Jonathan Lewis on Proxy Authentication that is available for end users via Sql Plus in 10g R2. This post made me think about the availability of the same in BI Publisher 10.1.3.2 wherein one can leverage the proxy authentication(for database connections that are on 10g) to do VPD or row level security. This article would demostrate how to use Proxy Authentication to do row level security within BI Publisher. Of course, this applies only if you are 10.1.3.2 version of BIP and 10g of database. For users who are on earlier releases, I would follow this one up with another article for showing how to go about doing the same.Assume that there are end users Analyst and Executive within BI Publisher(Analyst and Executive are also users within the 10g R2 database). There is one table under Analyst that has all the Employee details. Assume that the Analyst is working on the Employee table but would like Executive end user to see only those data wherein the salary is greater than 7000.

Create a Proxy user bipubapp_user that would be used to proxy authenticate both Analyst and Executive. The following commands are performed as a user Sys(any admin user)

Create user bipubapp_user identified by welcome1;

grant create session to bipubapp_user;

create user Analyst identified by welcome1;

grant create session, create table to Analyst;

alter user Analyst grant connect through bipubapp_user;

create table Analyst.Employees as select * from HR.Employees;

create user Executive identified by welcome1;

grant create session to Executive;

alter user Executive grant connect through bipubapp_user;

The next step is to set up the VPD policy within the database. I have a user called VPD_ADMIN to source the function and the VPD policy.

1. Create the following VPD function.

create or replace FUNCTION Executive_Apply 
                       RETURN VARCHAR2
AS
BEGIN
if USER = ‘ANALYST’ then
   RETURN ’1=1′;
else
IF USER = ‘EXECUTIVE’ THEN
   return ‘salary > 7000′;
ELSE
return ’1=0′;
end if;
end if;
END;

2. Add a VPD policy with the above function

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;

3. Check whether VPD is working

SQL> conn bipubapp_user[executive]/welcome1
Connected.
SQL> show user
USER is “EXECUTIVE”
SQL> select count(*) from analyst.employees;

  COUNT(*)
———-
        44

SQL> conn bipubapp_user[analyst]/welcome1
Connected.
SQL> show user
USER is “ANALYST”
SQL> select count(*) from analyst.employees;

  COUNT(*)
———-
       107

The next step is to go to the BI Publisher admin console and create 2 users Analyst and Executive.

bip1.jpg

Create a datasource that would use the bipubbapp_user as the login username. Make sure to enable proxy authentication while creating the datasource.

bip2.jpg

Give the necessary privileges to Analyst and Executive. Log out and log in as Analyst and create a report which would extract the data from the Employees table.

bip3.jpg

View the report. I have not created any templates here. This is just to show you how Proxy Authentication works in BIP.

Log out and log in as Executive. View the same report.

bip4.jpg

Ofcourse, this works only with databases that are on 10g and the BIP version has to be 10.1.3.2 or above. For the earlier releases, i would follow up with another article that would show you how to set application contexts in order to use VPD. On the same lines, Mark Rittman has an interesting article on setting up VPD in OBI EE. Interesting read.

I have not gone into details of Proxy Authentication. One can use secure application roles to have one more layer of authentication. For more information on Proxy Authentication, would request you to go through the docs here.

About these ads

5 Responses to “BI Publisher 10.1.3.2 and VPD”

  1. I hope this doesn’t sound like a mutual back-slapping club, but again, and excellent article, thanks. Now for me, I need to get VPD /proper/ working with OBIEE – you can “turn a switch” on the 10.1.3.2 release and have Oracle VPD take on the whole process of row-level security. I’ll have to put an example together soon.

    regards

    Mark

  2. [...] on July 24th, 2007. In my last article, i had written about leveraging Proxy Authentication for VPD in BIP 10.1.3.2. But in earlier [...]

  3. [...] you had gone through my previous article here on implementing VPD using proxy authentication from BI Publisher, i would have blogged about a [...]

  4. Srikanth said

    It is very interesting. I am having a different scenario. We are using VPD in our company for reporting. We want to build a cube using analytical workspace manager and want to apply the vpd to the cube through the admin tool. Can some one give me a step by step procedure on how to do it.

    Thanks
    Srikanth

  5. Sachin Bansal said

    Hi,

    I followed exactly the same steps… But I get the following error

    ORA-28150: proxy not authorized to connect as client

    Could you please help me on this..

    Thanks and Regards,

    Sachin Bansal

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 158 other followers

%d bloggers like this: