How to set Oracle password to never expire

ORA-28002: the password will expire within x days

Many times we face issue of user password expiry in the DB. For this problem you can assign UNLIMITED limit to the user. This is not exactly for the user, but it’s for the profile in which your DB user exists. 

You can easily change the profile of users in oracle DB. By default the profile is DEFAULT that is having unlimited limit for Password i.e  PASSWORD_LIFE_TIME  .

Let’s use this in practical 

For example, we have new developer joined our company and he/she needs a DB user or you can say fresher. So what happened is, he/she most of the cases she tried to login with wrong password and got DB account locked.

For this case follow the following steps :-

You need to connect with the sysdba privileges.

SQL> conn sys/**** as sysdba

SQL> alter user test identified by test account unlock;

Now the 2^nd case, we need to change the limit of our PROFILE that we have created to avoid the AUDIT issues. E.g. TEST is the default profile.

ERROR:
ORA-28002: the password will expire within x days

First check the status and profile of the USER by below sql :-

SQL> select username, account_status, PROFILE from dba_users where username='TEST';

Now you know that TEST is user in TEST profile.

Now check, what the limit of this profile by below sql :- 

Select PROFILE, RESOURCE_NAME, LIMIT from dba_profiles where PROFILE like 'TEST';

PROFILE                        RESOURCE_NAME                    LIMIT

---------                            -----------------------                        -------

TEST                        PASSWORD_LIFE_TIME               180

This indicates that password will expire in 180 days. So you need to change this limit. Please follow below steps:-

SQL> alter profile TEST limit PASSWORD_LIFE_TIME  unlimited;

All this you need to do why SYSDBA privileges.

Now check the status the status again :-

 Select PROFILE, RESOURCE_NAME, LIMIT from dba_profiles where PROFILE like 'TEST';

PROFILE                        RESOURCE_NAME                    LIMIT

-----------                              ----------------------                        -----------------

TEST                        PASSWORD_LIFE_TIME               UNLIMITED

Now this user will never expire.

Stay happy, keep troubleshooting ;)