EBS and Apex integration without SSO

 

Requirement Overview

Many organizations use Oracle’s Application Express (APEX) web development tool as a means to extend the user interface of their Oracle EBS applications. A key requirement for doing this successfully is to make the APEX applications conveniently available to the user from within the EBS application suite. The typical way to do this is to define a custom EBS responsibility for each APEX application, then link each responsibility to its corresponding app. When an EBS user then selects an APEX-related responsibility, the APEX application will open in its own browser window, appearing to the user as an integrated part of the EBS suite itself. Ideally, the user should not have to sign in to the APEX application. A seamless integration must be smart enough to automatically authenticate the user to APEX based solely on the fact that the user is coming in from the EBS suite, where authentication has already been done.

Oracle recommends two alternatives for achieving this type of integration:

1. Oracle Access Manager
2. Oracle Single Sign-On

While these approaches have their merits, they each require a significant amount of configuration effort, usually involving a DBA or APEX administrator, as described in the documents above. For organizations looking for an easier way to integrate their APEX applications with their EBS suite, there is another, simpler approach.

This post describes an EBS-to-APEX integration method which requires less configuration than the approaches mentioned above. This method can be implemented by any developer with access to the APEX application builder, the EBS system administrator responsibility, and the APPS database schema.

Solution Overview

Our solution consists of three steps:

1. Define a wrapper package in the database. This package will contain a function to return the user’s current database session id which was established upon login to the EBS suite.
2. Build an “auto-login” page in the APEX application. This page will be the target that the user will be redirected to when the APEX-related responsibility is selected in EBS. It will automatically authenticate the user to APEX and then branch to the application home page.
3. Define the EBS responsibility and its link to the related APEX application.

Solution Assumption

Our integration solution assumes that the APEX authentication scheme is based on EBS user names.

Solution Details

Step 1: Define Wrapper Package

The key step in our integration approach is to allow APEX to authenticate a user based on the user’s already-established EBS session id. Using this session id, the APEX app can query the user_name and automatically complete the authentication process, as we will see in the steps below.

The standard database function to derive a user’s current session id is icx_sec.getsessioncookie. A small problem arises, however, if we try to call this function directly from the parsing schema for the APEX application. Due to lack of permissions on the underlying APPS objects that the function uses, the function will likely throw an error.

To solve this problem, we will create a small PL/SQL “wrapper” package in the database as shown below. The purpose of this package is to allow the APEX application to invoke the getsessioncookie function with all of the security privileges necessary to do so. So we will create our wrapper package in the APPS schema with AUTHID DEFINER invoker rights. This option instructs the package to always run under the rights of its owning schema (APPS) rather than the rights of the invoking schema

We must also grant to our APEX parsing schema execute privilege on the wrapper package and select privilege on the table apps.icx_sessions. Once this is done, the APEX parsing schema can run the getsessioncookie function successfully by calling the wrapper function apps.apex_wrapper.getsessioncookie.

create package apps.apex_wrapper authid definer as
function getsessioncookie return number;
end apex_wrapper;

create package body apps.apex_wrapper as
function getsessioncookie
  return number is
begin 
  return icx_sec.getsessioncookie;
end;
end apex_wrapper;

grant execute on apps.apex_wrapper to ;
grant select on apps.icx_sessions to ;

Step 2: Build an auto-login page in the APEX application

A page must be created in each APEX application to handle login processing upon redirect actions from EBS. We will use page 103 for this purpose here. This page must contain the following components:

Page Item:	P103_USER_NAME
Type:	Hidden
Value Protected:	Yes
Used:	Always, replacing any existing value in session state
Source Type:	SQL Query (return single value)

<SELECT u.user_name
  FROM apps.icx_sessions s,
       apps.fnd_user u
 WHERE u.user_id = s.user_id
   AND s.session_id = apps.apex_wrapper.getsessioncookie
   AND s.last_connect + NUMTODSINTERVAL(s.time_out,'MINUTE') > SYSDATE>

This item will derive the EBS user name from the fnd_user table on page load. Note that the second condition uses the wrapper package which we created in step 1. The last condition in the where clause insures that the session is currently active.

Dynamic Action:	Submit page automatically
When Event:	Page Load
Condition Type:	Item is NOT NULL
Condition Item:	P103_USER_NAME
True Action:	Submit Page
Fire On Page Load:	Yes

This dynamic action will automatically submit the page if the P103_USER_NAME query above returned a value (i.e., if the user has an active session.)

Page Process:	Login
Process Type:	PL/SQL code
Process Point:	Processing

PL/SQL Code:

apex_authentication.post_login(
  p_username => :P103_USER_NAME
);

This process will execute the login process when the page is submitted. The post_login procedure performs all of the standard APEX login processing except for the authentication of a user name and password. Since we have already verified the existence of an active session and have derived the user name for that session, no further authentication is necessary.

The post_login procedure will do the following:

• complete the login process using the user name contained in P103_USER_NAME
• redirect the window to the application home page

Step 3: Define the EBS responsibility for the APEX application

A responsibility for the APEX application must be defined in EBS and assigned to the appropriate users. Definition of the menu form function to the APEX application is fairly straightforward. The standard format for the HTML Call field in the EBS Form Functions window is:

GWY.jsp?targetAppType=APEX&p=:::::::

So to launch an APEX application with id 101, we use this HTML Call:

GWY.jsp?targetAppType=APEX&p=101:103::::::
Where:        101 = APEX app id 103 = APEX auto login page