10 Replies Latest reply: May 27, 2010 2:44 PM by 693783 RSS

    ORA-28002: the password will expire within string days

    fsze88hoho
      Experts,
      OS : centOS5, oracle 11g
      I discovered an error when I try to login my oracle schema,
      sqlplus kennam/******@kennam
      It give me an error ORA-28002: the password will expire within string days
      Cause: The user's account is about to about to expire and the password needs to be changed
      Action: change the password or contact the DBA
      http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e24280.htm
      I fixed it already by alter user kennam identified by ******
      But,.... I would like to know when will happen again later on? (how long will that schema to be expire again)?

      This is the first time I meet this happen
      Thanks ;)
        • 1. Re: ORA-28002: the password will expire within string days
          584650
          Check the user's profile, you will find that in 11g the default expiration date (if they are using the default profile) has changed from UNLIMITED to 180 days. You probably just crossed the 180 days and have entered the GRACE period for the password. After the N amount of grace days (password_grace_time) you will be forced to change the password. I am guaranteeing there will be applications out there that will not be able to deal with this (changing the password because expiry time has been reached).

          I'm trying to find a way to change the expiry date for current users without having to set a password for that user again and setting the password_expire_time to UNLIMITED in the profile doesn't update the EXPIRY_DATE in DBA_USERS.
          • 2. Re: ORA-28002: the password will expire within string days
            JustinCave
            You need to find out what profile is assigned to your user
            SELECT profile
              FROM dba_users
             WHERE username = 'KENNAM'
            Then you'll want to look at the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters in the DBA_PROFILES table for the profile name identified above.

            Justin
            • 3. Re: ORA-28002: the password will expire within string days
              Mohammed Mehraj Hussain
              that is depends on the profile setting..
              what is the profile of that user..

              u can chech in dba_users .
              and by selecting that profile u can check it..

              SQL> select resource_name,liMit from dba_profiles where profile='DEFAULT';

              RESOURCE_NAME LIMIT
              -------------------------------- ----------------------------------------
              COMPOSITE_LIMIT UNLIMITED
              SESSIONS_PER_USER UNLIMITED
              CPU_PER_SESSION UNLIMITED
              CPU_PER_CALL UNLIMITED
              LOGICAL_READS_PER_SESSION UNLIMITED
              LOGICAL_READS_PER_CALL UNLIMITED
              IDLE_TIME UNLIMITED
              CONNECT_TIME UNLIMITED
              PRIVATE_SGA UNLIMITED
              FAILED_LOGIN_ATTEMPTS 10
              PASSWORD_LIFE_TIME UNLIMITED

              RESOURCE_NAME LIMIT
              -------------------------------- ----------------------------------------
              PASSWORD_REUSE_TIME UNLIMITED
              PASSWORD_REUSE_MAX UNLIMITED
              PASSWORD_VERIFY_FUNCTION NULL
              PASSWORD_LOCK_TIME UNLIMITED
              PASSWORD_GRACE_TIME UNLIMITED

              expire_time=PASSWORD_LIFE_TIME
              • 4. Re: ORA-28002: the password will expire within string days
                fsze88hoho
                Yes, you are right,
                I have one more question, am I possible(the right) to change the field PASSWORD_LIFE_TIME to be UNLIMITED from dba_profiles of that profile (DEFAULT)?
                Many thanks
                SQL> select resource_name,resource_type, limit from dba_profiles where profile='
                DEFAULT';
                
                RESOURCE_NAME                    RESOURCE 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 180
                
                RESOURCE_NAME                    RESOURCE LIMIT
                -------------------------------- -------- ----------
                PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
                PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
                PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
                PASSWORD_LOCK_TIME               PASSWORD 1
                PASSWORD_GRACE_TIME              PASSWORD 7
                • 5. Re: ORA-28002: the password will expire within string days
                  Pavan Kumar
                  Hi,

                  You can as per requirement.

                  The number of days the same password can be used for authentication
                  password_life_time <value | UNLIMITED | DEFAULT>

                  - Pavan Kumar N
                  • 6. Re: ORA-28002: the password will expire within string days
                    fsze88hoho
                    hum.....
                    No,
                    ORA-01733 virtual column not allowed here
                    Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view.
                    Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.
                    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e1500.htm
                    [oracle@localhost ~]$ export ORACLE_SID=kennam
                    [oracle@localhost ~]$ sqlplus / as sysdba
                    SQL> update dba_profiles set limit='UNLIMITED' where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';
                    update dba_profiles set limit='UNLIMITED' where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME'
                                            *
                    ERROR at line 1:
                    ORA-01733: ???????????
                    Any idea(s)? ;)
                    • 7. Re: ORA-28002: the password will expire within string days
                      Pavan Kumar
                      Hi,

                      ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

                      - Pavan Kumar N
                      • 8. Re: ORA-28002: the password will expire within string days
                        fsze88hoho
                        Dear Stellios,Justin Cave,Mohd Mehraj Hus...,Pavan Kumar
                        Thank you, all of you was helping to solve my problem and wasted your valuable time ;)
                        • 9. Re: ORA-28002: the password will expire within string days
                          656804
                          Yep. The statement to change the profile is certainly

                          ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

                          However, my question is despite changing the profile, my users are still getting the warning about password expiration.
                          ORA-28002: the password will expire within 7 days


                          I understand that if they reset it now, the problem shouldn't occur.
                          But is there a way to have the existing users not get this warning anymore without resorting to resetting the password ?

                          -AS
                          • 10. Re: ORA-28002: the password will expire within string days
                            693783
                            Hi ,

                            ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

                            the above stmt will ensure that the users who are assigned with default profile shldn't be getting issues.


                            Thx
                            JP