关于密码重用参数PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX之间的关系及其演示

原创

371690509 2014-03-24 18:43:00 博主文章分类：ORACLE ©著作权

文章标签 oracle profile PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME 文章分类 办公效率

©著作权归作者所有：来自51CTO博客作者371690509的原创作品，请联系作者获取转载授权，否则将追究法律责任

测试环境：10.2.0.2.0
测试用户：SCOTT
测试用的三组密码：oracle1 oracle2 oracle3

PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX：这两个参数必须互相关联设置，password_reuse_time指定了密码不能重用前的天数，而password_reuse_max则指定了当前密码被重用之前密码改变的次数。

总结两者之间的规律：
(1)PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME都为UNLIMITED，密码可以随意重用，没有任何限制。
(2)PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME均为指定值时，必须满足这两者的条件时才可以重用密码。
(3)当PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME两个有其中一个不为UNLIMITED，则密码永远不能重用。

以下为验证过程：

--确认SCOTT使用的DEFAULT profile
SQL> select USERNAME from dba_users where PROFILE='DEFAULT' and USERNAME='SCOTT';
USERNAME
------------------------------
SCOTT

第一种情况，PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX值均为UNLIMITED

SQL>  select profile,limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_REUSE_TIME';
PROFILE                        LIMIT
------------------------------ ----------------------------------------
DEFAULT                        UNLIMITED
SQL>  select profile,limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_REUSE_MAX';
PROFILE                        LIMIT
------------------------------ ----------------------------------------
DEFAULT                        UNLIMITED
--测试
SQL> alter user scott identified by oracle;
User altered.
SQL> /
User altered.
SQL> /
User altered.

可以发现，用户密码可以不受限制的重用

第二种情况，PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX值均不为UNLIMITED，这里设定ASSWORD_REUSE_TIME 1/1440表示一分钟后可以重用，PASSWORD_REUSE_MAX 1表示密码更新过一次即可重用。

SQL> alter profile DEFAULT limit PASSWORD_REUSE_MAX 1;
Profile altered.
SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME 1/1440;
Profile altered.
--测试
SQL> alter user scott identified by oracle1;
User altered.
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') as a from dual;
A
-------------------
2014-03-20,23:40:45
SQL> alter user scott identified by oracle;
User altered.
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') as a from dual;
A
-------------------
2014-03-20,23:42:05
SQL> alter user scott identified by oracle1;
User altered.
SQL>  select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') as a from dual;
A
-------------------
2014-03-20,23:42:18
--间隔不足一分钟，更改提示失败
SQL> alter user scott identified by oracle;
alter user scott identified by oracle
*
ERROR at line 1:
ORA-28007: the password cannot be reused
--直接用上一次的密码更改，更改失败
SQL> alter user scott identified by oracle1;
alter user scott identified by oracle1
*
ERROR at line 1:
ORA-28007: the password cannot be reused

由上面的实验，我们可以看出，PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX值均不为UNLIMITED，必须同时满足这两个条件才可以更改密码。

第三种情况，PASSWORD_REUSE_TIME 为UNLIMITED，PASSWORD_REUSE_MAX为1

SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
Profile altered.
--测试
SQL>  alter user scott identified by oracle1;
alter user scott identified by oracle1
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user scott identified by oracle2;
User altered.
SQL> alter user scott identified by oracle1;
alter user scott identified by oracle1
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user scott identified by oracle;
alter user scott identified by oracle
*
ERROR at line 1:
ORA-28007: the password cannot be reused

由此可以看出，当PASSWORD_REUSE_TIME 为UNLIMITED，PASSWORD_REUSE_MAX为指定值，密码在任何时候都不可以重用。

第四种情况，PASSWORD_REUSE_MAX 为UNLIMITED，PASSWORD_REUSE_TIME为1/1440。

SQL> alter profile DEFAULT limit PASSWORD_REUSE_MAX unlimited;
Profile altered.
SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME 1/1440;
Profile altered.
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') as a from dual;
A
-------------------
2014-03-20,23:50:16
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') as a from dual;
A
-------------------
2014-03-20,23:53:10
SQL> alter user scott identified by oracle2;
alter user scott identified by oracle2
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user scott identified by oracle1;
alter user scott identified by oracle1
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user scott identified by oracle;
alter user scott identified by oracle
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user scott identified by oracle4;
User altered.
SQL> alter user scott identified by oracle;
alter user scott identified by oracle
*
ERROR at line 1:
ORA-28007: the password cannot be reused

从上面的实验可以看出，当PASSWORD_REUSE_MAX为UNLIMITED，PASSWORD_REUSE_TIME为指定值，密码在任何时候都不可以重用。