Managing Password Expiration in Oracle Databases

Managing Password Expiration in Oracle Databases

Oracle databases, by default, enforce password expiration policies to enhance security. While crucial for production environments, these policies can become cumbersome during development or testing. This tutorial explains how to manage password expiration settings in Oracle, including disabling expiration for specific users or globally adjusting the default behavior.

Understanding Password Profiles

Oracle uses profiles to define password policies. A profile dictates rules around password complexity, lifetime, reuse, and account lock behavior. The DEFAULT profile applies to users who haven’t been explicitly assigned a different profile. You can view the current settings of a profile using the following query:

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT';

This query will show you the current limits for various password-related parameters. The key parameter for password expiration is PASSWORD_LIFE_TIME. A value of UNLIMITED disables expiration, while a numeric value specifies the number of days before a password expires.

Disabling Password Expiration for a Specific User

To disable password expiration for a particular user, you need to modify the profile assigned to that user. First, identify the profile the user is using:

SELECT profile FROM dba_users WHERE username = 'your_username';

Replace 'your_username' with the actual username. Then, alter the profile to set the PASSWORD_LIFE_TIME to UNLIMITED:

ALTER PROFILE your_profile_name LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Replace your_profile_name with the profile name identified in the previous step.

Disabling Password Expiration for All New Users (Modifying the DEFAULT Profile)

If you want to disable password expiration for all new users created without a specific profile assignment, modify the DEFAULT profile:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Important: This change only applies to users created after the profile modification. Existing users will retain their current profile settings.

Addressing Expired and Locked Accounts

If a user’s password has already expired, and their account may be locked, you’ll need to take additional steps:

1. Unlock the Account: If the account is locked, unlock it using:

   ALTER USER your_username ACCOUNT UNLOCK;
   

2. Reset the Password: Reset the password to a new value. This is crucial, even if you intend to revert to the old password value.

   ALTER USER your_username IDENTIFIED BY new_password;
   

   Or, to reset the password to the existing hashed value (useful for avoiding disruption), you can use a more advanced technique that retrieves the existing hash and re-applies it (see the "Advanced: Resetting to Existing Hash" section below).

Advanced: Resetting to Existing Hash

In certain scenarios, you may want to reset a user’s password to its existing hash, preserving the original password without requiring the user to change it. This is useful when you’re modifying profiles and don’t want to force a password reset. The method for doing this varies depending on the Oracle version.

Oracle 11g and later: Newer Oracle versions store password hashes in the SPARE4 column. The following SQL*Plus script demonstrates how to retrieve the SPARE4 hash and re-apply it:

SET VERIFY ON
SHOW VERIFY

SET DEFINE '&'
SHOW DEFINE

DEFINE USER_NAME = 'your_username'

SELECT
  ACCOUNT_STATUS,
  TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE,
  TO_CHAR(EXPIRY_DATE, 'YYYY-MM-DD HH24:MI:SS') AS EXPIRY_DATE
FROM
  DBA_USERS
WHERE
  USERNAME = '&USER_NAME';

DEFINE OLD_SPARE4 = ""

COLUMN SPARE4HASH NEW_VALUE OLD_SPARE4

SELECT 
  '''' || SPARE4 || '''' AS SPARE4HASH
FROM 
  SYS.USER$ 
WHERE 
  NAME = '&USER_NAME';

DEFINE OLD_SPARE4

ALTER USER &USER_NAME IDENTIFIED BY VALUES &OLD_SPARE4;

SELECT
  ACCOUNT_STATUS,
  TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE,
  TO_CHAR(EXPIRY_DATE, 'YYYY-MM-DD HH24:MI:SS') AS EXPIRY_DATE
FROM
  DBA_USERS
WHERE
  USERNAME = '&USER_NAME';

Important Considerations:

• Security: While disabling password expiration can be convenient for development, it weakens security. Never disable expiration in production environments.
• Auditing: Keep track of changes to password profiles for auditing purposes.
• Profile Hierarchy: Users can be assigned to different profiles, so understanding the profile hierarchy is crucial for effective password management.