Managing Oracle User Password Expiration
Oracle Password Expiration Configuraton
Oracle's DEFAULT profile sets PASSWORD_LIFE_TIME to 180 days by default, causing passwords to expire and potentially disrupt application connectivity.
Checking Current Password Expiration
To check a user's password expiration date:
SELECT username, profile, expiry_date
FROM dba_users
WHERE username = 'EXAMPLE_USER';
To view default password lifetime setting:
SELECT * FROM dba_profiles
WHERE profile='DEFAULT'
AND resource_name='PASSWORD_LIFE_TIME';
Disabling Password Expiration for All Users
Modify the DEFAULT profile to remove password lifetime restrictions:
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Users with expired passwords must reset them:
ALTER USER EXAMPLE_USER IDENTIFIED BY new_password;
Disabling Password Expiration for Specific Users
- Create a new profile mirroring DEFAULT settings: ```
CREATE PROFILE NO_EXPIRE_PROFILE LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED;
- Assign the profile to the user: ```
ALTER USER EXAMPLE_USER PROFILE NO_EXPIRE_PROFILE;
Adjusting Failed Login Attempts
Modify account lockout threshold in DEFAULT profile:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 50;
Key Password-Related Profile Parameters
- FAILED_LOGIN_ATTEMPTS: Maximum login attempts before lockout
- PASSWORD_LIFE_TIME: Days before password expiration
- PASSWORD_LOCK_TIME: Duration of account lock after failed attempts
- PASSWORD_GRACE_TIME: Warning period before password expiration
