Oracle Application Express – End User Change Password Procedure

We have several applications built using Oracle Application Express, and are accessed by several end users. The Authentication mechanism that we have adopted is – “Database Account” at the global level and within each application, the access mode is set to “Restricted Access” and the users are listed within the application who needs access to it. So every time a new user needs an access, or when the user forgets their password we had to reset the password and allow the user to change the password from the application.

The procedure that I have adopted is split into 3 stages as detailed below.


STAGE 1 - Database Activities

• For the end user to change their password they need “ALTER USER” privilege. We have created roles to control various privileges to the Application Express users and then granted the “ALTER USER” privilege to the role, the role is then assigned to the Application Express End Users.
• Create a PLSQL procedure that performs the password validation & changing passwords for the End users.
  The PLSQL procedure is defined as below –

CREATE OR REPLACE PACKAGE BODY BCFHPTL.APEX_UTILS_PKG
AS
PROCEDURE CHANGE_DB_PASSWORD_APEX (in_user IN VARCHAR2,
in_cur_pwd IN VARCHAR2,
in_new_pwd IN VARCHAR2)
AS
passwords_mismatch exception;
l_proc_name VARCHAR2 (200)
:= 'APEX_UTILS_PKG.CHANGE_DB_PASSWORD_APEX' ;
l_cur_db_pwd VARCHAR2 (200) DEFAULT NULL ;
l_new_db_pwd VARCHAR2 (200) DEFAULT NULL ;

BEGIN
--dbms_output.put_line ('1 'in_user'-'in_new_pwd'-'in_cur_pwd);
IF (in_user IS NOT NULL AND in_cur_pwd IS NOT NULL AND in_new_pwd IS NOT NULL )
THEN
-- dbms_output.put_line ('2 'in_user'-'in_new_pwd'-'in_cur_pwd);
-- Get the current password value from database
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_cur_db_pwd
USING in_user;
--dbms_output.put_line('Current db password is 'l_cur_db_pwd);
-- Change password as specified by user for "Existing Password"
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_cur_pwd
' ACCOUNT UNLOCK ';

-- Retrieve the password value for Existing password
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_new_db_pwd
USING in_user;
--dbms_output.put_line('Old password specified value is 'l_new_db_pwd);
IF l_cur_db_pwd != l_new_db_pwd
THEN
RAISE passwords_mismatch;
ELSE
-- dbms_output.put_line (in_user'-'in_new_pwd);
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_new_pwd
' ACCOUNT UNLOCK ';
END IF;
ELSE
RAISE_APPLICATION_ERROR (
-20001,
'Error in ' l_proc_name ' - ' 'Username, Old Password, New Password and Confirm Password Must be Present'
);
END IF;
EXCEPTION
WHEN passwords_mismatch then
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY VALUES '''
l_cur_db_pwd
''' ACCOUNT UNLOCK ';
RAISE_APPLICATION_ERROR (
-20005,
'Error in ' l_proc_name 'Old Password specified does not match the value in Database'
);
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (
-20006,
'Error in ' l_proc_name SUBSTR (SQLERRM, 1, 132)
);
END CHANGE_DB_PASSWORD_APEX;
END APEX_UTILS_PKG;
/

STAGE 2 - Oracle Application Exporess
Create a global application, which accepts the username, current password ( which will be a default password from DBA’s) , new password , confirm new password. This application then executes a PL/SQL block to change the password.
First created an application which looks like this –

Create a new application with just a login page in it.
Inside the application, the following are defined in the Login Page.
ITEMS

• P101_USERNAME - capture UserName


• P101_X_OLD_PWD - capture Old Password declared as “Password”


• P101_X_NEW_PWD - capture New Password declared as “Password”


• P101_X_CNF_PWD - Capture Confirm New Password.

BUTTONS
Two buttons are added.

• CANCEL - Close this application and redirects to the application from which the “Change Password” application has been called from.


• SUBMIT - Submits the application and alters the User Password.

The details are as shown below.


VALIDATIONS
A validation process has been created to verify whether the values specified in the fields P101_X_NEW_PWD and P101_X_CNF_PWD are same, if not throws an error message.
The PLSQL block contains the following code to validate the inputs by user-

BEGIN
IF :p101_x_new_password = :p101_x_confirm_password
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;If the above routine throws an error then the following message will be displayed –Both New and confirm Password fields must match. The details of the validation process are as below –



PROCESSES
An “after Submit” process has been created which calls a database procedure to change the passwords.
This process has the following PLSQL block –

The implementation of the validation process is as shown below -