Oracle APEX May 16, 2026 hannanmentor@gmail.com 10 min read

Oracle APEX Authentication: Securing Your Application the Right Way

Authentication is the gateway to every Oracle APEX application. Before a user sees a single page, APEX checks their identity. Getting authentication right means your application is secure, professional, and flexible enough to integrate with your organization’s existing identity infrastructure.

In this guide, we cover all the major APEX authentication options — from the built-in schemes to custom implementations — and share practical guidance on choosing and configuring the right one for your situation.

How APEX Authentication Works

Every APEX application has an Authentication Scheme — a configuration object that defines how users prove their identity. When an unauthenticated user accesses a protected page, APEX redirects them to the login page. After credentials are submitted, the authentication scheme validates them and either grants access or rejects the attempt.

Once authenticated, APEX stores the user’s identity in the APP_USER session state variable, which you can use throughout your application for authorization and personalization.

Built-In Authentication Schemes

1. APEX Accounts

APEX Accounts stores usernames and passwords in the APEX internal schema. Administrators manage accounts through the APEX workspace administration interface.

Best for: Development, prototyping, small internal tools with a handful of users.

Not ideal for: Production applications with many users or enterprise environments where accounts should be centrally managed.

2. Database Accounts

Database Accounts uses Oracle database user accounts for authentication. Users log in with their database username and password.

Best for: Developer tools, DBA utilities, applications where users already have database accounts.

Not ideal for: End-user applications — granting database accounts to regular users is a security risk and a management overhead.

3. LDAP Directory

LDAP authentication delegates credential validation to an LDAP server (Microsoft Active Directory, Oracle Internet Directory, OpenLDAP, etc.).

-- Key LDAP configuration in Authentication Scheme:
-- Host: your-ldap-server.domain.com
-- Port: 389 (standard) or 636 (SSL)
-- Use SSL: Yes (recommended for production)
-- Distinguished Name String: cn=%LDAP_USER%,ou=Users,dc=yourdomain,dc=com
-- Search Filter (optional): (&(objectClass=person)(sAMAccountName=%LDAP_USER%))

Best for: Organizations with Active Directory or an existing LDAP directory — users authenticate with their domain credentials.

Practical tip: Test LDAP connectivity from the database server first using tools like ldapsearch before configuring in APEX. Many LDAP issues stem from network connectivity or SSL certificate problems.

4. Social Sign-In

APEX includes built-in support for OAuth2-based social sign-in, including Google, Facebook, and any provider that supports OpenID Connect. Users click a “Sign in with Google” button and authenticate with their existing account.

Best for: Public-facing applications, B2C portals, applications where you want to eliminate password management.

5. No Authentication (Public Access)

Public applications require no login. Any user can access any page.

Best for: Public information portals, read-only dashboards, landing pages.

Custom Authentication: Full Control

When none of the built-in schemes fit your requirements, you can write a custom authentication function. This is the most flexible option and is very commonly used in enterprise applications with custom user tables.

The custom authentication function takes a username and password and returns TRUE if authentication succeeds, FALSE if it fails:

CREATE OR REPLACE FUNCTION app_authenticate (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
) RETURN BOOLEAN IS
  v_stored_hash  VARCHAR2(200);
  v_count        NUMBER;
BEGIN
  -- Find the user and retrieve the stored password hash
  SELECT password_hash
  INTO   v_stored_hash
  FROM   app_users
  WHERE  UPPER(username) = UPPER(p_username)
  AND    account_locked = 'N'
  AND    account_active = 'Y';
  
  -- Compare the provided password against the stored hash
  -- Using APEX's built-in password hashing utility
  IF APEX_UTIL.GET_HASH(p_password) = v_stored_hash THEN
    -- Update last login timestamp
    UPDATE app_users 
    SET last_login = SYSDATE, failed_attempts = 0
    WHERE UPPER(username) = UPPER(p_username);
    COMMIT;
    RETURN TRUE;
  ELSE
    -- Increment failed attempt counter
    UPDATE app_users 
    SET failed_attempts = NVL(failed_attempts, 0) + 1
    WHERE UPPER(username) = UPPER(p_username);
    COMMIT;
    RETURN FALSE;
  END IF;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- User not found — return FALSE (never expose which field was wrong)
    RETURN FALSE;
  WHEN OTHERS THEN
    -- Log and return FALSE on any unexpected error
    RETURN FALSE;
END app_authenticate;

In your APEX authentication scheme, set the type to “Custom” and reference this function. APEX calls it automatically when a user submits the login form.

Post-Authentication Processing

After successful authentication, you often need to:

Load user profile data into session state
Set the user’s application role or permission level
Redirect to a role-specific landing page
Log the login event for auditing

Use the Post-Authentication Procedure in your Authentication Scheme settings to run a PL/SQL procedure after login:

CREATE OR REPLACE PROCEDURE post_auth_setup IS
  v_role      VARCHAR2(50);
  v_full_name VARCHAR2(100);
  v_user_id   NUMBER;
BEGIN
  -- Load user details from the application user table
  SELECT user_id, full_name, role_code
  INTO   v_user_id, v_full_name, v_role
  FROM   app_users
  WHERE  UPPER(username) = UPPER(V('APP_USER'));
  
  -- Store in session state for use throughout the application
  APEX_UTIL.SET_SESSION_STATE('APP_USER_ID',   v_user_id);
  APEX_UTIL.SET_SESSION_STATE('APP_USER_NAME', v_full_name);
  APEX_UTIL.SET_SESSION_STATE('APP_USER_ROLE', v_role);
  
  -- Log the login event
  INSERT INTO login_audit (user_id, login_time, session_id)
  VALUES (v_user_id, SYSDATE, V('APP_SESSION'));
  COMMIT;
  
EXCEPTION
  WHEN OTHERS THEN
    NULL; -- Don't prevent login on audit failure
END post_auth_setup;

Authorization vs Authentication

Authentication answers “Who are you?” Authorization answers “What are you allowed to do?” Both are essential, and APEX handles both.

Once you have authenticated the user and stored their role, you use Authorization Schemes to control page and component visibility:

-- Authorization Scheme: PL/SQL Function Returning Boolean
-- Type: Function Body
RETURN :APP_USER_ROLE = 'ADMIN';

Apply this scheme to pages, regions, buttons, or menu items that should only be visible to administrators. Non-administrators see a clean interface without the restricted elements.

Securing the Login Page Itself

A few important hardening steps for your APEX login page:

Always use HTTPS — Never serve an APEX login page over HTTP in any environment beyond local development
Do not indicate which field is wrong — Return the same generic message for “username not found” and “wrong password.” Specific messages help attackers enumerate valid usernames
Implement account lockout — After N failed attempts, lock the account and require administrator intervention or a reset email
Add CAPTCHA for public applications — APEX supports reCAPTCHA integration on login pages
Set session timeouts — Configure Maximum Session Idle Time and Maximum Session Duration in Application Security settings

APEX Session Management

APEX manages user sessions automatically, but you should understand the key settings:

Session Idle Time — How long a session can be inactive before it expires (typical: 30–60 minutes for business apps)
Maximum Session Duration — Absolute maximum session length regardless of activity (typical: 8–12 hours for business apps, shorter for sensitive systems)
Session State Protection — Prevents tampering with item values via URL. Enable it and set appropriate protection levels on sensitive items

Conclusion

Choosing the right authentication scheme is one of the first architectural decisions in any APEX application. For internal enterprise applications with Active Directory, LDAP is the natural choice. For applications with their own user base, a custom authentication function gives you complete control. For public-facing apps, Social Sign-In removes the burden of password management entirely.

Whatever scheme you choose, pair it with a solid post-authentication procedure, proper authorization schemes, and sensible session security settings. Authentication is your first line of defense — make it count.

Tags: forms-migration oracle-apex

Previous Next