使用PASSWORD_VERIFY_FUNCTION設定使用者密碼複雜度

依據PASSWORD_VERIFY_FUNCTION可以設定oracle使用者的密碼複雜度，比如密碼長度>=10，必須包含字母/數字等
首先需要建立一個密碼驗證的function，然後設定profile的PASSWORD_VERIFY_FUNCTION即可

SQL> select TEXT from dba_source where NAME='VERIFY_JUSTIN_USER';

TEXT
------------------------------------------------------------------------------------------------------------------------------------
FUNCTION verify_JUSTIN_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  := 10;
        differMinLength    := 2;--HAD-1
        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;

188 rows selected.

SQL> select * from dba_profiles;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
JUSTIN_PROFILE                 PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_JUSTIN_USER

此後建立user可以指定該profile，密碼若驗證無法透過澤使用者建立會失敗
SQL> create user sagda identified by"asd245(" profile JUSTIN_PROFILE;
create user sagda identified by"asd245(" profile JUSTIN_PROFILE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password must be greater than 10 characters.

SQL> create user sagda identified by"asd245234155" profile JUSTIN_PROFILE;
create user sagda identified by"asd245234155" profile JUSTIN_PROFILE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should contain at least  one digit, one character and one punctuation