从4个方面实战oracle的密码操作

ptajxcqvnd

　　较好的实践是，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) < 4 THEN
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
<<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. Check for the punctuation
<<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>>
-- 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) < 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;
/