Business Intelligence – Oracle

Archive for July 23rd, 2007

BI Publisher 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 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 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
if USER = ‘ANALYST’ then
   RETURN ‘1=1’;
   return ‘salary > 7000’;
return ‘1=0’;
end if;
end if;

2. Add a VPD policy with the above function

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

3. Check whether VPD is working

SQL> conn bipubapp_user[executive]/welcome1
SQL> show user
SQL> select count(*) from analyst.employees;


SQL> conn bipubapp_user[analyst]/welcome1
SQL> show user
SQL> select count(*) from analyst.employees;


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


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


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.


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.


Ofcourse, this works only with databases that are on 10g and the BIP version has to be 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.

Posted in Bi Publisher | 5 Comments »