较%ignore_a_1%实践是，Oracle的密码操作通过profile来实现，而资源则是通过资源消费组来控制，profile其实是种限制。 通过profile来控制密码的使用，大抵有四： 1） 密码的历史 在这里，有两个参数：password_reuse_time和password_reuse_max，比较好的实践是，这两
较好的实践是，Oracle的密码操作通过profile来实现，而资源则是通过资源消费组来控制，profile其实是种限制。
通过profile来控制密码的使用，大抵有四：
1） 密码的历史
在这里，有两个参数：password_reuse_time和password_reuse_max，比较好的实践是，这两个参数当关联起来使用。 如：password_reuse_time=30，password_reuse_max=10，
用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
实验：
会话1：sys
sys@ORCL> create profile p1 limit password_reuse_time 1/1440 password_reuse_max 1;
Profile created.
sys@ORCL> alter user scott profile p1;
User altered.
sys@ORCL> alter user scott password expire;
User altered.
sys@ORCL> alter profile p1 limit password_reuse_time 5/1440 password_reuse_max 1;–5分钟后可重用该密码，但这期间必须要被改成其他密码一次
Profile altered.
sys@ORCL> alter user scott password expire;
User altered.
会话2：scott
scott@ORCL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[Oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Sep 3 01:11:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
idle> conn scott/Oracle
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: –使用原密码，即Oracle
Retype new password:
ERROR:
ORA-28007: the password cannot be reused
Password unchanged
idle> conn scott/Oracle
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: –使用新密码，改成think
Retype new password:
Password changed
Connected.
会话1：sys
sys@ORCL> alter user scott password expire;
User altered.
会话2：scott
scott@ORCL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[Oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Sep 3 01:19:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
idle> conn scott/think
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: –使用最早的密码，即Oracle
Retype new password:
Password changed
Connected.
scott@ORCL>
2） 密码的登入校验
在这方面，也有两个参数:
failed_login_attempts:锁定前允许的最大失败登录次数
password_lock_time:锁定时间
实验：
会话1：sys
sys@ORCL> drop profile p1 cascade;
Profile dropped.
sys@ORCL> create profile p1 limit failed_login_attempts 1 password_lock_time 1/1440;–失败一次就被锁，被锁1分钟
Profile created.
sys@ORCL> alter user scott profile p1;
User altered.
会话2：scott
[Oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Sep 3 01:42:46 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
idle> conn scott/think
ERROR:
ORA-01017: invalid username/password; logon denied
idle> conn scott/Oracle
ERROR:
ORA-28000: the account is locked
idle> conn scott/Oracle –1分钟之后
Connected.
3） 密码的生命周期
同样地，这也是有两个参数：
password_life_time:密码的寿命
password_grace_time:宽限时间，特指将达到寿命前的那些时光
实验：
会话1：sys
sys@ORCL> drop profile p1 cascade;
Profile dropped.
sys@ORCL> create profile p1 limit password_life_time 2/1440 password_grace_time 2/1440;
Profile created.
sys@ORCL> alter user scott profile p1;
User altered.
会话2：scott
[Oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Sep 3 01:56:59 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
idle> conn scott/Oracle
ERROR:
ORA-28002: the password will expire within 0 days
Connected.
4） 密码的复杂性
在$Oracle_HOME/rdbms/admin/utlpwdmg.sql,有个密码函数，借此，则可控制密码复杂性
现将该函数摘入如下：
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 username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, ‘Password same as or similar to user’);
END IF;
— Check for the minimum length of the password
IF length(password) raise_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’) 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. 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(-20003, ‘Password should contain at least one digit, one character and one punctuation’);
END IF;
— 2. Check for the character
>
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. Check for the punctuation
>
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;
>
— 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);
IF abs(differ) IF length(password) 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 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;
/
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/466492.html
微信扫一扫 
支付宝扫一扫 
