Overview of OBIEE Act as 11g

OBIEE Act as is a proxy authentication functionality which authorize a user to act as another user when navigating in the OBI dashboards.  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:

1.       Defining the Association Between Proxy Users and Target Users

2.       Creating Session Variables for Proxy Functionality

3.       Creating a Custom Message Template for Proxy Functionality

4.       Modifying the instanceconfig.xml File for Proxy Functionality

5.       Assigning the privilege and restarting the BI Presentation Service.

Prerequisites

  1. You will need create table privileges in your database
  2. Oracle doc on using Mapviewer with either Oracle Spatial or Locator

References

  1. http://prasadmadhasi.com/2012/02/11/obiee-11g-act-as-proxy-user-functionality-step-by-step/
  2. http://gerardnico.com/wiki/dat/obiee/actas

 

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 read access.
  • Proxylevel value is case sensitive and must be all lowercase.

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 see how to create users in weblogic console.

 

Step 2: Creating Session Variables for Proxy Functionality

2.1: Import OBEE11G_PROXY_ACTAS table into physical layer.

OBIEE Act As 11g - Creating Session Variables

 

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)’)
OBIEE Act as - authenticate proxy users

 

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

OBIEE Act as - proxy level

Step 3: 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 the custom message template, you place the SQL to retrieve this information in the following XML elements: Elements

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

In this step create “LogonParamSQLTemplate.xml” file and place it under “<Middleware Home>\Oracle_BI1\bifoundation\web\msgdb\customMessages”

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

 

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, SQL_Paint.SQL_Paint =  –>
<!– 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>

 

Step 4: 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:

  1. In the <TemplateMessageName> elements: The name of the custom message template in the Custom Messages folder (The default name is LogonParamSQLTemplate)
  2. 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.
    1. For example between the <ServerInstance> node, you can insert: <LogonParam>
      <TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
      <MaxValues>100</MaxValues>
      </LogonParam>
    2. 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.
  3. Restart BI Services

Have more questions? We can help:

eCapital Advisors

eCapital Advisors is one of the nation’s largest performance management and business analytics firms. Since 2001, we have been building long-term relationships and exceptional analytics solutions for leading organizations. With hundreds of clients in an array of industries, we have the experience, the solutions and the team to help meet your goals.

Comments are closed.