Friday, August 28, 2020

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.


Labels: , ,

2 Comments:

At August 28, 2020 at 2:12 PM , Blogger Shyam said...

Great Information with examples. After change password if email send to user will be more useful. kindly add this part in this blogs.

 
At July 18, 2021 at 9:43 AM , Blogger mac said...

Hi all, thanks so much for the information. I am new to APEX and PLSQL and relatively new to SQL so apologies if I ask a dumb question.

I have implemented the login which works great! I have put in place the pieces for the Change Password form but I am experiencing an error which I cannot fix. In every instance I am receiving the error "Old password is incorrect". I know for a fact that the password is correct as I sign out and back in before trying to make the change.

To try to fix the issue I have copied the login process part which checks the password replacing your original code. It fails as well. I tried changing the code to turn the password checker into a function that returns a boolean but still no luck.

I have since changed the code back to your example. No change.

I would appreciate any advice you might have for me.

Thanks.

Brett

 

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to Post Comments [Atom]

<< Home