Custom Authentication Scheme

Introduction
Oracle APEX provides flexible options for securing your applications, including the ability to create custom authentication schemes. Custom authentication lets you implement your own login logic, integrating with external systems, databases, or specific business rules. This approach is useful when preconfigured schemes do not meet your requirements or you need a tailored security solution.

How to Create a Custom Authentication Scheme in Oracle APEX

1. Open Your Application
   Log in to Oracle APEX and open the application where you want to create the custom authentication scheme.

2. Navigate to Authentication Schemes
   Go to Shared Components > Authentication Schemes under the Security section.

3. Create a New Authentication Scheme
   Click Create, then select Custom as the authentication scheme type.

4. Define Your Authentication Logic

   • Provide a name for the scheme (e.g., Custom_DB_Auth).

   • Write a PL/SQL function that validates user credentials. This function should return TRUE if authentication is successful and FALSE otherwise.

   • Example PL/SQL snippet:

   DECLARE
     l_count NUMBER;
   BEGIN
     SELECT COUNT(*) INTO l_count
     FROM my_users
     WHERE username = :USERNAME
       AND password = :PASSWORD;
     RETURN l_count = 1;
   END;
   

   Replace my_users with your user table and implement proper password hashing as needed.

5. Configure Login and Logout URLs
   Set URLs or pages for login and logout if you want custom behavior beyond default APEX pages.

6. Make the Scheme Current
   Save the scheme and select Make Current to activate it for your application.

7. Test the Authentication Scheme
   Run the application and verify the login process works as expected with your custom logic.

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.

1. 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.

Best Practices

• Always hash and salt passwords securely; never store them in plain text.

• Keep your authentication function efficient to avoid slowing down user login.

• Validate inputs to prevent SQL injection or other security vulnerabilities.

• Document your custom authentication scheme clearly for future maintenance.

• Thoroughly test all login and logout scenarios before deploying to production.

Oracle APEX Documentation
For detailed guidance on creating custom authentication schemes, visit:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/custom-authentication-schemes.html

Conclusion
Creating a custom authentication scheme in Oracle APEX offers powerful control over user authentication, allowing you to implement specific security policies and integrate with diverse systems. By following best practices and carefully testing your scheme, you can ensure a secure and seamless login experience tailored to your application's needs.