SET scan off CREATE OR REPLACE PACKAGE CESResetPW AS -- FILE NAME..: cesresetpw.sql -- RELEASE....: 6.1 (was 5.4) -- OBJECT NAME: CESRESETPW -- PRODUCT....: GENWEB -- RELEASED...: 7/28/04 -- USAGE......: CES BANNER Web Banner/WebForms Password Reset Package Specification -- AUTHOR.....: Stephen Rea; Maristream, Inc. -- FUNCTION...: Reset the user's Banner/WebForms password to their Banner user ID, -- given the last 4 digits of their SSN and the first 5 characters of their -- last name. A reset key is generated and e-mailed to them (they must have a -- goremal record) to be used to then do the reset. -- -- The e-mailing requires either our email_files routine (see "E-Mailing From -- Oracle" on my web site, http://www.oracletips.info) or the Oracle daemon -- process to be running on a unix machine (see "Running System Commands from -- PL/SQL Using Pipes" on my web site) to send the e-mails. Uncomment either -- the email_files call or the daemon call in the code to use whichever one -- you want. It defaults to using the email_files routine (UTL_SMTP based). -- -- Updates: -- 12/9/04 - Reset the password to a temporary value instead of their Banner -- user ID for better security and to meet our new 8-character alphanumeric -- security standards. Expire the password to force the user to change it -- on first login. Change the e-mail to reflect usage with Oracle Password -- Management (instead of my Banner Password Aging system) and the new pre- -- expired temporary password. Use the same value for the reset key and -- the temporary password (and change the field on the screen to just say -- Temporary Password). Remove the 1-minute countdown timer (since I'm -- switching away from using my Banner Password Aging system). -- 12/13/04 - Put abbreviated instructions to the right of the fields. -- 1/5/05 - Changed e-mailing to use either our email_files routine -- (UTL_SMTP based) or the Oracle daemon program (UNIX mail based), -- defaulting to email_files in the code. -- NOTE: If you use this at your site, be sure to change my_name and my_email -- to your own name and e-mail address. I don't want to be receiving e-mails -- from your users! Also, be sure your SSN/name combinations are unique. If -- not, you will have to have some other criteria to uniquely identify the -- user. Use the following SQL and investigate the resulting names to see if -- they would cause a problem: -- select substr(spbpers_ssn,6),substr(spriden_last_name,1,5), -- count(*) from spriden,spbpers where spriden_change_ind is null -- and spriden_pidm = spbpers_pidm group by substr(spbpers_ssn,6), -- substr(spriden_last_name,1,5) having count(*) > 1; -- Declare the procedures called within the Web environment: the procedure that -- creates the Banner/WebForms password reset screen (P_DispPWReset) and the -- procedure that is called when the Submit button is pressed (P_ProcPWReset). -- -- These procedures must be declared to Web Tailor and marked as "Insecure -- Access Allowed". Also, "grant alter user to baninst1;" is required. -- Below is how we did this at UACES. Your setup will be different (such -- as adding it to the Login screen). /* Allow baninst1 to change passwords: sqlplus as a DBA user or as user system grant alter user to baninst1; Compile the cesresetpw package as baninst1: sqlplus as baninst1 -- @cesresetpw.sql Set up all grants to schemas and roles: create public synonym cesresetpw for cesresetpw; START gurgrtb cesresetpw START gurgrth cesresetpw Add the new display procedure to WebTailor: New WebTailor Administration Customize a Web Menu or Procedure Click on Create button and set: Page Name: cesresetpw.P_DispPWReset Description: Reset Banner/WebForms Password Module: Web for General Non Secured Access Allowed: checked Page Title: Reset Banner/WebForms Password Header Text: Reset Banner/WebForms Password Associated Roles: Employee (Note: No Back Link can be done in this page) Click on Submit Changes button Add the new process procedure to WebTailor: New WebTailor Administration Customize a Web Menu or Procedure Click on Create button and set: Page Name: cesresetpw.P_ProcPWReset Description: Process Banner/WebForms Password Reset Module: Web for General Non Secured Access Allowed: checked Associated Roles: Employee Click on Submit Changes button */ PROCEDURE P_DispPWReset; PROCEDURE P_ProcPWReset( ssn4 varchar2, name5 varchar2, tpwd varchar2 ); END CESResetPW; / SHOW errors SET scan on SET scan off CREATE OR REPLACE PACKAGE BODY CESResetPW AS -- Declare variables for the package's release version, the message text string -- and severity level (1 = Stop, 2 = Warning, 3 = OK), along with the last value -- entered for the SSN and last name, and the temporary password. curr_release VARCHAR2(10) := '6.1'; my_name varchar2(30) := 'Steve Rea'; my_email varchar2(30) := 'srea@maristream.org'; msg_text VARCHAR2(1000) := NULL; msg_level NUMBER := 3; ssn4_prev varchar2(4) := ''; name5_prev varchar2(5) := ''; tpwd varchar2(8) := NULL; -- Declare cursors used in the package. For CESRESETPW, this includes the -- pidm, e-mail address, username, profile, account status and employment -- status, along with an 8-character alphanumeric temporary password created -- from the user's current encoded password, for the employee with the given -- last 4 digits of the SSN and the first 5 letters of the last name. CURSOR employee_PW_cur(ssn4 varchar2, name5 varchar2) IS SELECT spriden_first_name || ' ' || spriden_last_name, spriden_pidm,goremal_email_address,username,profile, account_status,pebempl_empl_status, 'T' || decode(instr(translate(substr(password,1,7), '0123456789',RPAD('',10,'~')),'~'),0,substr(password,1,6) || '9', substr(password,1,7)) FROM spriden,spbpers,goremal,gobeacc,pebempl,dba_users WHERE spriden_pidm = spbpers_pidm AND spriden_change_ind is null AND SUBSTR(spbpers_ssn,6) = ssn4 AND LOWER(SUBSTR(spriden_last_name,1,5)) = LOWER(name5) AND spriden_pidm = goremal_pidm AND goremal_preferred_ind = 'Y' AND goremal_status_ind = 'A' AND goremal_emal_code = 'SMTP' AND spriden_pidm = gobeacc_pidm AND spriden_pidm = pebempl_pidm AND gobeacc_username = username; -------------------------------------------------------------------------- -- Procedure P_DispPWReset (Display Banner/WebForms Password Reset Screen) -- builds the HTML to display the fields to query on to get the associated -- employee password to reset, or to actually reset the employee's password. -- It includes creating fields and their associated variables for: -- The last 4 digits of the SSN (ssn4). -- The first 5 characters of the last name (name5). -- The temporary password e-mailed to the employee (tpwd). -- Along with one button: -- A Submit button (which calls P_ProcPWReset to process the password -- query or reset, and display the result). -- -- This procedure must be declared to Web Tailor and marked as "Insecure -- Access Allowed". Also, "grant alter user to baninst1;" is required. PROCEDURE P_DispPWReset IS BEGIN -- Open the form, showing the most recent message text, if any. twbkwbis.P_OpenDoc('cesresetpw.P_DispPWReset'); IF msg_text IS NOT NULL THEN twbkfrmt.P_PrintMessage('' || msg_text || '',TO_CHAR(msg_level)); msg_text := NULL; msg_level := 3; END IF; -- Print instructions. twbkfrmt.P_PrintMessage('To reset your password, follow the instructions to the right of the fields below:
'); /* twbkfrmt.P_PrintMessage('To reset your password:
'); twbkfrmt.P_PrintMessage('1) Enter your SSN and Name information into the first two fields below, leaving the third field blank, and click Submit - it will e-mail you a temporary password.
'); twbkfrmt.P_PrintMessage('2) Enter the temporary password you received in the e-mail into the third field and click Submit again (don''t erase the SSN or Last Name fields) - it will reset your password to that temporary password.
'); twbkfrmt.P_PrintMessage('3) Log into Banner/WebForms using that temporary password - it will tell you that the password has expired (click OK) and have you change to a new password (which you haven''t used recently and which must be at least 8 characters in length and have at least 1 letter and 1 number in it).'); */ -- Start building the form. twbkwbis.P_DispInfo('cesresetpw.P_DispPWReset','DEFAULT'); HTP.FormOpen('cesresetpw.P_ProcPWReset','post'); -- this happens when submit button pressed twbkfrmt.P_TableOpen('DATAENTRY', cattributes=>'Summary="This table allows resetting of the employee password."'); -- Define field for last 4 digits of SSN for query. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Last 4 digits of your SSN: ', idname=>'ssn4_id')); twbkfrmt.P_TableData(htf.FormText('ssn4','8','4',ssn4_prev,cattributes=>'ID="ssn4_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Fill in these first two fields first, then click Submit, and wait for the',idname=>'ssn4_id')); twbkfrmt.P_TableRowClose; -- Define field for first 5 characters of last name for query. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('First 5 characters of your last name: ', idname=>'name5_id')); twbkfrmt.P_TableData(htf.FormText('name5','8','5',name5_prev,cattributes=>'ID="name5_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('   e-mail it will send you containing your temporary password.',idname=>'name5_id')); twbkfrmt.P_TableRowClose; -- Define field for the Temporary Password for resetting the password. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Temporary Password (e-mailed to you): ', idname=>'tpwd_id')); twbkfrmt.P_TableData(htf.FormText('tpwd','8','8',tpwd,cattributes=>'ID="tpwd_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Fill in this field after e-mail received, and click Submit again.',idname=>'tpwd_id')); twbkfrmt.P_TableRowClose; -- Finish specifying the data entry fields with a call to twbkfrmt.P_TableClose -- to match the starting twbkfrmt.P_TableOpen. twbkfrmt.P_TableClose; HTP.Para; -- Add the Submit button. The HTP.FormSubmit button (labeled 'Submit') will -- call the "post" procedure (cesresetpw.P_ProcPWReset) specified in the -- HTP.FormOpen call above. HTP.FormSubmit(cvalue=>'Submit'); -- Finish building the form by closing the form and document. The value -- passed to twbkwbis.P_CloseDoc is usually the current release version -- of this form, and is placed at the bottom of the form. HTP.FormClose; twbkwbis.P_CloseDoc(curr_release); END; -------------------------------------------------------------------------- -- Procedure P_ProcPWReset (Process the Banner/WebForms Password Reset) -- either does a query for the employee's encoded password (given the last -- 4 digits of the SSN and the first 5 characters of the last name) and -- gets the temporary password value from it and e-mails that to the -- employee when the Temporary Password field is blank, or resets the -- employee's password to that temporary password if the Temporary -- Password field has been filled in. -- -- This procedure must be declared to Web Tailor and marked as "Insecure -- Access Allowed". Also, "grant alter user to baninst1;" is required. PROCEDURE P_ProcPWReset( ssn4 varchar2, name5 varchar2, tpwd varchar2 ) IS name varchar2(60); pidm spriden.spriden_pidm%TYPE; email goremal.goremal_email_address%TYPE; username dba_users.username%TYPE; profile dba_users.profile%TYPE; account_status dba_users.account_status%TYPE; empl_status pebempl.pebempl_empl_status%TYPE; temp_pswd varchar2(8); msg varchar2(2000); res number; BEGIN -- Get the employee and password information for the given SSN and Last Name -- substrings. OPEN employee_PW_cur(ssn4,name5); FETCH employee_PW_cur INTO name,pidm,email,username,profile, account_status,empl_status,temp_pswd; -- See if this user has a limited profile (blank out if unlimited). IF employee_PW_cur%FOUND THEN SELECT COUNT(*) INTO res FROM dba_users du,dba_profiles dp WHERE du.username = username AND du.profile = dp.profile AND dp.resource_name = 'PASSWORD_LIFE_TIME' AND dp.resource_type = 'PASSWORD' AND dp.limit != 'UNLIMITED'; IF res = 0 THEN profile := NULL; END IF; END IF; -- Check for no match for SSN/name combination, or no Banner/WebForms account, -- or no e-mail address. IF employee_PW_cur%NOTFOUND OR temp_pswd IS NULL OR email IS NULL THEN msg_text := 'No Employee Banner/WebForms account or e-mail was found for SSN ' || ssn4 || ' and Last Name ' || name5; msg_level := 1; -- Make sure this is an active employee (not terminated). ELSIF empl_status != 'A' THEN msg_text := 'You do not have an active employee record.  Your password has not been reset.'; msg_level := 1; -- Don't allow password resets for accounts that have been locked (keep -- terminated employees from reactivating their user ID). ELSIF account_status = 'LOCKED' THEN msg_text := 'Your Banner/WebForms account is locked.  Please contact ' || my_name || ' at ' || my_email || ' to reset it.'; msg_level := 1; -- E-mail the Temporary Password to the user's e-mail address in the database. ELSIF tpwd IS NULL THEN msg_text := 'Your Temporary Password and instructions have been e-mailed to ' || email; msg_level := 2; ssn4_prev := ssn4; name5_prev := name5; if length(replace(translate(lower(name),' abcdefghijklmnopqrstuvwxyz', rpad('~',26,'~')),'~','')) > 0 then name := '"' || name || '"'; end if; msg := chr(10) || 'Please follow these instructions ' || 'closely to reset your Banner/WebForms password. (This was ' || 'just now requested in a web session.)' || chr(10) || chr(10) || 'Your Banner/WebForms Temporary Password will be: ' || temp_pswd || chr(10) || chr(10) || 'To reset your Banner/WebForms password:' || chr(10) || ' 1) Put ' || temp_pswd || ' into the Temporary Password ' || 'field in the Reset Banner/WebForms Password web page you are ' || 'currently in. (Your SSN and Last Name information should ' || 'still be filled in - if not, fill them back in as well.)' || chr(10) || ' 2) Click on the Submit button to reset your password to ' || temp_pswd || '.' || chr(10) || chr(10) || 'After your password has been reset:' || chr(10) || ' 1) Log into Banner/WebForms, putting ' || temp_pswd || ' into the Password field.' || chr(10) || ' 2) You will get a message that says your password has ' || 'expired. Click on the OK button.' || chr(10) || ' 3) You will get a "Changing password" screen. Enter ' || temp_pswd || ' into the Old Password field.' || chr(10) || ' 4) Enter a new password that you haven''t used before, ' || 'which must be at least 8 characters in length and with at ' || 'least 1 letter and 1 number in it, into both the New Oracle ' || 'Password and the Retype New Password fields.' || chr(10) || ' 5) Click on the OK button.' || chr(10) || 'Then you can use the Banner/WebForms screens. Remember your ' || 'new password to use it for subsequent Banner/WebForms logins.' || chr(10); -- Uncomment either the email_files call or the daemon call below, depending on -- which one you want to use. email_files(from_name => my_email, to_names => name || ' <' || email || '>', subject => 'Banner/WebForms Password Reset', message => msg); /* res := common.daemon.execute_system( 'mail -s "Banner/WebForms Password Reset" ' || email || ' <After logging into Banner / WebForms using that password, it will tell you that your password has expired, and you will need to change it as described in the Password Reset e-mail.'; -- Give an error message when an invalid Temporary Password is entered. ELSE msg_text := 'The wrong Temporary Password was entered - your password has not been reset.  Try again.'; msg_level := 1; END IF; -- Redisplay the Reset Banner/WebForms Password web page with the resulting -- message. CLOSE employee_PW_cur; P_DispPWReset; EXCEPTION WHEN OTHERS THEN -- If the e-mail failed, have the user contact user support to reset their -- password. IF msg_level = 2 THEN msg_text := 'Database e-mail is not currently available.  Please contact ' || my_name || ' at ' || my_email || ' to reset the Banner/WebForms password for your user ID (' || username || ').'; -- If a problem occurred while running the ALTER USER commands, the password -- wasn't reset. ELSIF msg_level = 3 THEN msg_text := 'Can not reset your password at this time.'; msg_level := 1; -- If some other error occurred, ask a person to reset the password, instead -- of using this web page. ELSE msg_text := 'A problem occurred with this page.  Please contact ' || my_name || ' at ' || my_email || ' to reset the Banner/WebForms password for your user ID (' || username || ').'; msg_level := 1; END IF; P_DispPWReset; END; END CESResetPW; / SHOW errors SET scan on