Wednesday 19 December 2012

How to Change Password Expiry to Unlimited in Oracle 11gR2


##########How to change password Expiry to unlimited in Oracle 11gR2##############
----------------------Check user (SCOTT) password expiry date-----------------------------

select username, account_status, to_char(expiry_date, 'DD-MM-YYYY') EXP_DATE from dba_users where username = 'SCOTT';

---------------------------Check User(SCOTT) Profile----------------------------------------------

select profile from dba_users where username='SCOTT';

------------------------------Check this Profile Expiry Limit------------------------------------

select * from dba_profiles where profile = 'DEFAULT' and resource_name LIKE 'PASSWORD_LIFE_TIME';

-----------------------Change Expiry to Unlimited for this Profile-------------------------------

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

------------------------------Reset Password for User(SCOTT)-------------------------------------

ALTER USER SCOTT IDENTIFIED BY tiger;
--------------------------------------------------------------------------------------------------

4 comments:

  1. Hi Abbas, Awesome demonstration over here.

    My question is what if i want to change the limit only for SCOTT user. Not to the DEFAULT profile mentioned here.

    Because you are changing the profile which is assigned to many other users.

    I think its better to add new profile and then assign to SCOTT.

    But I got the thing what i was looking for. Thanks for your demonstration.

    ReplyDelete
    Replies
    1. YOU ARE EXACTLY RIGHT, CREATING A NEW PROFILE IS BETTER, BUT MORE WORK ;-)

      Delete