How to setup password never expire for oracle user in Oracle

Sometimes for a test user in oracle , we often get this error :-

SQL> conn scott/tiger
ERROR:
ORA-28002: the password  will expire in 7 days
Connected

We want that as its a test user, its password never expire. We have to make a change in the profile associated with this user.

SQL> select profile from dba_users where username='SCOTT';

PROFILE
------------------------------------------------------------------------------------------
PROFILE

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

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

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 10

we need to change the limit of password_life_time parameter to unlimited.

SQL> alter profile PROFILE limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

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

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 UNLIMITED

Check that the expirary date column is null of desired user in DBA_USERS view

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME='SCOTT';

USERNAME                       ACCOUNT_STATUS            EXPIRY_DATE        PROFILE

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

SCOTT                            OPEN                                                         PROFILE

Similarly if we want failed login attempts has to be unlimited for a test user then alter the profile associated with the test user :-

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered

I hope this article helped you.

Regards,

Amit Rath