Oracle APEX Security PL/SQL

APEX Custom Auth Settings, Decoded

Why I Build this

I was building an internal APEX app — strictly for people on our corporate network. The default authentication worked, but it had a few things that kept bothering me. Anyone with valid credentials could log in from anywhere. Sessions expired with a useless error page. There was no audit trail — no way to know who logged in, when, or from where. And passwords were stored in plain text, which I just couldn't leave alone. So I did what any developer does — I built it myself. Custom authentication, from scratch, in PL/SQL. Turned out to be one of the best learning experiences I've had with APEX. Here's exactly how I did it.

Img 1 : Head to Shared Components → Authentication Schemes → Create, choose Custom as the Scheme Type, and plug in your function and procedure names.

Quick note before we get into the code — the samples here are based on what I actually built and tested. They're meant to give you a working starting point, not a copy-paste solution. The real value is in the structure: the sentry, the audit log, the session handler. Once you understand how the pieces connect, swap in whatever business logic fits your situation. Your IP rules, your user table, your redirect pages — make it yours.

What we're building

1. Setting up the database tables
2. The sentry function (IP whitelist check)
3. Handling invalid sessions gracefully
4. The authentication function
5. The post-logout procedure
6. The "Enable Legacy Auth" setting explained

How a Custom APEX Auth Scheme Works

Before jumping into code, it helps to understand what APEX is actually calling and when. A custom authentication scheme is made up of several functions and procedures that APEX invokes at different points in a user's session lifecycle. Here's the full picture:

allowed_ips

Stores IP patterns that are whitelisted to access the app

fn_custom_sentry

Called before login — checks if the user's IP is allowed

fn_custom_authenticate

Called at login — validates the entered username and password

proc_handle_invalid_session

Called on expired or invalid sessions — shows a friendly error

proc_post_logout

Called after logout — cleans up, logs the event, redirects

session_audit_log

Records every login, logout, and invalid session event

---

Step 1  Setting Up the Database Tables

You need three tables in place before you write any PL/SQL logic. These are the foundation everything else sits on.

Table 1: allowed_ips - The IP Whitelist

This is where you define which IP addresses are allowed into the application at all. You can store exact IPs like '10.0.0.1' or wildcard patterns like '10.0.%' to cover an entire subnet. The LIKE comparison in the sentry function makes this really flexible.

The is_active flag means you can disable an IP range without deleting the record — handy if you're troubleshooting or temporarily blocking something. The created_by and created_date columns are automatic audit fields so you always know who added which rule and when.

  SQL — allowed_ips table

CREATE TABLE allowed_ips (
    ip_id        NUMBER        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ip_pattern   VARCHAR2(50)  NOT NULL,  -- e.g. '10.0.%', '192.168.1.%'
    description  VARCHAR2(255),
    is_active    CHAR(1)       DEFAULT 'Y' NOT NULL CHECK (is_active IN ('Y','N')),
    created_by   VARCHAR2(100) DEFAULT USER,
    created_date TIMESTAMP     DEFAULT SYSTIMESTAMP
);

Table 2: session_audit_log - The Audit Trail

Every important event gets written here. Logins, failed logins, logouts, invalid session attempts — everything. This table became my debugging lifeline more than once when something weird happened and I needed to figure out what went on.

  SQL — session_audit_log table

CREATE TABLE session_audit_log (
    log_id       NUMBER         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type   VARCHAR2(50)   NOT NULL,  -- INVALID_SESSION | LOGIN_OK | LOGOUT
    username     VARCHAR2(100),
    client_ip    VARCHAR2(64),
    apex_app_id  NUMBER,
    apex_session VARCHAR2(100),
    reason_code  VARCHAR2(100),            -- e.g. SESSION_EXPIRED, BAD_PASSWORD
    user_agent   VARCHAR2(500),
    extra_info   VARCHAR2(4000),            -- JSON or free-text extra context
    event_time   TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL
);

Table 3: CUSTOM_AUTHENTICATION - The User Store

This holds the actual user accounts. You control who has access, what their role is, and whether their account is active. The STATUS column uses 'A' for active — anything else is treated as inactive by the authentication function.

  SQL — CUSTOM_AUTHENTICATION table

CREATE TABLE CUSTOM_AUTHENTICATION (
    USER_ID          NUMBER PRIMARY KEY,
    USER_NAME        VARCHAR2(50),
    PASSWORD         VARCHAR2(30),
    ROLE             VARCHAR2(30),
    CREATE_DATE      DATE,
    STATUS           VARCHAR2(5),   -- 'A' = Active, anything else = inactive
    EMAIL            VARCHAR2(30),
    RESET_TOKEN      VARCHAR2(100),
    RESET_REQUESTED  DATE
);

⚠ Security note: This table stores passwords in plain text for simplicity — that's fine for a demo or learning exercise, but never do this in production. Before you go live, add proper password hashing using DBMS_CRYPTO with SHA-256 or PBKDF2. The structure is intentionally simple here so the auth logic stays readable.

---

Step 2  The Sentry Function :-

This is the very first thing APEX calls when someone hits your application. Before the login page even renders, this function runs and decides whether the user's IP address is allowed to be here at all. If it returns FALSE, APEX immediately invokes the invalid session handler and the user never sees a login form. They're just blocked.

One thing I had to handle carefully: when your app sits behind a load balancer or reverse proxy, REMOTE_ADDR will give you the proxy's IP, not the actual client. The real IP usually comes through in HTTP_X_FORWARDED_FOR, so we check that first and fall back to REMOTE_ADDR if it's null.

  PL/SQL — fn_custom_sentry

CREATE OR REPLACE FUNCTION fn_custom_sentry
  RETURN BOOLEAN
IS
  l_ip    VARCHAR2(64);
  l_count NUMBER;
BEGIN
  -- Retrieve the real client IP (works behind a load balancer too)
  l_ip := owa_util.get_cgi_env('HTTP_X_FORWARDED_FOR');
  IF l_ip IS NULL THEN
    l_ip := owa_util.get_cgi_env('REMOTE_ADDR');
  END IF;

  -- Check against an allowed-IP table
  SELECT COUNT(*)
    INTO l_count
    FROM allowed_ips
   WHERE l_ip LIKE ip_pattern;   -- e.g. '10.%' or exact match

  RETURN l_count > 0;
END fn_custom_sentry;
/

Img 2 : When the sentry returns TRUE and a valid session exists, the user gets through without re-authentication. If there's no session, APEX calls the authentication function — that's when credentials are checked. Either way, keep your sentry logic strict — it's the first and most important gate.

---

Step 3  Handling Invalid Sessions :-

An invalid session happens in three situations: the user's session has expired, someone tries to access a page URL directly without logging in, or the sentry function returned FALSE. Without a custom handler, APEX shows a fairly cryptic error that most users won't understand.

My version does two things — it logs the event to the audit table with enough detail to diagnose what happened, and it shows the user a message that actually tells them what's going on and what to do.

  PL/SQL — proc_handle_invalid_session

CREATE OR REPLACE PROCEDURE proc_handle_invalid_session IS
    l_client_ip VARCHAR2(64);
BEGIN
    -- Capture the client IP
    l_client_ip := owa_util.get_cgi_env('REMOTE_ADDR');

    -- Write a detailed audit record
    INSERT INTO session_audit_log (
        event_type,
        client_ip,
        apex_app_id,
        apex_session,
        user_agent,
        reason_code,
        event_time
    ) VALUES (
        'INVALID_SESSION',
        l_client_ip,
        apex_application.g_flow_id,
        apex_application.g_instance,
        owa_util.get_cgi_env('HTTP_USER_AGENT'),
        'SESSION_EXPIRED_OR_IP_BLOCKED',
        SYSTIMESTAMP
    );
    COMMIT;

    -- Show a user-friendly error message
    apex_error.add_error(
        p_message          => 'Access Denied: Your IP address is not whitelisted 
                               or your session has expired. Please contact the IT team.',
        p_display_location => apex_error.c_on_error_page
    );

EXCEPTION
    WHEN OTHERS THEN
        -- Even if logging fails, show the error message
        apex_error.add_error(
            p_message          => 'Session error. Please contact the IT team.',
            p_display_location => apex_error.c_on_error_page
        );
END proc_handle_invalid_session;
/

The EXCEPTION block at the bottom is important. If for some reason the INSERT fails — maybe the audit table is locked or there's a connectivity issue — we still want the user to see the error message. We never want a logging failure to hide the actual problem from the user.

Img 3 : When the sentry returns FALSE, APEX immediately calls the invalid session procedure.

---

Step 4  The Authentication Function :-

Once the sentry approves the IP, the login page appears. When the user submits their credentials, APEX calls this function. It checks the submitted username and password against the CUSTOM_AUTHENTICATION table and returns TRUE or FALSE. Either way, it logs the attempt to the audit table.

A few things I'm deliberately doing here: trimming and uppercasing the username so that 'Admin', 'ADMIN', and ' admin ' all match the same record, and immediately returning FALSE if either input is null so we never run a query with null parameters.

  PL/SQL — fn_custom_authenticate

CREATE OR REPLACE FUNCTION fn_custom_authenticate (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
) RETURN BOOLEAN
IS
    l_count  NUMBER := 0;
    l_stored VARCHAR2(255);
BEGIN
    -- Validate inputs — reject nulls immediately
    IF p_username IS NULL OR p_password IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Compare username (case-insensitive) and active status
    -- NOTE: In production, compare hashed passwords instead!
    -- e.g., AND PASSWORD = DBMS_CRYPTO_HASH(p_password)
    SELECT COUNT(*)
      INTO l_count
      FROM CUSTOM_AUTHENTICATION
     WHERE UPPER(USER_NAME) = UPPER(TRIM(p_username))
       AND PASSWORD         = p_password
       AND UPPER(STATUS)    = 'A';

    IF l_count = 1 THEN
        -- Log successful login
        INSERT INTO session_audit_log (
            event_type, username, client_ip, apex_app_id, event_time
        ) VALUES (
            'LOGIN_OK',
            UPPER(TRIM(p_username)),
            owa_util.get_cgi_env('REMOTE_ADDR'),
            apex_application.g_flow_id,
            SYSTIMESTAMP
        );
        COMMIT;
        RETURN TRUE;
    ELSE
        -- Log failed login attempt
        INSERT INTO session_audit_log (
            event_type, username, client_ip, apex_app_id, reason_code, event_time
        ) VALUES (
            'LOGIN_FAIL',
            UPPER(TRIM(p_username)),
            owa_util.get_cgi_env('REMOTE_ADDR'),
            apex_application.g_flow_id,
            'BAD_CREDENTIALS',
            SYSTIMESTAMP
        );
        COMMIT;
        RETURN FALSE;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END fn_custom_authenticate;

Img 4 : When the sentry returns TRUE but no valid session exists, APEX calls the authentication function — enter the right credentials and you're in.

---

Step 5  The Post-Logout Procedure :-

When a user clicks Logout, APEX calls this procedure. It's the cleanup step — log the event, clear any application-level cache, and redirect the user somewhere useful. The redirect includes a reason code that the login page can use to show a contextual message.

  PL/SQL — proc_post_logout

CREATE OR REPLACE PROCEDURE proc_post_logout IS
BEGIN
    -- Step 1: Write a detailed LOGOUT record to the audit log
    INSERT INTO session_audit_log (
        event_type,
        username,
        client_ip,
        apex_app_id,
        apex_session,
        reason_code,
        user_agent,
        event_time
    ) VALUES (
        'LOGOUT',
        apex_application.g_user,               -- The currently logged-in user
        owa_util.get_cgi_env('REMOTE_ADDR'),   -- Their IP address
        apex_application.g_flow_id,            -- Application ID
        apex_application.g_instance,           -- Session ID
        'USER_INITIATED_LOGOUT',               -- How the logout happened
        owa_util.get_cgi_env('HTTP_USER_AGENT'), -- Browser info
        SYSTIMESTAMP
    );
    COMMIT;

    -- Step 2: Clear application-level cache for this user
    APEX_UTIL.CLEAR_APP_CACHE(
        p_app_id => apex_application.g_flow_id
    );

    -- Step 3: Redirect to login page with a reason code
    apex_util.redirect_url(
        p_url => apex_page.get_url(
            p_page        => 9999,
            p_clear_cache => '9999',
            p_items       => 'P9999_REASON',
            p_values      => 'SESSION_EXPIRED'
        )
    );

EXCEPTION
    WHEN OTHERS THEN
        -- Redirect even if logging fails
        apex_util.redirect_url(
            p_url => apex_page.get_url(p_page => 9999)
        );
END proc_post_logout;
/

The P9999_REASON trick: By passing P9999_REASON = 'SESSION_EXPIRED' in the redirect URL, your login page (page 9999) can read this value and display something like "You've been logged out. Please sign in again." It's a small UX detail but it makes the app feel genuinely polished compared to just landing on a blank login form with no context.

---

What Is "Enable Legacy Authentication Attributes"?

This is a setting inside the APEX authentication scheme configuration that tripped me up the first time I saw it. It controls whether APEX exposes old HTMLDB-era CGI environment variables and cookie-based session attributes that existed before modern APEX versions. HTMLDB was what Oracle APEX was called before version 2.0 — we're talking early-to-mid 2000s web development.

Setting this to Yes makes those old-style session cookies and CGI variables available for backward compatibility with very old applications. For anything built in the last several years, this should always be No. Enabling it exposes a broader attack surface, can interfere with modern session management, and is only needed if you're migrating a genuinely ancient APEX or HTMLDB application.

Setting	When to Use
No (Default - Recommended)	All new applications. Any app built after APEX 3.x. Your setup.
Yes	Only when migrating legacy HTMLDB or very old APEX apps that explicitly depend on old-style CGI vars

---

Img 5 :Flow Diagram

Wrapping Up

Once all five pieces are in place — the three tables, the sentry, the authentication function, the invalid session handler, and the post-logout procedure — you wire them together in APEX under Shared Components → Authentication Schemes → Create. Each component maps to the corresponding field in the scheme configuration.

What I ended up with was an authentication layer I actually understood end to end. No black boxes. Every login, every logout, every blocked IP attempt — it's all in the audit log. When something goes wrong (and it will), you have a clear trail to follow.

The IP whitelist alone saved me a couple of awkward conversations with the security team. If you're building anything internal, I'd recommend adding it from day one rather than retrofitting it later. It's much easier to set up when you're starting fresh.