Create Custom Authentication in 5 Easy Steps in Oracle Apex

In this post, I am describing the 5 easy steps to create custom authentication in Oracle Apex.

Custom authentication in Oracle Apex means you can use users created in a table having the fields such as password, active status, first name, last name, email, etc. This kind of authentication is mostly preferred by the developers because they have more control over it. To create custom authentication in Oracle Apex, follow these steps:

Oracle Apex Custom Authentication

Step-1: First, create a table to store user's information as described above. The structure should be as follows:

CREATE TABLE  "APP_USERS" 
   (	"USER_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOT NULL, 
	"USERNAME" VARCHAR2(30) NOT NULL, 
	"PASSWORD" VARCHAR2(100) NOT NULL, 
	"FIRST_NAME" VARCHAR2(20) NOT NULL, 
	"LAST_NAME" VARCHAR2(20) NOT NULL, 
	"EMAIL" VARCHAR2(75) NOT NULL, 
	"STATUS" VARCHAR2(1) NOT NULL,
	"USER_TYPE" VARCHAR2(20) DEFAULT 'ENDUSER', 
	 CONSTRAINT "PK_APP_USERS" PRIMARY KEY ("USER_ID")
  USING INDEX  ENABLE, 
	 UNIQUE ("USERNAME")
  USING INDEX  ENABLE
   )
/

Step-2: Create a view for the above table to get only records having the STATUS is equal to "A". You can assume the status A for active and I for inactive users.

CREATE OR REPLACE FORCE EDITIONABLE VIEW  "VW_APP_USERS" ("USERNAME", "PASSWORD", "FIRST_NAME", "LAST_NAME", "EMAIL") AS 
  select username, 
        password, 
        first_name, 
        last_name, 
        email 
 from   app_users 
 where  status = 'A'
/

Step-3: Now in Oracle Apex, click on the Shared Components > Application Items and create the following items:

Step-4: Then create a database package to authenticate the users, as shown below:

create or replace package pkg_auth_users
 as 
 
  function fnc_user_auth(p_username in varchar2, 
                        p_password in varchar2) return boolean; 
 
end pkg_auth_users;

create or replace package body pkg_auth_users 
 as 
  
  function fnc_user_auth(p_username in varchar2, 
                        p_password in varchar2) return boolean is 
   
    l_username   vw_app_users.username%type; 
    l_first_name vw_app_users.first_name%type; 
    l_last_name  vw_app_users.last_name%type; 
    l_email      vw_app_users.email%type; 
     
  begin 
     
    select username, 
           first_name, 
           last_name, 
           email 
    into   l_username, 
           l_first_name, 
           l_last_name, 
           l_email 
    from   vw_app_users
    where  upper (username) = upper (p_username) 
    and    password = p_password; 
     
     
    apex_util.set_session_state(p_name  => 'SESSION_USERNAME', 
                                p_value => l_username); 
    apex_util.set_session_state(p_name  => 'SESSION_FIRST_NAME', 
                                p_value => l_first_name); 
    apex_util.set_session_state(p_name  => 'SESSION_LAST_NAME', 
                                p_value => l_last_name); 
    apex_util.set_session_state(p_name  => 'SESSION_EMAIL', 
                                p_value => l_email); 
     
    return true; 
     
  exception 
    when no_data_found then 
      return false; 
     
  end fnc_user_auth; 
  
end pkg_auth_users;