How to create Custom Authentication in Oracle APEX

Authentication is the first line of defense in any application, and in Oracle APEX, it plays a vital role in controlling how users access your system. While APEX provides several built-in authentication schemes, there are scenarios where the default options may not fully align with your business requirements. That’s when Custom Authentication becomes essential.

In this blogpost, I’ll walk you through the step-by-step process of creating a Custom Authentication scheme in Oracle APEX—so you can design a secure, flexible, and application-specific login mechanism tailored exactly to your needs. If you need any visual assistance then you can follow this tutorial.

STEP- 1: 

Create a database table (if not exist) to store application user information. In my case I use this table structure to store user information. You may add/drop column as per your requirements.

    CREATE TABLE APP_USER 

       (    USER_SL NUMBER, 

    USER_NAME VARCHAR2(20), 

    PASS_WORD VARCHAR2(3000), 

    REF_ID NUMBER, 

    USER_TYPE VARCHAR2(10), 

    CREATED_BY NUMBER, 

    CREATION_DATE DATE DEFAULT sysdate, 

    LAST_UPDATE_BY NUMBER, 

    LAST_UPDATED_DATE DATE, 

    USER_ID NUMBER

       ) ;

STEP- 2:

Login you application as developer mode. Open application builder home page and click on the "Shared Components" large button. It will redirct to shared component page. Click on the "Authentication Schemes" link under the "security" section. You will redirect to authentication schemes report page. Click on the "Create" button. Authentication create page will be open. Choose the "Based on a pre-configured scheme from the galary" option from "Create Scheme" type.

Click "Next" button. It will redirect to another page. Set some required properties as shown below-

    Name:                CUSTOM_LOGIN

    Scheme Type:    Custom (Select from drop down list)

    Authentication Function Name: custom_login

    PL/SQL Code:

                            FUNCTION login(

                                p_username      IN  VARCHAR2

                                , p_password    IN  VARCHAR2

                            ) RETURN BOOLEAN

                            IS

                                l_count     NUMBER;

                            BEGIN

                                SELECT COUNT(*)

                                INTO l_count

                                FROM app_user

                                WHERE UPPER(user_name) = UPPER(p_username)

                                AND pass_word        = p_password;

    

                                IF l_count > 0 THEN

                                    RETURN TRUE;

                                ELSE

                                    RETURN FALSE;

                                END IF;

                            END;

                            /

Validate the code and click on the "Create Authentication Scheme" button. You will redirect to the "Authentication Schemes" report page. Here you will see your newly created custom authentication.

STEP- 3:

Click on your custom authentication. You will redirect to authentication scheme edit page. Click on the "Make Current Scheme" button. A popup alert showing you a confirmation message. Click "OK". 

STEP- 4:

Open page no. 9999 (Login Page). Navigate to process tab and select "Login" process.

Change it's properties as like below-

Type:     Execute Code

Location:    Local Database 

Language: PL/SQL

PL/SQL Code: 

                        apex_authentication.login(

                            p_username      => :P9999_USERNAME

        , p_password    => apex_util.get_hash(apex_t_varchar2(:P9999_USERNAME, :P9999_PASSWORD), NULL)

                        );

Validate the code and click "OK".  Save the page and run it. Login with your custom user and enjoy the custom authentication process. 

STEP- 5 (Optional):

This step is not mandatory but it's have a great impact if you implement it. Remove the PL/SQL code from step 2 and create the function in database level. The benefit is to create in database level is- you can call it from many application as per your need. Just open the SQL Editor and paste the below code-

                            CREATE OR REPLACE FUNCTION login(

                                p_username      IN  VARCHAR2

                                , p_password    IN  VARCHAR2

                            ) RETURN BOOLEAN

                            IS

                                l_count     NUMBER;

                            BEGIN

                                SELECT COUNT(*)

                                INTO l_count

                                FROM app_user

                                WHERE UPPER(user_name) = UPPER(p_username)

                                AND pass_word        = p_password;

    

                                IF l_count > 0 THEN

                                    RETURN TRUE;

                                ELSE

                                    RETURN FALSE;

                                END IF;

                            END;

                            /

Compile the code. That's it. Implementing Custom Authentication in Oracle APEX gives you the flexibility to design a login process that goes beyond the built-in options. By tailoring authentication to your specific business rules, you can ensure both security and user convenience.

With the steps outlined in this guide, you now have a clear path to build and manage your authentication logic whether it’s integrating with existing systems, validating against custom tables, or enforcing unique business rules. APEX empowers you to take full control of the authentication process, resulting in applications that are not only secure but also perfectly aligned with organizational needs.