Introduction
In this post, we will walk through creating a Custom Authentication Scheme in Apex, what are the advantages of using a custom Authentication scheme and when to use the default scheme which is created by default with each new application.
Background
When you create a new Apex application, it comes with a default Authentication scheme. This authentication scheme used to establish the identity of each user who accesses your application, the default authentication scheme requires that a user has an Apex account (Application Express Accounts are user accounts that are created within and managed in the Oracle Application Express user repository. Those accounts can be created by workspace admins in the apex workspace panel or using plsql API).
The user then should provide his user name and password if he wants to use your application, these credentials are evaluated and they either pass or fail, if the credentials pass, the user has access to the application. Otherwise, access is denied.
You use this authentication scheme and the Application Express Accounts if your applications do not require special requirements and if the users' accounts can only be created or managed by the workspace admins.
On the other hand, if you, for example, want your user's account to have more attributes or if you want to implement registration/reset password functionality into your applications to give the public-users who have an account the ability to reset the passwords or even create a new account. Then it is much better to use a custom authentication scheme and custom user account based on your requirements. In this case, you will have full control over the authentication scheme and the user's table, and you are responsible for defining the user's role, handling password strength/expiration.
Another advantage of using a custom Scheme based on custom users table is when you have many workspaces, then your users will be shared across all the workspaces.
When you create an authentication scheme, you have the option of choosing from several pre-configured authentication schemes, copying an authentication scheme from an existing application, or creating your own custom authentication scheme.
In this article, we will create one from scratch. But before that, we have to create a users' table where we will save the accounts information.
Let´s start
In this example, we will just implement a username and email in addition to the password.
Create Database objects
For creating the table you can use the code below
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
);
Now we want to add a user to the users' table, before that we want to draw your attention that we should not save the user's password in plain text, it is not recommended for security and privacy reasons. So we will hash the password and save it in hashed form, to do this we have two options, we can hash the password in the Apex application before the page is been submitted when we create a user or when the user registers, in this approach we have to include this process in all of our applications. The second option is to create a trigger that will be triggered automatically each time we insert a new user into the users' table. Because the trigger is based on the table, we don't have to rewrite the code each time we create an application in apex.
Later when we implement update user password functionality (In part2), we will need to hash the password again (when a user changes his password). So let's make the encryption reusable by creating a function and call it anywhere we need it.
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;
Notice the upper and the lower functions, we want to standardize our data so all the user names are in upper case and the emails are in lower case.
Now we create a trigger which runs before insert and calls the encryption function
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;
let's insert the user now
Insert into TB_USERS (USER_NAME,PASSWORD,EMAIL) values ('ADMIN','admin', 'admin@email.com');
Once we have created the table we can now start creating the authentication functions. The authentication function accepts two parameters username and password. It will first check if the user exists in the users' table. If the user does not exist in the user's table, the function will return false and the login is denied otherwise if the user exists, it will compare the user's saved password with the one which the function has received if they match, the function returns true and the user will log in otherwise it returns false and the login is denied.
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;
We almost have done with building the backend, well the plsql part, and we will now start implementing what we did in Apex.
Create a new Application
The first step is to create a new Application, we will name it Custom Auth
Next, go to SQL Workshop and navigate to SQL Scripts
Click on Create and paste the "create users table" script, give it a name then click Run (we can actually upload a script file which includes all our code and run it, but we want to create everything step by step)
After you see the success message, go back to SQL scripts and create a new script, paste the "encryption function" script give it a name and click run
Now to create the trigger and inserting the user we will create a new script, just do the same steps and paste the "create trigger" code and the "insert user" code
You should now see one user in your users' table
Again go to SQL scripts and run "create Auth function" script
Now navigate to the application we have created, go to shared components then go to Authentication schemes
Click on Create button it will open a wizard for creating an Authentication scheme, in the first step choose the option "based on the pre-configured scheme", click next
in the next step, give the Authentication scheme a name, and choose the scheme type custom it will show now many fields, in the Authentication Function Name field write your Auth function which you have created and click Create
Now run your application and log in using the user which we have inserted in our users' table if everything were successfully you will be redirected to the Home page.
Conclusion
so that's it, we hope you enjoyed and you learned something :), feel free to ask us if you run into any issue.
In the next post, we will implement the remember me functionality so our users will authenticate themself just once and if they close the browser and later open it again, they will automatically log in, and we will create a page where our users can register or update/reset there password.