Creating a Custom Authentication Schema [APEX 18.1]

Updated: Dec 3, 2018

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.


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.


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 
 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
 select username,
 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
  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
  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;
    select username,
    into   l_username,
    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;
    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

Never Miss a Post. Subscribe Now!

Happy is the man that findeth wisdom, and the man that getteth understanding. (Proverbs 3:13)

Lucas Rayner © 2018 

  • Branco Twitter Ícone
  • Branca Ícone LinkedIn