Understanding PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX in Oracle Database

The parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX in Oracle Database control password reuse policies. They are part of the password profile settings, and they work together to ensure users do not reuse their previous passwords, enhancing security.

1. PASSWORD_REUSE_TIME

  • Definition: This parameter specifies the minimum number of days that must pass before a previously used password can be reused.
  • Default Value: UNLIMITED (no restriction based on time).
  • Valid Values:
    • Positive integer: Specifies the number of days.
    • UNLIMITED: Passwords can be reused at any time.

2. PASSWORD_REUSE_MAX

  • Definition: This parameter specifies the maximum number of password changes that must occur before a previously used password can be reused.
  • Default Value: UNLIMITED (no restriction based on count).
  • Valid Values:
    • Positive integer: Specifies the number of password changes.
    • UNLIMITED: Passwords can be reused without any count restriction.

3. How These Parameters Work Together

These two parameters are interdependent:

  • If PASSWORD_REUSE_TIME is set and PASSWORD_REUSE_MAX is UNLIMITED, the time-based restriction applies.
  • If PASSWORD_REUSE_MAX is set and PASSWORD_REUSE_TIME is UNLIMITED, the count-based restriction applies.
  • If both are set to UNLIMITED, there is no restriction, and passwords can be reused freely.
  • If both are set to specific values, both conditions must be satisfied before a password can be reused.

4. Examples

Example 1: Time-Based Restriction Only


ALTER PROFILE DEFAULT LIMIT
PASSWORD_REUSE_TIME 180
PASSWORD_REUSE_MAX UNLIMITED;
  • Explanation: A user cannot reuse a password within 180 days, regardless of how many times they change it during this period.

Example 2: Count-Based Restriction Only


ALTER PROFILE DEFAULT LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 5;
  • Explanation: A user cannot reuse any of their last 5 passwords, but there is no time restriction.

Example 3: Combined Time and Count Restriction


ALTER PROFILE DEFAULT LIMIT
PASSWORD_REUSE_TIME 90
PASSWORD_REUSE_MAX 3;
  • Explanation:
    • A user cannot reuse any of their last 3 passwords.
    • A password can only be reused if it is older than 90 days and is not one of the last 3 passwords.

Example 4: No Restriction on Password Reuse


ALTER PROFILE DEFAULT LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED;
  • Explanation: Passwords can be reused at any time and any number of times.

5. Scenarios to Illustrate

Scenario 1: PASSWORD_REUSE_TIME 60, PASSWORD_REUSE_MAX UNLIMITED

  • Setup:

    ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 60  
    PASSWORD_REUSE_MAX UNLIMITED;
  • A user changes their password on Day 1.
  • They cannot reuse this password until Day 61, regardless of how many times they change their password in between.

Scenario 2: PASSWORD_REUSE_TIME UNLIMITED, PASSWORD_REUSE_MAX 3

  • Setup:

    ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED  
    PASSWORD_REUSE_MAX 3;
  • A user cannot reuse a password if it is one of the last 3 passwords, irrespective of time.

Scenario 3: PASSWORD_REUSE_TIME 30, PASSWORD_REUSE_MAX 5

  • Setup:

    ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 30  
    PASSWORD_REUSE_MAX 5;
  • Behavior:
    • A user cannot reuse any of their last 5 passwords.
    • They must also wait at least 30 days after setting a password before reusing it.

6. Querying Current Settings

Check the password reuse settings for all profiles:


SELECT PROFILE, RESOURCE_NAME, LIMIT 
FROM DBA_PROFILES 
WHERE RESOURCE_NAME IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX');

7. Considerations

Security Best Practices

  • Set both parameters to values that balance security and usability. For example:

    ALTER PROFILE DEFAULT LIMIT 
PASSWORD_REUSE_TIME 90
PASSWORD_REUSE_MAX 5;
    • This ensures users wait at least 90 days and cannot reuse the last 5 passwords.

Compliance

  • Organizations may have compliance requirements (e.g., SOX, GDPR) that mandate certain password reuse policies. Configure accordingly.

Custom Profiles

  • For different roles (e.g., administrators, end-users), create custom profiles with tailored settings.

8. Testing the Policy

  • After setting up these restrictions, attempt to reuse a previous password.
  • Example:

    ALTER USER test_user IDENTIFIED BY old_password;
    • If the conditions are not met, you will get an error like:
      ORA-28007: the password cannot be reused

By properly configuring PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX, you can enforce a robust password policy in your Oracle database.






Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment