SSO for Oracle APEX: Connecting a Portal Directly to Entra ID

The portal is an APEX application running on ORDS, on its own database. It is not the EBS database: the operational data it displays lives in EBS and is reached through database links, exposed locally as a set of views and PL/SQL APIs so that the application code never talks to EBS objects directly.

It has two completely different user populations:

External customer users. Several thousand of them, working for the customer's own business customers. They have no account anywhere in the customer's infrastructure. They self-register through a registration page, confirm their email address, and are then approved and assigned to a group, region, district or single location, which determines the scope of the data they see. Their username is their email address and their credentials live in the APEX workspace repository.

Internal employees. Sales staff, dispatchers and administrators who use the portal to see what their customers see, to resolve open cases and to manage user access. Every one of them already has an EBS account, an EBS responsibility set, and an Entra ID account.

Two populations, one login page. That is the problem the original implementation had to solve.

The original design, which ran in production for well over a year, solved it with a custom authentication scheme and a heuristic on the username:

-- If the user name is an email address, we authenticate against the APEX
-- account repository. Otherwise it is an EBS user name.
if instr(p_username, '@') > 0 then
    return apex_authenticate(
        p_username => upper(p_username),
        p_password => p_password
    );
else
    return ebs_authenticate(
        p_username => p_username,
        p_password => p_password
    );
end if;

An @ in the username meant "customer user, authenticate against the APEX account repository". No @ meant "this is an EBS user name, authenticate against EBS".

Both branches did more than check a password.

The APEX branch validated the credentials with apex_util.is_login_password_valid, then checked that the user was a member of the workspace group configured for this application, and finally checked an active_flag in the portal's own user table. Three independent gates, so that deactivating a user in the portal was sufficient to lock them out even if the workspace account survived.

The EBS branch took the username and password and validated them against FND_USER over the database link, through a PL/SQL API on the EBS side. If the credentials were good and the user had the appropriate E-Business Suite responsibility, the employee was in, with the same session mechanics as any other user.

I want to be clear that this was a valid design, not a workaround anybody should be embarrassed about. It reused the identity that already existed. Employees never had to maintain a second password for the portal. And it fit the authorization model, which resolves EBS responsibilities and always has, so the portal was already treating EBS as the system of record for internal identity. Authenticating against the same system was the consistent choice.

What broke it was not a flaw in the portal. It was the EBS SSO project described in the earlier series.

Once EBS moved behind Entra ID, employees stopped typing an EBS password. They click through to the Microsoft login they already know, complete MFA, and land in EBS. The local FND_USER password does not disappear, but it stops being part of anyone's working day. Within a few weeks most people could not have told you what theirs was, and the ones who could were the ones who had never used the portal.

That leaves an application whose login form asks employees for a credential they no longer possess in any practical sense. The failure mode is not a security hole, it is a support queue: password reset requests for a password that exists only so that one application can keep validating it. Maintaining a live password purely to feed a login form that SSO was supposed to replace is exactly backwards.

So the change was not really optional, and framing it as "we wanted MFA on the portal" undersells it. MFA is the benefit. The forcing function was that the credential the portal depended on had already been retired everywhere else.

The one genuine design weakness, while we are here, was the @ heuristic itself. It assumed no EBS user name would ever contain an @ and that every customer user name would. It held, but it encoded an identity policy in a string function.

The obvious option was to reuse what the Asserter project had already built. There is an OCI IAM Identity Domain in place, federated to Entra ID, and registering an APEX application in it as an OIDC client is not hard. It would have put EBS and the portal behind one set of sign-on policies, which is the kind of consolidation that looks good on an architecture slide.

We did not do it, and the reason is a licensing limit rather than a technical one.

The domain is of type Oracle Apps Premium. That is not an arbitrary choice: the EBS Asserter is not available on the Free or Oracle Apps domain types at all, so an EBS SSO project lands you on Oracle Apps Premium or Premium by definition. Oracle Apps Premium is priced for exactly this shape of use case, per user per month, and it is deliberately constrained on everything that is not an Oracle application.

The constraint that matters here is the object limit for non-Oracle apps. Oracle Apps Premium is documented at 10, against 5,000 for a Premium domain. And the definition is explicit: custom-developed applications count, with applications built using APEX named in the documentation as an example. Applications built with Visual Builder do not count. Whatever the reasoning behind that distinction, the effect is that an APEX portal consumes one of a very small number of slots.

Except it does not consume one slot. It consumes three. DEV, QAT and PROD are separate confidential applications in the domain, each with its own client ID, secret and redirect URLs. One logical application, three registrations. Add a second APEX application on the same estate and you are at six of ten before anything unusual has happened. The limit stops being theoretical much faster than the number suggests.

Two footnotes to that, both worth knowing:

The limit is currently not enforced for Oracle Apps and Oracle Apps Premium domains, and Oracle states in the same documentation that it will be enforced in future. So you can register more than ten today and nothing will stop you. Building an architecture whose viability depends on an announced limit continuing not to be enforced is a bet with a due date, and the due date is set by somebody else.

You can also change the domain type to Premium, which lifts the limit to 5,000. But the domain type is a property of the domain, not of an individual application, so that decision reprices every user in it. Moving to Premium to accommodate one APEX application means paying the Premium rate for the entire EBS user population, which is a large bill for a redirect.

And that is really the point. Weigh the cost against what the extra hop buys, and the answer is nothing. Every employee who signs in to the portal exists in Entra ID. The identity domain is federated to Entra ID, so their conditional access policies and MFA apply either way. Routing the portal through the domain would add a redirect, a second place to register clients and rotate secrets, and a component in the login path, in exchange for a control we already have at the other end.

One clarification, because it is easy to get this backwards. The Asserter integration itself does not consume a slot. EBS is an Oracle application, and supporting Oracle applications is the entire reason the Oracle Apps Premium domain type exists. The non-Oracle limit applies to third-party commercial software and to custom-built applications, which is precisely where an APEX portal lands. The scarce resource is not being spent on what the domain was bought for. It would only be spent on ours.

The replacement is one declarative authentication scheme. In APEXLang, the entire OIDC configuration is this:

authentication azure-authentication (
    name: Azure-Authentication
    type: socialSignIn
    settings {
        credentialStore: @azure-authentication
        discoveryUrl: https://login.microsoftonline.com/<tenant-id>/v2.0/.well-known/openid-configuration
        tokenAuthMethod: basic
        scope: openid,profile,email
        username: #email#
        convertUsernameToUpperCase: true
    }
    loginProcessing {
        postAuthenticationProcedureName: cmn_authentication_pkg.azure_post_authentication
        switchInSession: true
    }
    postLogout {
        redirectTo: url
        url: /ords/r/<workspace>/<application-alias>
    }
)

A few points worth drawing out:

The discovery URL does the work. Pointing at the tenant's .well-known/openid-configuration means the authorization endpoint, token endpoint, JWKS URI and supported algorithms are all resolved at runtime. No endpoint URLs are hardcoded anywhere, which also means tenant-level changes on the Microsoft side do not require an application change.

The client secret never enters Git. The repository carries the client ID and nothing else. The credential itself is created once per environment by hand in the application, rather than deployed through the pipeline, because the pipeline is non-interactive and a secret prompt at install time is not an option there. A one-time manual step per environment is a fair price for keeping the secret out of version control entirely.

switchInSession: true is what makes the dual login possible. The application's default authentication scheme is still the custom one used by customer users. The Entra scheme is a second scheme that the login page can switch to per session, so both populations continue to share one application and one login page.

The username claim is email, uppercased. More on that below, because it is the single most consequential decision in the whole change.

The login page keeps the familiar username and password fields for customer users, along with the registration and forgot-password links. Employees get a second, deliberately unobtrusive button:

button sign-in-with-azure (
    buttonName: SIGN_IN_WITH_AZURE
    label: Mitarbeiter Login
    behavior {
        action: redirectThisApp
        target: {
            page: 1
            request: APEX_AUTHENTICATION=Azure-Authentication
        }
    }
)

The APEX_AUTHENTICATION request parameter is the built-in mechanism for selecting a non-default authentication scheme. Clicking the button sends the employee to Entra ID, through conditional access and MFA, and back into the portal. No username is typed on our side at all, which is the point: the portal never sees an employee credential again.

The visual weight of that button matters more than it sounds. Several thousand customer users see this page and none of them have a Microsoft account in the customer's tenant. A prominent "sign in with Microsoft" button would generate a steady trickle of confused clicks and support tickets from people who are certain they are supposed to use it. It is styled to be findable by the few hundred people who need it and easy to ignore for everyone else. Putting the employee entry point on a separate page whose URL is simply circulated internally would be a reasonable alternative, and arguably a cleaner one, since the customer-facing page would then carry no reference to it at all.

The result is one page serving both populations, with the choice made by a single click rather than by inspecting what the user typed.

The part of this change that generalises beyond one customer is the split it creates.

Everything that decides who you are now happens outside the portal. What used to be roughly 200 lines of PL/SQL covering credential validation, EBS password checks and error message handling is gone.

Everything that decides what you may do is unchanged and still comes from EBS. The portal's authorization schemes resolve responsibilities across the database link:

function is_internal_admin
return boolean as
begin
    return has_oebs_responsibility(
        p_user_name => get_current_user_name(),
        p_responsibility_key => 'XX_PORTAL_ADMIN'
    );
end is_internal_admin; 

An employee is a portal administrator because they hold that responsibility in EBS, not because of anything configured in APEX or in Entra ID. Responsibility assignment stays in the EBS user management process that the customer already runs, with its existing approval workflow and audit trail. Nothing had to be modelled as Entra ID groups, and no SCIM provisioning of authorization data was required.

This is worth stating plainly because it is the opposite of how these projects are often scoped. The instinct is to move roles into the IdP at the same time as authentication. Keeping them separate meant the change touched one login page and one package, and the authorization model was not in scope at all.

The join between the two halves is the username, and that deserves more attention than it usually gets.

username: #email# with convertUsernameToUpperCase: true means APP_USER after an Entra login is the uppercased email address from the email claim. The EBS responsibility lookup then has to resolve that email back to an FND_USER.

This is the same decision the Asserter project had to make, and it has the same failure mode in both architectures. If an employee's email address in EBS is stale or differs in case or domain, the user authenticates perfectly and then lands in a portal where they hold no responsibilities at all. It presents as an authorization bug and gets investigated as one, when the actual cause is a mismatched key.

Here the risk is smaller than it looks, and for a reason worth stating: the EBS email attribute has to be correct anyway. The Asserter matches on it, and EBS sends workflow notifications, approval requests and reports to it. An employee with a wrong address in FND_USER has broken approvals long before they have a broken portal login, so the field is already maintained, already noticed when wrong, and already owned by somebody. Reusing an attribute that the business has independent reasons to keep accurate is a much better position than introducing a new one that only the login path depends on.

Two things still worth checking:

• If your environment does not have that property, pick something else. The Entra oid claim is immutable per tenant and is a stronger key than an email address, provided you are willing to store it against the EBS user.

• Confirm that the email claim is actually issued. For accounts without a mail attribute, and for B2B guests, it may be absent unless configured as an optional claim on the app registration. An absent claim means an empty username and a failed login with an unhelpful message.

The two projects at the same customer make a reasonable decision rule.

If the application speaks OIDC, let it speak OIDC. For APEX, that is a Social Sign-In scheme, a web credential and a discovery URL. There is no broker to license, no container to patch, and nothing to redeploy after a clone.

If the application does not speak OIDC, as with EBS, you need a component that does. The Asserter plus an OCI IAM Identity Domain is the supported path, it is inexpensive, and I have described it in detail in the earlier series.

What you should not do is route the OIDC-capable application through the broker as well, purely for architectural symmetry. It adds a hop, a failure mode and a component to maintain, in exchange for nothing the application could not do on its own, and on an Oracle Apps Premium domain it spends a scarce registration slot doing so.

The more useful lesson is about sequencing. Before you put EBS behind SSO, inventory everything that authenticates people against FND_USER passwords: portals, integrations, scheduled jobs, interface accounts, that reporting tool nobody has looked at since 2019. Each is a dependency on a credential you are about to take out of circulation.

We had this one on the plan from the beginning rather than in the support queue, because we built the portal and we operate it, so its dependency on FND_USER was never in doubt. That is the comfortable position, and it rests entirely on knowing your own architecture in detail. In a landscape that has grown for twenty years, where nobody holds a complete picture of what validates passwords against FND_USER, a dependency like this is genuinely easy to overlook. It will not announce itself during the project. It announces itself afterwards, when users start calling.

The migration itself, from a first working prototype to production, took less time than writing this post about it. The interesting work was not the OIDC configuration. It was deciding what the username means.