Blog: How to extend Oracle Sales Cloud using APEX and JWT

Due to the popularity of our previous blog 'Get the most out of Oracle APEX logs' published in January 2018 we decided to continue the Oracle APEX series. Rene Mikami is back with more amazing Oracle APEX  suggestions and tips! 

In this blog I want to explore one of the 'PaaS for SaaS' use cases. I will explain how to extend the Oracle Sales Cloud (OSC) service with Oracle APEX to achieve their seamless coexistence. Oracle Application Express (APEX) is a low-code development platform to design, develop and deploy database-driven applications, either on-premise or in the cloud.

Along with other services within the Oracle Software-as-a-Service (SaaS) offering, OCS can be extended by invoking third-party applications. Providing a secure JWT token OSC allows the other applications to access and interact with its objects. JWT token can be passed as a URL parameter.

At Infomentum, one of our customers uses Oracle APEX to extend OSC using a native APEX function Apex_web_service.make_rest_request_b to perform REST calls to update objects. One of the requirements is to use the JWT token provided as Authentication header. 

Here is an example of how to set the headers before executing make_rest_request: 

Requests executed by OSC with a valid JWT token can be trusted and, therefore, used to customise user authentications. This meant that users are no longer required to pass through the login page every time they access OSC. Even better, the users are able to take advantage of some of the unique features accessible only to the authenticated user, for example they have the ability to customise their Interactive Grid. This functionality requires preliminary validation of the passed JWT token in order to confirm its format and whether it contains the OSC username (this will be the user who will authenticate in APEX). 

The customised authentication was configured with the following steps: 

• In your Oracle APEX application, go to Shared Component > Authentication Schemes > Based on a pre-configured scheme from the gallery. Set a name for the scheme and select the Scheme type as custom;
• The only configuration required is to add the 'Sentry Function Name'. This will be our function  called any time the user requests this application's page, and the 'Authentication Function Name', which is our default authentication function.

Sentry Function Name: This will check if there is a valid session (which we will create if it doesn’t exist and there is a valid JWT token provided) for every request. It is important to note that if there is no JWT provided, the user will be redirected to the default login page set in Session Not Valid > Go to configuration.

Authentication Function Name: This function performs the authentication when the user is on the login page (in case there is no JWT token provided as URL parameter). It should contain two parameters (p_username and p_password) and should return a boolean. 

Let's explore the actual source code of these functions:

The complete code of all functions and the utility function used to extract the username from the JWT token can be viewed here.

I also suggest wrapping those functions into a PL/SQL package. Another suggestion would be to validate the expiry date in the JWT token in the Sentry function in order to increase its reliability when executing requests to OSC. 

For testing purposes, you can use a URL formatted as following (note that the URL parameters are sent in APEX format): 

https://<host>/apex/f?p=<APP>:<PAGE>:::::JWT,:<JWT_TOKEN>

We hope you found this blog post useful. Let us know if you have any comments or questions, and if you would like me to continue the Oracle APEX series of posts.