PASSWORD_VERIFY_FUNCTION（⼝令复杂性验证）PASSWORD_VERIFY_FUNCTION（⼝令复杂性验证）
在官⽅⽂档中该参数的阐述：
PASSWORD_VERIFY_FUNCTION⼦句允许PL/SQL密码复杂性验证脚本作为参数传递给CREATEPROFILE语句
⼀、PASSWORD_VERIFY_FUNCTION参数语法：
语法：
ALTER PROFILE profile LIMIT
{ resource_parameters | password_parameters } ...
;
password_parameters 中 PASSWORD_VERIFY_FUNCTION 部分如下:
{| PASSWORD_VERIFY_FUNCTION
{ function|NULL|DEFAULT }
}
对于FUNCTION（函数）指定密码复杂度验证例程的名称，该FUNCTION必须存在于SYS⽤户中，并且您必须对该函数具有执⾏权限.
指定NULL表⽰未执⾏密码验证.
⼆、Oracle数据库提供默认脚本中创建的两个例程
Oracle数据库提供默认脚本，在11g数据库提供默认脚本中可以创建的两个例程，但你也可以创建⾃⼰的例程或使⽤第三⽅软件
1、verify_function_11G
- 密码复杂度：
密码必须⾄少包含⼀个数字，⼀个字符
密码长度⾄少为8
2、verify_function
- 密码复杂度：
密码必须包含⾄少⼀个数字，⼀个字符和⼀个标点符号
密码长度⾄少为4
3、执⾏脚本
$ 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（⼝令复杂性验证）
1、查看当前开启⽤户及其profile
lect urname,profile from dba_urs 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;
5、若⾃⼰有对密码复杂度有其他需求，把 “utlpwdmg.sql” 脚本按需修改执⾏即可，原脚本内容如下：
Rem
Rem $Header: rdbms/admin/utlpwdmg.sql/st_rdbms_11.2.0/12013/01/3101:34:11 skayoor Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c)2006,2013, Oracle and/or its affiliates.
Rem All rights rerved.
Rem
Rem NAME
Rem utlpwdmg.sql- script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by tting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can u to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem skayoor 01/17/13- Backport skayoor_bug-14671375from main
Rem asurpur 05/30/06- fix -5246666 beef up password complexity check
Rem nireland 08/31/00- Improve check for urname=password.#1390553
Rem nireland 06/28/00- Fix null old password test.#1341892
Rem asurpur 04/17/97- Fix for bug479763
Rem asurpur 12/12/96- Changing the name of password_verify_function Rem asurpur 05/30/96- New script for default password management
Rem asurpur 05/30/96- Created
Rem
-- This script ts the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed bad
-- 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
-- urname, etc. The ur 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
CREATE OR REPLACE FUNCTION verify_function_11G
(urname 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 varchar师德培训体会
2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
rever_ur varchar2(32);
BEGIN
digitarray:='0123456789';
chararray:='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-
- Check for the minimum length of the password
IF length(password)<8THEN
rai_application_error(-20001,'Password length less than 8');
END IF;
-- Check if the password is same as the urname or urname(1-100)
IF NLS_LOWER(password)= NLS_LOWER(urname)THEN
rai_application_error(-20002,'Password same as or similar to ur');
END IF;
FOR i IN1..100LOOP
i_char := to_char(i);
if NLS_LOWER(urname)|| i_c应用文写作总结
har = NLS_LOWER(password)THEN
rai_application_error(-20005,'Password same as or similar to ur na有书真好作文600字
me ');
END IF;
END LOOP;
-- Check if the password is same as the urname reverd
FOR i in REVERSE 1..length(urname)LOOP
rever_ur := rever_ur || substr(urname, i,1);
END LOOP;
IF NLS_LOWER(password)= NLS_LOWER(rever_ur)THEN
rai_application_error(-20003,'Password same as urname reverd');
END IF;
-- Check if the password is the same as rver name and or rvername(1-100)
lect name into db_name from sys.v$databa;
if NLS_LOWER(db_name)= NLS_LOWER(password)THEN
rai_application_error(-20004,'Password same as or similar to rver name');
END IF;
FOR i IN1..100LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password)THEN
rai_application_error(-20005,'Password same as or similar to rver 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','databa1','account1','ur1234','password1','oracle123','computer1','abcdefg1','change_on_install')T HEN
rai_application_error(-20006,'Passwo祖国画
rd too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password :='oracle';
FOR i IN1..100LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password)THEN
rai_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 digit
isdigit:=FALSE;
m := length(password);
FOR i IN1..10LOOP
FOR 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
rai_application_error(-20008,'Password must contain at least one digit, one character');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN1..length(chararray)LOOP
FOR LOOP
IF substr(password,j,1)= substr(chararray,i,1)THEN
ischar:=TRUE;
GOTO endarch;
END IF;
END LOOP;
END LOOP;
IF ischar =FALSE THEN
rai_application_error(-20009,'Password must contain at least one \
digit, and one character');
END IF;
<<endarch>>
-- Check if the password differs from the previous password by at least
-- 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 <3THEN
IF length(password)< length(old_password)THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR LOOP
IF substr(password,i,1)!= substr(old_pa如何写议论文
ssword,i,1)THEN
differ := differ +1;
END IF;
END LOOP;
IF differ <3THEN
rai_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;
/
GRANT EXECUTE ON verify_function_11G TO PUBLIC;
-- This script alters the default parameters for Password Management
-- This means that all the urs on the system have Password Management -- enabled and t to the following values unless another profile is
-- created with parameter values t to different value or UNLIMITED
-- is created and assigned to the ur.
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;
-- Below is the older version of the script
-- This script ts the default password resource parameters
-- This script needs to be run to enable the password features.
-
- However the default resource parameters can be changed bad
-- 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
-- urname, etc. The ur 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
CREATE OR REPLACE FUNCTION verify_function
(urname varchar2,
password varchar2,
