CREATE OR REPLACE PACKAGE pw AUTHID CURRENT_USER IS
--============================================================================
-- Copywright 2002, john flack JohnF@smdi..com
--=============================================================================
-- DESCRIPTION
-- This package allow users or DBAs to chaange passwords on the
-- database to which they are connected.
-- Accepts username, new password, and passsword validate.
--
-- Owned by my SHARED object SCHEMA, "COMMMON"
--  (no need FOR SYS TO OWN this) runs ON  8.1 OR later.  I grant execute to
--  PUBLIC AND give it a PUBLIC SYNONYM:
-- 
-- Usage
-- For changing the password for USER thiss_user to this_password,
--       pw.change(username       => 'thiss_user',
--                 password       => 'thiss_password',
--                 check_password => 'thiss_password');
-- The check password is required to insurre that the user typed it
-- correctly.
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- john        07/17/2001 Initial Creationn
--=============================================================================
   FUNCTION CHANGE (
      PASSWORD         IN   VARCHAR2
     ,check_password   IN   VARCHAR2
     ,username         IN   VARCHAR2 DEFAULT NULL
   )
      RETURN BOOLEAN;

   PROCEDURE CHANGE (
      PASSWORD         IN   VARCHAR2
     ,check_password   IN   VARCHAR2
     ,username         IN   VARCHAR2 DEFAULT NULL
   );

   FUNCTION error_message
      RETURN VARCHAR2;
END pw;
/

CREATE OR REPLACE PACKAGE BODY pw IS
--============================================================================
-- Copywright 2002, john flack
--=============================================================================
-- DESCRIPTION
-- This package allow users or DBAs to chaange passwords on the
-- database to which they are connected.
-- Accepts username, new password, and passsword validate.
--
-- Owned by my SHARED object SCHEMA, "COMMMON"
--  (no need FOR SYS TO OWN this) runs ON  8.1 OR later.  I grant execute to
--  PUBLIC AND give it a PUBLIC SYNONYM:
-- 
-- Usage
-- For changing the password for USER thiss_user to this_password,
--       pw.change(username       => 'thiss_user',
--                 password       => 'thiss_password',
--                 check_password => 'thiss_password');
-- The check password is required to insurre that the user typed it
-- correctly.
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- john        07/17/2001 Initial Creationn
--=============================================================================
   err_msg VARCHAR2 (255);

   FUNCTION CHANGE (
      PASSWORD         IN   VARCHAR2
     ,check_password   IN   VARCHAR2
     ,username         IN   VARCHAR2 DEFAULT NULL
   )
      RETURN BOOLEAN IS
      unmatched_verification EXCEPTION;
      insufficient_privileges EXCEPTION;
      PRAGMA EXCEPTION_INIT (insufficient_privileges, -1031);
      user_name VARCHAR2 (30) := UPPER (NVL (username, USER));
      changed BOOLEAN := TRUE ;
   BEGIN
      IF UPPER (PASSWORD) = UPPER (check_password) THEN
         EXECUTE IMMEDIATE 'ALTER USER ' || user_name || ' IDENTIFIED BY
 '
                           || PASSWORD;
      ELSE
         RAISE unmatched_verification;
      END IF;

      RETURN changed;
   EXCEPTION
      WHEN unmatched_verification THEN
         err_msg := 'New Password does not match the Check Password.';
         changed := FALSE ;
         RETURN changed;
      WHEN insufficient_privileges THEN
         err_msg := 'You do not have the authority to change
 ' || user_name
                    || '''s
 password.';
         changed := FALSE ;
         RETURN changed;
      WHEN OTHERS THEN
         err_msg := SQLERRM;
         changed := FALSE ;
         RETURN changed;
   END CHANGE;

   PROCEDURE CHANGE (
      PASSWORD         IN   VARCHAR2
     ,check_password   IN   VARCHAR2
     ,username         IN   VARCHAR2 DEFAULT NULL
   ) IS
   BEGIN
      IF CHANGE (PASSWORD, check_password, username) THEN
         DBMS_OUTPUT.put_line ('Password Changed.');
      ELSE
         DBMS_OUTPUT.put_line (err_msg || ' Password not changed.');
      END IF;
   END CHANGE;

   FUNCTION error_message
      RETURN VARCHAR2 IS
   BEGIN
      RETURN err_msg;
   END error_message;
END pw;
/


-- And a pair of web toolkit procedures too execute it - mine are in the
-- application schema so i can keep consisstant look and feel, but they could
-- have been owned by COMMON too.  if theyy look a bit funny, its because they
-- were built as PSPs:

CREATE OR REPLACE PROCEDURE pw_change_form (
   MESSAGE   IN   VARCHAR2 DEFAULT NULL
) AS
--============================================================================
-- Copywright 2002, john flack
--=============================================================================
-- DESCRIPTION
-- This procedure is for web based passworrd change
--
-- Owned by my SHARED object SCHEMA, "COMMMON"
--  (no need FOR SYS TO OWN this) runs ON  8.1 OR later.  I grant execute to
--  PUBLIC AND give it a PUBLIC SYNONYM:
-- 
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- john        07/17/2001 Initial Creationn
--=============================================================================
BEGIN
   HTP.prn (
      '
 <HTML>
 <HEAD>
 <TITLE>CHANGE Password</TITLE>
 <META content="text/html; charset=windows-1252" http-equiv=Content-TYPE>
 <LINK href="/bwns/text.css" rel=stylesheet>
 </HEAD>
 <BODY>
 '
   );
   bwns_util.stdpagehead;
   HTP.prn ('
 <H1>CHANGE your password</H1>
 <P>');
   HTP.prn (MESSAGE);
   HTP.prn (
      '</P>
 <FORM ACTION="pw_change" METHOD="post">
 <TABLE WIDTH="100%">
 <TR><TD>Please enter your NEW password:</TD><TD><INPUT TYPE="password"
 NAME="PW1" SIZE="30" MAXLENGTH="30"></TD></TR>
 <TR><TD>Please Re-Enter your password TO confirm:</TD><TD><INPUT
 TYPE="password" NAME="PW2" SIZE="30" MAXLENGTH="30"></TD></TR>
 </TABLE>
 <P><CENTER><INPUT TYPE="submit" VALUE="Submit New Password"></CENTER></P>
 </FORM>
 '
   );
   bwns_util.stdpagefoot;
   HTP.prn ('
 </BODY>
 </HTML>
 ');
END;
/

CREATE OR REPLACE PROCEDURE pw_change (
   pw1   IN   VARCHAR2 DEFAULT NULL
  ,pw2   IN   VARCHAR2 DEFAULT NULL
)
AUTHID CURRENT_USER AS
--============================================================================
-- Copywright 2002, john flack
--=============================================================================
-- DESCRIPTION
-- This procedure is for web based passworrd change responses
--
-- Owned by my SHARED object SCHEMA, "COMMMON"
--  (no need FOR SYS TO OWN this) runs ON  8.1 OR later.  I grant execute to
--  PUBLIC AND give it a PUBLIC SYNONYM:
-- 
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- john        07/17/2001 Initial Creationn
--=============================================================================
BEGIN
   IF pw1 IS NULL THEN
      pw_change_form ('You must specify a password.');
   ELSIF pw.CHANGE (pw1, pw2) THEN
      HTP.prn (
         '
 <HTML>
 <HEAD>
 <TITLE>CHANGE Password</TITLE>
 <META content="text/html; charset=windows-1252" http-equiv=Content-TYPE>
 <LINK href="/bwns/text.css" rel=stylesheet>
 </HEAD>
 <BODY>
 '
      );
      bwns_util.stdpagehead;
      HTP.prn (
         '
 <H1>Password Changed</H1>
 <P>Your password has been successfully changed.  You will need TO USE this
 NEW password the
 NEXT TIME that you LOG IN TO the SYSTEM.  SELECT the LINK below TO RETURN
 TO
 the main menu.</P>
 '
      );
      bwns_util.stdpagefoot;
      HTP.prn ('
 </BODY>
 </HTML>
 ');
   ELSE
      pw_change_form (pw.error_message);
   END IF;
END;
/