This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 13, 2011 3:04 AM by user8610659 RSS

How to set database user password to never expire

772384 Newbie
Currently Being Moderated
Hello all,

I have a linux server with Oracle 11g installed which is used primarily for testing by a few developers and QA folks. I want to set the DB user password to never expire. I have run the following (which works on a windows machine) but the password still states that it will expire in 7 days. Is there something else I can do to 'force' the pw to infinity? I've tried stopping shutting down the DB, stopping / restarting listener...

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
select * from dba_profiles where profile = 'DEFAULT' and resource_name LIKE 'PASSWORD_LIFE_TIME';
"PROFILE"     "RESOURCE_NAME"     "RESOURCE_TYPE"     "LIMIT"
"DEFAULT"     "PASSWORD_LIFE_TIME"     "PASSWORD"     "UNLIMITED"

select profile from dba_users where username='TEST';
"PROFILE"
"DEFAULT"

select username, account_status, to_char(expiry_date, 'DD-MM-YYYY') EXP_DATE from dba_users where username = 'TEST';
"USERNAME"     "ACCOUNT_STATUS"     "EXP_DATE"
"TEST"     "OPEN"     ""

Help appreciated.
  • 1. Re: How to set database user password to never expire
    Chinar Guru
    Currently Being Moderated
    Hi,do not need restart database and listener,That is possible limit parameter password_grace_time check this,second may some users execute explicitly "alter user test password expire",investigate this reasons.
  • 2. Re: How to set database user password to never expire
    Alessandro Zenoni Newbie
    Currently Being Moderated
    It's very strange.

    I have HP-UX system with Oracle 11gR2 Rac Installation.
    I performed your same steps and it's ok.

    But I don't understand, in your query from dba_user you got exp_date=NULL. Where is the problem?

    Bye
    Alessandro
  • 3. Re: How to set database user password to never expire
    591186 Guru
    Currently Being Moderated
    With 11g, Password_life_time in DEFAULT profile is set to 180days.

    Default profile values in 11g.
    SQL> SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';
    
    PROFILE                        RESOURCE_NAME                    RESOURCE
    ------------------------------ -------------------------------- --------
    LIMIT
    ----------------------------------------
    DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD
    10
    
    DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD
    180
    
    DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD
    UNLIMITED
    
    
    PROFILE                        RESOURCE_NAME                    RESOURCE
    ------------------------------ -------------------------------- --------
    LIMIT
    ----------------------------------------
    DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD
    UNLIMITED
    
    DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD
    NULL
    
    DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD
    1
    
    
    PROFILE                        RESOURCE_NAME                    RESOURCE
    ------------------------------ -------------------------------- --------
    LIMIT
    ----------------------------------------
    DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD
    7
    
    7 rows selected.
    Change the password_grace_time to unlimited.

    HTH
    -Anantha
  • 4. Re: How to set database user password to never expire
    772384 Newbie
    Currently Being Moderated
    Thank you all for your input. Anantha, I tried your recommendation to alter the PASSWORD_GRACE_TIME to 'UNLIMITED' and now get the following error when I login: ORA-28011: The account will expire soon; change your password now


    These are my settings:

    select resource_name, resource_type, limit
    from dba_profiles
    where profile='DEFAULT';


    "RESOURCE_NAME"     "RESOURCE_TYPE"     "LIMIT"
    "COMPOSITE_LIMIT"     "KERNEL"     "UNLIMITED"
    "SESSIONS_PER_USER"     "KERNEL"     "UNLIMITED"
    "CPU_PER_SESSION"     "KERNEL"     "UNLIMITED"
    "CPU_PER_CALL"     "KERNEL"     "UNLIMITED"
    "LOGICAL_READS_PER_SESSION"     "KERNEL"     "UNLIMITED"
    "LOGICAL_READS_PER_CALL"     "KERNEL"     "UNLIMITED"
    "IDLE_TIME"     "KERNEL"     "UNLIMITED"
    "CONNECT_TIME"     "KERNEL"     "UNLIMITED"
    "PRIVATE_SGA"     "KERNEL"     "UNLIMITED"
    "FAILED_LOGIN_ATTEMPTS"     "PASSWORD"     "10"
    "PASSWORD_LIFE_TIME"     "PASSWORD"     "UNLIMITED"
    "PASSWORD_REUSE_TIME"     "PASSWORD"     "UNLIMITED"
    "PASSWORD_REUSE_MAX"     "PASSWORD"     "UNLIMITED"
    "PASSWORD_VERIFY_FUNCTION"     "PASSWORD"     "NULL"
    "PASSWORD_LOCK_TIME"     "PASSWORD"     "1"
    "PASSWORD_GRACE_TIME"     "PASSWORD"     "UNLIMITED"
  • 5. Re: How to set database user password to never expire
    Chinar Guru
    Currently Being Moderated
    Change your users password using alter user identified by pass,hope next time according this profile setting your user password will not expire
  • 6. Re: How to set database user password to never expire
    772384 Newbie
    Currently Being Moderated
    I still get the same ORA-28011 error after changing the pw. Should I stop/start Oracle?
  • 7. Re: How to set database user password to never expire
    rajeysh Guru
    Currently Being Moderated
    this error is given when the PASSWORD_LIFE_TIME has expired and the Grace Period time begins
    "PASSWORD_GRACE_TIME" "PASSWORD" "UNLIMITED"
    change PASSWORD_GRACE_TIME 0,
  • 8. Re: How to set database user password to never expire
    772384 Newbie
    Currently Being Moderated
    Sorry, I don't get it...both PASSWORD_LIFE_TIME & PASSWORD_GRACE_TIME are set to 'UNLIMITED'. There must be something else that is confusing Oracle? Setting PASSWORD_GRACE_TIME to 0 only causes a different Oracle error: ORA-28002: the password will expire in 0 days.
  • 9. Re: How to set database user password to never expire
    rajeysh Guru
    Currently Being Moderated
    user6902559 wrote:
    Sorry, I don't get it...both PASSWORD_LIFE_TIME & PASSWORD_GRACE_TIME are set to 'UNLIMITED'. There must be something else that is confusing Oracle? Setting PASSWORD_GRACE_TIME to 0 only causes a different Oracle error: ORA-28002: the password will expire in 0 days.
    change the password for the user and also refer the below link.
    alter user username identified by yourpassword;
    refer:
    http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html
  • 10. Re: How to set database user password to never expire
    772384 Newbie
    Currently Being Moderated
    rajeysh,

    I have done exactly the steps in the solution but still get the ORA-28011: the account will expire soon. Could this be Linux related issue with Oracle? Or SQL Developer? I will try using SQLplus command. If I could figure out the webserver port nbr I could run Enterprise Manager in a browser and try changing the settings in the OEM.
  • 11. Re: How to set database user password to never expire
    rajeysh Guru
    Currently Being Moderated
    SQL> select LIMIT, RESOURCE_NAME from dba_profiles 
    where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME') 
    and PROFILE=(select profile from dba_users where username='YOUR_USERNAME');
    show your output of the above query here.

    and also

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

    Edited by: rajeysh on Jul 1, 2010 1:16 AM
  • 12. Re: How to set database user password to never expire
    772384 Newbie
    Currently Being Moderated
    Here you go..

    select LIMIT, RESOURCE_NAME from dba_profiles
    where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME')
    and PROFILE=(select profile from dba_users where username='TEST');
    "LIMIT"     "RESOURCE_NAME"
    "UNLIMITED"     "PASSWORD_LIFE_TIME"
    "UNLIMITED"     "PASSWORD_REUSE_TIME"
    "UNLIMITED"     "PASSWORD_REUSE_MAX"
    "UNLIMITED"     "PASSWORD_GRACE_TIME"


    select profile from dba_users where username='TEST';
    "PROFILE"
    "DEFAULT"
  • 13. Re: How to set database user password to never expire
    rajeysh Guru
    Currently Being Moderated
    SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

    SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 0;

    change the user password.
    SQL> conn arju/a
    ERROR:
    ORA-28002: the password will expire within 10 days
    SQL> password
    Changing password for ARJU
    Old password:
    New password:
    Retype new password:
    Password changed
    SQL> conn arju/a!12
    Connected.

    Now you can see the change time by .
    SQL> select ctime, ptime from sys.user$ where name='ARJU';

    CTIME PTIME
    --------- ---------
    08-MAY-08 08-JUL-08


    From blog:
    http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html

    Edited by: rajeysh on Jul 1, 2010 1:45 AM
  • 14. Re: How to set database user password to never expire
    rajeysh Guru
    Currently Being Moderated
    have you done ?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points