ORA-28001: the password has expired

ORA-28001: the password has expired

Sometime we face issue in Oracle, we got complaints from application team that our oracle user is expired every 30 days. DBA need to configured the profile of USER according to the environmental need.

Following are the steps to check user and make it profile to never expired:

1. Check the USER and PROFILE need to changed.

SELECT username, account_status, profile FROM dba_users WHERE ACCOUNT_STATUS LIKE '%EXPIRED%';

2. Verify the profiles associated with different users.
Note: If yes then you change the profile it will affect all other user associated with that profile

SELECT username FROM dba_users WHERE profile = 'DEFAULT';

3. Check the current status of profiles

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

Note: PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.

4. Change the limit of password to unlimited:
Note: Using DEFAULT profile, you can check your profile in 1 step.

-- PASSWORD LIFE TIME No of days when password expired
alter profile DEFAULT limit password_life_time UNLIMITED;
-- PASSWORD REUSE TIME: Same password is used multiple time
alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;

5. Verify the change of Profiles:

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

6. Change the password or use old password and fire the command:

alter user DBUSER identified by newpa$$word;

7. Check the user is Unexpire and unlocked:

SELECT username, account_status, EXPIRY_DATE FROM DBA_USERS WHERE username='DBUSER';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.