APEX and Auth0: A Seamless Single Sign-On Integration

Introduction

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.

Setting Up Auth0 in Oracle APEX

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.

What You'll Need from Oracle APEX

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

Configuring Auth0

With the necessary APEX URLs, we can now set up Auth0 to handle authentication for your Oracle APEX application.

Accessing the Auth0 Dashboard

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

• Log in to the Auth0 dashboard and go to the Applications section.
• Click Create Application and choose a name for the app that will handle authentication for Oracle APEX.
• Select Regular Web Applications as the application type.

2. Configure the Application Settings

• In the application's settings, add the Callback URL that you retrieved from Oracle APEX.
• Set the Logout URL to your APEX App Home URL, which will redirect users back to the app after they log out.

3. Copy Client ID and Client Secret

• In the Credentials tab of the newly created application, copy the Client ID and Client Secret. These will be used later when configuring Auth0 on APEX.

4. Create the Second Application for API Access

• Go back to the Applications section and click Create Application again.
• Name this second application and select Machine to Machine Applications as the type.
• When prompted to select an API, choose Auth0 Management API (or your custom API if you've set one up).
• Under Permissions, select:
  • read:users
  • read:roles
  • read:role_members

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

• As with the first app, go to the Credentials tab of this second application and copy down the Client ID and Client Secret. You'll use these credentials to securely retrieve user roles from Auth0.

Creating an Organisation in Auth0

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

1. In the Auth0 dashboard, navigate to the Organisations tab in the menu on the left-hand side.
2. Click on Create Organisation.
3. Enter a Readable ID (used internally and for URLs) and a Display Name (visible to users).
   • The Readable ID should be a URL-friendly identifier, such as your-org-name.
   • The Display Name will be seen by users on the login page, so make sure it's clear and recognisable.

Step 2: Configure Connections

1. After creating the organisation, click on the Connections tab.
2. Select Username-Password-Authentication (or other appropriate connection types) and link it to the organisation.
   • This ensures that users logging in via this organisation will authenticate using their username and password.

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.

Next Steps: Configuring Oracle APEX to Use Auth0

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

1. Navigate to Workspace Utilities within your Oracle APEX instance.
2. Select Web Credentials, then click Create.
3. Give it a meaningful name, such as Auth0, and assign it a static ID, like Auth0.
4. For Authentication Type, select Basic Authentication.
5. Paste the Client ID and Client Secret from your main Auth0 application and click Save.

Step 2: Configure APEX to Use Auth0 for Authentication

1. Go to the APEX application you want to configure.
2. Navigate to Shared Components and select Authentication Schemes.
3. Create a new authentication scheme by pressing Create.
4. Select Based on a pre-configured scheme from the gallery, give it a name (e.g., Auth0), and choose Social Sign-In as the scheme type.

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.

1. Go back to the Shared Components for your app.
2. Under Application Items, click Create and define the following item:
   • G_SUB
3. Set Session State Protection for both items to Restricted - May not be set from browser.

Retrieving and Managing User Roles in Oracle APEX

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:

1. GET_ACCESS_TOKEN: This function sends a request to Auth0 to retrieve an access token. The token is needed to authenticate API calls to Auth0.
   • It builds a JSON request containing the client ID and secret, then sends this request using the APEX_WEB_SERVICE package.
   • The response contains the access token, which is returned for use in subsequent API calls.
2. MAP_USERS_ROLES: This procedure uses the access token to fetch the roles assigned to a specific user from Auth0.
   • It sends a GET request to the Auth0 Management API using the user's subject (sub) and retrieves the roles.
   • The roles are then added to an internal list (APEX_T_VARCHAR2) and mapped to authorisation schemes within APEX using APEX_AUTHORIZATION.ENABLE_DYNAMIC_GROUPS.
3. AUTH_SCHEME_POST_PROCESSING: This procedure is automatically called after a user logs in. It retrieves the user's subject ID (from G_SUB) and maps the user to their Auth0 roles by calling MAP_USERS_ROLES.

Mapping Auth0 Roles to Authorisation Schemes in Oracle APEX

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

1. In the Auth0 dashboard, navigate to User Management and then Roles.
2. Click on Create Role.
3. Provide a name for the role (e.g., Admin, Editor), and optionally add a description to clarify its purpose.
4. Save the role.

Step 2: Create Authorisation Schemes in Oracle APEX

Next, we'll create matching authorisation schemes in Oracle APEX to map these roles from Auth0.

1. In Oracle APEX, go to your application's Shared Components.
2. Select Authorisation Schemes.
3. Click Create, and choose From Scratch.

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.

Using Auth0 for Other Web Applications (ASP.NET Core Example)

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.

Creating Additional Applications in Auth0

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:

1. Create a new Application in Auth0 for your ASP.NET Core app, similar to how we set up the Oracle APEX application.
2. Plug the Client ID, Client Secret, and Domain from Auth0 into your ASP.NET Core app's appsettings.json.
3. Use the Auth0 SDK for ASP.NET Core to handle authentication, ensuring your users can log in with the same credentials as they do for Oracle APEX.

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.

Conclusion

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.