OBIEE 11g - 'Act As' functionality

When setting up security for new users, it is often useful to have a test ID to work with.  However, OBIEE has the ability to allow specific users the ability to 'Act As' another user.  This is nice functionality when setting up security or troubleshooting access issues and/or setting someone's preferences for them before giving them access to the tool.  I borrowed much of this from the following wiki (http://gerardnico.com/wiki/dat/obiee/actas), but it wasn't as straight forward as I needed it (I'm probably not as deep as Nicolas Gerard on the ins and outs of OBIEE), and it didn't have correct SQL for the PROXYLEVEL variable (which, in all fairness, is incorrect in the Oracle documentation).  I'll go through the process of setting this up in a way that makes more sense to me.  Hopefully, this will be helpful to others as well. 

1.  Create a table (I used the OBIEE_PROXY name as mentioned in Nico Gerard's wiki)

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

  ) 

2.  Populate the table.  I populated it with my ID as the PROXY_USER_ID, added all users in the PROXY_TARGET_ID and put the PROXY_LEVEL value as 'full'.  The choices on the proxy level are 'full' or 'restricted' (all lower case).  The table looks something like this.

 

3. Bring in the table into the Physical Layer in the RPD.  Of course, you'll need to create an alias so you can have two tables to create a data model.   I didn't bring this forward to the Business or Presentation layers, as I don't want to necessarily report on it.


4. Create Session Variables.  You'll need to create two session variables.  One called PROXY and one called PROXYLEVEL.  I created a separate connection pool for use with the Initialization block.  NOTE:  The SQL given in the Oracle documentation for the PROXYLEVEL variable is wrong.  You will see below that I have crossed out the original SQL statement and udpated it with the correct statement.  If you use the corrected SQL in the PROXYLEVEL variable, it will allow the PROXYLEVEL to be retrieved from table.  This is documented in Oracle Problem ID 1418227.1.  The Default Initialization Strings for the variables are as follows:

PROXY:

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 (Note:  Do not use the SQL in the Oracle documentation, which is shown crossed out below.  It will not work.  Use the SQL as shown in yellow below to allow the PROXYLEVEL variable to work):

SELECT PROXY_LEVEL
  FROM OBIEE_PROXY
 WHERE UPPER(PROXY_USER_ID) = UPPER(':USER')
   AND UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')

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)')

When setting my variables, I set the PROXYLEVEL default to 'restricted'.   

5. Create a Custom Message Template.   You will need to create this template in the following location (the 'xxxx' will depend on the folder name you used to do your install):

 \xxxx\OracleBI1\bifoundation\web\msgdb\messages\

I named my template LogonParamSQLTemplate.xml (as suggested in Nico Gerard's wiki).  You will have to create this, as this template does not exist otherwise.  I made a copy of one of the existing files in the messages folder, saved it off as LogonParamSQLTemplate and then pasted the following (change "physical_dbname"."conn_pool_name" to the connection pool name used in the session variable setup in step 4):

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

6. Update the instanceconfig.xml file to include the below elements.  The file should be located at:

[OBIEE_HOME]\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\

Be sure to back up this file before making updates.  For mine I added it before the </ServerInstance> tag at the end.  See picture below.

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

(See picture below for placement)



 7. Check OBIEE Security settings to make sure you've provisioned access to proxy for the appropriate role.  If you click on Administration, and select 'Manage Privileges'.

 

You should see the option of providing access to 'proxy'.




7. Restart services.  I didn't test whether or not this will work without restarting services, but I doubt it would work without it.    

Now, when you click on your name, and select 'Act As', it should give you a small window with a list of the users you inserted into the table in Step 2.