Business Intelligence – Oracle

BI Publisher 5.6.2 and VPD

Posted by Venkatakrishnan J on July 24, 2007

In my last article, i had written about leveraging Proxy Authentication for VPD in BIP 10.1.3.2. But in earlier releases of BIP especially for people who are still on 5.6.2 version, there was no proxy authentication. Also, if a customer wants to leverage VPD within an Oracle database that is of earlier releases like 9i/8i then Proxy Authentication will not work. So in this article i would be focussing on 2 methods of using VPD within BIP 5.6.2. I will talk about 2 cases here. To be honest, the first one does not leverage VPD within the database. But i have included it here just for the sake of completeness.

1. If the idea is just to pass the username & get the data based on the user, this can be done directly from the BIP itself without going back to the database (without setting up VPD in the database).

Consider there are 2 enterprise users Executive & Analyst. We have to create a report from a table that has a column called userlogged. The idea is when Analyst logs in one must see the Analyst user’s (userlogged = ‘ANALYST’ in the sqlquery) data. Similarly, for the executive user one must see the Executive User’s data. 
               To implement this we would be creating an XMLP report with DataTemplate as the data source.
For more on Templates refer Tim Dexter’s blog and of course the BIP 5.6.2 User Guide

xmlp1.jpg

xmlp2.jpg
Use the following script for Data Template.

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD” Version=”1.0″>
    <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
    <properties>
     <property name=”showControls” value=”false”/>
     <property name=”online” value=”true”/>
     <property name=”parameterColumns” value=”3″/>
     <property name=”openLinkInNewWindow” value=”true”/>
    </properties>
    <parameters>
     <parameter dataType=”character” name=”P_USERNAME”/>
    </parameters>
    <dataQuery>
     <sqlStatement name=”Q1″>
 select EMPLOYEE_ID, JOB_TITLE, FIRST_NAME, EMAIL, HIRE_DATE, SALARY from employees a, jobs b where a.job_id = b.job_id  and UPPER(ADMIN_USER) = upper(:P_USERNAME)
</sqlStatement>
    </dataQuery>
    <dataStructure>
     <group name=”G_EMPLOYEE” source=”Q1″>
      <element name=”EMPLOYEE_ID” value=”EMPLOYEE_ID”/>
      <element name=”JOB_TITLE” value=”JOB_TITLE”/>
      <element name=”FIRST_NAME” value=”FIRST_NAME”/>
      <element name=”EMAIL” value=”EMAIL”/>
      <element name=”HIRE_DATE” value=”HIRE_DATE”/>
      <element name=”SALARY” value=”SALARY”/>
     </group>
    </dataStructure>
   </dataTemplate>

Here, the ${SYSUSER.username} variable would substitute the actual usernames into the query & hence would return different results for each logged in user. For complex queries one can use nested case statements to use this username variable & implement the logic accordingly. It is important to note the positioning of the parameter variable tags since placing anywhere else would not return the valid data. The other option would be is to have the where clause statement itself returned back to the bip by using lexical references. This way one need not implement vpd at the database level.

2. If the idea is to have some complex queries based on the username passed then VPD at the database level can be used.

 2.1.In the following example, the 2 users analyst & executive would be mapped within the database as admin & admin1 respectively. These users would be accessing the data under the HR schema which would be the VPD owner

 2.2.   The VPD policy would then be applied on a table called hr.countries over which the column COL_USER would be the column that maintains the user details.

 2.3.   Before creating the policy one would have to create application context package using the following script. create or replace context HR using HR.Context_Package_XML;create or replace PACKAGE Context_Package_XML AS
FUNCTION BeforeReportTrigger return boolean;
P_USERNAME VARCHAR2(100);
END;
create or replace PACKAGE BODY Context_Package_XML IS
FUNCTION BeforeReportTrigger RETURN BOOLEAN IS
v_ouser VARCHAR2(30);
BEGIN
DBMS_Session.Set_Context(‘HR’,’SETUP’,’TRUE’);
v_ouser := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);
IF upper(P_USERNAME) = ‘ANALYST’ THEN
BEGIN
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN’);
END;
ELSE
IF UPPER(P_USERNAME) = ‘EXECUTIVE’ THEN
BEGIN
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN1’);
END;
ELSE
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘HR’);
END IF;
END IF;
DBMS_Session.Set_Context(‘HR’,’SETUP’,’FALSE’);
RETURN TRUE;
END;
END Context_Package_XML;

Create the vpd function

create or replace FUNCTION user_only (
p_schema IN VARCHAR2 DEFAULT NULL,
p_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
AS
BEGIN
if sys_context(‘HR’,’USER_NAME’) = ‘HR’ then
RETURN ‘1=1’;
else
IF upper(SYS_CONTEXT(‘HR’,’USER_NAME’)) = ‘ADMIN’ THEN
return ‘upper(con_user) = ”ADMIN”’;
ELSE
IF upper(SYS_CONTEXT(‘HR’,’USER_NAME’)) = ‘ADMIN1’ then
return ‘upper(con_user) = ”ADMIN1”’;
else
return ‘1=0’;
end if;
end if;
end if;
END;

Add a vpd policy using dbms_rls.add_policy procedure

BEGIN
DBMS_RLS.add_policy
(object_schema => ‘HR’,
object_name => ‘COUNTRIES’,
policy_name => ‘COUNTRY_SEL_POL’,
function_schema => ‘HR’,
policy_function => ‘USER_ONLY’,
statement_types => ‘SELECT’);
END;

This application context package would set the context depending on the username (Analyst or Executive). This package would be run by using a before report trigger that’s available in BIP. Once this is done, the next step is to create a report using Data Templates. One can use the following Data Template code.

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD1″ defaultPackage=”Context_Package_XML” Version=”1.0″>
                                <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
    <parameters>
                                        <parameter dataType=”character” name=”P_USERNAME”/>
                                </parameters>
    <dataQuery>
     <sqlStatement name=”Q1″>
 select COUNTRY_ID, COUNTRY_NAME, REGION_ID,CON_USER from countries
</sqlStatement>
    </dataQuery>
                                <dataTrigger name=”beforeReport” source=”Context_Package_XML.BeforeReportTrigger”/>
    <dataStructure>
     <group name=”G_COUNTRIES” source=”Q1″>
      <element name=”COUNTRY_ID” value=”COUNTRY_ID”/>
      <element name=”COUNTRY_NAME” value=”COUNTRY_NAME”/>
      <element name=”REGION_ID” value=”REGION_ID”/>
                                                <element name=”CON_USER” value=”CON_USER”/>
     </group>
    </dataStructure>
</dataTemplate>

What this basically does is that it calls a before report trigger(a Pl/SQL package wherein we pass the username of the user logged in using the ${SYSUSER.username} variable. The Pl/SQL package basically sets the context thus enabling the report to be executed as the user based on the context.

Advertisements

One Response to “BI Publisher 5.6.2 and VPD”

  1. […] BI Publisher 5.6.2 and Oracle VPD […]

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

 
%d bloggers like this: