/*****************************************************************************
*
* Filename         - $set_password.sql$
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - 
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   set_password.sql  $  $Revision:   1.2  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/set_password.svl  $
* 
*    Rev 1.2   20 Sep 1999 14:42:06   tagtrak
*  
* 
*    Rev 1.1   02 Apr 1998 19:38:04   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 10:22:26   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/

/*
 * SET_PASSWORD will accept the login_name, current_password (unencrypted),
 * new_password (unencrypted). It will set the password if validation
 * succeeds, otherwise, it will return an appropriate error.
 */
PROMPT create or replace procedure set_password

create or replace procedure set_password (
        p_login_name           in  tagtrak_users.loginname%TYPE,
        p_current_password     in  tagtrak_users.tagtrak_password%TYPE,
        p_new_password         in  tagtrak_users.tagtrak_password%TYPE,
        p_retval               out number,
      p_override             in  varchar2 DEFAULT 'N'
)

as
        /* Declare variables for the login_name, group_name, surname and firstname */
        login_name             tagtrak_users.loginname%TYPE;
        group_name             tagtrak_users.group_name%TYPE;
        surname                tagtrak_users.surname%TYPE;
        firstname              tagtrak_users.firstname%TYPE;
        lock_flag              tagtrak_users.lock_flag%TYPE;
        existing_password      tagtrak_users.tagtrak_password%TYPE;

        encrypted_in_password  tagtrak_users.tagtrak_password%TYPE;
        encrypted_new_password tagtrak_users.tagtrak_password%TYPE;
begin

        /* The first check to save time is to check the length of the */
        /* new password.  If its less than 6 characters or more then  */
        /* 15 characters, then return BAD_PASSWORD                    */

        if (length(p_new_password) < 6 or length(p_new_password) > 15) then
                /* BAD_PASSWORD - wrong length */
                p_retval := -1033;
                return;
        end if;


        /* Get the login_name, group_name, surname and firstname of the user */
        begin
                select
                        loginname,
                        group_name,
                        surname,
                        firstname,
                        lock_flag,
                        tagtrak_password
                into
                        login_name,
                        group_name,
                        surname,
                        firstname,
                        lock_flag,
                        existing_password
                from
                        tagtrak_users
                where
                        loginname = p_login_name;

                exception
                        when NO_DATA_FOUND then
                                /* UNKNOWN_USER */
                                p_retval := -102;
                                return;
        end;

        /*
         * Now do the checks
         */

        /* Check for account lockout */
        if (lock_flag != 0) then
                /* ACCOUNT_LOCKOUT */
                p_retval := -1028;
                return;
        end if;

        /* Check to see if the password is equal to the table columns */
        if (p_new_password = login_name or
                        p_new_password = group_name or
                        p_new_password = surname    or
                        p_new_password = firstname) then
                /* BAD_PASSWORD */
                p_retval := -1033;
                return;
        end if;

        /* Encrypt the current password */
        encrypted_new_password := user_authentication.encrypt_password (p_new_password);

      if NVL(p_override, '?') != 'Y' then
         /* The (supervisor) override flag is set, ignore validation checks -
          just set the password */

          encrypted_in_password := user_authentication.encrypt_password (p_current_password);

         /* Make sure the password passed in is the same as the existing password */
          if (encrypted_in_password != existing_password) then
                /* BAD_PASSWORD */
                p_retval := -1033;
                return;
          end if;

        /* Check password with the current encrypted password */
         if (encrypted_new_password = existing_password) then
            /* BAD_PASSWORD */
            p_retval := -1033;
            return;
         end if;

      end if; -- p_override = 'Y' 

        /* Password is OK, so set it */
        update
                tagtrak_users
        set
                tagtrak_password = encrypted_new_password,
                pwd_set_date     = SYSDATE,
                pwd_expired_flag = 0
        where
                loginname = p_login_name;

        /* SUCCESS, so return 0 */
        p_retval := 0;

end set_password;
/
