OBIEE Step by Step Guide

December 1, 2009

OBIEE Impersonator / Proxy Creation

I think this is one of the very few software BI tools that has Off the Shelf functionality to impersonate as an user and see how it looks like.. Oracle/Siebel did a good job on this feature.. Lets commend them for adding this feature to developer’s arsenal.

Where does this help? Lets say you want to check whether a row level security you implemented has been working for this user or not? check whether this particular user has access to this column? See what kind of problems a particular user is having while navigating through the dashboard.. To troubleshoot all these problems, an administrator or one who is part of administrators can act as if he is that particular user.. ie you act as a proxy or act as an impersonator for this user..

Well, even though its completely out of the box, enabling this feature is far easy and no custom code addition is necessary.

This can be done in two steps.

1. Creating a view in your database to support this feature. Lets say its Oracle DB and the user you are logging in as has “Create View” priveleges on the database.

Run this script.

DROP VIEW PROXY_AS_VW;
CREATE VIEW PROXY_AS_VW AS SELECT a.pr_user_id AS
proxy_id,
  b.pr_user_id AS
target_id,
  b.display_name,
  ‘full’ AS
proxy_level
FROM obi_sec_user a,
  obi_sec_user b
WHERE a.pr_user_id IN
  (SELECT c.user_id
   FROM obi_sec_group_user c,
     obi_sec_group d
   WHERE c.grp_id = d.pr_grp_id
   AND d.grp_name = ‘Presentation Server Administrators’)
AND b.pr_user_id NOT IN
  (SELECT e.user_id
   FROM obi_sec_group_user e,
     obi_sec_group f
   WHERE e.grp_id = f.pr_grp_id
   AND f.grp_name = ‘Presentation Server Administrators’)
   UNION
   SELECT a.pr_user_id AS
proxy_id,
  b.pr_user_id AS
target_id,
  b.display_name,
  ‘restricted’ AS
proxy_level
FROM obi_sec_user a,
  obi_sec_user b
WHERE a.pr_user_id IN
  (SELECT c.user_id
   FROM obi_sec_group_user c,
     obi_sec_group d
   WHERE c.grp_id = d.pr_grp_id
   AND d.grp_name = ‘Proxy Restricted’)
AND b.pr_user_id NOT IN
  (SELECT e.user_id
   FROM obi_sec_group_user e,
     obi_sec_group f
   WHERE e.grp_id = f.pr_grp_id
   AND f.grp_name in (‘Presentation Server Administrators’,’Proxy Restricted’))

To know the structure of the tables involved in creating this view, please see the following DDL scripts.

CREATE TABLE “OBI_SEC_GROUP”
   ( “PR_GRP_ID” VARCHAR2(10 CHAR),
 “GRP_NAME” VARCHAR2(50 CHAR)
   );

CREATE TABLE “OBI_SEC_GROUP_USER”
   ( “USER_ID” VARCHAR2(10 CHAR),
 “GRP_ID” VARCHAR2(50 CHAR),
 “ENV_ID” VARCHAR2(10 CHAR)
   );

CREATE TABLE “OBI_SEC_TYPE”
   ( “PR_TYPE_ID” VARCHAR2(10 CHAR),
 “TYPE_NAME” VARCHAR2(50 CHAR)
   );

CREATE TABLE “OBI_SEC_TYPE_USER”
   ( “USER_ID” VARCHAR2(10 CHAR),
 “TYPE_ID” VARCHAR2(50 CHAR),
 “SEC_VAL” VARCHAR2(50 CHAR)
   );

CREATE TABLE “OBI_SEC_USER”
   ( “PR_USER_ID” VARCHAR2(10 CHAR),
 “FST_NAME” VARCHAR2(100 CHAR),
 “LST_NAME” VARCHAR2(100 CHAR),
 “DISPLAY_NAME” VARCHAR2(200 CHAR)
   );

These tables are part of my security set up and can be used as part of external table authentication.

Once this view is executed,  modify the instanceconfig.xml file. This file is found under \\OracleBIData\web\config\.

Add these tags between <ServerInstance> tags.

<ParamList>
<Param name=”IMPERSONATE”
source=”serverVariable”
nameInSource=”REMOTE_USER”
stripWindowsDomain=”true”/>
</ParamList>

<LogonParam>
 <TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
 <MaxValues>500</MaxValues>
</LogonParam>

Also, create a custom message by creating a file called LogonParamSQLTemplate.xml file. This file will be placed in /OracleBI/web/msgdb/customMessages/ folder

<WebMessageTables xmlns:sawm=”com.siebel.analytics.web.messageSystem“>

<WebMessageTable system=”SecurityTemplates” table=”Messages“>
<WebMessage name=”LogonParamSQLTemplate“>
<XML>
<logonParam name=”RUNAS“>
  <getValues>EXECUTE PHYSICAL CONNECTION POOL “Security”.”OBI Security Connection Pool” select TARGET_ID, DISPLAY_NAME from PROXY_AS_VW where PROXY_ID=’VALUEOF(NQ_SESSION.USER)'</getValues>
  <verifyValue>EXECUTE PHYSICAL CONNECTION POOL “Security”.”OBI Security Connection Pool” select TARGET_ID from PROXY_AS_VW where PROXY_ID=’VALUEOF(NQ_SESSION.USER)’ and TARGET_ID=’@{VALUE}'</verifyValue>
  <getDelegateUsers />
  </logonParam>
  </XML>
  </WebMessage>
  </WebMessageTable>
  </WebMessageTables>
Now, after doing all these configuration steps, please restart your OBI server. Login to the dashboard and go to the top menu where its says “Settings”. Choose “Act as” and a pop up should open with all users available in the server so you can act as a proxy.
As always, Kudos to all OBIEE evangelists !!

Thanks for your feedback……

 

PS: The tags mentioned above
<ParamList>
<Param name=”IMPERSONATE”
source=”serverVariable”
nameInSource=”REMOTE_USER”
stripWindowsDomain=”true”/>
</ParamList>

are optional considering you just want to enable Proxy settings. These are used for SSL purposes.

Also, the connection pool that is used here should allow ‘Execute direct database queries’ privilege for that particular connection pool. For this, open the Admin Tool, Manage –> Security –> Groups –> Proxy /Presentation Server Administrators –> Permissions –> Execute Direct Database Requests –> Choose ‘Allow’ from the dropdown.

Also make sure that Proxy Restricted access is enabled for these groups in the Webcat –> Administration –> Manage Privileges –> Add Proxy and Presentation Services Admin into this.

Advertisements

3 Comments »

  1. Hi Harikv,

    I noticed in your getValues section, you put added in DISPLAY_NAME on top of the user_id.

    Do you know how to get the Act As functionality to work but showing the DISPLAY_NAME instead of the USER_ID?

    I’ve tried a few ideas, but they end up failing the check PROXY = RUNAS.

    -Joe

    Comment by Joe Bertram — December 9, 2009 @ 8:26 pm

  2. can you show via screen shots please.

    Thanks

    Comment by Joann — May 19, 2010 @ 3:48 am

  3. Hi,

    Can you provide sample data for the five table?

    That will be helpful to understand the concept.

    Comment by John — June 2, 2010 @ 5:53 am


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: