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%';

--check all username and profile name
SELECT username,profile FROM dba_users;

-- find profile of specific user

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

--Verify user present in that profile.

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:

-- If you know the password
alter user DBUSER identified by newpa$$word;

-- If you don't know the password
col username for a20
col password for a30
col account_status for a20
select a.username, b.password,a.account_status from dba_users a,sys.user$ b where b.name = a.username ;

--Reset password with this command:
alter user dbuser identified by values 'password';

7. Check the user is good to go

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

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 )

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.