About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Wednesday, May 29, 2013

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

2 comments:

  1. it shows, table doesn't exist.. !!!

    ReplyDelete
    Replies
    1. please paste the error here and version of your database too

      Thanks
      Amit Rath

      Delete