How to setup Custom Authentication in Oracle APEX

Hi,
Recently on ODTUG mailing list one fellow asked about using custom authentication schemes in APEX. i thought it would be good idea for my blog post :)

In this video i will demonstrate how to create login page which authenticates username and passwords from database tables in Oracle APEX.

Note: The purpose of this post is just to show the setting up custom authentication therefore i have used a very simple db table with passwords stored as text.

In real world this password approach in not recommended you must store passwords in encrypted columns or use any procedure to convert passwords into encrypted form.

Database structure

 create table my_users
(user_name varchar2(10),
 user_pwd varchar2(10));

insert into my_users
values('Tom','Secret');

insert into my_users
values('Baig','oracle');

commit;

Authentication Function

create or replace function 
validate_user_from_db (p_username in varchar2, p_password in varchar2)
return boolean
as
    v_pw_check varchar2(1);
begin
    select 'x'
    into v_pw_check
    from my_users
    where upper(user_name) = upper(p_username)
    and user_pwd = p_password;
    
    apex_util.set_authentication_result(0);
    return true;
exception when no_data_found then
    apex_util.set_authentication_result(4);
    return false;
end validate_user_from_db;

Authentication messages

0: Normal, successful authentication
1: Unknown User Name
2: Account Locked
3: Account Expired
4: Incorrect Password
5: Password First Use
6: Maximum Login Attempts Exceeded
7: Unknown Internal Error

A little code to test our authentication function( This function for only test in SQL prompt)

declare
 vresult varchar2(10);
begin 
 if validate_user_from_db ('baig','oracle') then 
    dbms_output.put_line('OK');
 else
     dbms_output.put_line('ERROR');
 end if;
end;

And finally a video tutorial steps



Happy learning,
Zeeshan Baig