How to enable and configure the OBIEE proxy (Act as) functionality

Principle

The “Act As” or Proxy functionality gives the opportunity to some users to run the dashboards, the reports, … as someone else. Once you are logged into OBIEE dashboards, you can “act as” a list of predefined users, this feature is useful to check credentials, move/configure “my dashboard”, delivers, … without having to ask/know every user password.

From the dashboard Menu : Settings >> Act As

As you can see I was logged as “administrator” and I’m now acting as Borkur Steingrimsson

How does it works

OBIEE need a source of information stored outside the BI server that can give the list of users under which the logged user can “act as”. Once selected the dashboard is reloaded like if you are that user (with same target user privileges or read only privileges).

The proxy table

In my environment I already have an Oracle schema called “OBIEE” that contains user credentials, groups, …So I’m going to add a table there in which I will defined for each user, their possible target user and the right (full or restricted)

CREATE TABLE OBIEE_PROXY_USER( PROXYID VARCHAR2(100) NOT NULL, TARGETID VARCHAR2(100) NOT NULL, PROXYLEVEL VARCHAR2(100) NOT NULL)

Insert some data :

Create Session Variables for Proxy Functionality (inside the repository)

Session Variable	Associated Initialization Block
	Initialization Block Name: Set_RUNAS Initialization String Example: Select ‘Empty’ from dualThis init block is to set the RUNAS variable to some default and avoid error message in log like in NqsServer.log. 2007-09-09 00:05:15 [nQSError: 13011] Query for Initialization Block ‘ProxyLevel’ has failed. 2007-09-09 00:05:15 [nQSError: 13011] Query for Initialization Block ‘ProxyBlock’ has failed. [nQSError: 23006] The session variable, NQ_SESSION.RUNAS, has no value definition.….
PROXY	Initialization Block Name: ProxyBlock Initialization String Example: select targetid from obiee_proxy_user where ‘VALUEOF(NQ_SESSION.RUNAS)’=targetid and ‘:USER’=proxyId (Note that you will need to modify this SQL according to the schema of your database.) Execution precedence : Set_RUNAS
PROXYLEVEL	ProxyLevel Initialization String Example: select proxylevel from obiee_proxy_user where ‘VALUEOF(NQ_SESSION.RUNAS)’=targetid and ‘:USER’=proxyId (Note that you will need to modify this SQL according to the schema of your database.) Execution precedence : ProxyBlock

Modify the instanceconfig.xml File for Proxy Functionality

Here comes the more tricky parts …
First locate your instanceconfig.xml and back-up it !
The default location is like …./OracleBIData/web/config/
open the file and add the following code just before the </ServerInstance>

<LogonParam>
<TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
<MaxValues>100</MaxValues>
</LogonParam>
LogonParamSQLTemplate is your new message name (you will have to re use the exact same name just after !)
100 is the max number of user you can “Act as”

NOTE from the Doc : The name that you specify in the <TemplateMessageName> element must match the name that you specify in the <WebMessage> element in the custom message file.

Creating a Custom Message Template for Proxy Functionality

You need to create a custom message template for the proxy functionality that contains the SQL to get the list of target users that a proxy user can act as. This list appears in the User box in the Act As dialog box.

In the custom message template, you place the SQL to retrieve this information in the following XML elements. So let’s create a custom webmessage.
The new XML file should be placed in SAROOTDIR\web\msgdb\customMessages folder, where SAROOTDIR is the installation directory
(For me … /OracleBI/web/msgdb/customMessages/)

Name your file according to what TemplateMessageName you define in the instance Config ! (what you just did before !)
For me “LogonParamSQLTemplate.xml”

Here is the content

<?xml version="1.0" encoding="utf-8" ?>
<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 SystemUsers.OBIEE select targetId from OBIEE_PROXY_USER where proxyId='@{USERID}'</getValues>
<verifyValue>EXECUTE PHYSICAL CONNECTION POOL SystemUsers.OBIEE select targetId from OBIEE_PROXY_USER where proxyId ='@{USERID}' and targetId='@{VALUE}'</verifyValue>
<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL
SystemUsers.OBIEE select proxyId, proxyLevel from OBIEE_PROXY_USER where targetId ='@{USERID}'</getDelegateUsers>
</logonParam>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

Enable “Act as” privilege

Log in the dashboards with an admin user, go to “Settings” >> Administration >> Manage Privileges
Search for proxy and set the thing according to your needs.

Restart & Test

Now Last but not least restart the BI server and the presentation Server.
For the presentation server you can just “Reload Files and Metadata” in the Administration

If that does not work …
Well check your logs
…/OracleBI/server/Log/
and … /OracleBIData/web/log/

Extra steps

Since in our project we are using LDAP authentication, I had to disable the “required for authentication” for the rpd init block that check the user credential inside the ldap.

The reason is probably that OBI does not know the password of the user you want to “act as”.

Some ideas

If your users and credentials are stored inside the Database, this feature can be used to test “group credentials”, since almost all the logic is implemented inside simple SQL … Great OBIEE !

Source and biography
Oracle/Siebel documentation

Oracle OBIEE Doc details : Oracle® Business Intelligence Presentation Services Administration Guide >> Managing Oracle BI Presentation Services Security>> Process of Authorizing Users to Act for Others

A Word about the Author (Nicolas De Brabanter)

Since this the first time I contribute to this blog, I will shortly introduce myself. I used to be an IT consultant (for 10 years) labelled “Senior DWH”, “DWH architect” or even “DWH ETL Specialist”. My domain of expertise is BI & Data Warehouse (concepts, architecture, modelling, DB, etc …. ). I’m happy to share with Borkur a project that involves OBIEE, OWB, Oracle DB 10g and an acceptable DWH (1.5 TB)

in