Authenticate Oracle APEX Workspaces with Google

Backstory

Whilst presenting at KSCOPE'25, I was performing a live demonstration using APEX Authentication for both the APEX Workspace and my APEX application; AND I was using the ADMIN password for both of these; AND it had a public vanity URL; AND I was demonstrating the new APEX 24.2 hide/show password functionality; AND I had a login issue. As the audience and myself were trying to resolve it, I was heckled with a 'Show your password!’ shout from the audience. I laughed along during that moment, however back home, I thought OMG, if my ADMIN password (~~ILuvOraclAP3XsoMuch~~) had been revealed, on a public facing app, then … gulp… someone could have got in and totally seen all my EMP and DEPT demonstration data. Oh the tragedy!!

So I thought, well I’ll just use Google Authentication on my instance then… I searched up my blog to find an article I had already written about this… I found that I’d hallucinated writing about this… so never mind, here is the first blog I’ve ever written about this subject - I hope.

Once I set it up - I’m impressed how quickly it is to navigate between workspaces. You should try it.

Steps

Sign in to Google Cloud Auth Platform here and create a Client
Use these settings (changing the Name of the App and the URIs)

https://example.com/ords/apex_authentication.callback

Note: the URL #2 is not required unless you using the ‘Access Denied’ feature below
In the next page take a copy of your Client ID and Client Secret
Now sign in to your APEX Administration Service Instance
Head over to Manage Instance > Security
Click on the Social Sign In Edit Link
Enter your Client ID & Client Secret (twice). Change the Provider to Google & change the Scope as per the picture below
Click Apply Changes
Go back in using the Social Sign In Edit Link
Click Make Current Scheme
Go to Manage Workspace > Manage Developers and Users
Click Create User and associate their google email address with the INTERNAL workspace
Click Create User
Repeat the above two steps of any other workspace assignments
⚠️ Do not log out of this browser tab ⚠️
In another browser/incognito mode, access APEX Workspace Sign in
Success - Easy was’’t it?
⚠️ Make sure you can log back into INTERNAL on this tab before closing the other browser tab ⚠️

Other Google Users

What if a user tries to access APEX workspace who is not welcome here. Basically this user has nothing to do with APEX, my project or anything low-code, but has suddenly signed in for no reason.

Q. Is it secure?

A. Yes its secure. But I don’t want this to happen in the first place. Basically Jessie can GTFO of my environment.

Q. Is it even possible to stop randos logging in to my workspace

A. Yes. first we can show them this screen

Then when they click the blue button they can choose another user…

… and now try to log in again with Marcello 🎉🎉🎉

Q. Wheres can I find the code for this

A. Instructions below…

Steps

Go to over to Manage Instance > Security > Social Sign (Edit Link)

Add this to the PL/SQL Code window - changing the two constant variables

 procedure post_auth is
   v_allowed_user        CONSTANT VARCHAR2(4000) DEFAULT UPPER('MARCELLO.BIELSA@GMAIL.COM');
   v_google_chooser_url  VARCHAR2(4000) DEFAULT NULL;
   v_google_client_id    CONSTANT VARCHAR2(4000) DEFAULT
     'put-your-google-client-id-here.apps.googleusercontent.com';
 begin
   -- Check if the logged-in user is not the authorized one.
   if :APP_USER != v_allowed_user
   then 

     -- IMPORTANT: First, delete the invalid session that APEX just created
     -- for the unauthorized user. This prevents orphaned sessions.
     apex_session.delete_session(p_session_id => :APP_SESSION);

     -- The APEX URL must be encoded to be safely used as a parameter in another URL.
     -- v_login_url_encoded := apex_util.url_encode(p_url => apex_util.host_url );

     -- Construct the final Google Account Chooser URL, telling it where to go next.
     v_google_chooser_url := apex_string.format(
         'https://accounts.google.com/o/oauth2/v2/auth?client_id=%0&redirect_uri=%1&response_type=code&scope=%2&prompt=select_account',
         v_google_client_id,
         apex_util.host_url,
         utl_url.escape( 'openid email profile')
         );

     -- Directly print the minimal HTML page, injecting the dynamic URL into the button link.
     -- We use q'[]' syntax to avoid issues with single quotes in the HTML.
     htp.p(
       q'[<!DOCTYPE html>
 <html lang="en">
 <head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1.0">
   <title>Access Denied</title>
   <style>
     body { margin: 0; display: flex; justify-content: center; align-items: center; min-height: 100vh; font-family: sans-serif; background-color: #f0f2f5; color: #333; }
     .container { text-align: center; padding: 30px; background-color: #fff; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); max-width: 400px; width: 90%; }
     h1 { font-size: 24px; margin-bottom: 10px; color: #d9534f; }
     p { font-size: 16px; line-height: 1.5; margin-bottom: 20px; }
     .button { display: inline-block; padding: 10px 20px; background-color: #007bff; color: #fff; text-decoration: none; border-radius: 5px; transition: background-color 0.3s ease; }
     .button:hover { background-color: #0056b3; }
   </style>
 </head>
 <body>
   <div class="container">
     <h1>Access Denied</h1>
     <p>Sorry, this page isn't available for your user account.</p>
     <a href="]' || v_google_chooser_url || q'[" class="button">Choose a Different Account</a>
   </div>
 </body>
 </html>]'
     );

     -- Stop the APEX engine to prevent it from rendering anything further.
     APEX_APPLICATION.STOP_APEX_ENGINE;
   end if;
 end post_auth;

Add this to the Post-Authentication Procedure Name
```
 post_auth
```
Make sure it looks like this
Click Apply Changes
In another window, check it out with a google account you don’t want to have access to the application.
⚠️ Make sure you can log back into INTERNAL before closing the other browser tab ⚠️

ENJOY!

What the picture? Sitting on a deckchair enjoy the sun outside The Empress, Harrogate.

Google Development Environment Authentication Schemes for Oracle APEX

Backstory

Steps

Other Google Users

Steps

Subscribe to my newsletter

Matt Mulvaney

Matt Mulvaney