.jpg)
較好的實(shí)踐是，oracle的密碼操作通過profile來實(shí)現(xiàn)，而資源則是通過資源消費(fèi)組來控制，profile其實(shí)是種限制。 通過profile來控制密碼的使用，大抵有四： 1） 密碼的歷史 在這里，有兩個(gè)參數(shù)：password_reuse_time和password_reuse_max，比較好的實(shí)踐是，這兩
較好的實(shí)踐是，Oracle的密碼操作通過profile來實(shí)現(xiàn)，而資源則是通過資源消費(fèi)組來控制，profile其實(shí)是種限制。
通過profile來控制密碼的使用，大抵有四：
1） 密碼的歷史
??? 在這里，有兩個(gè)參數(shù)：password_reuse_time和password_reuse_max，比較好的實(shí)踐是，這兩個(gè)參數(shù)當(dāng)關(guān)聯(lián)起來使用。 如：password_reuse_time=30，password_reuse_max=10，
??? 用戶可以在30天以后重用該密碼，要求密碼必須被改變超過10次。
??? 實(shí)驗(yàn)：
??? 會(huì)話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.
??? 會(huì)話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.
??? 會(huì)話1：sys
??? sys@ORCL> alter user scott password expire;
??? User altered.
??? 會(huì)話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） 密碼的登入校驗(yàn)
??? 在這方面，也有兩個(gè)參數(shù):
??? failed_login_attempts:鎖定前允許的最大失敗登錄次數(shù)
??? password_lock_time:鎖定時(shí)間
??? 實(shí)驗(yàn)：
??? 會(huì)話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.
??? 會(huì)話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） 密碼的生命周期
??? 同樣地，這也是有兩個(gè)參數(shù)：
??? password_life_time:密碼的壽命
??? password_grace_time:寬限時(shí)間，特指將達(dá)到壽命前的那些時(shí)光
??? 實(shí)驗(yàn)：
??? 會(huì)話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.
??? 會(huì)話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） 密碼的復(fù)雜性
??? 在$Oracle_HOME/rdbms/admin/utlpwdmg.sql,有個(gè)密碼函數(shù)，借此，則可控制密碼復(fù)雜性
??? 現(xiàn)將該函數(shù)摘入如下：
??? 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;
??? /
.png)
