Cost-Efficient APEX Open ID Authentication for SaaS: Role-Based Emulation of Auth0 Organizations
If you see my work on APEX Authentication with AUTH0 Organizations for SaaS, you'll see that the feature is pretty awesome. It provides multiple login screens, guided flows, user assignments to defined organizations and a single configuration of web applications. I think it's great and it's something to consider if the license is within your budget.
However what if the license is not within your budget? Well AUTH0 provides a Free licence for 7000 users and 1000 tokens. If we could emulate AUTH0 organizations somehow, then perhaps the Free license is suitable for us.
Something that's been floating around my brain for a few weeks is the possibility of using a specific naming convention of AUTH0 Roles to emulate Organizations. I thought I'd put it to the test and the results are rather impressive.
So what you'll get from following this blog are
APEX Authentication with AUTH0
AUTH0 Roles mapped to native APEX Roles
Seamless Logout/Login featuring the user's organization logo on an AUTH0 Login Page
The organization logo is provided to AUTH0 via a REST Module and the ID (or secret) of that organization is provided via the URL.
Identification of the users' organization in APEX using an application Item
Potential to use the Application Item in using a VPD to control data
An APEX page to view all users in the current Organisation.
Note: I'm British, so I want to write Organisation, but sometimes it'll spellcheck to Organization 😀
Let's start...
Prerequisites
- First, complete the AUTH0 integration as per this blog. This will get your APEX application authenticated with AUTH0 and ready to start this blog.
AUTH0 Configuration
In AUTH0 Dashboard, click on Branding > Universal Login > Advanced Options
Click on Classic, to allow Extensive custom code support and click Save Changes (confirming any warning)
Change the tab to Login and enable the Customize Login Page feature
In the HTML section, change the theme section at line 59 to this
theme: { logo: config.extraParams.logo, primaryColor: colors.primary ? colors.primary : 'green' },Click on User Management > Roles and we are going to create the Roles we need to identify users within an Organisation.
Click on Create Role and create an ORG:WATER role and once it's created, go ahead and create another role called ORG:BETTYS. These two Roles are going to represent our two Organisations.
Click on User Management > Users > find your username > Roles > Assign Roles > ORG:WATER > Assign
We are going to deliberately not assign ORG:BETTYS as we will test this later
DB Setup for Organisations
Create a table to hold our Organisations
create table organisation ( id number generated by default on null as identity constraint organisation_id_pk primary key, name varchar2(255 char), description varchar2(4000 char), secret_id varchar2(64 char), logo blob, logo_filename varchar2(512 char), logo_mimetype varchar2(512 char), logo_charset varchar2(512 char), logo_lastupd date ) ;In APEX, Create a Form & Report and use it to enter these Organisations - see picture below
You must upload some images: you can use these images
In APEX, ensure that these images, above, are uploaded to the records. The APEX Page Wizard will provide a file browse item for you. Don't worry about the download link in the Interactive Report not working
APEX Application Items
In APEX, click on Shared Components > Application Items > Create and create an application Item called G_ORG_ROLE_ID
Create another application Item called G_ORG_NAME
Create another application Item called G_ORG_SECRET
Create another application Item called G_ORG_ID
Check you have the following Application Items
REST Module
We are going to create a REST Module for AUTH0 to fetch our Organisation logo for our Authh0 Login Page. We are going to configure it as per the screenshot below
Click on SQL Workshop > RESTful Services
Register your schema for ORDS if not already
Click Modules > Create Module
For Module Name enter organisation
For Base Path enter
/org/Click Create Module
Click Create Template
In URI Template enter
getLogo/:secret_idand then click Create Template
Click Create Handler
In Source Type, select Media Resource
in Source type
SELECT logo_mimetype, logo FROM organisation WHERE secret_id = :secret_idand click Create Handler
Copy the Full URL to the clipboard
APEX Authentication Configuration
Edit your APEX AUTH0 Authentication Scheme
Change Authentication URI Parameters to be
logo=[PASTE Full URL FROM CLIPBOARD)and then change the :secret to &APP_AJAX_X01. so that the full string looks like this
logo=https://g4cf-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/wksp_x/org/getLogo/&APP_AJAX_X01.In the Authentication PL/SQL code, replace all code with the version below.
Remember to change the constant values
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 'nj7g'; l_secret CONSTANT VARCHAR2(32767) DEFAULT 'd1Vj'; l_script CONSTANT VARCHAR2(32767) DEFAULT '/ords/r/'; l_token CLOB DEFAULT empty_clob; l_sub VARCHAR2(32767) DEFAULT apex_util.url_encode(:G_SUB); l_group_names apex_t_varchar2; l_workspace_path_prefix apex_workspaces.path_prefix%TYPE DEFAULT NULL; BEGIN -- Get Path prefix manually because OWA_UTIL.GET_CGI_ENV('SCRIPT_NAME') doesnt work here FOR x IN ( SELECT lower( w.path_prefix ) path_prefix FROM apex_workspaces w, apex_applications a WHERE w.workspace = a.workspace AND a.application_id = :APP_ID ) LOOP l_workspace_path_prefix := x.path_prefix; END LOOP; 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) ); IF apex_json.get_varchar2(p_path=>'[%d].name',p0=> i,p_values=>j) LIKE 'ORG:%' THEN :G_ORG_ROLE_ID := apex_json.get_varchar2(p_path=>'[%d].id',p0=> i,p_values=>j); :G_ORG_SECRET := apex_string.split( apex_json.get_varchar2(p_path=>'[%d].name',p0=> i,p_values=>j),':')(2); FOR X IN ( SELECT * FROM ORGANISATION WHERE secret_id = :G_ORG_SECRET ) LOOP :G_ORG_ID := x.id; :G_ORG_NAME := x.name; END LOOP; END IF; END LOOP; -- save group names in session apex_authorization.enable_dynamic_groups ( p_group_names => l_group_names ); IF :G_ORG_NAME IS NULL THEN apex_util.set_custom_auth_status( p_status=>'User:'|| :USER ||' is not associated with an Organisation Role.'); apex_util.redirect_url ( p_url => l_domain || '/v2/logout?returnTo=' || APEX_UTIL.URL_ENCODE( OWA_UTIL.get_cgi_env ('REQUEST_PROTOCOL') || '://' || OWA_UTIL.get_cgi_env ('HTTP_HOST') || -- Start workaround for OWA_UTIL.GET_CGI_ENV('SCRIPT_NAME') not working l_script || l_workspace_path_prefix || '/' || LOWER(:APP_ALIAS) || -- End Workaround '/home' ) || '%3Frequest%3DAPEX_AUTHENTICATION%253DAUTH0%26x01%3D&G_ORG_SECRET.&client_id=' || l_client ); END IF; END p_post_processing;Next is a tricky bit, in the Logout URL, you should see part of the string in the middle that says...
AUTH0&client_idyou have to paste this string...
%26x01%3D&G_ORG_SECRET....below between the AUTH0 and the &client_id
So that it looks like
AUTH0%26x01%3D&G_ORG_SECRET.&client_idClick Apply Changes
Display AUTH0 Organisation users within APEX
Now we are going to view all the users in the Organisation the user has signed in with.
TODO - Replace this solution with REST Modules
Create a new Interactive Report Page called Org Users based on the following SQLSELECT c001 user_id, c002 email, c003 picture, c004 name FROM apex_collections WHERE collection_name = 'ORG_USERS'When the new Org Users page opens in Page Designer, edit the PICTURE column on the Org Users Region and set the Column Formatting > HTML Expression to be
<img src="#PICTURE#" height="128px">Create a Before Header Process called Fetch Org Users with the following code.
Remember to change the constant values
DECLARE
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 'ndfdfj';
l_secret CONSTANT VARCHAR2(32767) DEFAULT 'd1Vsmo';
l_token CLOB DEFAULT empty_clob;
l_collection_name apex_collections.collection_name%TYPE DEFAULT 'ORG_USERS';
BEGIN
apex_collection.create_or_truncate_collection(l_collection_name);
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/roles/%0/users', :G_ORG_ROLE_ID ),
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
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => l_collection_name,
p_c001 => apex_json.get_varchar2(p_path=>'[%d].user_id',p0=> i,p_values=>j),
p_c002 => apex_json.get_varchar2(p_path=>'[%d].email',p0=> i,p_values=>j),
p_c003 => apex_json.get_varchar2(p_path=>'[%d].picture',p0=> i,p_values=>j),
p_c004 => apex_json.get_varchar2(p_path=>'[%d].name',p0=> i,p_values=>j) );
END LOOP;
END;
Testing
- Now we can test a custom URL that we can give our customers. We do this by adding our Organisation's secret to the end of the URL.
The secret is intended to be a random HEX string, however, in this blog, it's plain text and represents the Organisation name.
i.e. Instead of BETTYS, I'd chosen something like ea26d14f5aeda006ca9fa6ba4e2cd14e so that URLs cannot be easily guessed
## Water
https://g4cf-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home?x01=WATER
## Bettys
https://g4cf-lufatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/wksp_x/z-products/home?x01=BETTYS
Here are examples of the login pages
Visit the Org Users page and view the users within this Organisation
Log out of APEX, you'll see the AUTH0 login page with the correct logo for your user.
Either (1) Create a user without an ORG: role or (2) Remove the ORG: role from your username
Sign back in and you will be immediately logged out again as your user does not have permission for this application
If you followed option (2) above, reapply the role to the user.
Retrospect
I think this solution is rather good. However, there are some things I noted during this journey
Since the users are together in AUTH0, only separated by role, it's possible to log in using a Login Page for an organization that the user is not associated with. This is no big deal since it's just visual - once logged in, APEX will associate the user with their correct Organisation and the correct logo will appear on logout ✔️
I would encourage the use of REST Table Sync to synchronize the Data with a table to reduce the number of tokens issued.
The AUTH0 API can be used to create & user users within a role/organization as well as view them. This has not been included in this blog however I intend to publish an AUTH0 package in the future with token optimisation.
Since the Organisation is resolved to the ID on my Organisation table (see G_ORG_ID Application Item) I can implement a VPD to only show data for that Organisation. In this way, I can implement a SaaS solution.
I hope you enjoyed this blog and found it useful.
What's the picture? It's a field towards the end of summer, east of Ripon. Visit Yorkshire!