Proxy User Functionality in OBIEE 11G

The Act As functionality enables you to act for another user in OBIEE. When a user  acts as another  user, the proxy user can access the objects in the catalog for which the other user has permission.

This functionality is useful, for example, when you must work on another user's dashboard or content, or when IT support staff wants to troubleshoot another user's account.

To use the Act As functionality, the administrator must enable you to act for another user. When the administrator authorizes you to act for another user, the administrator can grant you full access or restricted access to another user's account.

• Full access — When you are granted full access, you inherit the target user's privileges and can change the user's default dashboard and modify the user's content and preferences.

• Restricted access — When you are granted restricted access, you maintain your user privileges, but inherit the target user's permission for viewing data. Restricted access enables you only to view the user's data.
  

When you have enabled a user to act as a proxy user, that user can display the Act As option in the global header of Presentation Services to select the target user to act as, provided the Act As Proxy privilege has been set.

Process

1) Create a table in database, in my case, it is ACTAS

2) Insert data into table.

SQL> insert into ACTAS (PROXY_USER_ID, PROXY_TARGET_ID, PROXY_LEVEL)

Values ('weblogic', 'mhanumantha', 'full');

3) Import ACTAS table into Physical Layer and create one connection pool.

4) Drag and drop the same table to BMM Layer and Presentation Layer.

5) Creating Session Variables for Proxy Functionality

There are two system session variables along with their associated initialization blocks that you create to authenticate proxy users.

Initialization Block and Session variables

6) Creating a Custom Message Template for Proxy Functionality

You must create a custom message template for the proxy functionality that contains the SQL statement to perform the following tasks:

• 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.

Create LogonParamSQLTemplate.xml file and place it under

C:\OBIEE11g\product\fmw\Oracle_BI1\bifoundation\web\msgdb\customMessages

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

In my case, the content of LogonParamSQLTemplate.xml is as below.

<?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">

<getValues>EXECUTE PHYSICAL CONNECTION POOL "Usage Tracking"."ACTAS_CP"

select PROXY_TARGET_ID from ACTAS where PROXY_USER_ID='@{USERID}'

</getValues>

<verifyValue> EXECUTE PHYSICAL CONNECTION POOL "Usage Tracking"."ACTAS_CP"

select PROXY_TARGET_ID from ACTAS where PROXY_USER_ID='@{USERID}' and PROXY_TARGET_ID='@{VALUE}'

</verifyValue>

<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL "Usage Tracking"."ACTAS_CP"

select PROXY_USER_ID, PROXY_LEVEL from ACTAS where PROXY_TARGET_ID='@{USERID}'

</getDelegateUsers>

</logonParam>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

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

Insert below XML code between <ServerInstance> node in instanceconfig.xml of OBI Presentation service.

<LogonParam>

<TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>

<MaxValues>100</MaxValues>

</LogonParam>

8) Enable “Act As” privilege

Login to presentation services with weblogic or admin user and set the proxy privilege as shown below.

Add Users or Roles as shown below.

Now check the Act As Functionality

Go to weblogic, select Act As and select target user ID.

Dynamic Change of Target User on Database

If we want to impersonate the multiple target users with one proxy user we need to create multiple rows on single proxy user and it leads to data duplication. To avoid this, we will enable Write back functionality.

Write back

Write Back is the ability to enter values directly into a report and have those values used in calculations and charts in the report. For example, a report can have Sales Quota Amount defined as a write back field, Sales Amount as a field from the data warehouse, and Percentage of Quota as a calculated field (Sales Amount/Sales Quota Amount). When viewing the report you can change the Sales Quota Amount and the Percentage of Quota field recalculates appropriately.

Process

Write back configuration in repository at all 3 layers.

Physical Layer:

Create one connection pool for Write Back

Un-check Cache in physical table properties. In my case table is ACTAS

Logical Layer:

Select “PROXY_TARGET_ID” logical column and double click for Properties Tab.

Check the Writeable check box.

Presentation Layer:

Presentation Column Properties Go to-> General tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for WriteBack feature.

Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named "Execute Direct Database Requests".

In my case it is BI Administrator Role.

Write Back Configuration at the file level

1) Add the LightWriteback tag within the serverinstance tag of instanceconfig.xml file ( file path - $ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1 )

<LightWriteback>true</LightWriteback>

If this entry already exists then no modifications required.

2) WriteBack Template. You may give any name for the writeback.xml file. 

eg: writeback.xml

Things to do before you start the Template:

→ Identify the columns that are to be referenced.

→ We can use the column position or by column id in the XML definition.

→ We must include both insert and update statements in the template.

Template to be placed in the path:

obiee11g_home/Oracle_BI1/bifoundation/web/msgdb/CustomMessages

Write-Back Template example

Name - eg. writeback.xml

<?xml version="1.0" encoding="utf-8"?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">

<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

<WebMessage name="writeback">

<XML>

<writeBack connectionPool="writeback_cp">

<insert> </insert>

<update>UPDATE ACTAS SET PROXY_TARGET_ID='@{c6371106687f8d8fd}' WHERE PROXY_USER_ID='@{c28f23212876134aa}' </update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

Note: In above XML, you can see PROXY_TARGET_ID and PROXY_USER_ID values, these are generated at Advanced tab when we generate report with the above columns

Write back Configuration in Analytics

1) Give Privilege to the Role for Write Back ( Administration -> Manage Privileges -> Write Back & add the role for writeback privilege).

2) Develop report with columns PROXY_USER_ID and PROXY_TARGET_ID.

Enable Write back on PROXY_TARGET_ID column properties.

Add SQL Filter to the report on PROXY_USER_ID

Click on Results tab and edit table view properties.

Go to Write Back tab, check the Enable Write Back check box and give the details as shown below.

Template name(“writeback”) should be same as <WebMessage name="writeback"> in writeback.xml

Now save the report and add this to dashboard page.

Now enter the target id → click on Apply → go to weblogic → select Act As

There we are able to see target id to impersonate and click on OK

We can see signed in as weblogic [acting as mhanumantha]

Please let me know if any concerns on this.

Thanks,

Hanumantha Reddy Muchumari