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.

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 57 other followers