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 Name	Comments
EBS_USER_ID	Key to User; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_ID	Key to Responsibility; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_APPL_ID	Key to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions)
EBS_SEC_GROUP_ID	Key to Security Group; To check EBS authorization and to set EBS context (icx_sessions)
EBS_TIME_OUT	Session Time Out in Oracle E-Business Suite (icx_sessions)
EBS_URL	URL to return to EBS Homepage from APEX (icx_session_attributes)
EBS_ORG_ID	EBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility
EBS_APPLICATION_NAME	To 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:

   BEGIN
      RETURN apps.apex_global.check_ebs_credentials; 
   END;

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
      RETURN BOOLEAN
   IS
      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
      IS
         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
         AND   isa.name = '_USERORSSWAPORTALURL';
         
      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
         
   BEGIN
      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;
      ELSE
         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;

         apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
         apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
         apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));      
         apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));      
         apex_util.set_session_state('EBS_URL',l_ebs_url);     
         apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);    

         apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');         
      END IF;
      
      CLOSE get_apps_credentials;
      RETURN l_authorized;
      
   EXCEPTION
      WHEN OTHERS THEN
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      
   END;

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