较好的实践是，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;
/