Oracle密码复杂度PASSWORD_VERIFY_FUNCTION _password

博观而约取，厚积而薄发。这篇文章主要讲述Oracle密码复杂度PASSWORD_VERIFY_FUNCTION相关的知识，希望能为你提供帮助。
PASSWORD_VERIFY_FUNCTION（口令复杂性验证）

在官方文档中该参数的阐述： PASSWORD_VERIFY_FUNCTION子句允许PL/SQL密码复杂性验证脚本作为参数传递给CREATEPROFILE语句

一、PASSWORD_VERIFY_FUNCTION参数语法：语法：

ALTER PROFILE profile LIMIT resource_parameters | password_parameters... ; | PASSWORD_VERIFY_FUNCTION function | NULL | DEFAULT

对于FUNCTION（函数）指定密码复杂度验证例程的名称，该FUNCTION必须存在于SYS用户中，并且您必须对该函数具有执行权限.
指定NULL表示未执行密码验证.
二、Oracle数据库提供默认脚本中创建的两个例程1、verify_function_11G

- 密码复杂度：密码必须至少包含一个数字，一个字符密码长度至少为8

2、verify_function

- 密码复杂度：密码必须包含至少一个数字，一个字符和一个标点符号密码长度至少为4

3、执行脚本
```html/xml
$ cd $ORACLE_HOME
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlpwdmg.sql
Function created.
Grant succeeded.
Profile altered.
Function created.
Grant succeeded.
SQL>

4、在profile中修改PASSWORD_VERIFY_FUNCTION（口令复杂性验证） ```html/xml 1、查看当前开启用户及其profile select username,profile from dba_users where account_status=OPEN; 2、使用缺省的profile-DEFAULT 修改口令复杂性验证为 "VERIFY_FUNCTION_11G" 例程 #即：密码必须至少包含一个数字，一个字符，密码长度至少为8 alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G; 3、使用缺省的profile-DEFAULT 修改口令复杂性验证为 "verify_function" 例程 #即：密码必须包含至少一个数字，一个字符和一个标点符号，密码长度至少为4 alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION; 4、关闭密码口令复杂度认证 alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null;

【Oracle密码复杂度PASSWORD_VERIFY_FUNCTION】5、若自己有对密码复杂度有其他需求，把 “utlpwdmg.sql” 脚本按需修改执行即可，原脚本内容如下：

RemRem $Header: rdbms/admin/utlpwdmg.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $RemRem utlpwdmg.sqlRemRem Copyright (c) 2006, 2013, Oracle and/or its affiliates.Rem All rights reserved.RemRemNAMERemutlpwdmg.sql - script for Default Password Resource LimitsRemRemDESCRIPTIONRemThis is a script for enabling the password management featuresRemby setting the default password resource limits.RemRemNOTESRemThis file contains a function for minimum checking of passwordRemcomplexity. This is more of a sample function that the customerRemcan use to develop the function for actual complexity checks that theRemcustomer wants to make on the new password.RemRemMODIFIED(MM/DD/YY)Remskayoor01/17/13 - Backport skayoor_bug-14671375 from mainRemasurpur05/30/06 - fix - 5246666 beef up password complexity checkRemnireland08/31/00 - Improve check for username=password. #1390553Remnireland06/28/00 - Fix null old password test. #1341892Remasurpur04/17/97 - Fix for bug479763Remasurpur12/12/96 - Changing the name of password_verify_functionRemasurpur05/30/96 - New script for default password managementRemasurpur05/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/< password> as sysdba before running the script 接着，文件中定义了一个密码复杂度校验函数 VERIFY_FUNCTION_11G ，并预先声明了一些将要使用的变量及其变量类型。 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; ischarboolean; db_name varchar2(40); digitarray varchar2(20); chararray varchar2(52); i_char varchar2(10); simple_password varchar2(10); reverse_user varchar2(32); 接着是函数体部分，这块儿才是重点，BEGIN开始函数体，为变量digitarray、chararray赋值，用于后续检测密码中是否含有数字和大小写字母。 BEGIN digitarray:= 0123456789; chararray:= abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ; 接着正式进入了密码复杂度检测的部分，首先对密码长度进行了检测，要求密码长度大于等于8位，若密码长度小于8位，则放回一个错误-20001 -- Check for the minimum length of the password IF length(password) < 8 THEN raise_application_error(-20001, Password length less than 8); END IF; ?接着比对了密码和用户名的相似度，若密码与用户名相同或与用户名后拼接上1-100的数字相同，则返回错误 -20002，-20005 -- Check if the password is same as the username or username(1-100) IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20002, Password same as or similar to user); END IF; FOR i IN 1..100 LOOP i_char := to_char(i); if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN raise_application_error(-20005, Password same as or similar to user name ); END IF; END LOOP; ?判断密码与用户名的逆序是否相同，若相同则返回错误 -20003 -- Check if the password is same as the username reversedFOR i in REVERSE 1..length(username) LOOP reverse_user := reverse_user || substr(username, i, 1); END LOOP; IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN raise_application_error(-20003, Password same as username reversed); END IF; ?比对了密码和服务器名的相似度，若密码与服务器名相同或与服务器名后拼接上1-100的数字相同，则返回错误 -20004，-20005 -- 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) THEN raise_application_error(-20004, Password same as or similar to server name); END IF; FOR i IN 1..100 LOOP i_char := to_char(i); if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN raise_application_error(-20005, Password same as or similar to server name ); END IF; END LOOP; 比对密码是否为‘welcome1’、‘database1’、‘account1’、‘user1234’这类弱口令，若是则返回错误-20006 -- 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) THEN raise_application_error(-20006, Password too simple); END IF; ?比对密码是否为oracle或oracle拼接上1-100的数字，若相同则返回错误 -20007 -- Check if the password is the same as oracle (1-100) simple_password := oracle; FOR i IN 1..100 LOOP i_char := to_char(i); if simple_password || i_char = NLS_LOWER(password) THEN raise_application_error(-20007, Password too simple ); END IF; END LOOP; 接下来是verify_function_11G函数判断密码是否含有数字和字符的部分? 判断密码是否包含至少一个数字(0-9)，若不包含则返回错误-20008 -- Check if the password contains at least one letter, one digit -- 1. Check for the digit isdigit:=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) THEN isdigit:=TRUE; GOTO findchar; END IF; END LOOP; END LOOP; IF isdigit = FALSE THEN raise_application_error(-20008,Password should contain at least one \\ digit, one character); END IF; ?判断密码是否包含至少一个字母(a-z，A-Z)，若不包含则返回错误-20009 -- 2. Check for the character < < findchar> > ischar:=FALSE; FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THEN ischar:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20009, Password should contain at least one \\ digit, one character); END IF; < < endsearch> > 到这一步，VERIFY_FUNCTION_11G这个密码复杂度校验函数就结束了。? 判断新密码是否与原密码至少存在3个不同的字符，如果不存在则返回错误-20011 -- Check if the password differs from the previous password by at least -- 3 letters IF old_password IS NOT NULL THEN differ := length(old_password) - length(password); differ := abs(differ); IF differ < 3 THEN IF length(password) < length(old_password) THEN m := length(password); ELSE m := length(old_password); END IF; FOR i IN 1..m LOOP IF substr(password,i,1) != substr(old_password,i,1) THEN differ := differ + 1; END IF; END LOOP; IF differ < 3 THEN raise_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; / 赋予PUBLIC用户verify_function的函数执行权限，并更改ORACLE的profile文件，配置密码的最常使用期限为180天、密码过期锁定时间为7天（即密码超过180天使用期限后若7天内不更改密码则会将该账户锁定）、密码重用时间为不限制、密码重用最大时间为不限制（两个参数都为UNLIMITED时，密码可以随意重用，两参数均为指定值时，必须都满足才可以重用密码。两参数有其中一个不为UNLIMITED，则密码不能重用）、登陆失败锁定次数为10次、密码锁定时间为一天、密码复杂度校验函数为verify_function_11G。 GRANT EXECUTE ON verify_function_11G TO PUBLIC; -- 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 LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION verify_function_11G;