Subject: ultpwdmg.sql has incorrect test for null old passwords Creation Date: 30-JUN-2000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ultpwdmg.sql has incorrect test for null old passwords ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Initial version in which fix will appear: 8.2 External Description: ultpwdmg.sql has incorrect test for null old passwords Workaround: Edit to correct the test. However, the obvious edit which fixes the test will actually prevent CREATE USER and ALTER_USER by a DBA from succeeding. Impact: Moderate. How to determine rediscovery: If utlpwdmg.sql contains the lines ... IF old_password = '' THEN raise_application_error(-20004, 'Old password is null'); then you have the broken version. Following the change for bug 947460, operations which can't provide the old password to the password verifiction PL/SQL function (if it is in effect) won't actually call the function but will error out immediately. This is because the old password would be NULL and hence the verification routine can't therefore check it against the new password. There are a couple of exceptions to this. If the operation is CREATE USER ( or the equivalent GRANT) or the person running ALTER USER has DBA privs, then the verification function WILL be called with a NULL old password. The verification function actually contains a test whose purpose is to error out if a NULL old password is provided. Fortunately, this test is broken else CREATE USER and ALTER USER as DBA would fail! However, after the failed test, we continue to make comparisions against the old password which is incorrect. Resolution: The obvious change a customer might make if they noticed this would be to change IF old_password = '' THEN ... raise error and quit ENDIF; password comparision stuff ... to IF old_password is NULL THEN ... raise error and quit ENDIF; password comparision stuff ... However, if they did this, then they'd soon notice CREATE USER failures. The change made here is to reverse the logic so that we skip the old password comparision code which followed in the function if the above test didn't error out i.e. we now have ... IF old_password IS NOT NULL THEN do the password comparison stuff ENDIF; We will never raise an error from NULL password in the default verification function since it can now, following the change for bug 947460, only be called with a NULL password when that is a valid thing to do. Other attempts are trapped by the kernel. Testing Done: testcase, short regress, tkzgsuit Files changed: utlpwdmg.sql File1: /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 File2: utlpwdmg.sql <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 2: 2: Rem $Header: utlpwdmg.sql 30-may-96.17:02:41 asurpur Exp $ >----------- Changed to: 2: Rem $Header: utlpwdmg.sql 28-jun-2000.16:13:24 nireland Exp $ <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 6: 6: Rem Copyright (c) Oracle Corporation 1996, 1997. All Rights Reserved. >----------- Changed to: 6: Rem Copyright (c) Oracle Corporation 1996, 2000. All Rights Reserved. >----------- Added in utlpwdmg.sql line 22: 22: Rem nireland 06/28/00 - Fix null old password test. #1341892 <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 127: 127: IF old_password = '' THEN 128: raise_application_error(-20004, 'Old password is null'); 129: END IF; 130: -- Everything is fine; return TRUE ; 131: differ := length(old_password) - length(password); >----------- Changed to: 128: IF old_password IS NOT NULL THEN 129: differ := length(old_password) - length(password); <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 133: 133: IF abs(differ) < 3 THEN 134: IF length(password) < length(old_password) THEN >----------- Changed to: 131: IF abs(differ) < 3 THEN 132: IF length(password) < length(old_password) THEN <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 136: 136: ELSE >----------- Changed to: 134: ELSE <----------- Removed from /ade_ukddr/nireland/.ade/nireland_bug-1341892.t2t/rdbms/admin/utlpwdmg.sql#0 line 138: 138: END IF; 139: differ := abs(differ); 140: FOR i IN 1..m LOOP 141: IF substr(password,i,1) != substr(old_password,i,1) THEN 142: differ := differ + 1; 143: END IF; 144: END LOOP; 145: IF differ < 3 THEN 146: raise_application_error(-20004, 'Password should differ by at \ 147: least 3 characters'); 148: END IF; >----------- Changed to: 136: END IF; 137: 138: differ := abs(differ); 139: FOR i IN 1..m LOOP 140: IF substr(password,i,1) != substr(old_password,i,1) THEN 141: differ := differ + 1; 142: END IF; 143: END LOOP; 144: 145: IF differ < 3 THEN 146: raise_application_error(-20004, 'Password should differ by at \ 147: least 3 characters'); 148: END IF; 149: END IF; Procedures changed (pertinent): n/a Integration: Will be checked into 8.2 UK ADE View Name: nireland_sigma ADE Transaction ID: /ade_ukddr/nireland/.ade/txn_storage/bug-1341892/nireland_bug-1341892 Reviewed by: rkng (owner) clei (backup) Can this be backported to older releases? (if not, justification) Yes, to 8.1.6 onwards --- Defect Analysis ---- History: Always there since V8 introduced password management. Suggested Contact: Nick Ireland Defect Type: Code Developer time to fix defect: (in multiples of 8 hour days) 0.2 Additional action taken: Lessons learned for bug prevention: Lessons learned for bug detection: