Re: Verify_user function not validating as designed

I glanced at the code and don't see any obvious problems, but how do you know the function is even being invoked ? You don't show anything that would imply it is, such as associating the user joe with a profile that uses the function.

If you haven't already done so, you might consider directly invoking the function, putting in a couple of DBMS_OUPUT.PUTLINE statements, or changing your test to exercise one of the earlier (and simpler) checks. Does it act as expected if you:

SQL> alter user joe identified by "a"; -- should raise -20002

P.S. You don't need to commit after altering the user.

Hope this helps.

Jim

wilcoxd.forum_at_gmail.com (Dan) wrote:

>This verify_user function is published in many areas on the web, with
>slight variations. In attempting to use and test it, however, I
>noticed that the steps it takes to check "password differs from th
>previous password by at least [x] letters" does not seem to be
>working.
>
>I've posted my findings below in sections labeled RESULTS, EXPECTED
>RESULTS, and SCRIPT.
>
>Wondering if anyone has either used this before and/or has suggestions
>on how to remedy the issue:
>
>RESULTS:
>Step 1:
> SQL> alter user joe identified by "abcdefg!1";
> alter user joe identified by "abcdefg!1"
>
> User altered.
>
> SQL> commit;
>
> Commit complete.
>
>Step 2:
> SQL> alter user joe identified by "abcdefg!2";
> alter user joe identified by "abcdefg!2"
>
> User altered.
>
>EXPECTED RESULTS:
>According to the script should get:
> ORA-20006:Password should differ by at least 3 characters
>
>SCRIPT:
>CREATE OR REPLACE FUNCTION verify_user ( username VARCHAR2
> , password VARCHAR2
> , old_password varchar2)
> RETURN boolean
> IS
>
> passwordMinLength INTEGER;
> passwordLength INTEGER;
> differ INTEGER;
> differMinLength INTEGER;
> isDigit BOOLEAN;
> isChar BOOLEAN;
> isPunct BOOLEAN;
> digitArray VARCHAR2(20);
> punctArray VARCHAR2(25);
> charArray VARCHAR2(52);
>
> BEGIN
>
> digitArray := '0123456789';
> charArray :=
>'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
> punctArray := '!"#$%&()``*+,-/:;<=>?_';
> passwordMinLength := 7;
> differMinLength := 3;
> passwordLength := LENGTH(password);
> isDigit := FALSE;
> isChar := FALSE;
> isPunct := FALSE;
>
> -- +------------------------------------------------+
> -- | Check if the password is same as the username |
> -- +------------------------------------------------+
> IF NLS_LOWER(password) = NLS_LOWER(username) THEN
> raise_application_error(-20001, 'Password same as or
>similar to user');
> END IF;
>
>
> -- +-------------------------------------------------+
> -- | Check that password is more than [x] characters |
> -- | in length. |
> -- +-------------------------------------------------+
> IF (LENGTH(password) < passwordMinLength) THEN
> raise_application_error(-20002, 'Password must be greater
>than '
> ||
> passwordMinLength
> ||
> ' characters.');
> END IF;
>
>
> -- +----------------------------------------------------+
> -- | Check if the password is too simple. A dictionary |
> -- | of words may be maintained and a check may be made |
> -- | so as not to allow the words that are too simple |
> -- | for the password. |
> -- +----------------------------------------------------+
> IF NLS_LOWER(password) IN ( 'welcome'
> , 'database'
> , 'account'
> , 'user'
> , 'password'
> , 'oracle'
> , 'computer'
> , 'abcd') THEN
> raise_application_error(-20003, 'Password too simple');
> END IF;
>
> -- +-----------------------------------------------------+
> -- | Check if the password contains at least one letter, |
> -- | one digit and one punctuation mark. |
> -- +-----------------------------------------------------+
>
> -- +-----------------------------------------------------+
> -- | (1.) Check for the digit |
> -- +-----------------------------------------------------+
> FOR i IN 1..10 LOOP
> FOR j IN 1..passwordLength LOOP
> IF SUBSTR(password,j,1) = SUBSTR(digitArray,i,1) THEN
> isDigit := TRUE;
> GOTO findchar;
> END IF;
> END LOOP;
> END LOOP;
>
> IF isDigit = FALSE THEN
> raise_application_error(-20004, 'Password should contain
>at least '
> ||
> ' one digit,'
> ||
> ' one character and'
> ||
> ' one punctuation');
> END IF;
>
> -- +-----------------------------------------------------+
> -- | (2.) Check for the character |
> -- +-----------------------------------------------------+
> <<findchar>>
> FOR i IN 1..LENGTH(charArray) LOOP
> FOR j IN 1..passwordLength LOOP
> IF SUBSTR(password,j,1) = SUBSTR(charArray,i,1) THEN
> isChar := TRUE;
> GOTO findpunct;
> END IF;
> END LOOP;
> END LOOP;
>
> IF isChar = FALSE THEN
> raise_application_error(-20004, 'Password should contain
>at least '
> ||
> ' one digit,'
> ||
> ' one character and'
> ||
> ' one punctuation');
> END IF;
>
> -- +-----------------------------------------------------+
> -- | (3.) Check for the punctuation |
> -- +-----------------------------------------------------+
> <<findpunct>>
> FOR i IN 1..LENGTH(punctArray) LOOP
> FOR j IN 1..passwordLength LOOP
> IF SUBSTR(password,j,1) = SUBSTR(punctArray,i,1) THEN
> isPunct := TRUE;
> GOTO endsearch;
> END IF;
> END LOOP;
> END LOOP;
>
> IF isPunct = FALSE THEN
> raise_application_error(-20004, 'Password should contain
>at least '
> ||
> ' one digit,'
> ||
> ' one character and'
> ||
> ' one punctuation');
> END IF;
>
> <<endsearch>>
>
> -- +-----------------------------------------------------+
> -- | Check that the new password is not null. |
> -- +-----------------------------------------------------+
> IF old_password = '' THEN
> raise_application_error(-20005, 'Old password is null');
> END IF;
>
>
> -- +-----------------------------------------------------+
> -- | Check if the password differs from the previous |
> -- | password by at least [x] letters. |
> -- +-----------------------------------------------------+
> differ := ABS(LENGTH(old_password) - LENGTH(password));
>
> IF differ < differMinLength THEN
>
> IF LENGTH(password) < LENGTH(old_password) THEN
> passwordLength := LENGTH(password);
> ELSE
> passwordLength := LENGTH(old_password);
> END IF;
>
> FOR i IN 1..passwordLength LOOP
>
> IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1)
>THEN
> differ := differ + 1;
> END IF;
> END LOOP;
>
> IF differ < differMinLength THEN
> raise_application_error(-20006, 'Password should
>differ by at least '
> ||
> differMinLength
> ||
> ' characters.');
> END IF;
>
> END IF;
>
> -- +-----------------------------------------------------+
> -- | Well, looks like we passed all of the requirements. |
> -- | Simple return 'true'. |
> -- +-----------------------------------------------------+
> RETURN(true);
>
> END;
>
>Thanks

Being ordinary and nothing special is a full-time job. jp_mcmahon_at_hotmail.com (Jim McMahon in real life) Received on Wed Nov 10 2004 - 21:52:15 CST