MS365 Single Sign-On with Oracle APEX

What You Need:

• Azure AD Premium (for advanced SSO features)
• Oracle APEX 20.1+ (built-in SAML/OAuth support)
• Domain verification in Azure AD
• SSL certificates for secure communication
• Admin access to both Azure AD and APEX workspace

Approach

1. Clarify environment / Aclarar ambiente

• APEX version: Oracle APEX 24.2.10
• Where is ORDS running (Autonomous DB, on-prem, OCI, etc.)?
  • Autonomous DB
  • adb in APEX URL --> Autonomous DataBase
  • us-ashburn-1 --> OCI region
  • oraclecloudapps.com --> Oracle's cloud application domain
  • /ords/... --> built-in ORDS endpoint that comes with ADB
  • ORDS lives inside the ADB instance.
  • APEX + ORDS are both tied to the Autonomous DB instance.
  • For REST modules, wallets, ACLs, etc., we are working in the Autonomous DB + ORDS managed stack, not a custom ORDS deployment.

2. Choose protocol / Escoger protocolo

• OIDC (cleaner with modern Entra + web apps).
• In Azure, create normal Entra ID App Registration
• From APEX, calling the OAuth2 v2 / OIDC endpoints with scope openid
• In APEX, create an Authentication Scheme of type Social Sign-In / OpenID Connect and point it to those Azure endpoints.
  • App Builder --> Shared Components --> Authentication Schemes [Create]
  • Authentication Schemes [Create]
    • Scheme Type: Social Sign-In
    • ...

3. Work with Azure admin / Trabajar con el admin de Azure

• Get app registration set up in Entra ID (Azure AD).
• Configure callback URL (redirect URI).
• Obtain client ID, client secret, and tenant/issuer URL.

4. Configure APEX authentication scheme / Configurar el esquema de autenticación en APEX

• Set up Social Sign-In / OIDC in the APEX authentication scheme.
• Test the roundtrip: redirect to Microsoft, log in, return to APEX, and confirm APP_USER is set.

5. Add authorization logic / Añadir lógica de autorización

• Decide how you’ll store app-specific roles (tables, APEX authorization schemes, etc.).
• Map roles to Entra groups / claims or to a local table.

Step 1 – Create the App Registration in Azure

2.1 Basic info

• Name: APEX-SSO or APEX-YourAppName-M365.
• Supported account types (keep it simple for now):
  • Leave “Accounts in this organizational directory only (Single tenant)” selected
    (good for internal corporate app tied to this tenant).

2.2 Redirect URI (optional at this screen)

We’ll eventually use:

On Autonomous DB, that host will look like:

But APEX will tell you the exact value later, so you have two options:

• Option A (safe): leave Redirect URI blank for now, click Register, and add it after we create the APEX auth scheme.
• Option B (if you already know your ORDS URL): you can put it now.

For now, easiest: leave it empty → click Register.

Step 2 – Grab the important Azure values

After you click Register, you land on the app's Overview page.

Write down:

• Application (client) ID
• Directory (tenant) ID

Then:

1. Go to Certificates & secrets → Client secrets
2. Click New client secret
   • Description: APEX-SSO-secret
   • Expiry: 6/12/24 months – up to your security policy.
3. Copy the secret value now and save it somewhere safe (once you leave the page you can't see it again).

We'll use:

• Tenant ID
• Client ID
• Client Secret

in the APEX authentication scheme.

Step 3 – Configure APEX Authentication Scheme (OIDC / Social Sign-In)

In APEX 24.2.10:

1. Log into APEX Builder.
2. Open your application.
3. Go to Shared Components → Security → Authentication Schemes.
4. Click Create.
5. Choose:
   • Create Scheme → From scratch
   • Scheme Type: Social Sign-In (or OpenID Connect depending on exact wording)
6. Give it a name, e.g. OIDC - Microsoft Entra ID

Now fill the important fields (names may vary slightly, but conceptually):

4.1 OpenID Connect settings

• Authentication Provider or Provider Type: OpenID Connect
• Discovery URL (important – this is where OIDC "lives"):

  https://login.microsoftonline.com/<TENANT_ID>/v2.0/.well-known/openid-configuration

  (replace <TENANT_ID> with your Directory (tenant) ID from Azure).

• Client ID: your Azure Application (client) ID
• Client Secret: the secret you created
• Scope:

  openid profile email

  (you can also add offline_access if you want refresh tokens later, but not needed just for SSO).

• Username Column / Attribute Mapping:
  • Map APEX username to a claim like preferred_username or upn.
  • Often preferred_username or email is fine.

Save the scheme.

4.2 Get the Callback URL from APEX

After you save, APEX will show a Callback URL for that scheme.

It looks like:

Copy this value.

Now go back to Azure:

1. In the app registration, go to Authentication.
2. Under Redirect URIs, add:
   • Platform: Web
   • URI: paste that Callback URL from APEX.
3. Save.

4.3 Make this scheme the current one

In APEX Authentication Schemes:

• Set your new OIDC - Microsoft Entra ID scheme as Current.

At this point, the OIDC wiring is "ready".

Step 4 – Test the full roundtrip

Let's confirm the whole flow:

1. In APEX, create a simple page (or use an existing one) with a region showing:

   <p>APP_USER = &APP_USER.</p>

2. Run the application from the builder (or via URL).

If everything is correct:

• You hit your APEX app → immediately get redirected to Microsoft login.
• You sign in with an organization account (user@yourcompany.com).
• After success, you get redirected back to APEX.
• You see your page and the value of APP_USER should be something like:
  • user@yourcompany.com (depending on which claim you mapped).

If that works, SSO is alive 💡

If it fails, typical issues:

• Redirect URI mismatch (Azure vs APEX).
• Wrong tenant ID in discovery URL.
• Wrong client secret / expired secret.

Step 5 – Add authorization logic (roles)

Now that "who are you?" is solved, we decide "what can you do?".

You asked: Which option is best or ideal? Local tables? APEX schemes? Map to Entra groups?

6.1 Option A – Local roles table in the database (recommended starter)

Structure:

• APP_USERS
  • USER_ID (PK)
  • USERNAME (e.g., user@company.com)
  • ACTIVE_FLAG
  • whatever else (full name, etc.)
• APP_ROLES
  • ROLE_ID (PK)
  • ROLE_CODE (e.g., ADMIN, MANAGER, REPORT_VIEWER)
  • DESCRIPTION
• USER_ROLES
  • USER_ID
  • ROLE_ID

Flow:

1. In the Post-Authentication process for your scheme, you call a PL/SQL procedure, e.g.:

   my_security_pkg.post_auth (
       p_username => apex_authentication.get_user
   );

   Inside my_security_pkg.post_auth:

   • If p_username not in APP_USERS, insert it.
   • Optionally set defaults (e.g., first logged-in user becomes ADMIN, or you manage manually).
   • No roles assignment logic hardcoded; you do that via admin UI / SQL later.
2. Create Authorization Schemes in APEX that check roles:
   • e.g., Authorization Scheme: Is_Admin

   RETURN my_security_pkg.user_has_role(
       p_username => :APP_USER,
       p_role_code => 'ADMIN'
   );

3. Apply authorization schemes to pages, regions, buttons, etc.

Pros:

• All app behavior controlled in the DB/APEX layer.
• Easy to debug and modify without touching Azure.
• Good starter path while you're figuring the org's group structure.

6.2 Option B – Use Entra ID groups / app roles directly

You can tell Azure to send group IDs or app roles as claims in the token.

Flow:

1. In Azure App Registration → Token configuration:
   • Add Group claims or App roles.
2. In APEX, read these claims (depending on the mapping options your scheme exposes).
3. Your Authorization Schemes check something like:
   • Does the user's claims contain some-group-id?
   • Or does the roles claim contain Admin?

Pros:

• Roles are managed centrally in Entra ID.
• Good for large organizations with many apps sharing roles.

Cons:

• More Azure-side complexity.
• You tie app behavior to Azure configuration and group IDs → less DB-centric.

---

6.3 Hybrid approach (often ideal)

• Source of truth for roles is still in the DB (Option A).
• But you auto-sync / auto-assign based on Entra claims in your Post-Auth PL/SQL:
  • If the token has group XYZ, assign user role ADMIN in your local tables.
  • If not, give them just basic role.

Gives you:

• Central identity/entitlement from Entra.
• Local, easy-to-query roles in the DB.