Introduction

Our goal for our APEX application is to create a single platform accessible to multiple businesses without any awareness of each other. Instead of creating individual cloned applications for specific customers, we aim to avoid these maintenance complexities.

Additionally, we require different branded log in screens, enabling each business to access their respective data. User roles will be consistently maintained across the system.

To meet these requirements, we plan to set up Users and Roles within the Identity Provider. Utilizing an API, all User management and Role assignments can be easily handled within APEX, eliminating the need for administrators to log in to the Identity Provider separately.

Advantages of Using an Identity Provider:

Centralized authentication and authorization management.
Seamless user experience with Single Sign-On (SSO) capability.
Enhanced security via multi-factor authentication options.
Streamlined user account provisioning and access control.

Auth0 provides a feature where organizations can be managed within the tenant. The diagram below shows an example flow. However, in our solution, we are going to bypass this first sign-in window and have APEX handle that side.

B2B Organizations Business Case diagram

The following steps describe how to set this up.

Pre-Requisites

Run the following in SQL Workshop

  select APEX_AUTHENTICATION.GET_CALLBACK_URL from dual;

From the results, just copy everything up to the callback phrase, e.g

 https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/apex_authentication.callback

Paste this into Notepad as the Callback URL

Obtain your APEX Home URL.. and paste it to Notepad as Home URL e.g.

 [BASE URL]/ords/r/[WORKSPACE]/[APP]/home?request=APEX_AUTHENTICATION%3DORGS

e.g

 https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home?request=APEX_AUTHENTICATION%3DORGS

Now we need to Encode the Home URL for use later, so go here https://www.urlencoder.org/ and paste in the Home URL and replace the section of the full URL below
```
 home?request=APEX_AUTHENTICATION%3DORGS
```
with
```
 home?request=APEX_AUTHENTICATION%3DORGS&x01=
```
click ENCODE and copy the encoded URL to Notepad as Encoded Login URL
Append the below phrase to the Encoded Home URL in Notepad
```
 &G_ORG_ID.
```
so that the end of the string looks like
```
 APEX_AUTHENTICATION%253DORGS%26x01%3D&G_ORG_ID.
```

Auth0 Configuration

Sign up for Auth0 - see the plan details for more information. A trial account can access the Organization features, however, you'll need to upgrade your Auth0 account to use it in production.
Once in the Dashboard, click on the top left and copy your AUTH0 Domain to Notepad as AUTH0 Domain. Mine is dev-ipga6 (obfuscated of course).

Take this Domain (e.g dev-ipga6) & Country (e.g. uk) and create a discovery URL and save it in Notepad as the Discovery URL i.e.

 https://YOUR_AUTH0_DOMAIN.COUNTRY.auth0.com/.well-known/openid-configuration

like this for example

 https://dev-ipga6.uk.auth0.com/.well-known/openid-configuration

Once started, just follow the Create Application Wizard
Choose Name = APEX Organizations and Regular Web Applications and click Create
When the Quickstart tab shows, immediately change to the Organizations tab
Click the Disable Grants Now button and Confirm
Wait 30 Seconds before continuing
Ensure that Business Users & No Prompt are selected. You can choose others, however, this blog is designed for this selection only.
Click Save Changes
Wait 30 Seconds before continuing
Click Organizations in the side menu
Click Create Organization
Enter a lowercase Organization Name & Description
Click Add Organization
At the top, the Organization ID is displayed. Copy this to Notepad as Organization ID
Click Save Changes
Whilst still in Organizations, click Members and Add Member(s) from your users to this Organization. Tip: Use email addresses
Click on Connections and click Enable Connections
Select Username-Password-Authentication
Click Enable Connection
Keep Disable Auto-Membership selected
Repeat the above steps for a second organization. For example here is another one I created
Go back to Applications > Applications > APEX Organizations > Connections
Click Enable Connections For Organizations
Change to the Settings tab
Copy the Client ID and Client Secret to Notepad

Copy the Home URL from Notepad (Not the encoded one) to the Application Login URI e.g

https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home?request=APEX_AUTHENTICATION%3DORGS

Copy the Callback URL from Notepad to the Allowed Callback URLs e.g.

https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/apex_authentication.callback

Allowed Logout URLs paste in the home URL without any parameters i.e it ends with /home e.g

https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home

Click Save Changes
Click on Applications > APIs and copy the API Audience (next to Auth0 Management API) to the clipboard and then paste into Notepad as the API Audience.

Note: The API Audience appears in my PL/SQL code below, however, the code assumes how it's named. If you see any audience issues, please adjust the PL/SQL code accordingly
Click on Auth0 Management API
Click on Machine To Machine Applications and Authorize the APEX Organizations application
On the Permissions (Under APEX Organizations application, just might have to click the Up Chevron), select the following three permissions and click Update
READ:USERS

READ:ROLES

READ:ROLE_MEMBERS

These permissions allow us to use the Management API to find the Roles our user is assigned to
Click on User Management > Roles > Create Role
Create three test roles (for demonstration, you can remove them later)
MANAGERS
SUPERVISORS
SUPERUSERS
Click on User Management > Users
Click the three dots/ellipses next to your Username and change your password (IDK why. I had to do this before the first login)
Click on your Username > Roles
Assign all roles you just created to your username

APEX Configuration

In APEX click App Builder > Workspace Utilities > Web Credentials > Create

Use the following table:
Name: ORGS
Client ID or Username: Paste Client ID from Notepad
Client Secret or Password: Paste Client Secret from Notepad
Verify Client Secret or Password: Paste Client Secret from Notepad
Click Create
Click Shared Components > Authorization Schemes > Create > Next
Use the following details:
Click Create to create a new Authorization Scheme and click Next
Repeat the above steps to create Authorization Schemes for MANAGERS and SUPERUSERS (as appropriate)
Click Shared Components > Security Attributes > Source for Role or Group Schemes and set it to Custom Code
Click APEX Application > Shared Components > Application Items > Create
In Name type G_SUB and click Create Application Item
Click APEX Application > Shared Components > Application Items > Create
In Name type G_ORG_ID and click Create Application Item
Click APEX Application > Shared Components > Authentication Schemes > Create
Click Next on Based on a pre-configured scheme from the gallery
Use the following table
Name: ORGS
Scheme Type: Social Sign-In
Credential Store: ORGS
Discovery URL: Paste Discovery URL from Notepad
Scope: email,profile
Authentication URI Parameters: organization=&APP_AJAX_X01.
Username: email
Additional User Attributes: sub,user_id,name
Map Additional User Attributes To: G_SUB
Click Create Authentication Scheme
Click on ORGS Authentication Scheme
Set Switch in Session to Enabled
In the Post-Logout URL, set Go To to URL

Set URL to this:

https://YOUR_AUTH0_DOMAIN.COUNTRY.auth0.com/v2/logout?returnTo=[ENCODED HOME URL]&client_id=[YOUR CLIENT ID]

e.g.

https://dev-ipga6.uk.auth0.com/v2/logout?returnTo=https%3A%2F%2Fg4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com%2Fords%2Fr%2Fwksp_x%2Fz-products%2Fhome%3Frequest%3DAPEX_AUTHENTICATION%253DORGS%26x01%3D&G_ORG_ID.&client_id=ZoElK

This should ensure that you can log out of APEX & AUTH0 and then return to the APEX Application's AUTH0 login page under the same Organization.

In the Source > PL/SQL Code > paste the code below replacing the three constant default values with your credentials

PROCEDURE p_post_processing
IS
    l_token_clob     CLOB;
    l_clob          CLOB;
    j apex_json.t_values;
    l_domain CONSTANT VARCHAR2(32767) DEFAULT 'https://dev-ipga6.uk.auth0.com'; 
    l_client CONSTANT VARCHAR2(32767) DEFAULT 'nj7ERT';
    l_secret CONSTANT VARCHAR2(32767) DEFAULT 'd1VjYS';
    l_token  CLOB DEFAULT empty_clob;
    l_sub    VARCHAR2(32767) DEFAULT apex_util.url_encode(:G_SUB);
    l_group_names apex_t_varchar2;
BEGIN

    :G_ORG_ID := apex_json.get_varchar2('org_id');

    apex_web_service.set_request_headers(
        p_name_01        => 'Content-Type',
        p_value_01       => 'application/json' );

    l_token_clob := apex_web_service.make_rest_request(
        p_url => l_domain || '/oauth/token',
        p_http_method => 'POST',
        p_body => apex_string.format(
                  '{"client_id":"%0",
                    "client_secret":"%1",
                    "audience":"%2/api/v2/",
                    "grant_type":"client_credentials"}',
                    l_client,
                    l_secret,
                    l_domain ));

    apex_json.parse(j, l_token_clob); 
    l_token := apex_json.get_clob(p_path=>'access_token',p_values=>j);

    apex_web_service.clear_request_headers;

    apex_web_service.set_request_headers(
        p_name_01        => 'Accept',
        p_value_01       => 'application/json',
        p_name_02        => 'Authorization',
        p_value_02       => 'Bearer ' || l_token );

    l_clob := apex_web_service.make_rest_request(
        p_url => l_domain || apex_string.format('/api/v2/users/%0/roles',l_sub),
        p_http_method => 'GET' );

    apex_json.parse(p_values => j, p_source => l_clob);

   FOR i IN 1 .. apex_json.get_count(p_path=> '.', p_values=> j) 
   LOOP
        -- add all group names to l_group_names
         apex_string.push (
                  p_table => l_group_names,
                  p_value => apex_json.get_varchar2(p_path=>'[%d].name',p0=> i,p_values=>j) );   

   END LOOP;

    -- save group names in session
    apex_authorization.enable_dynamic_groups (
        p_group_names => l_group_names );

END p_post_processing;

On Page 1, add an Interactive Report region using the following SQL statement
```
select *
  from APEX_WORKSPACE_SESSION_GROUPS
 where apex_session_id = :APP_SESSION
```
This will show you the AUTH0 Roles that have been translated into Dynamic Groups
Optional: Add a test, you can add the MANAGERS, SUPERVISORS or SUPERUSERS APEX Authorization Schemes to APEX Components, to test that the Authorization is working correctly.

Testing

Prepare your application URL like this

 [BASE URL]/ords/r/[WORKSPACE]/[APP]/home?x01=[ORGANIZATION ID]&request=APEX_AUTHENTICATION%3DORGS

e.g

 https://g4cf7-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home?x01=org_Wahz&request=APEX_AUTHENTICATION%3DORGS

Paste your URL into a browser tab & you'll see a Login Screen. Enter your AUTH0 User Credentials and click continue
Once in your APEX application, you should see the Auth0 Roles, mapped to APEX Authorizations & correctly observed by APEX. You should also see three entries in your Interactive Report
Sign out of APEX and you will see the Auth0 Login screen again, all set to log back into your APEX application.
Next, try signing in with a different organization
Auth0 will also provide you with the org_id when you login

In the code above, we grab the org_id and store it in G_ORG_ID.
This is significant should you wish to implement a VPD (as explained in my other SaaS blog).
G_ORG_ID is also used within the signout URL, so on logout, it diverts the user back to their organization sign-in page.
Next steps? You can distribute the URLs to your users or use a reverse proxy like Caddy to prettify your URLs. See more on this in my Caddy Blog.
ENJOY!

What's the cover image in this blog? It's Saltburn Pier. Visit Yorkshire!

Thanks to Jon Dixon for pointing out the X01 method 👍

APEX SaaS with Auth0 Organizations

Pre-Requisites

Auth0 Configuration

APEX Configuration

Testing