In this post, we will see how to create custom authentication scheme in Oracle APEX.

Custom authentication enables users to log in to APEX application with username and password stored in custom database table. This will provide complete control over the authentication interface.

APEX application will validate username and password against data stored in database table and provide access to user.

Let us create custom authentication scheme in oracle APEX step by step:

Step1===> Create Database table to store user credentials including username and password.

Use below table creation script to create table.

CREATE TABLE XX_APEX_CUSTOM_USERS (
    USER_ID      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USERNAME     VARCHAR2(100) UNIQUE NOT NULL,
    PASSWORD     VARCHAR2(255) NOT NULL,
    IS_ACTIVE    CHAR(1) DEFAULT 'Y',
    FULL_NAME    VARCHAR2(200)
)

Navigate to SQL workshop —->SQL Commands

Copy and paste above table creation script under SQL commands and click on run.

Table is created.

Create Function to encrypt password using SHA-256 algorithm

CREATE OR REPLACE FUNCTION xx_hash_password (
    p_password IN VARCHAR2
) RETURN VARCHAR2 IS
    l_hash_raw RAW(32);
BEGIN
    l_hash_raw := dbms_crypto.hash(
        utl_i18n.string_to_raw(p_password, 'AL32UTF8'),
        dbms_crypto.hash_sh256
    );
    RETURN lower(rawtohex(l_hash_raw));
END;

If we are getting this error, then our schema doesn’t have access to DBMS_CRYPTO package.

Login with SYS user and provide grant to APEX schema using below command:

GRANT EXECUTE ON DBMS_CRYPTO TO your_schema;

Now try again once access is granted.

Insert some sample data for users with username and password.

BEGIN
    INSERT INTO xx_apex_custom_users (
        username,
        password,
        is_active,
        full_name
    ) VALUES ( 'admin',
               xx_hash_password('admin123'),
               'Y',
               'Admin User' );
    INSERT INTO xx_apex_custom_users (
        username,
        password,
        is_active,
        full_name
    ) VALUES ( 'john',
               xx_hash_password('john@2024'),
               'Y',
               'John Doe' );
    INSERT INTO xx_apex_custom_users (
        username,
        password,
        is_active,
        full_name
    ) VALUES ( 'jane',
               xx_hash_password('jane@2024'),
               'Y',
               'Jane Smith' );
END;

Step2===> Create New Authentication Scheme with custom type.

Navigate to Shared components—>Security—>Authentication scheme

Click on Create-to-create new authentication scheme.

Click on Next.

Provide valid authentication scheme name and scheme type as Custom.

Create PLSQL function with the below script:

    FUNCTION xx_custom_auth_scheme (
        p_username IN VARCHAR2,
        p_password IN VARCHAR2
    ) RETURN BOOLEAN IS
        l_stored_password VARCHAR2(100);
    BEGIN
        SELECT
            password
        INTO l_stored_password
        FROM
            xx_apex_custom_users
        WHERE
            upper(username) = upper(p_username);
        RETURN l_stored_password = xx_hash_password(p_password);
    EXCEPTION
        WHEN no_data_found THEN
            RETURN FALSE;
    END;

Provide same function name for Authentication Function Name field as shown below:

Click on the Create Authentication scheme.

Once authentication is created, we need to make an authentication scheme as current authentication scheme.

Open XX_CUSTOM_AUTH_SCHEME and click on “Make Current Scheme”.

Click on OK.

Step3: Run the application.

Provide username as “john” and password as “john@2024” which we have inserted into table earlier.

Click on Sign in.

Here we can see, application is logged in with john.