【PROFILE】PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX参数在Oracle不同版本中的差别

最新推荐文章于 2024-04-30 11:24:17 发布

cuanchuwei1207

最新推荐文章于 2024-04-30 11:24:17 发布

阅读量421

点赞数

文章标签：数据库操作系统

在《【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)
参考链接：http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem21.htm#2065932
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)
参考链接：http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_611a.htm#SQLRF01310
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)
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm#SQLRF01310
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)
参考链接：http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6010.htm#sthref6888
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)
参考链接：http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_6010.htm#SQLRF01310
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/519536/viewspace-616369/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-616369/