In today's interconnected world, providing a seamless and secure authentication experience is a must for modern web applications. Whether managing internal tools or client-facing platforms, users expect a streamlined, centralised login process that works across multiple applications. This is where Single Sign-On (SSO) comes into play. SSO enables a single login that works across different systems, enhancing both user experience and security.
One of the most effective ways I have found to implement SSO is through Auth0, a versatile identity and access management platform. Auth0 simplifies the often-complex authentication process by providing a secure, cloud-based solution that can be integrated with virtually any application. Whether it's social logins (Google, Facebook, etc.), enterprise connections (like LDAP or Microsoft Active Directory), or custom databases, Auth0 supports a variety of authentication methods, allowing developers to implement robust security without reinventing the wheel.
This blog post explores how to integrate Auth0 with Oracle APEX, a low-code application development platform, to manage authentication and user roles. We'll guide you through setting up Auth0 as an authentication scheme within Oracle APEX, dynamically retrieving user roles and mapping them to authorisation schemes to control access to various components of your application.
While the focus will be on Oracle APEX, we'll briefly touch on how Auth0's flexibility allows you to extend this authentication setup to other technologies, such as ASP.NET Core, showcasing how you can create a unified login experience across multiple applications using different tech stacks.
Let's dive into the power of Auth0 and see how it can transform your Oracle APEX application by simplifying authentication and delivering a unified, secure login experience.
In this section, we'll walk through the process of setting up Auth0 to handle authentication for your Oracle APEX application. We'll start by configuring the necessary details in Oracle APEX and then move over to the Auth0 dashboard to create and configure the required applications.
Before we dive into the Auth0 configuration, you'll need to gather a couple of key details from Oracle APEX:
1. Callback URL: This is the URL that Auth0 will use to redirect users back to your Oracle APEX application after successful authentication. You can retrieve this by running the following query in Oracle APEX:
SELECT APEX_AUTHENTICATION.GET_CALLBACK_URL FROM DUAL;
The result will look something like this for apex.oracle.com:
https://apex.oracle.com/pls/apex/apex_authentication.callback
For other environments, the base part of the URL may differ, but the structure remains the same. You can ignore any query string parameters after the callback portion of the URL.
2. APEX App Home URL: This is where users will be redirected after logging in, typically the home page of your Oracle APEX app. It's often set to page 1, or if you are using friendly URLs, it could be something like /home.
For example:
https://apex.oracle.com/pls /apex/r/ws_dsp/auth-demo/home
With the necessary APEX URLs, we can now set up Auth0 to handle authentication for your Oracle APEX application.
Head to Auth0's website, if you haven't signed up, to create a free account. Once registered, you can log in to the Auth0 dashboard, where you'll manage applications, users, roles, and more.
1. Create the Main Application in Auth0
2. Configure the Application Settings
3. Copy Client ID and Client Secret
4. Create the Second Application for API Access
This setup will allow the application to query the user roles and other relevant information.
5. Copy Client ID and Client Secret for API Access
To enhance the branding and management of your login page, we can create an Organisation within Auth0. Organisations allow customised branding, better user management, and other useful features. Here's how to create and configure an organisation in Auth0:
Step 1: Create an Organisation
Step 2: Configure Connections
Step 3: Note the Organisation ID
Once the organisation is created and configured, make a note of the Organisation ID. This ID will be required when we configure Oracle APEX to use Auth0. You can find the Organisation ID in the Settings tab of your organisation.
Now that we've set up our Auth0 applications, we can configure Oracle APEX to use Auth0 as its authentication provider. Follow the steps below to integrate Auth0 into your APEX application and map user attributes to application items.
Step 1: Add Auth0 Credentials to Oracle APEX
Step 2: Configure APEX to Use Auth0 for Authentication
Step 3: Configure Authentication Scheme Settings
Once inside the authentication scheme setup, configure the following settings. (Adjust these as needed for your environment.)
After entering these values, click Save.
Step 4: Create Application Items for User Attributes
We've used the variable G_SUB in the configuration above, which maps to the sub claim from Auth0. This claim identifies the user. We need to create an application item to store this data.
Now that we've configured Auth0 as the authentication provider for Oracle APEX, we need a way to retrieve user roles from Auth0. We will use a custom PL/SQL package to call Auth0's API, fetch the roles for the authenticated user, and handle them within APEX. Below is the package we'll create to manage user roles.
Step 1: Creating the PL/SQL Package
Create a PL/SQL package in Oracle APEX called AUTH0_UTILS_PKG. This package contains the functions and procedures that will handle retrieving user roles from Auth0.
Here's the full package body:
CREATE OR REPLACE PACKAGE BODY "AUTH0_UTILS_PKG" AS
----------------------------------------------------------------------------
-- CONSTANTS - AUTH0 DETAILS
----------------------------------------------------------------------------
CLIENT_ID CONSTANT VARCHAR2 (100) := '' ; -- Set your Auth0 client ID
CLIENT_SECRET CONSTANT VARCHAR2 (100) := '' ; -- Set your Auth0 client secret
DOMAIN CONSTANT VARCHAR2 (100) := '' ; -- Set your Auth0 domain (e.g., https://your-domain.uk.auth0.com)
----------------------------------------------------------------------------
-- FUNCTION GET_ACCESS_TOKEN
----------------------------------------------------------------------------
FUNCTION GET_ACCESS_TOKEN RETURN VARCHAR2 IS
v_ACCESS_TOKEN VARCHAR2 (1000) ;
v_REQUEST CLOB ;
v_RESPONSE CLOB ;
BEGIN
-- 1. CREATE JSON BODY FOR REQUEST
SELECT JSON_OBJECT (
KEY 'client_id' VALUE CLIENT_ID,
KEY 'client_secret' VALUE CLIENT_SECRET,
KEY 'audience' VALUE DOMAIN || '/api/v2/',
KEY 'grant_type' VALUE 'client_credentials'
) AS REQUEST_BODY
INTO v_REQUEST
FROM DUAL ;
-- 2. RESET HEADERS
APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE() ;
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type' ;
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'application/json' ;
-- 3. GET OUR ACCESS TOKEN FROM AUTH0
v_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST (
p_url => DOMAIN || '/oauth/token',
p_http_method => 'POST',
p_body => v_REQUEST
) ;
-- 4. EXTRACT TOKEN FROM REQUEST
SELECT ACCESS_TOKEN
INTO v_ACCESS_TOKEN
FROM JSON_TABLE ( v_RESPONSE, '$' COLUMNS (
ACCESS_TOKEN VARCHAR2 PATH '$.access_token',
SCOPE VARCHAR2 PATH '$.scope',
EXPIRES_IN NUMBER PATH '$.expires_in',
TOKEN_TYPE VARCHAR2 PATH '$.token_type'
) ) ;
RETURN v_ACCESS_TOKEN ;
END GET_ACCESS_TOKEN ;
----------------------------------------------------------------------------
-- PROCEDURE MAP_USERS_ROLES
----------------------------------------------------------------------------
PROCEDURE MAP_USERS_ROLES (
p_SUBJECT_USER IN VARCHAR2
) IS
v_RESPONSE CLOB ;
v_ROLES APEX_T_VARCHAR2 ;
v_TOKEN VARCHAR2 (1000) ;
BEGIN
-- 1. GET ACCESS TOKEN
v_TOKEN := GET_ACCESS_TOKEN ;
-- 2. RESET HEADERS & SET TOKEN AUTH
APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE() ;
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Authorization' ;
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'Bearer ' || v_TOKEN ;
-- 3. GET USER ROLES
v_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST (
p_url => DOMAIN || '/api/v2/users/' || p_SUBJECT_USER || '/roles',
p_http_method => 'GET'
) ;
-- 4. LOOP THROUGH ROLES
FOR r_ROLES IN (
SELECT *
FROM JSON_TABLE ( v_RESPONSE, '$[*]' COLUMNS (
ID VARCHAR2 PATH '$.id',
NAME VARCHAR2 PATH '$.name',
DESCRIPTION VARCHAR2 PATH '$.description'
) )
) LOOP
-- 5. FOR EACH ROLE, ADD IT TO v_ROLES
APEX_STRING.PUSH ( v_ROLES, r_ROLES.NAME ) ;
END LOOP ;
-- 6. MAP ROLES TO AUTHORIZATION SCHEME ROLES
APEX_AUTHORIZATION.ENABLE_DYNAMIC_GROUPS ( v_ROLES ) ;
END MAP_USERS_ROLES ;
----------------------------------------------------------------------------
-- PROCEDURE AUTH_SCHEME_POST_PROCESSING
----------------------------------------------------------------------------
PROCEDURE AUTH_SCHEME_POST_PROCESSING IS
v_SUBJECT VARCHAR2 (100) ;
BEGIN
-- 1. GET USER (SUBJECT) FROM APP ITEMS
v_SUBJECT := APEX_UTIL.URL_ENCODE ( v( 'G_SUB' ) ) ;
-- 2. MAP USER (SUBJECT) TO APEX ROLES
MAP_USERS_ROLES ( v_SUBJECT ) ;
END AUTH_SCHEME_POST_PROCESSING ;
END "AUTH0_UTILS_PKG";
Step 2: Understanding the Package Components
Now, let's break down the key components of this package:
Now that we've set up the PL/SQL package to retrieve roles from Auth0, the next step is to map those roles to Oracle APEX's authorisation schemes. This allows us to control access to different parts of the application based on the roles assigned to users in Auth0.
Step 1: Add Roles in Auth0
Step 2: Create Authorisation Schemes in Oracle APEX
Next, we'll create matching authorisation schemes in Oracle APEX to map these roles from Auth0.
Now, fill out the following details:
Repeat this process for any additional roles you want to map from Auth0 to APEX.
Step 3: Checking User Roles in Oracle APEX
Once you have the roles mapped in APEX, you can use them in your application to control access. For example, you can use the following PL/SQL code to check if the current user is a member of a specific role:
RETURN APEX_AUTHORIZATION.IS_MEMBER( 'Role_Name' );
This function will return TRUE if the user has the specified role and FALSE otherwise.
You can also use APEX's built-in Authorisation Scheme section to apply these checks to components like pages, regions, and buttons, ensuring that only users with the correct roles from Auth0 can access certain parts of your app.
One of the great advantages of using Auth0 is its flexibility to work across different web applications and technology stacks. Whether you're building applications in Oracle APEX, ASP.NET Core, or any other framework, Auth0 allows you to create multiple applications that can all share the same login details. This means your users can enjoy a unified login experience across all of your apps.
You can create multiple applications within Auth0 and configure them for different platforms or projects. Each application will have its own Client ID, Client Secret, and configurations, but your users will be able to log in with the same credentials across all of them.
For example, here's how you can easily configure an ASP.NET Core application to use Auth0:
Here's a screenshot of an ASP.NET Core app using Auth0 for authentication:
And here's the Oracle APEX app, also integrated with Auth0:
By using Auth0 across your applications, regardless of the language or framework, your users benefit from the convenience of a single login that works seamlessly everywhere. This streamlines the user experience and simplifies security management across your organisation.
In this blog post, we've explored how to integrate Auth0 with Oracle APEX, allowing you to manage authentication and user roles seamlessly. We walked through the setup process for creating applications in Auth0, configuring Oracle APEX to use Auth0 as the authentication provider, and mapping user roles from Auth0 to APEX authorisation schemes. By using a combination of Auth0's powerful identity management tools and Oracle APEX's flexible authorisation schemes, you can ensure secure, role-based access to your application.
We also touched on how Auth0's versatility extends beyond Oracle APEX, allowing you to use it with other web applications, such as ASP.NET Core, ensuring that your users enjoy the convenience of a single login across multiple apps and platforms.
Auth0's ability to centralise authentication across various technologies offers a significant advantage for both developers and users, streamlining security management and enhancing user experience. Whether you're working with low-code platforms like Oracle APEX or building full-stack apps in frameworks like ASP.NET Core, Auth0 provides a scalable solution for unified authentication and role management.
If you haven't already, I encourage you to explore Auth0's features and see how it can simplify the authentication process for your own applications.
For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.
Subscribe to Oracle APEX Insights if you want to stay tuned for more APEX updates.