Impersonate / Act as feature in OBIEE

The Impersonate feature in OBIEE enables one user to “act as” or login in as another user without the need for the other user’s password. Why would we need such a feature? This could be helpful in situations where you would want to delegate the rights to another user or when testers want to test certain functionalities (maybe from a security view point), for multiple users.

In this post, I’m going to show how the impersonation setup is done in OBIEE. I’m using version 11.1.1.6.0 for this post.

First you specify in a database table, the associations between user impersonation i.e. which user is allowed to log as which other user. Refer to this doc from Oracle and create your table.

CREATE TABLE OBIEE_PROXY
  (
    PROXY_USER_ID VARCHAR2(100 BYTE) NOT NULL
  , PROXY_TARGET_ID VARCHAR2(100 BYTE) NOT NULL
  , PROXY_LEVEL VARCHAR2(50 BYTE) NOT NULL
  , CONSTRAINT OBIEE_PROXY_PK PRIMARY KEY ( PROXY_USER_ID , PROXY_TARGET_ID ) ENABLE
  )

insert values about the users involved in the impersonation into this table. I have created two users which can impersonate each other.

The PROXY_LEVEL column determines the authority level which is of types

• Restricted — Permissions are read-only to the objects to which the target user has access. Privileges are determined by the proxy user's account (not the target user's account). For example, suppose a proxy user has not been assigned the Access to Answers privilege, and the target user has. When the proxy user is acting as the target user, the target user cannot access Answers.

• Full — Permissions and privileges are inherited from the target user's account. For example, suppose a proxy user has not been assigned the Access to Answers privilege, and the target user has. When the proxy user is acting as the target user, the target user can access Answers. Ref: Oracle Docs

Next you import this table into your repository. See below the screenshot of the table that I have imported into the physical layer.

Next, create a separate connection pool ( I called it “VarInitConn”) pool needed for the next step.

Next, you create session variables and initialization blocks to authenticate proxy users.

Open the BI Admin tool. go to the menu Manage –> Variables. In the variable manager dialog, select Session and on the RHS right click in an empty space to bring the context menu and choose “New Session Variable”. You can also do the same thing from the Menu  Action –> New –> Session –> Variable.

The below table shows the details of the session variables and their associated initialization blocks.

Variable	Initialization block
PROXY	name: proxyblock code: SELECT PROXY_TARGET_ID FROM OBIEE_PROXY WHERE UPPER(PROXY_USER_ID) = UPPER(':USER')    AND UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')
PROXYLEVEL	name: proxylevelblock code: SELECT PROXY_LEVEL FROM OBIEE_PROXY WHERE UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')   AND  UPPER(PROXY_USER_ID) = UPPER('VALUEOF(NQ_SESSION.RUNASORIGUSER)')

Next, create the custom message template that contains SQL statements to

• Obtain the list of target users that a proxy user can act as. This list is displayed in the User box in the Act As dialog box.
• Verify whether the proxy user can act as the target user.
• Obtain the list of proxy users that can act as the target user. This list is displayed on the target user's My Account screen.

Save (as per oracle docs) your script to  ORACLE_
INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplicati
on_obipsn\analyticsRes\customMessages

I saved the template file as LogonParamSQLTemplate.xml  in MW_HOME\Oracle_BI1\bifoundation\web\msgdb\messages (this is fine too.)

<?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">
       <!-- Repository physical_dbname.conn_pool_name -->
         <getValues>EXECUTE PHYSICAL CONNECTION POOL "DB11g_local"."VarInitConn"
                  select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}'
         </getValues>
         <verifyValue> EXECUTE PHYSICAL CONNECTION POOL "DB11g_local"."VarInitConn"
                  select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}' and PROXY_TARGET_ID='@{VALUE}'
         </verifyValue>
         <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL "DB11g_local"."VarInitConn"
                  select PROXY_USER_ID, PROXY_LEVEL from OBIEE_PROXY where PROXY_TARGET_ID='@{USERID}'
         </getDelegateUsers>
       </logonParam>
    </XML>
  </WebMessage>
</WebMessageTable>
</WebMessageTables>

Element	Description
getValues	Specifies the SQL statement to return the list of target users and corresponding proxy levels. The SQL statement must return either one or two columns, where the: First column returns the IDs of the target users Optional) Second column returns the names of the target users
verifyValue	Specifies the SQL statement to verify if the current user can act as the specified target user. The SQL statement must return at least one row if the target user is valid or an empty table if the target user is invalid.
getDelegateUsers	Specifies the SQL statement to obtain the list of proxy users that can act as the current user and their corresponding proxy levels. The SQL statement must return either one or two columns,where the: First column returns the names of the proxy users Optional) Second column returns the corresponding proxy levels

 

Next, edit instanceconfig.xml to configure impersonation settings.

just before the  </ServerInstance>  tag, add the following code

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

</ServerInstance>
</WebConfig>

Tag	Description
LogonParam	Serves as the parent element for the TemplateMessageName and MaxValues elements
TemplateMessageName	Specifies the name of the custom message template in the Custom Messages folder that contains the SQL statement to perform tasks related to displaying proxy and target users
MaxValues	Specifies the maximum number of target users to be listed in the Userbox in the Act As dialog box. If the number of target users for a proxy user exceeds this value, then an edit box, where the proxy user can enter the ID of a target user, is shown rather than list of target users.

Next, assign the “Act As Proxy privilege”  for each user or Application Role whom we want to enable as proxy users or give the “act as “ functionality. to do this:

Login into analytics as the BIAdministrator user (weblogic). In this case, I have assigned user1 and user2, BIAuthor role. Click the Administration link. Under the Security group, click on Manage Privileges.

 

Lastly bounce the BI Services.

Login to Answers and you will find the option to Act as, in the top left corner of your Answers page.

you will not be prompted for the password. you will login automatically as the target user (in this case user2)

 

Related Support doc: [ID 1418227.1]

Note: You may need to give the manageRepositories permission to each user you want to allow to act as a proxy user. This can be done by creating a new Application policy which in turn will relate to an Application role which in turn will have user groups assigned to it. But I have not done this part. I checked that currently in my system, only the BIAdministrator has the oracle.bi.server.manageRepositories permission.