Stop Oracle Password Expiry – Set Password Life Time to Unlimited

Oracle Password Life Time Set to Unlimited
-------------------------------------------------------

In Oracle database, sometimes user password gets expired automatically.
This happens because PASSWORD_LIFE_TIME is defined in the user profile.

To avoid password expiry, we can set password life time to UNLIMITED.

This is mostly required for application users and service accounts.


Step 1: Check user profile

First, check which profile is assigned to the user.

SELECT username, profile
FROM dba_users
WHERE username = 'ROHIT';

Most of the time, user is using the DEFAULT profile.


Step 2: Set password life time to unlimited

If user is using DEFAULT profile, run below command.

ALTER PROFILE DEFAULT
LIMIT PASSWORD_LIFE_TIME UNLIMITED;

If user is using some other profile, replace profile name.

ALTER PROFILE APP_PROFILE
LIMIT PASSWORD_LIFE_TIME UNLIMITED;


Step 3: Verify the setting

After changing the profile, verify the password life time value.

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


Step 4: Reset expired password (if required)

If user password is already expired, reset the password and unlock the account.

ALTER USER ROHIT IDENTIFIED BY new_password;
ALTER USER ROHIT ACCOUNT UNLOCK;


Now the user password will not expire automatically.

This setting is very useful for application schemas and batch users.
For normal users, password expiry is recommended for better security.