oracle数据库密码复杂度查询,Oracle11gR2创建
PASSWORD_VERIFY。。。
Oracle 11gR2创建PASSWORD_VERIFY_FUNCTION对应密码复杂度验证函数步骤
运⾏测试环境：数据库服务器Oracle Linux 5.8 + Oracle 11g R2数据库
相关⼯具：PL/SQL软件(连接Linux下的Oracle数据库),SecureCRT软件(远程连接Linux服务器)
详细步骤：
1、连接上Linux数据库服务器，切换到Oracle数据库⽤户桌⾯，打开终端，进⼊到环境变量$ORACLE_HOME⽬录
Last login: Fri Dec 11 13:26:18 2015 from 192.168.1.100
[root@Linux主机名 ~]# su - oracle
[oracle@Linux主机名 dbhome_1]$ cd $ORACLE_HOME/rdbms/admin
[oracle@Linux主机名 admin]$
2、查看Oracle11g数据库提供的默认密码复杂度函数脚本(Oracle安装⽬录下的/rdbms/admin/utlpwdmg.sql⽂件) [oracle@Linux主机名 admin]$ cat $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
脚本详细内容详见⽂章末尾
3、登录Oracle数据库并执⾏Oracle11g数据库提供的默认密码复杂度函数脚本
[oracle@Linux主机名 admin]$ sqlplus /nolog
SQL*Plus: Relea 11.2.0.1.0 Production on Fri Dec 11 13:33:58 2015
Copyright (c) 1982, 2009, Oracle. All rights rerved.
SQL> conn /as sysdba
Connected.
SQL> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
Function created.
SQL>
4、在PL/SQL中创建⽤户的资源⽂件，执⾏下⾯语句
CREATE PROFILE 资源⽂件名 LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNL凉茶有哪些
IMITED
CONNECT_TIME UNLIMITED
IDLE_TIME 600 --10⼩时连续不活动的话系统⾃动断开连接
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRI白金和铂金的区别
VATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10 --指定锁定⽤户的登录失败次数为10次，超过10次则系统被⾃动锁定
PASSWORD_LIFE_TIME 180 --指定⽤户同⼀密码锁允许使⽤的天数为180天
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1 --指定⽤户被锁定天数为1天
PASSWORD_GRACE_TIME 10 --数据库发出警告到登录失效前的宽限天数
PASSWORD_VERIFY_FUNCTION verify_function腹膜腔
_11G
5、测试更新⽤户密码
--创建⽤户并使⽤⾃定义的配置⽂件
create ur ⽤户名 identified by 密码 default tablespace 默认表空间名 temporary tablespace 临时表空间名 profile 资源⽂件名; --⽤户授权
grant connect,resource,exp_full_databa,imp_full_databa to ⽤户名;
--更新⽤户密码为简单的字符串
alter ur ⽤户名 identified by 123456;
--更新⽤户密码为复杂的字符串
alter ur ⽤户名 identified by Csdn_20151211;
6、结论：发现简单密码⽆法更新，复杂的密码更新成功。
附：$ORACLE_HOME/rdbms/admin/utlpwdmg.sql脚本源⽂件内容
Rem
Rem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) 2006, Oracle. 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 教育研究论文
Thi兔子介绍
s file contains a function for minimum checking of p美丽的春天作文
assword
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 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/ 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 varchar2(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) < 8 THEN
rai_application_error(-20001, 'Password length less than 8');
END IF;
-- Check if the password is sa工作说明书范文
me 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 IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(urname)|| i_char = NLS_LOWER(password) THEN
rai_application_error(-20005, 'Password same as or similar to ur name '); 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 IN 1..100 LOOP
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') THEN
rai_application_error(-20心里委屈的说说
006, 'Password too simple');
END IF;
-- 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
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 IN 1..10 LOOP
FOR j LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
