Wednesday, July 9, 2014

Authentication, Authorization and more Shared Components

So far I have not seen any customer who used Oracle Single Sign-on (OSSO) or Oracle Access Manager (OAM) collaborating with the Oracle E-Business Suite. So, I could not take advantage of the pre-configured Authentication Schemes in Oracle Application Express. In Oracle's White Paper a solution is described when using custom authentication for Oracle E-Business Suite Oracle. However, that solution is more suitable for a stand-alone application. For our problem, we are looking for a fully integrated application and therefor I developed another solution using the cookie settings in the icx_sessions table of Oracle E-Business Suite. Moreover, when checking the session variables I store more relevant Oracle E-Business Suite session information in Oracle Application Express Application Items ...

Application Items (Shared Components > Logic)
Scope: Application
Session State Protection: Restricted - May not be set from browser

Application Item NameComments
EBS_USER_IDKey to User; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_IDKey to Responsibility; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_APPL_IDKey to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions)
EBS_SEC_GROUP_IDKey to Security Group; To check EBS authorization and to set EBS context (icx_sessions)
EBS_TIME_OUTSession Time Out in Oracle E-Business Suite (icx_sessions)
EBS_URLURL to return to EBS Homepage from APEX (icx_session_attributes)
EBS_ORG_IDEBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility
EBS_APPLICATION_NAMETo be displayed at the left tophand corner (application_name from fnd_application_tl using EBS_RESP_APPL_ID)

The Application Items are used in queries or when setting the ‘environment’ (apps_initialize).

Authentication (Shared Components > Security)
The Oracle Application Express pages are directly launched from the E-Business Suite. Additional login is not desirable, so no Authentication Scheme.

Authorization (Shared Components > Security)
I created an Authorization Scheme 'Check EBS Credentials' that will check whether the user has a valid E-Business Suite session. If so, then session attributes are copied into the Application Items. If not, then an error message will be displayed that access is not allowed. The E-Business Suite function icx_sec.getsessioncookie is used to determine the session_id. This session_id is the key to retrieve additional information from the E-Business Suite tables icx_sessions and icx_session_attributes.

Authorization Schemes: Create> (B)
Next> (B)

Name: Check EBS Credentials
Scheme Type: PL/SQL Function Returning Boolean
PL/SQL Function Body:
      RETURN apps.apex_global.check_ebs_credentials; 
Error message displayed when scheme violated:  "Access not allowed: No valid E-Business Suite session."
Evaluation point: once per page view
Create Authorizartion Scheme (B)

Code of function check_ebs_credentials (from package body apps.apex_global):

   FUNCTION check_ebs_credentials
      c_ebs             VARCHAR2(240) := 'E-Business Suite';
      l_authorized      BOOLEAN;
      l_user_id         NUMBER;
      l_resp_id         NUMBER;
      l_resp_appl_id    NUMBER;    
      l_sec_group_id    NUMBER;
      l_org_id          NUMBER;
      l_time_out        NUMBER;
      l_ebs_url         VARCHAR2(100);
      l_appl_name       VARCHAR2(240);

      CURSOR get_apps_credentials
         SELECT iss.user_id
         ,      iss.responsibility_id
         ,      iss.responsibility_application_id
         ,      iss.security_group_id
         ,      iss.org_id
         ,      iss.time_out
         ,      isa.value
         FROM  apps.icx_sessions iss
         ,     apps.icx_session_attributes isa 
         WHERE iss.session_id = apps.icx_sec.getsessioncookie
         AND   isa.session_id = iss.session_id
      CURSOR get_appl_name (b_appl_id NUMBER)
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
      OPEN get_apps_credentials;
      FETCH get_apps_credentials 
      INTO l_user_id
      ,    l_resp_id 
      ,    l_resp_appl_id
      ,    l_sec_group_id
      ,    l_org_id
      ,    l_time_out
      ,    l_ebs_url;
      IF get_apps_credentials%NOTFOUND THEN  
         l_authorized := FALSE;
         l_authorized := TRUE;
         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;
         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;
         CLOSE get_appl_name;


      END IF;
      CLOSE get_apps_credentials;
      RETURN l_authorized;
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      

Setting the session timeout seems not to work. After a while the Oracle E-Business Suite session shows a timeout, but if you did not close the APEX page to upload a file, it still lets you upload and submit a file ... Is this a bug?

Security Attributes (Shared Components > Security)
Access to any page in the APEX application is not allowed when no E-Business Suite session is active. This is arranged by setting the Authorization Scheme as a Security Attribute. However, it is also possible to manage authorization per page. In the latter case don't set the authorization scheme as shared component.

Security > Security Attributes: Authorization
Authorization Scheme: Check EBS Credentials
Apply Changes (B)

PS: In an attempt to get the session timeout working, I also tried to set the Maximum Session Idle Time In Seconds to 1800 (default value E-Business Suite). This is also a Security Attribute: Session Timeout. I read somewhere that this was a a condition to get the session timeout working. Unfortunately, it didn't help. Besides setting the Maximum Session Idle Time In Seconds here at application level it was initially already set at instance level by the dba (Oracle Application Express Administration).


  1. Hi Marc,

    Thanks for the such a good article, it helped me to understand the concept. I'm trying to integrate EBS 12.1.3 with the APEX 4.1, but i'm unable to use the ICX session as EBS and APEX are on different domains. Please help me with the solution for this.


  2. @Sarvesh, you can't use the cookie because they are on different domains. This is a strict cookie policy. My suggestion would be to write a custom jsp passing the cookie value encrypted with a seed to APEX in the url. Then you can decrypt the cookie in APEX domain and look up the needed information in the icx_sessions table.

    @Marc... just an fyi... Great article but besides different domains issue, I found another problem where EBS was using HTTPS and APEX (being a non external app) was using HTTP. The call in WHERE iss.session_id = apps.icx_sec.getsessioncookie will not work because the owa_cookie call in that function will not be able to see the secure EBS cookie from the APEX page (it wont exist). I had to add a custom cookie rewrite in the GWY.jsp to write out another unsecured cookie.

    1. Hi Donald,

      Thank you for your valuable contribution. I am running into the same problem as you described having EBS using https and APEX on http. You resolved this by adding a custom cookie rewrite in the GWY.jsp to write out another unsecured cookie. Can you give me more information / instruction how to achieve this. Thanks in advance.

      Kind regards, Marc

    2. This comment has been removed by the author.

    3. Hi Donald,

      We are facing the same problem when integrating EBS and Apex on different domains.
      Could you provide me more information / instruction how to add custom cookie rewrite in the GWY.jsp?


  3. Hi Marc!

    Nice and useful blog post!
    Do you also have a list of needed grants to execute the package "apps.icx_sec.getsessioncookie" as the apex user?
    I think apart from the execute grant there are also couple of select grants needed for fnd and icx tables/views, right?


    1. Hi Marc,

      I will answer it by myself. :)
      We do not need any sepcial grants as we will use a package within apps schema and "definer authid"!

      So we only need the execute grants on this package.


  4. do you have a copy of the custom cookie rewrite in the GWY.jsp; we are facing a weird issue where the cookie is not getting set in the IE8 (apps.icx_sec.getsessioncookie call returns -1) and i think the custom GWY.jsp will solve our issue.

  5. Hi Guys Does anyone can share with me this custom jsp? I 'm having this : apps.icx_sec.getsessioncookie call returns -1. The EBS application is in server1 and APEX is in server2.


    Edu Mendes