Andrew Fraser DBA

Database Password Changes from users web page

This is a Pl/sql wrapper around “alter user” to allow front end interfaces (Servicedesk, Javascript or PHP web pages, etc.) to safely change passwords.

CREATE OR REPLACE PROCEDURE sys.php_reset_passwords
/***************************
|| Name : sys.php_reset_passwords
|| Author : Andrew Fraser
|| Date : 05-Apr-2012
|| Purpose : Allow users to reset their database passwords from a php web page
|| Parameters :
||     p_username - the AD username of the user - case INsensitive
||     p_new_password - the new password that the user has typed into web page - case SENSITIVE
||         Validation rules on that password are maintained in php code (mininmum length, different from username, etc.)
||         so watch out for strict dba_profile rules preventing password changes
||     p_result - returns to php whether the command succeeded or not.
|| Installation notes:
||     This procedure should be owned by sys and the php user account then needs execute privilege on this procedure only.
||     Users other than sys could own this procedure if they are given explicit select on dba_role_privs and dba_users.
|| Change History :
***************************/
   ( p_username IN VARCHAR2
   , p_new_password IN VARCHAR2
   , p_result OUT VARCHAR2 )
AS
   v_forbidden NUMBER := 0 ;
   v_num_users NUMBER := 0 ;
BEGIN
   /*
   || Prevent any attempts to change superuser passwords
   */
   SELECT COUNT(*) INTO v_forbidden FROM sys.dba_role_privs WHERE grantee = UPPER(p_username) AND granted_role IN ('DBA','IMP_FULL_DATABASE') ;
   IF v_forbidden > 0
   THEN
      p_result := 'superuser account cannot be changed with this utility' ;
   ELSE
      SELECT COUNT(*) INTO v_num_users FROM ALL_USERS WHERE username = UPPER(p_username) ;
      IF v_num_users = 1
      THEN
         EXECUTE IMMEDIATE 'alter user ' || p_username || ' identified by "' || p_new_password ||'" account unlock' ;
         p_result := 'success' ;
      ELSE
         p_result := 'no database account matches that username' ;
      END IF ;
   END IF ;
END ;
/