Verify_user function not validating as designed

From: Dan <wilcoxd.forum_at_gmail.com>
Date: 10 Nov 2004 10:29:43 -0800
Message-ID: <7f8f3f16.0411101029.3b891dc4@posting.google.com>

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 Received on Wed Nov 10 2004 - 12:29:43 CST