Example CREATE PROFILE Password Security Script

 The following SQL script can be used to create an example Oracle profile used for password security.


connect sys/manager as sysdba
Connected.

CREATE OR REPLACE FUNCTION verify_user_password (  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                        |
        -- +-----------------------------------------------------+
        <>
        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                      |
        -- +-----------------------------------------------------+
        <>
        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;

        <>

        -- +-----------------------------------------------------+
        -- | 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. |
        -- | Return 'true'.                                      |
        -- +-----------------------------------------------------+
        RETURN(true);

    END;
/

Function created.

CREATE PROFILE "USER_PROFILE"
    LIMIT   
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_GRACE_TIME 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_REUSE_TIME 120
    PASSWORD_VERIFY_FUNCTION verify_user_password
/

Profile created.