依据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
