Custom Authentication in Oracle APEX With Role-Aware Session Population

When building a multi-tenant portal where agency admins, company admins, and auditors log in through the same page into different experiences, standard authentication isn't enough. We built a custom authentication scheme where the post-authentication procedure is just as vital as the login check, seamlessly setting up the user's specific role and tenant context the moment they enter.

Why Custom Authentication?

APEX ships with several built-in authentication options: APEX Accounts, LDAP/Active Directory, Oracle Database Accounts, and Social Sign-In. These are great starting points but they all share a fundamental limitation: they authenticate, and then get out of the way if we explicitly doesn't change them.

When your application needs to know who someone is beyond just their username, you're on your own. What's their role? Which agency do they belong to? Are they allowed to approve requests or just view them?

That context lives in your own tables. Custom authentication lets you control the entire login pipeline starting from the credential check, the lockout logic, and the post-login of application state.

The Schema Behind It

Before looking at the PL/SQL, here's the shape of the data model that i created for my useage, your schema can be different from mine but the idea is generally same:

• AGN_USERS - core user record with STATUS and AGENCY_ID

• AGN_USER_AUTH - credential store: EMAIL, PASSWORD_HASH, LOGIN_FAILED_COUNT, LOCKED_UNTIL, LAST_LOGIN_AT

• AGN_ROLES - role definitions (company_admin, agency_admin, agency_user, auditor)

• AGN_USER_ROLES - the join table linking users to their assigned roles

The split between AGN_USERS and AGN_USER_AUTH is intentional and important. Authentication concerns (passwords, lockouts) are isolated from identity concerns (name, agency, status). That separation makes auditing, future MFA additions, and auth-scheme changes much cleaner.

Part 1: The Authentication Function

APEX custom authentication expects a FUNCTION(p_username VARCHAR2, p_password VARCHAR2) RETURN BOOLEAN. Return TRUE and APEX logs the user in. Return FALSE and the login page re-renders with an error.

Here's the full function:

CREATE OR REPLACE FUNCTION AGN_AUTHENTICATE (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
) RETURN BOOLEAN AS
    v_user_id       AGN_USERS.ID%TYPE;
    v_status        AGN_USERS.STATUS%TYPE;
    v_password_hash AGN_USER_AUTH.PASSWORD_HASH%TYPE;
    v_locked_until  AGN_USER_AUTH.LOCKED_UNTIL%TYPE;
    v_failed_count  AGN_USER_AUTH.LOGIN_FAILED_COUNT%TYPE;
BEGIN
    -- Look up user by email
    BEGIN
        SELECT
            u.ID,
            u.STATUS,
            ua.PASSWORD_HASH,
            ua.LOCKED_UNTIL,
            ua.LOGIN_FAILED_COUNT
        INTO
            v_user_id,
            v_status,
            v_password_hash,
            v_locked_until,
            v_failed_count
        FROM AGN_USERS u
        JOIN AGN_USER_AUTH ua ON ua.USERS_ID = u.ID
        WHERE LOWER(ua.EMAIL) = LOWER(TRIM(p_username));

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN FALSE;
    END;

    -- Check account is active
    IF v_status != 'ACTIVE' THEN
        RETURN FALSE;
    END IF;

    -- Check account is not locked
    IF v_locked_until IS NOT NULL
       AND v_locked_until > SYSTIMESTAMP THEN
        RETURN FALSE;
    END IF;

    -- Verify password
    IF v_password_hash = p_password THEN

        -- Success: reset failed attempts
        UPDATE AGN_USER_AUTH
        SET LOGIN_FAILED_COUNT = 0,
            LOCKED_UNTIL       = NULL,
            LAST_LOGIN_AT      = SYSTIMESTAMP,
            UPDATED_AT         = SYSTIMESTAMP
        WHERE USERS_ID = v_user_id;

        COMMIT;
        RETURN TRUE;

    ELSE
        -- Failure: increment counter, lock after 5 attempts
        UPDATE AGN_USER_AUTH
        SET LOGIN_FAILED_COUNT = LOGIN_FAILED_COUNT + 1,
            LOCKED_UNTIL = CASE
                WHEN LOGIN_FAILED_COUNT + 1 >= 5
                THEN SYSTIMESTAMP + INTERVAL '30' MINUTE
                ELSE NULL
            END,
            UPDATED_AT = SYSTIMESTAMP
        WHERE USERS_ID = v_user_id;

        COMMIT;
        RETURN FALSE;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN FALSE;
END AGN_AUTHENTICATE;
/

Let's walk through the key decisions.

Lookup by email, not username

The WHERE clause normalises both sides:

WHERE LOWER(ua.EMAIL) = LOWER(TRIM(p_username))

TRIM strips any accidental whitespace from what the user typed. LOWER on both sides makes the match case-insensitive without requiring a function-based index (though you should add one on LOWER(EMAIL) in production for performance). The email lives in AGN_USER_AUTH, not AGN_USERS, to keep credential data in one place.

Three gates before the password check

Before we even look at the password, the function checks:

1. Does the record exist? A NO_DATA_FOUND inside a nested BEGIN...END block returns FALSE cleanly, without bubbling up to the outer EXCEPTION handler.

2. Is the account active?

3. Is the account locked? The LOCKED_UNTIL timestamp is compared against SYSTIMESTAMP.

This ordering matters. You want to exit as early as possible with as little information as possible. Timing side channels are a real thing; returning quickly on "no user found" vs slowly on "wrong password" can let attackers enumerate valid emails through response time differences. All three early exits return at roughly the same point in execution.

Account Lockout: A Closer Look

The lockout logic is entirely in the ELSE branch triggered on every wrong password:

UPDATE AGN_USER_AUTH
SET LOGIN_FAILED_COUNT = LOGIN_FAILED_COUNT + 1,
    LOCKED_UNTIL = CASE
        WHEN LOGIN_FAILED_COUNT + 1 >= 5
        THEN SYSTIMESTAMP + INTERVAL '30' MINUTE
        ELSE NULL
    END,
    UPDATED_AT = SYSTIMESTAMP
WHERE USERS_ID = v_user_id;

A few things worth noting here:

The counter is atomic. We're doing LOGIN_FAILED_COUNT + 1 in the database, not reading the value into PL/SQL, incrementing it, then writing it back. That matters in a concurrent environment. two simultaneous failed logins won't both read 4, both compute 5, and both expect to be the lockout trigger.

The threshold is 5 attempts in my case. On the fifth failure (LOGIN_FAILED_COUNT + 1 >= 5), LOCKED_UNTIL is set to 30 minutes from now. Before that, it stays NULL. The check at the top of the function (v_locked_until > SYSTIMESTAMP) handles the rest.

Lockout resets on success. A successful login sets LOGIN_FAILED_COUNT = 0 and LOCKED_UNTIL = NULL. So if someone gets 4 wrong and then finally gets it right, they're fully reset. You could debate whether that's the right policy because some systems keep the failed count as an audit trail even after success. For this tutorial clean reset is simpler.

30 minutes is a soft lock, not a hard ban. The account unlocks automatically when SYSTIMESTAMP passes LOCKED_UNTIL. No admin intervention required for a standard lockout only for permanently suspended accounts (STATUS != 'ACTIVE').

A Note on Password Comparison

You'll notice the password check is a direct equality comparison:

IF v_password_hash = p_password THEN

The column is named PASSWORD_HASH, which implies the password has been hashed before storage but the comparison here is plain string equality, which means your application layer is expected to hash the input before calling this function, and the stored value is already a hash.

If you're calling this with a raw password, that's a serious problem. Storing or comparing plain-text passwords is never acceptable. In production, you should be hashing with a strong, slow algorithm. DBMS_CRYPTO with HASH_SH256 is a reasonable starting point in Oracle.

Part 2: The Post-Authentication Procedure

This is the piece that made me write this post.

APEX lets you specify a Post-Authentication Procedure in your authentication scheme. This procedure fires after a successful login, before the user lands on their first page. It's your window to populate session state with everything the application will need for the rest of that session.

Here's the full procedure:

CREATE OR REPLACE PROCEDURE AGN_POST_AUTHENTICATE AS
    v_user_id   AGN_USERS.ID%TYPE;
    v_agency_id AGN_USERS.AGENCY_ID%TYPE;
    v_role_name AGN_ROLES.ROLE_NAME%TYPE;
BEGIN
    SELECT u.ID, u.AGENCY_ID
    INTO   v_user_id, v_agency_id
    FROM   AGN_USERS u
    WHERE  LOWER(u.EMAIL) = LOWER(TRIM(APEX_APPLICATION.G_USER));

    SELECT r.ROLE_NAME
    INTO   v_role_name
    FROM   AGN_USER_ROLES ur
    JOIN   AGN_ROLES      r ON r.ID = ur.ROLES_ID
    WHERE  ur.USERS_ID = v_user_id
    ORDER BY
        CASE r.ROLE_NAME
            WHEN 'company_admin' THEN 1
            WHEN 'agency_admin'  THEN 2
            WHEN 'agency_user'   THEN 3
            WHEN 'auditor'       THEN 4
            ELSE 5
        END
    FETCH FIRST 1 ROW ONLY;

    APEX_UTIL.SET_SESSION_STATE(
        'APP_USER_ID',        TO_CHAR(v_user_id));
    APEX_UTIL.SET_SESSION_STATE(
        'APP_USER_AGENCY_ID', TO_CHAR(v_agency_id));
    APEX_UTIL.SET_SESSION_STATE(
        'APP_USER_ROLE',      v_role_name);

EXCEPTION
    WHEN OTHERS THEN
        NULL;
END AGN_POST_AUTHENTICATE;
/

How APEX Knows the Logged-In User

By the time this procedure fires, APEX has already set APEX_APPLICATION.G_USER to whatever username value the authentication function was given. In our case, that's the email address the user typed.

So the first query re-fetches the user's ID and AGENCY_ID using that email:

SELECT u.ID, u.AGENCY_ID
INTO   v_user_id, v_agency_id
FROM   AGN_USERS u
WHERE  LOWER(u.EMAIL) = LOWER(TRIM(APEX_APPLICATION.G_USER));

We already did this lookup in AGN_AUTHENTICATE but there's no way to pass values between a custom authentication function and a post-authentication procedure. Each is a separate call. The redundant lookup is the price of the clean architecture.

Resolving the User's Effective Role

Users can have multiple roles. The procedure resolves this to a single effective role using a priority order baked into a CASE expression:

ORDER BY
    CASE r.ROLE_NAME
        WHEN 'company_admin' THEN 1
        WHEN 'agency_admin'  THEN 2
        WHEN 'agency_user'   THEN 3
        WHEN 'auditor'       THEN 4
        ELSE 5
    END
FETCH FIRST 1 ROW ONLY

company_admin outranks everything. If someone has both agency_admin and auditor assigned, they log in as agency_admin. FETCH FIRST 1 ROW ONLY takes the winner. This is simpler than trying to handle multiple concurrent roles in the UI, and it maps naturally to how the business thinks about permissions: a person has a role in a context, not a bag of capabilities.

Setting Session State

Three application items get populated:

APEX_UTIL.SET_SESSION_STATE('APP_USER_ID',        TO_CHAR(v_user_id));
APEX_UTIL.SET_SESSION_STATE('APP_USER_AGENCY_ID', TO_CHAR(v_agency_id));
APEX_UTIL.SET_SESSION_STATE('APP_USER_ROLE',      v_role_name);

These need to be defined as Application Items in APEX (Shared Components → Application Items) before they'll persist. Once set, they're available everywhere:

• In SQL queries as bind variables: :APP_USER_ID, :APP_USER_AGENCY_ID, :APP_USER_ROLE

• In PL/SQL as V('APP_USER_ROLE')

• In conditions and expressions directly in the APEX builder

This is how a single application serves four different user types without branching logic scattered everywhere. The role is established at login and referenced wherever needed.

Using the Session Items for Role-Based Access

With APP_USER_ROLE in session state, you can drive authorization at multiple levels.

Page-Level Access

On any page in APEX, set an Authorization Scheme that evaluates:

:APP_USER_ROLE IN ('company_admin', 'agency_admin')

Pages that require admin access simply won't render for agency_user or auditor roles. APEX redirects them away before anything loads.

Region and Component Visibility

Individual regions, buttons, or columns can use the same item in their Server-Side Condition:

• Type: PL/SQL Expression

• Expression: :APP_USER_ROLE = 'company_admin'

A "Manage All Agencies" button only appears for company_admin. An "Edit" column only shows for users with write permissions. No custom JavaScript. No conditional rendering in code.

Data Scoping with Agency ID

APP_USER_AGENCY_ID is just as important. Every query that fetches agency-specific data can include:

WHERE agency_id = :APP_USER_AGENCY_ID

A company_admin might have a NULL agency ID, which you can use to mean "see everything." An agency_user always sees only their own agency's data. The filter is declarative and consistent — it doesn't rely on developers remembering to add a WHERE clause.

Wiring It Up in APEX

To hook all of this into APEX:

1. Go to Shared Components → Authentication Schemes → Create

2. Choose Custom as the scheme type

3. Set Authentication Function Name to AGN_AUTHENTICATE

4. Set Post-Authentication Procedure Name to AGN_POST_AUTHENTICATE

5. Make this scheme current

Then create your three Application Items (APP_USER_ID, APP_USER_AGENCY_ID, APP_USER_ROLE) under Shared Components → Application Items with session scope.

That's it. APEX handles the rest by calling your function on login, calling your procedure on success, and making the session items available to every page and component in the application.

What This Pattern Gets You

Custom authentication with a post-authentication hook gives you a clean separation of concerns:

• AGN_AUTHENTICATE answers one question: Are these credentials valid?

• AGN_POST_AUTHENTICATE answers a different question: Who is this person and what context do they bring?

The first is security. The second is identity. Keeping them separate makes both easier to reason about, test, and evolve independently.

The three session items (APP_USER_ID, APP_USER_AGENCY_ID, APP_USER_ROLE) become the foundation everything else builds on — page access, data filters, UI branching, audit trails. Populate them once, at login, and the rest of the application just asks :APP_USER_ROLE whenever it needs to make a decision.

If you're building anything beyond a simple single-role internal tool in Oracle APEX, this is the pattern worth reaching for.