Password reuse rules for Oracle 9i

Oracle uses PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX in PROFILE to determine whether the password can be reused and the restrictions on Password reuse.

However, after testing, I found that the description in the Oracle administrator guide is incorrect. I checked METALINK, although the two parameters are described in detail in an article on METALINK, some descriptions are still incorrect.

PASSWORD_REUSE_TIME is the minimum interval for Password reuse, in days. An integer or a score can be given. For example, 1/1440 indicates that oracle does not check every minute for efficiency reasons. Generally, there is an error of about 5 minutes, so it doesn't make much sense if the number is smaller than 1/144 ).

PASSWORD_REUSE_MAX is the minimum number of times the password is changed before the password is reused. There is no objection to these two items. The key is how they can be used together. It can be described in three situations:

1. Both PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME are UNLIMITED

In this case, the password can be reused at will without restrictions. This is also the DEFAULT value of DEFAULT profile. When both of these parameters are UNLIMITED, the two parameters are not considered to be used. Therefore, there is no restriction on Password reuse.

SQL> create profile prof_test limit password_reuse_max unlimited2password_reuse_time unlimited; the configuration file has created SQL> create user test identified by test profile prof_test; the user has created SQL> alter user test identified by test; the user has changed. SQL> alter user test identified by test; the user has changed.

2. One of PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME is UNLIMITED, and the other is another value.

At this time, your password will never be reused. Let's see what the administrator guide says:

Use the CREATE PROFILE statement to specify a time interval during which users cannot reuse a password. In the following statement, a profile is defined wherethe PASSWORD_REUSE_TIME clause specifies that the user cannot reuse the passwordfor 60 days.CREATE PROFILE prof LIMITPASSWORD_REUSE_TIME 60PASSWORD_REUSE_MAX UNLIMITED;In the next statement, the PASSWORD_REUSE_MAX clause specifies that the numberof password changes the user must make before the current password can be used again is three.CREATE PROFILE prof LIMITPASSWORD_REUSE_MAX 3PASSWORD_REUSE_TIME UNLIMITED;Note: If you specify PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX, you must setthe other to UNLIMITED or not specify it at all.

The document tells us to use only one of them and set the other as UNLIMITED, but this is incorrect. This will cause your password to never be reused.

SQL> alter profile prof_test limit password_reuse_max 3; the configuration file has changed SQL> select resource_name, limit from dba_profiles2where profile = 'Prof _ test' and resource_type = 'Password'; RESOURCE_NAMELIMIT too many characters You have selected 7 rows for ULTPASSWORD_GRACE_TIMEDEFAULT. SQL> alter user test identified by test; the user has changed. SQL> alter user test identified by test; alter user test identified by test * ERROR is located at row 1st: ORA-28007: cannot re-use password SQL> alter user test identified by t1; user changed. SQL> alter user test identified by t2; the user has changed. SQL> alter user test identified by t3; the user has changed. SQL> alter user test identified by test; alter user test identified by test * ERROR is located at row 1st: ORA-28007: password cannot be reused

After the profile is modified, it is only valid for subsequent operations by the test user. The first time you can change the password to test, it is because oracle does not record the initial password, and the second change will fail, because the password can no longer be reused.

According to the document, we only need to change the password three times to reuse it, but the test result is that the password cannot be reused.

SQL> alter profile prof_test limit password_reuse_max unlimited; the configuration file has changed SQL> alter user test identified by test; the user has changed. SQL> alter profile prof_test limit password_reuse_time 1/144; the configuration file has changed SQL> select resource_name, limit from dba_profiles2where profile = 'Prof _ test' and resource_type = 'Password'; then begin ------------ success LTPASSWORD_GRACE_TIMEDEFAULT: You have selected 7 rows. SQL> set time on16: 47: 29 SQL> alter user test identified by test; alter user test identified by test * ERROR row 1st: ORA-28007: you cannot re-use the password 16:47:48 SQL> 16:48:23 SQL> 16:59:45 SQL> alter user test identified by test; alter user test identified by test * ERROR is on Row 1st: ORA-28007: you cannot re-use the password 16:59:59 SQL> 17:07:32 SQL> alter user test identified by test; alter user test identified by test * ERROR is on Row 1st: ORA-28007: cannot re-use password 17:07:40 SQL> set time off

Modify PASSWORD_REUSE_TIME to 1/144, that is, about 10 minutes. We will test the oracle error after 10 minutes and 20 minutes respectively. The password cannot be reused.

3. PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME are not limited.

In this case, you only need to meet any condition to reuse the password. The description in Metalink is incorrect here. Password reuse does not need to meet both conditions, as long as one condition is met.

SQL> alter profile prof_test limit password_reuse_time unlimited; the configuration file has changed SQL> alter user test identified by test; the user has changed. SQL> alter profile prof_test limit2password_reuse_max 3 password_reuse_time 1/144; the configuration file has changed SQL> set time on17: 11: 30 SQL> alter user test identified by test; the user has changed. 17:11:47 SQL> alter user test identified by test; alter user test identified by test * ERROR row 1st: ORA-28007: cannot re-use password 17:11:56 SQL> alter user test identified by t1; the user has changed. 17:12:06 SQL> alter user test identified by t2; the user has changed. 17:12:12 SQL> alter user test identified by t3; the user has changed. 17:12:19 SQL> alter user test identified by test; the user has changed. 17:12:50 SQL> 17:13:45 SQL> alter user test identified by test; alter user test identified by test * ERROR is located in row 1st: ORA-28007: you cannot re-use the password 17:13:55 SQL> 17:14:00 SQL> 17:32:14 SQL> alter user test identified by test; the user has changed.

It took about one minute to reuse the test password for the first time, but no other password was used before the test password was reused for the second time. It can be seen that only one condition in PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME can be satisfied.

(