Wednesday, October 30, 2013

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.

8 comments:

  1. how to integrate OAM With OBIEE 11.1.1.7.140114 , DAC7.9.6.3 and Informatica9.0.1 for SSO? Is it possible ?

    ReplyDelete
    Replies
    1. Sorry I have no experience with OAM.

      Delete
  2. I have EBS Security in place to login to obiee. now there is a requirement to have SSO based direct logon to obiee as wel for soem users who want to login directly too.
    Is that possible to have both EBS and direct login to OBIEE with the same RPD. If yes, how we can achieve that.

    ReplyDelete
    Replies
    1. The short answer is yes, that is how we were setup at my previous engagement. Unfortunately where I am now we are not using OBIEE, so I don't have acces to my RPD to lookup how we did it. If I get some time, I'll see if I can load up the old RPD and see what we did.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. HI Ben, This is exactly what we require as we are using OBIEE/EBS Cookie authentication with BI only accessible from EBS. We've implemented OBI 11.1.1.7.140527. I've followed your instructions and amended the RPD, however the instanceconfig.xml file does not have the section, but has . If I add the section the system failes to start up. Should I add the entries into the section ?

    Also is the SA_Subject area a requirement or can we ignore this ?

    When I run the Agent I get this error:

    Error Codes: OPR4ONWY:U9IM8TAC
    State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
    [nQSError: 43113] Message returned from OBIS.
    [nQSError: 13040] The authentication init block 'Oracle E-Business SSO' failed during impersonation. (08004)

    Can yo please give me some advice on how to debug why this is happening?

    Cheers,

    Mat.

    ReplyDelete
    Replies
    1. The instanceconfig.xml does not have the Alerts section but had ps:Alerts

      Delete
  5. I've got this working now...

    This missing element; Activate the Direct EBS Database connectivity

    Excellent Post. Thank you.

    ReplyDelete