Custom Authentication Scheme Part 2
In Part 1 we showed you how to create your own custom authentication scheme. In this blog we want to add to our previously created custom authentication scheme the functions to change or reset the password.
Let's start with the function to change the password.
Before we can start we need the application from Part 1. If you haven't created it yet please follow the instructions from the first blog (https://tm-apex.blogspot.com/2020/03/custom-authentication-scheme-part1.html). Now we open the application builder of the application and create a new page which we call "CHANGE PASSWORD". In our example it is Page 9998. Change the Template Option to "Login". Then add a new static content region that we call "Change Password" and choose "Login" as Template Style. Next we need 4 Page-Items.
- Name: P9998_USERNAME
- Icon: icon-login-username
- Placeholder: Username
- Name: P9998_OLD_PASSWORD
- Icon: icon-login-password
- Placeholder: Enter Current Password
- Name: P9998_NEW_PASSWORD
- Icon: icon-login-password
- Placeholder: Enter New Password
- Name: P9998_NEW_PASSWORD_2
- Icon: icon-login-password
- Placeholder: Re-type New Password
When we have done this, we add three validations to the items that check the input before we change the password. First validation is to check if the username exists. For this we add a validation to P9998_USERNAME. Choose "Rows returned" as type and enter the following sql query:
SELECT USER_NAME
FROM TB_USERS
WHERE upper(USER_NAME) = UPPER(:P9998_USERNAME)
As error message we can write for example "Username does not exist".
Next we check if the old password is correct. Again add a validation to P9998_OLD_PASSWORD. Choose "PL/SQL Function (returning Error Text)" as type and enter the following pl/sql code:
DECLARE
error varchar2(200) := 'The old password is not correct.';
v_password tb_users.password%type;
BEGIN
select password
into v_password
from tb_users
where upper(user_name) = upper(:P9998_USERNAME);
if ENCRYPT_PASSWORD(upper(:P9998_USERNAME), :P9998_OLD_PASSWORD) != v_password then
return error;
else
return null;
end if;
END;
As a last validation we want to check if the both new passwords are identical. So again we need a validation to P9998_NEW_PASSWORD_2. Choose "PL/SQL Function (returning Error Text)" as type and enter the following pl/sql code:
DECLARE
error varchar2(200) := 'The password does not match.';
BEGIN
if :P9998_NEW_PASSWORD != :P9998_NEW_PASSWORD_2 then
return error;
else
return null;
end if;
END;
At this point you could add several more checks. For example, you could specify the minimum length of a password. Or if it should contain special characters, numbers etc. But for our demo it should be enough for now :-)
Next we need 2 buttons with which we can either start the process for changing the password or cancel the process.
- Name: CHANGE
- Label: Change Password
- Template: Text (Hot)
- Type: Normal
- Action: Submit Page
- Name: CANCEL
- Label: Cancel
- Template: Text
- Type: Primary
- Action: Redirect to Page in this Application
- Page: &APP_RETURN_PAGE.
Once we have done that, we need to add a process that changes the password. Name it for example "Change User Password" and enter the following PL/SQL Code:
Begin
Update tb_users
set password = ENCRYPT_PASSWORD(upper(:P9998_USERNAME), :P9998_NEW_PASSWORD)
WHERE upper(USER_NAME) = upper(:P9998_USERNAME);
end;
Success Message will be for example "The password has been changed.".
Finally add an after process branch to return to the previous page.
Type: Page or URL
Target: &APP_RETURN_PAGE.
To access the page you could add another entry for to the "Desktop Navigation bar".
That´s it :-)
As second topic we want to show you how to add a function to reset the password.
For that we move to the "Login" Page. In our example it will be Page 9999 and add a new Button "RESET" that redirect to Page 9997 in this application. Then we create a new page which we call "RESET PASSWORD". In our example it is Page 9997. Change the Template Option to "Login" and make the page as a public page, because at this point we are not authenticated. Then add a new static content region that we call "Reset Password" and choose "Login" as Template Style. Next we need a Page-Item that we name "P9997_USERNAME".
When we have done this, we add a validation to the page-item to check if the username exists. Choose "Rows returned" as type and enter the following sql query:
SELECT USER_NAME
FROM TB_USERS
WHERE upper(USER_NAME) = UPPER(:P9997_USERNAME)
As error message we can write for example "Username does not exist".
Next we need 2 buttons with which we can either start the process for changing the password or cancel the process.
- Name: RESET
- Label: Reset Password
- Template: Text (Hot)
- Type: Normal
- Width: Stretch
- Action: Submit Page
- Name: CANCEL
- Label: Cancel
- Template: Text
- Type: Normal
- Width: Stretch
- Action: Redirect to Page in this Application
- Page: 9999
Once we have done that, we need to add a process that reset the password. Name it for example "Reset Password" and enter the following PL/SQL Code:
declare
l_new_pw varchar2(80):= dbms_random.string('x',8);
l_username tb_users.user_name%type;
l_email tb_users.email%type;
l_body clob;
begin
select user_name, email
into l_username, l_email
from tb_users
where upper(user_name) = upper(:P9997_USERNAME);
l_body := 'Dear Sir or Madame,'||utl_tcp.crlf||utl_tcp.crlf;
l_body := l_body ||'your password has been reset to ' || l_new_pw ||utl_tcp.crlf;
l_body := l_body ||'Best regards'||utl_tcp.crlf;
l_body := l_body ||'The APEX Dev Team'||utl_tcp.crlf;
apex_mail.send(
p_to => l_email,
p_from => 'noreply@apex.com',
p_body => l_body,
p_subj => 'Reset Password');
apex_mail.push_queue;
update tb_users
set password = ENCRYPT_PASSWORD(upper(:P9997_USERNAME), l_new_pw)
where upper(USER_NAME) = l_username;
end;
Success Message will be for example "Your password has been reset. You will shortly receive an email with a new password".
Finally add an after process branch to return to the previous page.
Type: Page or URL
Target: 9999
All done...now you can change or reset your password by yourself :-)
We hope you enjoyed and you learned something :)
Feel free to ask us if you run into any issue.
