Configuring Agents with OBIA and EBS Cookie Integration

Where I am currently working, we are using EBS and OBIEE in a setup with EBS Cookie Authentication (blog post on this soon). This has some draw backs. One draw back is Agents can not be used. When trying to run an agent under this setup, as a user authenticated via EBS Cookie, you will get the following error:

[OBI-SEC-00015] Unable to find user in identity store

I opened an SR at the time, and the response back was a reference to an existing Bug (Bug 10632223: AGENTS NOT WORKING IN EBS SSO ENVIRONMENT WITH OBIEE 11G) and an enhancement (Bug 8326835: INTRODUCE SUPPORT FOR DELIVERS WITH EBS ICX_COOKIE).

According to the Oracle Support person, neither has been implemented. Apparently, Agents were never intended to be used with EBS cookie users, but only with users contained directly in usable authentication systems (ie OID/OAM/LDAP, etc).

Obviously, this is not acceptable, so I went to work to find a solution to get Agents up and running. We are pretty lucky here, in that every user whom can access OBIEE can access all subject areas in OBIEE. This might be different for you, so the end result might require you to do additional work.

Continue on to read for an explanation of what I did to get Agents up and running.

Steps to resolve Agents with EBS Cookie Authentication:

1. Create a new session initialization block called 'EBS Initialize Session Cookies'
2. Click on 'Edit Data Source' and input the default Initialization String as:

select '-1', '0' from dual;

3. Select the Oracle EBS OLTP Connection Pool
4. Press 'Edit Target Data' button and create 2 variables: ICX_SESSION_COOKIE and ACF
5. Define session variable: ICX_SESSION_COOKIE:
6. Check the "Enable any user to set the value" checkbox
7. Check the 'Security Sensitive" checkbox
8. Set the Default Initializer to:

'-1'

9. Define session variable: ACF
10. Check the "Enable any user to set the value" checkbox
11. Check the "Security Sensitive" checkbox
12. Set the Default Initializer to:

'0'

13. Redefine the 'Execute on connect' query for the Oracle EBS OLTP Connection Pools as follows:

Declare /* valueof (NQ_SESSION.ACF) */ v_icx_session_cookie varchar2(100) := 'valueof(NQ_SESSION.ICX_SESSION_COOKIE)'; begin if v_icx_session_cookie <> '-1' then APP_SESSION.validate_icx_session(v_icx_session_cookie); end if; end;

14. Update the session variable initialization block 'EBS Security Context' query to be:

select FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.SECURITY_GROUP_ID,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.USER_ID,
FND_GLOBAL.EMPLOYEE_ID,
NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') from dual
where NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') != 'GUEST'
NQS_PASSWORD_CLAUSE(and FND_GLOBAL.USER_NAME != 'GUEST')NQS_PASSWORD_CLAUSE

What this does is a couple of things:

1. If you attempt to login directly via the OBIEE login screen, and you do not exist in the internal weblogic LDAP, you will be authenticated against this SQL. Since we set up the new Execute on connect, as you have no cookie it will bypass that on-connect SQL. The FND_GLOBAL functions will return null/-1 for all values.
2. When the system is not logging in as an impersonated user, it will require the NQS_PASSWORD_CLAUSE to succeed, which is this case the value of USER_NAME is null and thus != GUEST. Hence the block will fail, and the user will not be allowed to login.
3. When an iBot/Agent runs this query, the contents of NQS_PASSOWRD_CLAUSE is ignored. Thus the query will return and allow the iBot/Agent to continue.

15. Log on to your server (via Putty or similar tool) and open the instanceconfig.xml file. For me this is located at:

${FMWHOME}/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml

16. Scroll down to the </Alerts> tag, and just above, add the following lines:

 

<!-- Custom Settings -->
     <ignorewebcatdeliveryprofiles>false</ignorewebcatdeliveryprofiles>
     <uppercaserecipientnames>false</uppercaserecipientnames>
<!-- Custom Settings End -->

17. At the bottom of the instanceconfig.xml file, right before the </ServerInstance> tag, place the following:

<!-- Custom Settings -->
<SubjectAreaMetadata>
<SystemSubjectArea>true</SystemSubjectArea>
</SubjectAreaMetadata>
<!-- Custom Settings End -->

Important Note: In order to not have to populate a physical system subject area table with all of my users, and then manually keep it up-to-date, the SA_SUBECT area was setup to pull from the W_EMPLOYEE_D dimension. This allows the ETL to automatically populate the users and email address, so new users will automatically appear in the SA Subject area, and will automatically have their delivery profile setup with their email address as it appears in EBS.

This subject area is simply a new logic table, Users, under the Core model.

As most of the columns are not required (see Oracle Note on SA Subject Area) they have been defaulted to null:

Finally, because the Employee Dimension is type 2, and we really only care about the current information for email address, we added a Content Filter:

18. For each subject area you want to allow Agents to access, they need to have the Authenticated User permissions applied. If not, then when the agent runs it will not be able to apply any filters, as it won't have access to those columns.

Note: Because of my circumstances, this was acceptable. It might not be for you. With some work, you might be able to force the Agent to authenticate as BI Author, or some other Application Role, in which case this step is not required.

19. Deploy the RPD file.

Agents will now work for all users.

One caveat. Agents can not be stored in a users My Folder, as the impersonated user that logs in will not have read access to that folder. Therefore, all Agents will need to be stored in the Shared Folders. Not ideal, but better than not having access to Agents at all.