1 2 Previous Next 18 Replies Latest reply: Apr 13, 2011 5:04 AM by user8610659 RSS

    How to set database user password to never expire

    772384
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    have you done ?
                                    1 2 Previous Next