Oracle数据库修改用户Profile后密码过期日期相关疑问

Hey there, let's break this down clearly for you based on how Oracle handles password expiration and profiles:

• After switching the user's profile to one with passwords set to never expire, the original EXPIRY_DATE value in DBA_USERS won't get cleared automatically—but this date will no longer have any effect. Oracle will strictly follow the new profile's rules (specifically PASSWORD_LIFE_TIME = UNLIMITED) and ignore the old expiry date entirely.
• You don't have to force-update EXPIRY_DATE to NULL for functionality, but you can do it for consistency in query results if you want (it's optional).

1. Profile Rules Take Priority Over Historical EXPIRY_DATE #

Oracle's password expiration logic is driven by the profile assigned to the user, not the EXPIRY_DATE field in DBA_USERS. Once you assign a profile with PASSWORD_LIFE_TIME UNLIMITED:

• Even if EXPIRY_DATE still shows a future expiration date, Oracle will never prompt the user to change their password due to expiration.
• The EXPIRY_DATE field only reflects the expiration timestamp calculated under the old profile—it becomes a stale record once the profile is updated.

2. How to Confirm & Apply the Never-Expire Profile #

First, verify which profiles have unlimited password lifetime:

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

Look for the profile where LIMIT is set to UNLIMITED, then assign it to your user:

ALTER USER your_target_username PROFILE your_never_expire_profile;

3. (Optional) Sync EXPIRY_DATE to NULL for Consistency #

You shouldn't directly update the DBA_USERS view (it's a system-backed view, direct edits are not recommended). Instead, use these safe methods to update the field:

• Reset the password (if allowed):
  If the user can tolerate a password reset (or you use their current password), run:

  ALTER USER your_target_username IDENTIFIED BY their_current_password;
  

  This will automatically set EXPIRY_DATE to NULL since the new profile enforces never-expiring passwords.
• Reset using the encrypted password (no actual password change):
  If you don't want to disrupt the user's current password, first fetch their encrypted password:

  SELECT password FROM dba_users WHERE username = 'YOUR_TARGET_USERNAME';
  

  Then use that encrypted value to "reset" the password without changing it:

  ALTER USER your_target_username IDENTIFIED BY VALUES 'encrypted_password_from_query';
  

  This will update EXPIRY_DATE to NULL while keeping the user's actual password intact.

内容的提问来源于stack exchange，提问作者user1929905