Business Intelligence – Oracle

Oracle BI EE 10.1.3.3.3/2 – Proxy User Functionality

Posted by Venkatakrishnan J on May 16, 2008

One of the very good features of BI EE is its ability to enable proxy functionality for all/some users. For example, as an Administrator user, one can proxy as another user and can find out what kind of dashboard other users have. One other good example is (given in the documentation) a manager user allowing one of his direct reportees to create reports for him. Though the setup for this functionality is straight forward, i thought i would blog about it since the functionality is commonly desired.(I believe Borkur had blogged about it before. Not sure where it is now. If anyone has the link, just leave a comment with the link and i would update the link here.) We shall be using 2 terms here. They are ProxyUser and TargetUser. You can understand what these 2 users mean from the below diagram

    

Basically ProxyUser is the user who would be proxying as other users. I.e User A can proxy as user B, C and D(targetUsers)

Enabling this functionality is a four step process.

1. Let BI Server and Presentation Server know about the list of target users.
2. Let BI Server and Presentation Server know about the list of proxy users.
3. Let BI Server and Presentation Server know about the relation between the target and proxy users (who will proxy in as whom)
4. Create an InitBlock and a system session variable called PROXY to allow the proxy user to authorize.

The first 3 steps are achieved by adding a set of tags to the instanceconfig.xml. Lets take a simple example. There are 2 users Administrator and paint. Our aim is to set up a proxy funtionality for the Administrator user so that he can proxy in as the paint user. In order to do that lets start with creating a simple table with the below structure.

CREATE TABLE PROXYTABLE
(
PROXYUSER VARCHAR2(100),
TARGETUSER VARCHAR2(100),
PROXYLEVEL VARCHAR2(20)
)

PROXYUSER – Stores the list of PROXYUSERS needed within BI EE. In our case, this column should have the Administrator user as one of the values.
TARGETUSER – Stores the list of TARGETUSERS needed within BI EE. In our case, this column should have the paint user as one of the values.
PROXYLEVEL – This can take 2 values — full or restricted. If full access is provided, then the proxyuser would have edit/read privileges of the target user (provided the proxyuser himself has those privileges). If restricted access is provided, then the proxyuser would only have read privileges (provided the proxy user himself has those privileges).

Now, lets insert the users as desired for our example into the table

INSERT INTO PROXYTABLE values('Administrator','paint','restricted');

Once this is done, create a seperate connection pool connecting to the database containing the above table.

    

Now, lets start with the first 3 steps mentioned above i.e to let the BI Server and Presentation Server know about the list of target, proxy users and their corresponding relationships. In order to do this, we need to first add a set of tags to the instanceconfig.xml that would let the presentation server know about an XML template(which would contain the queries to get the list of target, proxy users). So, add the tags given below to your instanceconfig.xml (between the ServerInstance tags)

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

    

In the above XML, TemplateMessageName tag value(LogonParamSQLTemplate) would be the Template name which we would be creating.

Go to {OracleBIDate}\web\msgdb\customMessages (if you do not have this folder, create one) and create the below XML file in this folder (you can give any name to the XML file)

<?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 Proxy.Proxy
  select TARGETUSER from PROXYTABLE where PROXYUSER='@{USERID}'</getValues>
  <verifyValue> EXECUTE PHYSICAL CONNECTION POOL Proxy.Proxy
  select TARGETUSER from PROXYTABLE where PROXYUSER='@{USERID}' and TARGETUSER='@{VALUE}'</verifyValue>
  <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL Proxy.Proxy
  select PROXYUSER, PROXYLEVEL from PROXYTABLE where TARGETUSER='@{USERID}'
  </getDelegateUsers>
  </logonParam>
  </XML>
  </WebMessage>
  </WebMessageTable>
</WebMessageTables>

Once this is done, restart the presentation server. Now, the next step is to initialize a system session variable PROXY from an init block. This will help in authenticating the proxy user as the target user.

    

    

In the InitBlock use this SQL.

SELECT TARGETUSER FROM PROXYTABLE WHERE  'VALUEOF(NQ_SESSION.RUNAS)' = TARGETUSER AND ':USER' = PROXYUSER

Do not worry about RUNAS session variable. This RUNAS session variable would be populated when you click on the target user. And based on what you have clicked, the PROXY user value would be populated. Now, if you log on as Administrator and click on Settings -> Act as, you would get the paint user.

    

    

Now, the Administrator user should be able to proxy as the Paint user

    

If needed, you can populate another session variable PROXYLEVEL to vary the access levels of the proxying user. But this should get you started.

Advertisements

6 Responses to “Oracle BI EE 10.1.3.3.3/2 – Proxy User Functionality”

  1. Daniel said

    Hi Venkat
    Great post!
    This is the URL you mentioned probably (http://www.rittmanmead.com/2007/10/04/how-to-enable-and-configure-the-obiee-proxy-act-as-functionality/)

    What if we want to proxy positions instead of user names?

  2. Mint said

    Thank you for good information~~*

    Please comeback to visit my blog too : http://about-pooltable.blogspot.com/

    I’m sorry , If you think this is spam. but may i thank you again.

    Bye

  3. thank you:))

  4. Anuradha said

    Hi, we have implemented the above steps as mentioned, but when i click on ‘Act As’ link, it is not able to retrieve users list and says ‘This functionality has not been enabled by your administrator.’.
    We have also checked NQServer.log file, it says:

    [nQSError: 13011] Query for Initialization Block ‘ProxyInitBlock’ has failed.
    [nQSError: 23006] The session variable, NQ_SESSION.RUNAS, has no value definition.

    Please let us know, if any other setup needs to be done.

  5. Limor said

    Hi Venkat,

    I have set the Act As functionality with all the steps you have provided – Thanks.
    Will it work also for regular users, meaning non presentation administrators? As example a manager and his secretary? I tried to add 2 regular users to the PROXYTABLE, but there is an error when trying to “Act As”.
    Do I need to add something more to this configuration?

    Thanks,
    Limor

  6. B Murray said

    Limor,
    I ran into the same thing. I was able to resolve the issue by checking “Allow direct database requests by default” on the General tab of the database in the Physical Layer of the Windows Admin tool. If you want that permission granted more fine grained you can goto the user or a group the user belongs to click on the “Permissions” tab click on the “Query Limit” tab and goto the “Execute Direct Database Requests” column, change the value from the default “Ignore” to “Allow” I saw from the sawserver.out.log file NQSerror 13017 “User or Group has not been granted the Direct Database Access privilege to access the database XYZ. Please verify the User/Group Permissions in the Oracle BI Administration Tool.” Venkat does that sound correct to you?
    Bob

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: