Introduction
Custom authentication in Oracle APEX gives developers full control over how users are validated before accessing an application. Unlike preconfigured schemes, a custom authentication approach allows integration with proprietary databases, external identity providers, API-based services, or unique login logic tailored to business rules. This level of flexibility is essential for applications that require more than just standard username and password validation or need to comply with specialized security protocols.
Custom authentication in Oracle APEX allows developers to define exactly how user credentials are verified when someone tries to access an application. This is essential when default methods such as APEX accounts, LDAP, or social sign-in do not meet specific business or security requirements. Implementing custom authentication involves writing your own logic, usually in PL/SQL, and configuring APEX to use this logic instead of the default mechanisms.
To start, go to Shared Components > Authentication Schemes, then click Create and choose From Scratch. Select Custom as the scheme type. Give the scheme a clear name, such as “Custom Auth Logic,” and set it as the current scheme. This tells APEX to use your logic during login.
Within the custom authentication scheme, you'll need to provide a PL/SQL function that returns a Boolean value. This function is where you define how the user is authenticated. A simple example might look like this:
RETURN my_auth_pkg.verify_credentials(:USERNAME, :PASSWORD);
In the database, create a package called my_auth_pkg with the verify_credentials function:
CREATE OR REPLACE PACKAGE BODY my_auth_pkg AS
FUNCTION verify_credentials(p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN IS
v_pwd users.password%TYPE;
BEGIN
SELECT password INTO v_pwd
FROM users
WHERE username = UPPER(p_username);
IF v_pwd = hash_util.hash(p_password) THEN
APEX_UTIL.SET_AUTHENTICATION_RESULT(0);
RETURN TRUE;
ELSE
APEX_UTIL.SET_AUTHENTICATION_RESULT(1);
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APEX_UTIL.SET_AUTHENTICATION_RESULT(1);
RETURN FALSE;
END;
END my_auth_pkg;
You can also add features like account lockouts, audit logging, or IP filtering inside this function to meet security policies.
Next, customize the login page. Add page items for username and password. The Login button should execute a PL/SQL process that runs the custom logic and either redirects to the home page or displays an error message.
Here is a sample process:
BEGIN
IF my_auth_pkg.verify_credentials(:P101_USERNAME, :P101_PASSWORD) THEN
APEX_AUTHENTICATION.POST_LOGIN(:P101_USERNAME);
ELSE
APEX_ERROR.ADD_ERROR(
p_message => 'Invalid credentials',
p_display_location => apex_error.c_inline_in_notification);
END IF;
END;
Set this process to run before header. Also make sure that on failed login attempts, the user stays on the login page and sees a useful error message.
You can use APEX_AUTHENTICATION.POST_LOGIN to complete the login programmatically after successful verification. This function sets up the session and redirects the user to the appropriate start page.
For logout handling, set a logout URL in your authentication scheme that points to a custom page or logs out the session and redirects the user.
Custom authentication in Oracle APEX is powerful because it gives you total control over who is allowed in and how login is handled. It works seamlessly with APEX session management and can be enhanced to support multi-factor authentication, REST-based verification, external token checks, or any other logic needed to secure your application.
Additional explanation
Creating a Custom Authentication Scheme in Oracle APEX gives you complete control over the authentication process, session management, and security policies. This method is ideal when built-in authentication methods (such as database authentication or LDAP) do not meet your requirements.
By implementing custom authentication, you can:
Build a custom login interface.
Define security policies for user sessions.
Audit and track user activity.
Integrate APEX with external applications using a common authentication mechanism.
Why Use Custom Authentication?
Custom authentication is the best choice when:
Built-in authentication methods (database, LDAP, SAML) are not sufficient.
You need a custom login form with additional validation logic.
Advanced security features are required (e.g., multi-factor authentication (MFA), session tracking, or login throttling).
Session expiration and activity tracking need to be customized.
Your application consists of multiple applications that need to share the same authentication session.
You need one-way redirection logic before page processing (e.g., redirecting users based on roles or login time).
Your APEX application must integrate with non-APEX systems using a shared authentication framework.
How to Implement Custom Authentication in Oracle APEX
1. Create a Custom Authentication Scheme
Navigate to Authentication Schemes
Open App Builder > Select your application.
Go to Shared Components > Click Authentication Schemes.
Click Create.
Choose Authentication Method
Select "Based on a preconfigured scheme from the gallery" > Choose Custom.
Enter Authentication Function
Under PL/SQL Function Returning Boolean, enter a function that validates user credentials.
2. Create a Custom PL/SQL Authentication Function
In SQL Workshop, create a PL/SQL function that verifies user credentials against a custom user table.
CREATE OR REPLACE FUNCTION custom_authentication (
p_username IN VARCHAR2,
p_password IN VARCHAR2
) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM users
WHERE username = LOWER(p_username)
AND password = UPPER(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => p_password)); -- Example: Hashing passwords
RETURN v_count = 1;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END custom_authentication;
/
This function checks if the provided username and password exist in the users table.
It uses MD5 hashing for password security (use SHA-256 or bcrypt for better security).
3. Configure Custom Authentication Scheme in APEX
Under PL/SQL Function Returning Boolean, enter:
return custom_authentication(:P101_USERNAME, :P101_PASSWORD);
Click Apply Changes to save.
4. Create a Custom Login Page
1️. Create a Login Page
Navigate to App Builder > Click Create Page.
Select Login Page > Choose Blank Page.
Add two Text Items:
P101_USERNAME (for username)
P101_PASSWORD (for password)
Add a Login Button and set its action to Submit Page.
Process Authentication on Login
Go to Processing > Create a new process:
Name: Authenticate User
Type: PL/SQL Code
Code:
IF custom_authentication(:P101_USERNAME, :P101_PASSWORD) THEN
APEX_UTIL.SET_SESSION_STATE('APP_USER', :P101_USERNAME);
APEX_AUTHENTICATION.LOGIN(p_username => :P101_USERNAME);
ELSE
APEX_UTIL.SET_SESSION_STATE('LOGIN_FAILED', 'Y');
RAISE_APPLICATION_ERROR(-20001, 'Invalid Username or Password');
END IF;
If authentication succeeds, the user is logged in.
If authentication fails, an error message is displayed.
5. Customizing Session Management & Security
Session Timeout: Set session expiration policies in Security Attributes under Shared Components.
Session Tracking: Store login activity in a custom table:
CREATE TABLE login_audit (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username VARCHAR2(50),
login_time TIMESTAMP DEFAULT SYSTIMESTAMP,
ip_address VARCHAR2(50)
);
Log user login details:
INSERT INTO login_audit (username, ip_address)
VALUES (:APP_USER, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
COMMIT;
Custom authentication in Oracle APEX gives you full control over login, session management, and security policies. It allows you to integrate with custom user repositories, external applications, and enforce advanced security measures. By using PL/SQL functions and session management techniques, you can build a secure and flexible authentication system tailored to your application's needs.
Conclusion
Becoming proficient in custom authentication within Oracle APEX unlocks powerful capabilities for building secure, user-aware applications. By designing your own logic using PL/SQL, dynamic actions, or REST integrations, you can ensure that authentication aligns precisely with your application’s needs. While it requires more effort than using built-in schemes, custom authentication offers unmatched adaptability for complex environments.
