‘Act As’ Functionality in OBIEE11.1.1.9

      Create few users here I have created 2 users in my instance or else you can carry on with the existing users if you have some users in your environment. Here I have created 2 users LLAVOIE, PBEAULIEU as an example.

 1. Create a table

CREATE TABLE "RISOBI"."OBEE11G_PROXY"

   (           "PROXY_USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE,

                "PROXY_TARGET_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE,

                "PROXY_LEVEL" VARCHAR2(10 BYTE) NOT NULL ENABLE,

                 CONSTRAINT "OBEE11G_PROXY_ACTAS_PK" PRIMARY KEY ("PROXY_USER_ID", "PROXY_TARGET_ID"); 
  2. Insert data into this table.

  3. Create a Session Initialization block PROXY_BLOCK as below:

Use below SQL and Data warehouse connection pool (where the table is created)

SELECT

PROXY_TARGET_ID,PROXY_LEVEL

FROM

WC_PROXY_ACTAS

WHERE

UPPER(PROXY_USER_ID)     = UPPER( ':USER')

AND UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')

In the Edit data target window, create the two session variables (Hit yes when asked if we want to use system session variables)

PROXY and PROXYLEVEL

4. Navigate to /data/mhome/Oracle_BI1/bifoundation/web/msgdb path on OBI server and create a folder customMessages if you don’t already see it. 
  5. Create an XML file “LogonParamSQLTemplate.xml“with the following content in it. Verify the highlighted connection pool name is accurate as per the target instance.

 

<?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 "OBIEE11G"."Proxy"

                  select PROXY_TARGET_ID from OBEE11G_PROXY where upper(PROXY_USER_ID)=upper('@{USERID}')

         </getValues>

         <verifyValue> EXECUTE PHYSICAL CONNECTION POOL "OBIEE11G"."Proxy"

                  select PROXY_TARGET_ID from OBEE11G_PROXY where upper(PROXY_USER_ID)=upper('@{USERID}') and upper(PROXY_TARGET_ID)=upper('@{VALUE}')

         </verifyValue>

         <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL "OBIEE11G"."Proxy"

                  select PROXY_USER_ID, PROXY_LEVEL from OBEE11G_PROXY where upper(PROXY_TARGET_ID)=upper('@{USERID}')

         </getDelegateUsers>

       </logonParam>

    </XML>

  </WebMessage>

 </WebMessageTable>

</WebMessageTables>

 6.  /u01/upgbiapp/Middleware/instances/upgbi/config/OracleBIPresentationServicesComponent/coreapplication_obips1

Modify instanceconfig.xml to add the below within the ServerInstance tag at the end:

<LogonParam>

<TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>

<MaxValues>250</MaxValues>

</LogonParam>

   

  7. Logon to analytics to navigate to manage privileges under catalog ‘Administration’

  8. Grant Act As privilege to the appropriate OBI role/users(Weblogic)


 

 9. Restart the BI services to make these changes into effect.

Note:

Before a proxy user can act as a target user, the target user must have signed into Presentation Services at least once and accessed a dashboard.

10.   Now logon as a user who have Act as privilege to verify:

Click on ‘Act As’ link under user name dropdown