If you use Oracle’s Application Express (Apex) as part of an E-Business Suite implementation (not a common setup, but I think it’s an awesome combination), you’ll be faced with some basic questions on integrating Apex user authentication and authorization into the existing E-Business Suite. Unless your custom Apex application is totally independent of your E-Business Suite and it just happens to share a common database, you’ll most likely want to to test user credentials against the E-Business Suite.

To recap, Apex has two primary functional user security models: Authentication and Authorization. Authentication schemes control user access to an entire application. The user’s user name and password are checked before the user is allowed to access the application. Authorization schemes, on the other hand, control display and user access to pages, items, buttons, and links within an application. Between these two security tools, you can easily control access to whole applications or just portions of a page.

For authentication, Apex comes with several built in schemes including a database user, a nice workspace-user model, and the ability to create custom authentication schemes. By creating a custom authentication scheme, we can transparently test user credentials in Apex against their E-Business Suite logon. If you use Oracle Single Sign On, this this exercise is moot for you! Application Express is SSO enabled.

First, we need a way to test the supplied Apex user credentials in the E-Business Suite. Oracle nicely provides this in a packaged function: fnd_web_sec.validate_login(). However, this function returns varchar2, while Apex requires an authentication function that returns boolean. By wrapping this in a custom function we can convert the return value to the required boolean value. Here’s an excerpt on calling this function from this handy custom package: apex_custom_pkg:

FUNCTION authenticate (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2)
  RETURN BOOLEAN
  AS
  BEGIN
    RETURN (fnd_web_sec.validate_login (p_username, p_password) = 'Y');
  EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
  END authenticate;

With the package compiled, and the execute grant and synonym created in your custom schema, all that’s left to do is register your custom authentication scheme in Apex. Hint: Using the powerful ability to Publish and Subscribe certain components such as Authentication and Authorization schemes is a really good idea. It’s reusing code at it’s best.

To register a custom scheme, select Shared Components in your Apex Application, and then Authentication Schemes in the Security block. You will most likely see an icon for Application Express – Current along with two inactive database schemes. We’re going to add a new scheme for the E-Business Suite and make it the current one.

Press Create, and then the From Scratch radio button. Next, you can name and describe your scheme; no one sees this but other developers so name it something to the point, such as EBS LOGON. You could continue to walk through the wizard, but for this exercise I usually just click Create Scheme at this point.

Now click to edit your newly created EBS LOGON scheme. Scroll down to the Page Session Management block for your first edit. Select your logon page in the Session Not Valid Page list of values, which is normally page 101. Then delete “-BUILTIN-” from Session Not Valid URL.

Next, scroll down to the Login Processing block and enter your Authentication Function. Unlike other PL/SQL functions you call in Apex, enter the package and function but do not use parenthesis, list the parameters or include a terminating semicolon.

In this same block, you can optionally include Pre-Authentication or Post-Authentication processes. For example, you may want to cache the fnd_user ID in an Apex Application Item (sort of like a global variable and created separately):

apex_util.set_session_state('FND_USER_ID',apex_custom_pkg.ebs_user_id());

Unlike the authentication function, you do include arguments, parenthesis and terminating semicolons!

Apex automatically sets a cookie for session security. If you have multiple Apex applications related to your E-Business Suite (which are simpler to support than one large application), you can set a common cookie to allow seamless deep linking between applications.

Last, you need to modify the Logout URL to redirect users to your logon page.

Change:

wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=4155:PUBLIC_PAGE

To:

wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:101:&SESSION.:LOGOUT

Save your changes. To activate your custom authentication scheme, select the Change Current link on the Authentication Schemes home page. Select your EBS LOGON and confirm your selection. Of course, you should test your custom authentication scheme, both on logon and logout.

Of course, all of this only tests that the user has a valid E-Business Suite logon; whether they are authorized to use your custom application is another matter.

There are many approaches to authorizing users. You could include a responsibility assignment check in the custom authenticate function, such as calling apex_custom_pkg.valid_user_resp(). However, in this case the authenticate function would be limited to a single user role.

Your most versatile option is create a custom authorization scheme and then check the user after initially validating the login. This would first let the user in past the E-Business Suite authentication, but if the user is not authorized to use your application they would be presented with a custom access denied message.

At our work, where we have a suite of Apex applications supporting our E-Business Suite, we created a single “Home” Apex application that consists of just one page with a list of authorized links to the other Apex applications.

This Home Apex application authenticates users against their E-Business Suite credentials, but because each application has the same cookie name as the Home application, users are transparently allowed into the other applications. If a user bookmarks a specific application, they are still challenged for credentials.

However, by applying authorization rules to that list of links, users without a valid authorization do not even see the link and are effectively blocked from the unauthorized application (we also apply the same authorization rules within the target applications).

The Home application also contains our master List of Values, Templates, Navigation Bar Entries and other components to which other applications can subscribe.

Authentication is the first step towards seamless E-Business Suite integration. With Apex you can create custom forms to store related data, execute transactions through public APIs and more.

What Do You Think?