【PROFILE】PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX引數在Oracle不同版本中的差別

jichengjie發表於2018-04-18
在《【PROFILE】使用Oracle的PROFILE對使用者資源限制和密碼限制的研究與探索》文章中介紹了Oracle的PROFILE的很多細節。其中PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX在Oracle的不同版中有較大的變化,這裡我們再做一下深入的測試和討論。
文章連結:http://space.itpub.net/519536/viewspace-616287


以下實驗以Oracle 10.2.0.3和Oracle 8.1.6兩個資料庫版本為例進行演示。萬變不離其宗,其他版本的實驗請自行嘗試,不再贅述。

一、先羅列一下在Oracle不同版本的官方文件中關於這兩個引數的簡要描述資訊
比較一下便可得到如下結論:
1.Oracle  8和Oracle  9的描述是一樣的,與10和11的描述不同;
2.Oracle 10和Oracle 11的描述是一樣的,與 8和 9的描述不同;
3.在Oracle  8和 9的版本中這兩個引數是不能同時有值的,每個引數是單獨生效的;
4.在Oracle 10和11的版本中這兩個引數必須同時有值才生效,任何一個引數如被設定為unlimited,相同的密碼將永遠不能被重新使用。

(1)Oralce 8
Oracle8i SQL Reference
Release 3 (8.1.7)
參考連結:
PASSWORD_REUSE_TIME
Specify the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

PASSWORD_REUSE_MAX
Specify the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED.

(2)Oracle 9i
Oracle9i SQL Reference
Release 2 (9.2)
參考連結:
PASSWORD_REUSE_TIME
Specify the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

PASSWORD_REUSE_MAX
Specify the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED.

(3)Oracle 10g
10g Release 2 (10.2)
參考連結:
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX  These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.

(4)Oracle 11gR1
11g Release 1 (11.1)
參考連結:
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX  These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.

(5)Oracle 11gR2
11g Release 2 (11.2)
參考連結:
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX  These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify a value for both of them.


二、我們在Oracle 10gR2的環境下實驗驗證一下關於PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX這兩個引數聯合使用的效果
1.確認資料庫版本
sys@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

2.建立PROFILE的規則釋義:超過一分鐘,並且密碼變更次數超過三後,密碼可以重用
sys@ora10g> CREATE PROFILE sec_profile LIMIT
  2     PASSWORD_REUSE_TIME 1/1440
  3     PASSWORD_REUSE_MAX  3
  4  /

Profile created.

3.驗證一下規則
sys@ora10g> SELECT   *
  2    FROM   dba_profiles
  3   WHERE   RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX')
  4           AND PROFILE = 'SEC_PROFILE';

PROFILE           RESOURCE_NAME                    RESOURCE LIMIT
----------------- -------------------------------- -------- ----------
SEC_PROFILE       PASSWORD_REUSE_TIME              PASSWORD .0006
SEC_PROFILE       PASSWORD_REUSE_MAX               PASSWORD 3

4.將新建立的PROFILE sec_profile授給sec使用者
sys@ora10g> alter user sec profile sec_profile;

User altered.

sys@ora10g> select USERNAME,PROFILE from dba_users where USERNAME = 'SEC';

USERNAME                       PROFILE
------------------------------ ------------------------------
SEC                            SEC_PROFILE

5.使用“set time on”命令開啟時間顯示,開始我們的實驗
1)我們先來實驗第一種場景:就是不滿足密碼改變次數,時限要求滿足和不滿足時的效果
(1)開啟時鐘顯示
sys@ora10g> set time on

(2)為了演示方便,我們先將使用者sec的密碼初始哈設定為“PassWord_0”
19:18:00 sys@ora10g> alter user sec identified by PassWord_0;

User altered.

(3)對於密碼“PassWord_0”的第一次密碼改變,我們可以透過下面的方法進行模擬
19:18:10 sys@ora10g> alter user sec identified by PassWord_1;

User altered.

(4)對於密碼“PassWord_0”的第二次密碼改變,我們可以透過下面的方法進行模擬
19:18:20 sys@ora10g> alter user sec identified by PassWord_2;

User altered.

(5)OK,我們先不模擬第三次變化,在沒有超過1分鐘的時限裡,我們嘗試重用密碼“PassWord_0”,意料之中,無法進行修改(原因:沒有滿足3次密碼變化的限制)
19:18:49 sys@ora10g> alter user sec identified by PassWord_0;
alter user sec identified by PassWord_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused

(6)在超過1分鐘的時限後,再次嘗試重用密碼“PassWord_0”,同樣,無法完成修改任務(原因:沒有滿足3次密碼變化的限制)
19:19:08 sys@ora10g> alter user sec identified by PassWord_0;
alter user sec identified by PassWord_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused

(7)此時,我們再來完成對於密碼“PassWord_0”的第三次密碼改變
19:19:09 sys@ora10g> alter user sec identified by PassWord_3;

User altered.

(8)此時相對於第一次使用這個“PassWord_0”密碼的時間間隔已經超過1分鐘,正如Oracle 10g官方文件中所述的一樣,我們已經可以重新使用這個密碼
19:19:15 sys@ora10g> alter user sec identified by PassWord_0;

User altered.

2)我們再來實驗另外一種場景:就是滿足密碼改變次數,但是沒有滿足時限要求的情況
(1)為了不與上面的密碼衝突,我們這次使用“pwd_0”這個密碼作為演示用例
19:43:00 sys@ora10g> alter user sec identified by pwd_0;

User altered.

(2)第一次密碼改變
19:43:10 sys@ora10g> alter user sec identified by pwd_1;

User altered.

(2)第二次密碼改變
19:43:20 sys@ora10g> alter user sec identified by pwd_2;

User altered.

(2)第三次密碼改變,到此,已經滿足重用“pwd_0”這個密碼的密碼修改次數的限制,但是還沒有滿足時限的限制
19:43:30 sys@ora10g> alter user sec identified by pwd_3;

User altered.

(3)連續嘗試重用“pwd_0”密碼,無法成功(原因:沒有滿足時限的限制)
19:43:40 sys@ora10g> alter user sec identified by pwd_0;
alter user sec identified by pwd_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused


19:43:50 sys@ora10g> alter user sec identified by pwd_0;
alter user sec identified by pwd_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused


19:43:55 sys@ora10g> alter user sec identified by pwd_0;
alter user sec identified by pwd_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused


19:43:59 sys@ora10g> alter user sec identified by pwd_0;
alter user sec identified by pwd_0
*
ERROR at line 1:
ORA-28007: the password cannot be reused

(4)當時限一過,密碼便可以順利的被重用了,符合Oracle 10g官方文件中的描述
19:44:00 sys@ora10g>
19:44:01 sys@ora10g> alter user sec identified by pwd_0;

User altered.

3)同樣可以使用上述的實驗過程,驗證當PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX其中任何一個引數(或兩個引數同時)被設定為“UNLIMITED”,密碼將永遠無法進行重用的規定。
(1)這裡我們只演示一種情況:PASSWORD_REUSE_TIME設定為unlimited,不做限制,PASSWORD_REUSE_MAX仍然為3
sys@ora10g> ALTER PROFILE sec_profile LIMIT
  2     PASSWORD_REUSE_TIME unlimited
  3     PASSWORD_REUSE_MAX  3
  4  /

Profile altered.

sys@ora10g> SELECT   *
  2    FROM   dba_profiles
  3   WHERE   RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX')
  4           AND PROFILE = 'SEC_PROFILE';

PROFILE       RESOURCE_NAME                    RESOURCE LIMIT
------------- -------------------------------- -------- --------------
SEC_PROFILE   PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
SEC_PROFILE   PASSWORD_REUSE_MAX               PASSWORD 3

(2)這樣限制後,無論嘗試多少次(這裡我們演示5次不同的變化次數),同樣的密碼都不會被重用(在Oracle 8和9版本中這樣設定後,三次改變之後便可以重新使用之前的密碼)
sys@ora10g> alter user sec identified by pwd_00;

User altered.

sys@ora10g> alter user sec identified by pwd_01;

User altered.

sys@ora10g> alter user sec identified by pwd_02;

User altered.

sys@ora10g> alter user sec identified by pwd_03;

User altered.

sys@ora10g> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused


sys@ora10g> alter user sec identified by pwd_04;

User altered.

sys@ora10g> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused


sys@ora10g> alter user sec identified by pwd_05;

User altered.

sys@ora10g> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused

6.小結
透過上面三個實驗場景的設計,我們已經可以驗證10g官方文件中關於PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX這裡兩個引數使用的描述了,再貼一段Oracle 10g官方文件關於設定細節的描述,便於比照上面的實驗進行比較
If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.

For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.

If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.

If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.

If you set both of these parameters to UNLIMITED, then the database ignores both of them.

三、我們在Oracle 8的環境下實驗驗證一下關於PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX這兩個引數單獨生效的文件描述
1.確認資料庫版本(我這裡使用一臺骨灰級別的Oracle 8.1.6作為實驗環境)
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0    Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

2.建立使用者sec並授權
SQL> create user sec identified by sec;
SQL> grant dba to sec;

3.OK,我們還是用在上面在10g上的建立語句,很顯然,在Oracle 8的環境中,這樣去建立是不被允許的(Oracle 8的官方文件中描述的完全正確)
SQL> CREATE PROFILE sec_profile LIMIT
  2  PASSWORD_REUSE_TIME 1/1440
  3  PASSWORD_REUSE_MAX  3
  4  /
CREATE PROFILE sec_profile LIMIT
*
ERROR at line 1:
ORA-28006: conflicting values for parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX


SQL> SELECT   *
  2   FROM   dba_profiles
  3  WHERE   RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX')
  4          AND PROFILE = 'SEC_PROFILE';

no rows selected

4.Oralce 8官方文件中已經明確說明,這兩個引數“有你就沒我”的原則,所以,我們重新建立,只保留3此密碼改變的限制,將PASSWORD_REUSE_TIME設定為“unlimited”
SQL> CREATE PROFILE sec_profile LIMIT
  2  PASSWORD_REUSE_TIME unlimited
  3  PASSWORD_REUSE_MAX  3
  4  /

Profile created.

SQL> SELECT   *
  2   FROM   dba_profiles
  3  WHERE   RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX')
  4          AND PROFILE = 'SEC_PROFILE';

PROFILE       RESOURCE_NAME                    RESOURCE LIMIT
------------- -------------------------------- -------- --------------
SEC_PROFILE   PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
SEC_PROFILE   PASSWORD_REUSE_MAX               PASSWORD 3

5.將該PROFILE授權給剛剛建立的sec使用者
SQL> alter user sec profile sec_profile;

User altered.

SQL> select USERNAME,PROFILE from dba_users where USERNAME = 'SEC';

USERNAME                       PROFILE
------------------------------ ------------------------------
SEC                            SEC_PROFILE

6.我們以密碼“pwd_00”為例完成這個實驗場景
1)設定sec使用者的密碼為“pwd_00”
SQL> alter user sec identified by pwd_00;

User altered.

2)嘗試在改變密碼兩次後重用密碼“pwd_00”,失敗,原因很顯然,沒有滿足密碼變化3次的要求
SQL> alter user sec identified by pwd_01;

User altered.

SQL> alter user sec identified by pwd_02;

User altered.

SQL> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused

3)嘗試再第三次修改使用者密碼之後重用密碼“pwd_00”,正如Oracle 8的官方文件所言,現在已經可以重新使用“pwd_00”密碼(還記得上面在10g中的實驗麼?同樣的設定在10g中結論是:永遠無法重用相同的密碼)
SQL> alter user sec identified by pwd_03;

User altered.

SQL> alter user sec identified by pwd_00;

User altered.

7.換一種場景,我們這回只對PASSWORD_REUSE_TIME引數進行設定,設定後的限制是:只要超過1分鐘的密碼就可以被重新使用
1)修改sec_profile以滿足我們的實驗要求
SQL> ALTER PROFILE sec_profile LIMIT
  2  PASSWORD_REUSE_TIME 1/1440
  3  PASSWORD_REUSE_MAX  unlimited
  4  /

Profile altered.

SQL> SELECT   *
  2   FROM   dba_profiles
  3  WHERE   RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX')
  4          AND PROFILE = 'SEC_PROFILE';

PROFILE       RESOURCE_NAME                    RESOURCE LIMIT
------------- -------------------------------- -------- --------------
SEC_PROFILE   PASSWORD_REUSE_TIME              PASSWORD .0006
SEC_PROFILE   PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

2)先將sec使用者的密碼設定為“pwd_00”
06:19:00 SQL> alter user sec identified by pwd_00;

User altered.

3)當經過30秒之後,我們做第一次嘗試,顯然不成功,因為沒有滿足1分鐘的時間間隔
06:19:30 SQL> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused


4)同樣的道理,在經過53秒之後嘗試,仍然無法重用“pwd_00”密碼
06:19:53 SQL> alter user sec identified by pwd_00;
alter user sec identified by pwd_00
*
ERROR at line 1:
ORA-28007: the password cannot be reused

5)當等待超過1分鐘之後,密碼“pwd_00”已經可以被重新使用,符合Oracle 8官方文件的描述(如果在10g中這樣進行設定,您將永遠無法重新使用任何密碼)
06:20:03 SQL> alter user sec identified by pwd_00;

User altered.

8.小結
透過上面在Oracle 8.1.6環境中的實驗,充分驗證了Oracle 8官方文件的正確性和可信性。

四、實驗總結
透過整個實驗的演示得到以下結論:
1.在Oracle  8和 9的版本中PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX兩個引數是不能同時有值的,每個引數是單獨生效的;
2.在Oracle 10和11的版本中這兩個引數必須同時有值才有限制效力,任何一個引數如被設定為unlimited,相同的密碼將永遠不能被重新使用。

經驗總結:
1.“實驗"是檢驗Oracle官方文件的唯一也是最好的手段。
2.Oracle官方文件是可信的,不過同樣需要我們用自己的雙手去一一驗證;
3.Oracle不同版本的官方文件對同樣內容的描述可能是不一樣的,這也體現了Oracle“與時俱進”的特性,這個世界裡永遠不變的就是“變化”,況且Oracle大部分的變化還是很人性化的,值得肯定;
4.Oracle的官方文件永遠是最好的Oracle資料,但要取之有道,否則不是被浩瀚的文件淹沒,就是抑鬱而歸;
5.實驗原則:首先接受它,然後用實驗來驗證它,沒有驗證的內容永遠可以認為是不可信賴的;
6.最後一點還是用這句古話與諸君共勉:紙上得來終覺淺,絕知此事要躬行!

Goodluck to you.

-- The End --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26870952/viewspace-2153073/,如需轉載,請註明出處,否則將追究法律責任。

相關文章