Introduction

It was excellent to see several sessions at CloudWorld 2023 finally demonstrating the use of Oracle APEX with Fusion. Oracle APEX is a great fit for extending ERP Cloud and a valid alternative to VBCS or VBS. APEX 23.2 has a built-in SaaS REST Data Source Type that vastly simplifies accessing data in the Fusion REST catalog.

This blog describes how to create an APEX authentication scheme that hooks into an existing Fusion ERP session.

With this authentication scheme in place, we can link to our APEX application from the Fusion springboard, and the APEX application can check Fusion for the current user and their JWT. Any attempt to open the APEX application without a current Fusion session will be rejected.

Fusion JWT Servlet

The key component used by the authentication is a Fusion servlet.

While logged into Fusion, it will return a JSON document with the current user and their JWT

<FUSION BASE URL>/fscmUI/HedTokenGenerationServlet?response_type=code

{
   "tokenDuration":14400000,
   "preferredName":"Matt Paine",
   "timeStamp":"2023-09-01 15:30:50 GMT",
   "token":"<JWT_TOKEN>",
   "status":"success",
   "user":"matt.paine@jmjcloud.com",
   "preferences":{
      "CURRENTLANGUAGE":"en",
      "NUMBERFORMAT":"#,##0.###",
      "NLSLANGUAGECODE":"US",
      "NLSLANGUAGESTRING":"AMERICAN",
      "NLSSORTBEHAVIOUR":"BINARY",
      "DECIMALSEPERATOR":".",
      "TIMEZONE":"UTC",
      "TIMEFORMAT":"h:mm a",
      "CURRENCY":"USD",
      "USERDISPLAYNAME":"Matt Paine",
      "USERNAME":"matt.paine@jmjcloud.com",
      "GROUPINGSEPERATOR":",",
      "DATEFORMAT":"dd-MMM-yyyy"
   }
}

if you don't have a current session, you'll be redirected to the sign-on page for Oracle. Try it yourself with your own Fusion instance!

APEX Application Items

Our APEX application will be configured with the following application items:

• SAAS_ACCESS_URL - the base URL for the linked Fusion pod; this can be set dynamically using a Computation or with a static value.

• SAAS_ACCESS_TOKEN - this field will hold the JWT from Fusion

• SAAS_PREFERRED_NAME - this field will hold the Fusion user's name.

• SAAS_USER - this field will hold the Fusion username.

APEX Login Page

The APEX application redirects unauthenticated sessions to the login page. The login page invoke the servlet and stores the returned values in the application items.

On PageLoad DA

An OnPageLoad Dynamic Action that checks for an existing token. If this is missing, we call a Page Level Javascript function, getAccessToken.

// If we don't already have an access token, go and get one
if(!'&SAAS_ACCESS_TOKEN!JS.'){
  getAccessToken('&SAAS_ACCESS_URL!JS.'); // Escape JS special characters
}

getAccessToken

Get Access Token calls the Fusion Servlet to get the JSON content.

• If this goes well, a second function, saveTokenToSession is called to extract the JSON content to the Application Items.

• If this fails, we do not have a valid Fusion session. In the use case below, we re-direct to a Unauthorized Access warning page in our application. This warns the user that they do not have a Fusion session and advises linking to the APEX application from the Fusion menu.

function getAccessToken(baseUrl) {
    // Path to the servlet, Ajax fetch options, and a delay to allow the popup to load
    let url = baseUrl + "/fscmUI/HedTokenGenerationServlet?response_type=code";
    let opts = {
        'credentials': 'include'
    };
    let loadTime = 3000;
    let spinner = apex.util.showSpinner();

    fetch(url, opts)
        .then(response => response.json())
        .then(data => {
            spinner.remove();
            saveTokenToSession(data.token,data.user,data.preferredName);
        })
        .catch(error => {
            spinner.remove();
            apex.message.clearErrors();
            window.open('f?p=&APP_ID.:UNAUTH:&APP_SESSION.:::', '_self');
        });
}

saveTokenToSession

An AJAX callback is called set the values of SAAS_USER, SAAS_PREFERRED_NAME, and SAAS_ACCESS_TOKEN.

function saveTokenToSession(token,user,preferredName) {
    let spinner = apex.util.showSpinner();
    apex.server.process('SET_ACCESS_TOKEN', {
            x01: token,
            x02: user,
            x03: preferredName
        })
        .then(response => {
            // Reload the page, with the access token now available in the session
            if (response.ok) {
                window.location.reload();
            }
            // An error occurred
            else {
                spinner.remove();
                apex.message.clearErrors();
                apex.message.showErrors({
                    type: "error",
                    location: "page",
                    message: `Unable to save access token. ${response.sqlerrm || 'Unknown error.'}`,
                    unsafe: true
                });
            }
        })
}

SET_ACCESS_TOKEN (AJAX Callback)

Assuming the AJAX callback goes well, the user is redirected back to the home page:

Page 1 will redirect to the login page (we don't have a session yet) - and as the SAAS_ACCESS_TOKEN and SAAS_USER are now populated, the login page authenticates the session.

IF :SAAS_ACCESS_TOKEN IS NOT NULL AND :SAAS_USER IS NOT NULL THEN
    APEX_CUSTOM_AUTH.LOGIN (
        p_uname       => :SAAS_USER,
        p_password    => :SAAS_ACCESS_TOKEN,
        p_session_id  => V('APP_SESSION'),
        p_app_page    => :APP_ID||':1');
END IF;

We will now be redirected one more time back to the home page, with the SAAS_ACCESS_TOKEN and SAAS_USER populated.

APEX Authentication Scheme

The APEX authentication scheme is simple; we check for the application items SAAS_USER and SAAS_ACCESS_TOKEN being successfully populated from Fusion.

Unless these are successfully populated, our application pages will be inaccessible.

Authorization

The process above gets us to the point where:

• The APEX application will re-use the JWT and Username from the Fusion session, regardless of how that was achieved (username/password, SSO etc).

• Application access will be denied unless you have a current Fusion session.

• We can link the application to the Springboard menu or as a Page Integration.

• Our application can be opened as a standalone application in a new browser tab, or embedded as an additional region within an existing Fusion page.

What we don't have at this point is any means to say whether the user is authorized to access the application (or data within the application). There are a couple of approaches we can use to control authorization:

• Synchronize roles (or data access) down to APEX from Fusion, and compare our current SAAS_USER to the list to control access.

• Create a custom table in APEX and manage authorization directly in APEX.

Access to the Fusion Springboard menu item (or a Page Integration page) can be controlled by testing for a custom role assignment using Expression Language.

• #{securityContext.userInRole['MY_CUSTOM_ROLE']}

It's important to note that this last step is not enough on its own to control authorization; it only hides and shows the Springboard item.

Alternatives

This is not the only way to extend Fusion with APEX; alternative approaches exist where you can authenticate the APEX application using the same IDCS instance (or identity domain) used by Fusion.

You can also pass a JWT on the URL that Fusion uses to open APEX; while this works, it is inherently insecure as the JWT is visible in the URL and could be captured from the browser history.

Conclusion

APEX is excellent for extending ERP Cloud; we have used it extensively for integrations, custom Fusion pages and reporting portals.

The approach shown above will allow you to link your APEX applications from Fusion, hooking into the user's Fusion session for authentication.

The resulting JWT can be used as a Bearer token to authenticate Fusion web service calls; any pages based on Fusion REST sources will be subject to the same security and data access controls that are configured in Fusion.

PostScript

This is my #JoelKallmanDay contribution. Joel shared elements of the solution above with us in 2019, and we are grateful to Joel and the rest of the APEX development team for all their support and the toolset that built the applications that we use every day!