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.