oracle密码维护
来源：互联网 发布：淘旧书 知乎 编辑：程序博客网 时间：2024/06/05 05:31
查看密码是否可以重复使用
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 10 --尝试10次，自动锁用户
DEFAULT PASSWORD_LIFE_TIME 180--密码有效期
DEFAULT PASSWORD_REUSE_TIME UNLIMITED--密码重用时间
DEFAULT PASSWORD_REUSE_MAX UNLIMITED--密码重用次数
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_GRACE_TIME 7--密码过期的提示时间
查看密码的过期时间
SQL> set linesize 200
SQL> select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users;
USERNAME EXPIRY_DATE ACCOUNT_STATUS
------------------------------ ------------------ --------------------------------
YTPBL 01-MAR-14 OPEN
YTMDM 01-MAR-14 OPEN
YTRUT 01-MAR-14 OPEN
YTMOT 01-MAR-14 OPEN
YTFLT 01-MAR-14 OPEN
YTEXP 01-MAR-14 OPEN
YTITF 01-MAR-14 OPEN
YTTRC 12-AUG-14 OPEN
YTMON 27-APR-14 OPEN
MONITOR 29-MAR-14 OPEN
SKYEYE 30-APR-14 OPEN
修改profile文件，立即生效
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------------------------------------ ---------------------------------------------------------------- --------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME UNLIMITED
SQL> alter profile default limit password_life_time 7;
Profile altered.
SQL> alter profile default limit password_reuse_time 7;
Profile altered.
SQL> alter profile default limit password_reuse_max 7;
Profile altered.
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------------------------------------ ---------------------------------------------------------------- --------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 3
DEFAULT PASSWORD_LIFE_TIME 7
DEFAULT PASSWORD_REUSE_TIME 7
DEFAULT PASSWORD_REUSE_MAX 7
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME UNLIMITED
7 rows selected.
针对profile是否能够影响到resource limitation，有个初始化参数很重要，默认是false，如果要开启profile里的限制功能就需要修改成true
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
System altered.
运行管理密码的脚本，生成相应的密码管理规则的函数
SQL> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
Function created.
启用调用密码管理规则的函数
SQL> alter profile default limit password_verify_function verify_function;
此时修改密码报错，因为太过简单
SQL> alter user test identified by test1;
alter user test identified by test1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one \
digit, one character and one punctuation
SQL> alter user test identified by "test<?89t";
User altered.
取消密码函数的验证规则
SQL> alter system set resource_limit=FALSE;
System altered.
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> alter user test identified by test;
User altered.
简单解读一下utlpwdmg.sql 脚本
创建一一个verify_function函数
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:='!"#$%&()``*+,-/:;<=>?_';
-- 检查密码是否与用户名相同
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- 检查密码长度
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- 检查密码简单性，及一些关键词，这里可以自己自定义
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. 检查密码是够带数字
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(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. 检查密码是否有字符
<<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 findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. 检查密码是否有特殊的字符
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m 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(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
--检查密码与之前的旧密码至少相差3个以上的字符
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
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(-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.
--修改profile里的默认限制，这里可以自己规定额
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;
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 10 --尝试10次，自动锁用户
DEFAULT PASSWORD_LIFE_TIME 180--密码有效期
DEFAULT PASSWORD_REUSE_TIME UNLIMITED--密码重用时间
DEFAULT PASSWORD_REUSE_MAX UNLIMITED--密码重用次数
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_GRACE_TIME 7--密码过期的提示时间
查看密码的过期时间
SQL> set linesize 200
SQL> select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users;
USERNAME EXPIRY_DATE ACCOUNT_STATUS
------------------------------ ------------------ --------------------------------
YTPBL 01-MAR-14 OPEN
YTMDM 01-MAR-14 OPEN
YTRUT 01-MAR-14 OPEN
YTMOT 01-MAR-14 OPEN
YTFLT 01-MAR-14 OPEN
YTEXP 01-MAR-14 OPEN
YTITF 01-MAR-14 OPEN
YTTRC 12-AUG-14 OPEN
YTMON 27-APR-14 OPEN
MONITOR 29-MAR-14 OPEN
SKYEYE 30-APR-14 OPEN
修改profile文件，立即生效
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------------------------------------ ---------------------------------------------------------------- --------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME UNLIMITED
SQL> alter profile default limit password_life_time 7;
Profile altered.
SQL> alter profile default limit password_reuse_time 7;
Profile altered.
SQL> alter profile default limit password_reuse_max 7;
Profile altered.
SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and resource_type ='PASSWORD';
PROFILE RESOURCE_NAME LIMIT
------------------------------------------------------------ ---------------------------------------------------------------- --------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS 3
DEFAULT PASSWORD_LIFE_TIME 7
DEFAULT PASSWORD_REUSE_TIME 7
DEFAULT PASSWORD_REUSE_MAX 7
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME UNLIMITED
7 rows selected.
针对profile是否能够影响到resource limitation，有个初始化参数很重要，默认是false，如果要开启profile里的限制功能就需要修改成true
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
System altered.
运行管理密码的脚本，生成相应的密码管理规则的函数
SQL> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
Function created.
启用调用密码管理规则的函数
SQL> alter profile default limit password_verify_function verify_function;
此时修改密码报错，因为太过简单
SQL> alter user test identified by test1;
alter user test identified by test1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one \
digit, one character and one punctuation
SQL> alter user test identified by "test<?89t";
User altered.
取消密码函数的验证规则
SQL> alter system set resource_limit=FALSE;
System altered.
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> alter user test identified by test;
User altered.
简单解读一下utlpwdmg.sql 脚本
创建一一个verify_function函数
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:='!"#$%&()``*+,-/:;<=>?_';
-- 检查密码是否与用户名相同
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- 检查密码长度
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- 检查密码简单性，及一些关键词，这里可以自己自定义
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. 检查密码是够带数字
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(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. 检查密码是否有字符
<<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 findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. 检查密码是否有特殊的字符
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m 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(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
--检查密码与之前的旧密码至少相差3个以上的字符
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
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(-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.
--修改profile里的默认限制，这里可以自己规定额
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;
0 0
- oracle密码维护
- oracle wallet使用与维护---oracle无密码登录
- oracle wallet使用与维护---oracle无密码登录
- Oracle数据库密码文件的使用和维护
- Oracle数据库密码文件的使用和维护
- Oracle数据库密码文件的使用和维护
- Oracle数据库密码文件的使用和维护
- Oracle 数据库密码文件的使用和维护
- Oracle密码文件的创建、使用和维护
- oracle 维护
- oracle维护
- Oracle密码文件的使用和维护 ORACLE教程 教程作者：佚名 教程来源：不详 教程栏目：ORACLE教程
- 关于代码中的密码维护
- oracle数据库日常维护
- Oracle数据库日常维护
- Oracle数据库日常维护
- 技术--oracle维护手册
- [ORACLE维护]清用户
- java如何使Double类型保留两位小数;
- 如何分析SQL Server中的deadlock trace
- Tab控制器
- C++基础-链表的建立、插入和删除
- Android Bundle类
- oracle密码维护
- JavaScript中类的实现
- 处理CLOB，BLOB，Date，Timestamp数据类型的方法
- 在新浪SAE上学习PHP - 2(转换json格式)
- HDU 1195 双广
- HTML5笔记（一）
- 加个动作试一下
- 是不是不努力看起来更潇洒
- Unity3D的FingerGesture插件
