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