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
- You will need create table privileges in your database
- Oracle doc on using Mapviewer with either Oracle Spatial or Locator
References
- http://prasadmadhasi.com/2012/02/11/obiee-11g-act-as-proxy-user-functionality-step-by-step/
- https://datacadamia.com/dat/obiee/obips/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.
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)’)
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 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 : 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 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 “\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:
EXECUTE PHYSICAL CONNECTION POOL “SECURITYANDPROXY”.”Connection Pool”
select PROXY_TARGET_ID from OBEE11G_PROXY_ACTAS where PROXY_USER_ID=’@{USERID}’
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}’
EXECUTE PHYSICAL CONNECTION POOL “SECURITYANDPROXY”.”Connection Pool”
select PROXY_TARGET_ID, PROXY_LEVEL from OBEE11G_PROXY_ACTAS where PROXY_TARGET_ID=’@{USERID}’
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:
- In the elements: The name of the custom message template in the Custom Messages folder (The default name is LogonParamSQLTemplate)
- In the 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 node, you can insert:
LogonParamSQLTemplate
100
- For example between the node, you can insert:
- The name that you specify in the element must match the name that you specify in the element in the custom message file.
- Restart BI Services
Have more questions about OBIEE Act As 11g (or other topics)? We can help:
- Contact us using the form here, or call us at 952.947.9300.
- Drowning in data but parched for predictive insights? Arrange a free half-day analytics health check.
- Talk to our experts face to face at one of our upcoming industry events.