Kishore Analytics Solutions: OBIEE 11G Act as Proxy user Functionality Step-By-Step

The act as functionality is a proxy authentication functionality which authorize a user to act as an other user when navigating in BIPresentation Service.

When a user (called the proxy user) acts as another (called the target user), the proxy user can access the objects in the catalog for which the target user has permission.

Enabling a user to act for another is useful, for example:

when a manager wants to delegate some of his work to one of his direct reports
when IT support staff wants to troubleshoot problems with another user’s objects.

Brief Steps :

Defining the Association Between Proxy Users and Target Users
Creating Session Variables for Proxy Functionality
Creating a Custom Message Template for Proxy Functionality
Modifying the instanceconfig.xml File for Proxy Functionality
Assigning the privilege and restarting the BI Presentation Service

Step1 : (Defining the Association Between Proxy Users and Target Users)

Create table in database .

CREATE
TABLE OBEE11G_PROXY_ACTAS
(
PROXY_USER_ID VARCHAR2(30 BYTE) NOT NULL ,
PROXY_TARGET_ID VARCHAR2(30 BYTE) NOT NULL ,
PROXY_LEVEL VARCHAR2(10 BYTE) NOT NULL ,
CONSTRAINT OBEE11G_PROXY_ACTAS_PK PRIMARY KEY ( PROXY_USER_ID , PROXY_TARGET_ID )
ENABLE
) ;

PROXY_USER_ID : ID of the proxy user
PROXY_TARGET_ID : ID of the target user
PROXY_LEVEL : Proxy level (either full or restricted). A Restricted level gives you only a read access. (Note:Proxylevel value is case sensitive and must be all lowercase.)

Insert data into table . In my case data is

So, the proxy user weblogic should act as proxy target ID once we are done with this exercise .

Note : I created all PROXY_TRGET_ID users in weblogic console .(Click Here to to see how to create users in weblogic console)

Step2 :(Creating Session Variables for Proxy Functionality)

2.1 : Import OBEE11G_PROXY_ACTAS table into physical layer.

2.2: There are two system session variables along with their associated initialization blocks that you create to authenticate proxy users:

PROXY :

SELECT
PROXY_TARGET_ID
FROM
OBEE11G_PROXY_ACTAS
WHERE
UPPER(PROXY_USER_ID) = UPPER( ‘:USER’)
AND UPPER(PROXY_TARGET_ID) = UPPER(‘VALUEOF(NQ_SESSION.RUNAS)’)

PROXYLEVEL :

SELECT
PROXY_LEVEL
FROM
OBEE11G_PROXY_ACTAS
WHERE
UPPER(PROXY_USER_ID) = UPPER(‘:USER’)
AND UPPER(PROXY_TARGET_ID) = UPPER(‘VALUEOF(NQ_SESSION.RUNAS)’)

Step3:(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.
Verify whether the proxy user can act as the target user.
Get the list of proxy users that can act as the target user. This list appears on the target user’s My Account screen.
In this step create “LogonParamSQLTemplate.xml” file and place it under “<Middleware Home>\Oracle_BI1\bifoundation\web\msgdb\customMessages”

Note : If you dont find the folder ‘customMessages’ , then create create folder with the name ‘customMessages’ and place the ‘LogonParamSQLTemplate.xml’ file .

In my case the content of ‘LogonParamSQLTemplate.xml is’

<?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”>
<!– for EXECUTE PHYSICAL CONNECTION POOL, “SECURITYANDPROXY”.”Connection Pool” = –>
<!– SAS Repository physical_dbname.conn_pool_name –>
<getValues>EXECUTE PHYSICAL CONNECTION POOL “SECURITYANDPROXY”.”Connection Pool”
select PROXY_TARGET_ID from OBEE11G_PROXY_ACTAS where PROXY_USER_ID=’@{USERID}’
</getValues>
<verifyValue> EXECUTE PHYSICAL CONNECTION POOL “SECURITYANDPROXY”.”Connection Pool”
select PROXY_TARGET_ID from OBEE11G_PROXY_ACTAS where PROXY_USER_ID=’@{USERID}’ and PROXY_TARGET_ID=’@{VALUE}’
</verifyValue>
<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL “SECURITYANDPROXY”.”Connection Pool”
select PROXY_TARGET_ID, PROXY_LEVEL from OBEE11G_PROXY_ACTAS where PROXY_TARGET_ID=’@{USERID}’
</getDelegateUsers>
</logonParam>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

Step4:(Modifying the instanceconfig.xml File for Proxy Functionality)

You can modify the Oracle BI Presentation Services configuration file (instanceconfig.xml) to specify the following information for proxy functionality:

in the <TemplateMessageName> elements: The name of the custom message template in the Custom Messages folder (The default name is LogonParamSQLTemplate)
in the <MaxValues> elements: The maximum number of target users to be listed in the User box in the Act As dialog box. If the number of target users for a proxy user exceeds this value, an edit box, where the proxy user can type the ID of a target user, is rendered rather than a drop-down list of target users. The default is 200.

For example between the <ServerInstance> node, you can insert:

<LogonParam>
<TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
<MaxValues>100</MaxValues>
</LogonParam>

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.

Restart Presentation Services .(Click here to see how to restart services)

Step5:(Assigning the privilege and restarting the BI Presentation Service)

For each user whom you want to authorize as a proxy user or for each Presentation Services group whose members you want to authorize as proxy users, you need to assign the Proxy privilege.

5.1: Login to Presentation services http://localhost:9704/analytics