In this post we will see how to create a custom authentication scheme in Oracle Application Express (APEX) based on a database table. For this, we will create a user table and a function that will be responsible for validating users who may or may not have access to the system.
Note: In this article we will not focus on the security part, password encryption, etc. This article aims to show in a simple way the creation of a custom authentication scheme.
Description
To build our authentication scheme, we will have a table of users who may have "Active" or "Disabled" satus. After creating the user table, as well as the status table - following the 3 NF- we will create a package that contains the function that will perform the validation of access data. This simple structure is essential to the creation of our authentication scheme and its complexity may vary according to the needs of the business.
The table structure will follow the pattern seen below.
P.S. Although using the "password" column as a text field, it is highly recommended - and good practice - to use an encrypted password field. In this example we are simply demonstrating how to create a custom authentication schema in APEX. Always use encrypted passwords on your production systems.
Execution
Now, we'll see step-by-step, from the creation of the tables until the creation of the custom authentication scheme.
1. We begin by creating the tables that will compose the user data: User table and user status.
create table status_user
(
status_id number generated as identity nocache,
status varchar2(30) not null unique,
code number(2) not null unique,
constraint pk_status_user primary key (status_id)
);create table app_user
(
user_id number generated as identity nocache,
username varchar2(30) not null unique,
password varchar2(20) not null,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
email varchar2(75) not null unique,
status_id number not null,
constraint pk_app_user primary key (user_id),
constraint fk_status_user foreign key (status_id) references status_user
(status_id),
constraint ck_mail check(regexp_like(email, '^(\S+)\@(\S+)\.(\S+)$'))
);2. Once the tables have been created, we insert two records into the status table.
insert into status_user (status, code) values ('Active', 10);
insert into status_user (status, code) values ('Disabled', 20);Note that we have defined the status "Active" with code 10 and the status "Disabled" as code 20.
3. To leave the function that will validate the user name, the password and whether the user is active or not, more organized, we create a view that only contains the users with status "Active". So, whenever the function queries a user in the view and does not find it, they will know that the user is in "Disabled" status or does not exist, not allowing their access to the system.
create or replace view vw_app_user
as
select username,
password,
first_name,
last_name
email
from app_user
where status_id = (select status_id
from status_user
where code = 10)4. Before creating our package, we will create some items in the application scope to receive the user data in the oracle APEX session.
Shared Components > Application Items > Create / Edit
Set the scope to Application and the session state protection as Restricted - May not be set from browser for each item we will create. The items are:
5. Now, we have created the package header that contains the definition of the authentication function that returns a boolean value (true or false).
create or replace package pck_user_tools
as
function fc_user_auth(p_username in varchar2,
p_password in varchar2) return boolean;
end pck_user_tools;Soon after, we created the body of the package.
create or replace package body pck_user_tools
as
function fc_user_auth(p_username in varchar2,
p_password in varchar2) return boolean is
l_username vw_app_user.username%type;
l_first_name vw_app_user.first_name%type;
l_last_name vw_app_user.last_name%type;
l_email vw_app_user.email%type;
begin
select username,
first_name,
last_name
email
into l_username,
l_first_name,
l_last_name,
l_email
from vw_app_user
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 fc_user_auth;
end pck_user_tools;Note: To set session state for a current APEX session, we use the SET_SESSION_STATE procedure of the APEX_UTIL package.
To learn more about the APEX_UTIL package and the SET_SESSION_STATE procedure, go to: Application Express API Reference
6. Finally, we will create our custom authentication scheme. To create the authentication scheme go to Shared Components > Authentication Schemes and click Create.
7. On the screen that will open, select the Based on a pre-configured scheme from the gallery option and click Next.
8. On the next screen, enter a name of your choice for the new authentication scheme
and in Schema Type select Custom. After you select the schema type, under Settings, inform the package and function in Authentication Function Name and click the Create Authentication Scheme button.
The schema will be created and set as default.
Note: If you want, you can delete the Application Express Accounts authentication scheme because it will no longer be necessary.
In practice...
To see how our authentication scheme works, go to the application Learning APEX App in the Users menu and enjoy!
Demo Application
Link: Learning APEX App
User (default): demo
Password: demo