Apex Custom Authentication.txt

The code needed for creating a custom authentication scheme in oracle apex.

Auth.pls

create or replace FUNCTION AUTH 
  (p_username in varchar2,  
   p_password in varchar2) 
return boolean 
IS 

  l_e_password              varchar2(255); 
  l_user_exist              number; 
  l_user_name               varchar2(255) := upper(p_username);
  l_password                varchar2(255);
  
BEGIN 

    --check if the user exist in the user's table
    select count(*)  
      into l_user_exist  
      from tb_users 
      where user_name = l_user_name; 
      
     --if the user exist
     if l_user_exist > 0 then 
     
          -- encrypt the password which has been recieved
          l_e_password := encrypt_password(l_user_name, p_password); 
          
          -- retrive the user's password
          select password  
            into l_password  
            from tb_users  
           where user_name = l_user_name; 
           
          --compare the user's password with the encrypted password
          
          --if the passwords match return true, otherwise return false
          if l_e_password = l_password then 
              return true; 
          else 
              return false; 
          end if; 
          
     --if the user does not exist in the user's table return false
     else 
          return false; 
     end if; 
     
     exception when others then
        return false;
end AUTH;

beforeInsert.pls

CREATE OR REPLACE TRIGGER  "T_BI_USERS" 
  BEFORE INSERT ON TB_USERS
  for each row 
BEGIN 
  :new.user_name := upper(:new.user_name); 
  :new.email := lower(:new.email); 
  :new.password := ENCRYPT_PASSWORD(upper(:new.user_name), :new.password); 
END; 

beforeUpdate.pls

CREATE OR REPLACE TRIGGER  "T_BU_USERS" 
  BEFORE UPDATE ON TB_USERS 
  for each row 
BEGIN 
  :new.user_name := upper(:new.user_name); 
  :new.password := ENCRYPT_PASSWORD(upper(:new.user_name), :new.password); 
END;

encrypt_password.pls

CREATE OR REPLACE FUNCTION ENCRYPT_PASSWORD
  (p_username in varchar2,
   p_password  in varchar2)
return varchar2
is
  l_password varchar2(255);
  l_salt  varchar2(30) := 'RANDOM_SALT_STRING';
begin

    l_password := 
     DBMS_OBFUSCATION_TOOLKIT.MD5(
        input_string => UPPER(p_username) || l_salt || UPPER(p_password));
        
    return l_password;
end ENCRYPT_PASSWORD;

insertUser.sql

Insert into TB_USERS (USER_NAME,PASSWORD,EMAIL) values ('ADMIN','admin', 'admin@email.com');

UserTable.sql

CREATE TABLE  "TB_USERS" 
( "USER_ID" NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1), 
  "USER_NAME" varchar2(255) NOT NULL ENABLE, 
  "PASSWORD"  varchar2(255) NOT NULL ENABLE,
  "EMAIL"  varchar2(255) NOT NULL ENABLE,
  PRIMARY KEY ("USER_ID") USING INDEX  ENABLE, 
  CONSTRAINT "USERS_U1" UNIQUE ("USER_NAME") USING INDEX  ENABLE
);

Thank u Mohamad