一、修改密码策略

1.创建PASSWORD_VERIFY_FUNCTION相应密码复杂度验证函数（下面会详细看utlpwdmg.sql ）  建议sys用户下创建

  SQL> @ $ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql → [verify_function|verify_function_11G]

Function created.

Profile altered.

Function created.

2.设置密码策略

SQL> alter system set resource_limit = true;

SQL> alter profile default limit password_verify_function verify_function;   

SQL> select * from dba_profiles where resource_NAME='PASSWORD_VERIFY_FUNCTION';

3.测试更新用户密码

①创建用户并使用自己定义的配置文件

SQL>create user test1 identified by test1 default tablespace USERS temporary tablespace TEMP;

②用户授权grant connect,resource,exp_full_database,imp_full_database to 用户名;

③修改密码策略

SQL> alter profile default limit password_verify_function verify_function;   

④更新用户密码为用户名相同

SQL> alter user test1 identified by test1;alter user test1 identified by test1*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20001: Password same as or similar to user

⑤更新用户密码为敏感关键字SQL> alter user test1 identified by oracle2 ;alter user test1 identified by oracle*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20025: Password must contain at least 1 digit(s)

⑥更新用户密码为纯数字SQL> alter user test1 identified by 123456;alter user test1 identified by 123456*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20022: Password must contain at least 1 letter(s)

⑦更新用户密码为字符+数字SQL> alter user test1 identified by Test1234;alter user test1 identified by Test1234*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20026: Password must contain at least 1 special character(s)

SQL> alter user test1 identified by Ocl13579; alter user test1 identified by Ocl13579*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20026: Password must contain at least 1 special character(s)

⑧更新用户密码为复杂字符串SQL> alter user test1 identified by Ocl_1245;

User altered.

4.取消Oracle密码复杂度检查：SQL> alter profile default limit password_verify_function null;

再次测试 

SQL> alter user test1 identified by test1;

User altered.

其他密码策略详见下面链接（陆续完善）

https://www.cnblogs.com/zhm1985/p/11958078.html

二、详细看下utlpwdmg.sql内容

1.我们先看下Oracle 12C的密码函数，sys用户下有三个不同级别的，不需要我们自己创建了

RemRem $Header: rdbms/admin/utlpwdmg.sql /main/13 2016/01/04 21:20:04 sumkumar Exp $RemRem utlpwdmg.sqlRemRem Copyright (c) 2006, 2015, Oracle and/or its affiliates. Rem All rights reserved.RemRem NAMERem utlpwdmg.sql - script for Default Password Resource LimitsRemRem DESCRIPTIONRem This is a script for enabling the password management featuresRem by setting the default password resource limits.RemRem NOTESRem This file contains a function for minimum checking of passwordRem complexity. This is more of a sample function that the customerRem can use to develop the function for actual complexity checks that the Rem customer wants to make on the new password.RemRem MODIFIED (MM/DD/YY)Rem sumkumar 12/15/15 - Bug 22369990: Make all PVFs as common objectsRem so as to make them available inside PDBsRem yanlili 09/18/15 - Fix bug 20603202: Handle quoted usernames ifRem called directlyRem hmohanku 02/17/15 - bug 20460696: add long identifier supportRem sumkumar 12/26/14 - Proj 46885: set inactive account time toRem UNLIMITED for DEFAULT profileRem jkati 10/16/13 - bug#17543726 : remove complexity_check,Rem string_distance, ora12c_strong_verify_functionRem since we now provide them by default with new dbRem creationRem skayoor 10/26/12 - Bug 14671375: Execute privilege on pwd verifyRem funcRem jmadduku 07/30/12 - Bug 13536142: Re-organize the codeRem jmadduku 12/02/11 - Bug 12839255: Compliant Password Verify functionsRem jmadduku 01/21/11 - Proj 32507: Add a new password verify functionRem STIG_verify_function and enhance functionality ofRem code that checks distance between old and newRem passwordRem asurpur 05/30/06 - fix - 5246666 beef up password complexity check Rem nireland 08/31/00 - Improve check for username=password. #1390553Rem nireland 06/28/00 - Fix null old password test. #1341892Rem asurpur 04/17/97 - Fix for bug479763Rem asurpur 12/12/96 - Changing the name of password_verify_functionRem asurpur 05/30/96 - New script for default password managementRem asurpur 05/30/96 - CreatedRem

-- This script sets the default password resource parameters-- This script needs to be run to enable the password features.-- However the default resource parameters can be changed based -- on the need.-- A default password complexity function is provided.

Rem *************************************************************************Rem BEGIN Password Management ParametersRem *************************************************************************

-- This script alters the default parameters for Password Management-- This means that all the users on the system have Password Management-- enabled and set to the following values unless another profile is -- created with parameter values set to different value or UNLIMITED -- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 180PASSWORD_GRACE_TIME 7PASSWORD_REUSE_TIME UNLIMITEDPASSWORD_REUSE_MAX UNLIMITEDFAILED_LOGIN_ATTEMPTS 10PASSWORD_LOCK_TIME 1INACTIVE_ACCOUNT_TIME UNLIMITEDPASSWORD_VERIFY_FUNCTION ora12c_verify_function;

/** The below set of password profile parameters would take into considerationrecommendations from Center for Internet Security[CIS Oracle 11g].

ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 3PASSWORD_REUSE_TIME 365PASSWORD_REUSE_MAX 20FAILED_LOGIN_ATTEMPTS 3PASSWORD_LOCK_TIME 1PASSWORD_VERIFY_FUNCTION ora12c_verify_function;*/

/** The below set of password profile parameters would take into consideration recommendations from Department of Defense Database Security Technical Implementation Guide[STIG v8R1].

ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 60PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 5FAILED_LOGIN_ATTEMPTS 3PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;*/

Rem *************************************************************************Rem END Password Management ParametersRem *************************************************************************

2.Oracle 11g的密码函数就需要自己创建了。可以选择执行utlpwdmg.sql或者手动创建。

执行脚本会verify_function和verify_function_11G两个函数

12C版本及以上的环境我们也可以手工创建。

RemRem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $RemRem utlpwdmg.sqlRemRem Copyright (c) 2006, Oracle. All rights reserved. RemRem NAMERem utlpwdmg.sql - script for Default Password Resource LimitsRemRem DESCRIPTIONRem This is a script for enabling the password management featuresRem by setting the default password resource limits.RemRem NOTESRem This file contains a function for minimum checking of passwordRem complexity. This is more of a sample function that the customerRem can use to develop the function for actual complexity checks that the Rem customer wants to make on the new password.RemRem MODIFIED (MM/DD/YY)Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check Rem nireland 08/31/00 - Improve check for username=password. #1390553Rem nireland 06/28/00 - Fix null old password test. #1341892Rem asurpur 04/17/97 - Fix for bug479763Rem asurpur 12/12/96 - Changing the name of password_verify_functionRem asurpur 05/30/96 - New script for default password managementRem asurpur 05/30/96 - CreatedRem

-- This script sets the default password resource parameters-- This script needs to be run to enable the password features.-- However the default resource parameters can be changed based -- on the need.-- A default password complexity function is also provided.-- This function makes the minimum complexity checks like-- the minimum length of the password, password not same as the-- username, etc. The user may enhance this function according to-- the need.-- This function must be created in SYS schema.-- connect sys/ as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function_11G(username varchar2,password varchar2,old_password varchar2)RETURN boolean IS n boolean;m integer;differ integer;isdigit boolean;ischar boolean;ispunct boolean;db_name varchar2(40);digitarray varchar2(20);punctarray varchar2(25);chararray varchar2(52);i_char varchar2(10);simple_password varchar2(10);reverse_user varchar2(32);

BEGIN digitarray:= '0123456789';chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check for the minimum length of the passwordIF length(password) < 8 THENraise_application_error(-20001, 'Password length less than 8');END IF;

-- Check if the password is same as the username or username(1-100)IF NLS_LOWER(password) = NLS_LOWER(username) THENraise_application_error(-20002, 'Password same as or similar to user');END IF;FOR i IN 1..100 LOOPi_char := to_char(i);if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THENraise_application_error(-20005, 'Password same as or similar to user name ');END IF;END LOOP;

-- Check if the password is same as the username reversed

FOR i in REVERSE 1..length(username) LOOPreverse_user := reverse_user || substr(username, i, 1);END LOOP;IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THENraise_application_error(-20003, 'Password same as username reversed');END IF;

-- Check if the password is the same as server name and or servername(1-100)select name into db_name from sys.v$database;if NLS_LOWER(db_name) = NLS_LOWER(password) THENraise_application_error(-20004, 'Password same as or similar to server name');END IF;FOR i IN 1..100 LOOPi_char := to_char(i);if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THENraise_application_error(-20005, 'Password same as or similar to server name ');END IF;END LOOP;

-- 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 ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THENraise_application_error(-20006, 'Password too simple');END IF;

-- Check if the password is the same as oracle (1-100)simple_password := 'oracle';FOR i IN 1..100 LOOPi_char := to_char(i);if simple_password || i_char = NLS_LOWER(password) THENraise_application_error(-20007, 'Password too simple ');END IF;END LOOP;

-- Check if the password contains at least one letter, one digit -- 1. Check for the digitisdigit:=FALSE;m := length(password);FOR i IN 1..10 LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(digitarray,i,1) THENisdigit:=TRUE;GOTO findchar;END IF;END LOOP;END LOOP;

IF isdigit = FALSE THENraise_application_error(-20008, 'Password must contain at least one digit, one character');END IF;-- 2. Check for the characterischar:=FALSE;FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THENischar:=TRUE;GOTO endsearch;END IF;END LOOP;END LOOP;IF ischar = FALSE THENraise_application_error(-20009, 'Password must contain at least one \digit, and one character');END IF;

-- Check if the password differs from the previous password by at least-- 3 lettersIF old_password IS NOT NULL THENdiffer := length(old_password) - length(password);

differ := abs(differ);IF differ < 3 THENIF length(password) < length(old_password) THENm := length(password);ELSEm := length(old_password);END IF;

FOR i IN 1..m LOOPIF substr(password,i,1) != substr(old_password,i,1) THENdiffer := differ + 1;END IF;END LOOP;

IF differ < 3 THENraise_application_error(-20011, 'Password should differ from the \old password by at least 3 characters');END IF;END IF;END IF;-- Everything is fine; return TRUE ; RETURN(TRUE);END;/

-- This script alters the default parameters for Password Management-- This means that all the users on the system have Password Management-- enabled and set to the following values unless another profile is -- created with parameter values set to different value or UNLIMITED -- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 180PASSWORD_GRACE_TIME 7PASSWORD_REUSE_TIME UNLIMITEDPASSWORD_REUSE_MAX UNLIMITEDFAILED_LOGIN_ATTEMPTS 10PASSWORD_LOCK_TIME 1PASSWORD_VERIFY_FUNCTION verify_function_11G;

-- Below is the older version of the script

-- This script sets the default password resource parameters-- This script needs to be run to enable the password features.-- However the default resource parameters can be changed based -- on the need.-- A default password complexity function is also provided.-- This function makes the minimum complexity checks like-- the minimum length of the password, password not same as the-- username, etc. The user may enhance this function according to-- the need.-- This function must be created in SYS schema.-- connect sys/ as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function(username varchar2,password varchar2,old_password varchar2)RETURN boolean IS n boolean;m integer;differ integer;isdigit boolean;ischar boolean;ispunct boolean;digitarray varchar2(20);punctarray varchar2(25);chararray varchar2(52);

BEGIN digitarray:= '0123456789';chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';punctarray:='!"#$%&()``*+,-/:;?_';

-- Check if the password is same as the usernameIF NLS_LOWER(password) = NLS_LOWER(username) THENraise_application_error(-20001, 'Password same as or similar to user');END IF;

-- Check for the minimum length of the passwordIF length(password) < 4 THENraise_application_error(-20002, 'Password length less than 4');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') THENraise_application_error(-20002, 'Password too simple');END IF;

-- Check if the password contains at least one letter, one digit and one-- punctuation mark.-- 1. Check for the digitisdigit:=FALSE;m := length(password);FOR i IN 1..10 LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(digitarray,i,1) THENisdigit:=TRUE;GOTO findchar;END IF;END LOOP;END LOOP;IF isdigit = FALSE THENraise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');END IF;-- 2. Check for the characterischar:=FALSE;FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THENischar:=TRUE;GOTO findpunct;END IF;END LOOP;END LOOP;IF ischar = FALSE THENraise_application_error(-20003, 'Password should contain at least one \digit, one character and one punctuation');END IF;-- 3. Check for the punctuationispunct:=FALSE;FOR i IN 1..length(punctarray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(punctarray,i,1) THENispunct:=TRUE;GOTO endsearch;END IF;END LOOP;END LOOP;IF ispunct = FALSE THENraise_application_error(-20003, 'Password should contain at least one \digit, one character and one punctuation');END IF;

-- Check if the password differs from the previous password by at least-- 3 lettersIF old_password IS NOT NULL THENdiffer := length(old_password) - length(password);

IF abs(differ) < 3 THENIF length(password) < length(old_password) THENm := length(password);ELSEm := length(old_password);END IF;

differ := abs(differ);FOR i IN 1..m LOOPIF substr(password,i,1) != substr(old_password,i,1) THENdiffer := differ + 1;END IF;END LOOP;

IF differ < 3 THENraise_application_error(-20004, 'Password should differ by at \least 3 characters');END IF;END IF;END IF;-- Everything is fine; return TRUE ; RETURN(TRUE);END;/

-- This script alters the default parameters for Password Management-- This means that all the users on the system have Password Management-- enabled and set to the following values unless another profile is -- created with parameter values set to different value or UNLIMITED -- is created and assigned to the user.

-- Enable this if you want older version of the Password Profile parameters-- ALTER PROFILE DEFAULT LIMIT-- PASSWORD_LIFE_TIME 60-- PASSWORD_GRACE_TIME 10-- PASSWORD_REUSE_TIME 1800-- PASSWORD_REUSE_MAX UNLIMITED-- FAILED_LOGIN_ATTEMPTS 3-- PASSWORD_LOCK_TIME 1/1440-- PASSWORD_VERIFY_FUNCTION verify_function;