1 2 Previous Next 22 Replies Latest reply: Jun 5, 2010 11:01 AM by stephen_price RSS

    Password Reuse Max question

    Dan A
      Hi.
      We have a consultant installing software on our dev. database. He tells me that he gets this message: ora-28007: the password cannot be reused when he tries to reset the users password. The user, after I checked, has teh DEFAULT profile, and for default profile we have this:
      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 UNLIMITED
      PASSWORD_LIFE_TIME UNLIMITED

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

      16 rows selected.

      So, what can I infer from this? That the consultant has dropped the user / changed the user's password more than 12 times?

      Not sure what to tell him.
      thanks
      DA
        • 1. Re: Password Reuse Max question
          Centinul
          When you have the PASSWORD_REUSE_TIME set to unlimited you can NEVER use the same password again. This is from the Oracle documentation found here:

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm#SQLRF01310

          "PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
          These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.

          If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.

          For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.

          If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.

          If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.

          If you set both of these parameters to UNLIMITED, then the database ignores both of them."
          • 2. Re: Password Reuse Max question
            Keith Jamieson
            SInce its a dev database, you could just alter the profile, but I would prefer to create a new profile and assign this consultants user to the new profile.

            I prefer to have my own profiles rather than using the oracle default profile.
            • 3. Re: Password Reuse Max question
              Dan A
              Thanks fellas!
              I am going to create a profile for him.
              DA
              • 4. Re: Password Reuse Max question
                575424
                See the manual for Password Reuse policy. Both parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX work together. Centinul has explained very well.

                http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/policies.htm#i1007340
                • 5. Re: Password Reuse Max question
                  Aman....
                  Centinul,
                  If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME
                  Very nice explnation.Just one correction I would like to make that we can't set both the parameters to an integer value as you mentioned.Integer value can be set only for one parameter could be eiher one.
                  Aman....
                  • 6. Re: Password Reuse Max question
                    Dan A
                    Well fellas Im trying this but no luck so far:
                    SQL> alter profile default limit
                    2 password_reuse_time 0
                    3 password_reuse_max default;
                    alter profile default limit
                    *
                    ERROR at line 1:
                    ORA-02377: invalid resource limit

                    I only want to change the default profile temporarily so that he can change his password as many times as needed.
                    Any advice?

                    When I change it to this

                    1 alter profile default limit
                    2 password_reuse_time 1
                    3* password_reuse_max unlimited
                    SQL> /

                    Workds ok. But I need the reuse time to be zero!

                    Message was edited by:
                    Dan A
                    • 7. Re: Password Reuse Max question
                      Jaffy
                      Hi,

                      You can set password_reuse_time as default and password_reuse_max default. As you need this profile temporarily you can set a profile with all all parameters as default so he can connect as many time as user wants.

                      http;//youngcow.net/doc/oracle10g/network.102/b14266/admusers.htm

                      Regards

                      Jafar
                      • 8. Re: Password Reuse Max question
                        Dan A
                        HI.
                        Im trying that with this
                        SQL> alter profile default limit
                        2 password_reuse_time default
                        3 password_reuse_max default;
                        alter profile default limit
                        *
                        ERROR at line 1:
                        ORA-02377: invalid resource limit
                        The default profile is set for unlimited and I simply want to change it !
                        • 9. Re: Password Reuse Max question
                          Aman....
                          Dan,
                          I doubt you can set the limit to 0.As this is what the error message is explaining too,
                          ORA-02377: invalid resource limit
                          Cause: specifying limit of 0
                          Action: specify limit > 0

                          source,
                          http://ora-02377.ora-code.com/
                          So I guess you have to mention the resource limit greater than 0 or use default for both.
                          Aman....
                          • 10. Re: Password Reuse Max question
                            Aman....
                            Just out of curiosity,why you need to alter the default profile?Let it be there and create one more and assign it to the users or this user specifically?Wont that work?
                            Aman....
                            • 11. Re: Password Reuse Max question
                              Dan A
                              Aman
                              yes Im clear about that - I realise that I cant set 0 (zero) for the value. But when I try and set both for "default" - I get an error!
                              here are the settings for the profile default right now:
                              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 UNLIMITED
                              PASSWORD_LIFE_TIME UNLIMITED

                              RESOURCE_NAME LIMIT
                              -------------------------------- ----------------------------------------
                              PASSWORD_REUSE_TIME UNLIMITED
                              PASSWORD_REUSE_MAX 12
                              PASSWORD_VERIFY_FUNCTION NULL
                              PASSWORD_LOCK_TIME UNLIMITED
                              PASSWORD_GRACE_TIME UNLIMITED
                              • 12. Re: Password Reuse Max question
                                Jaffy
                                Hi,

                                I think you are specifying profile name as default, where a default profile should exist by default. Create profile with different name, as i am able to create a profile with mentioned options.

                                Regards

                                Jafar
                                • 13. Re: Password Reuse Max question
                                  Aman....
                                  Dan,
                                  I understand that.Now what I can think is that this is some sort of restriction that if one tries to alter teh default profile than it can't be set to default keyword.I am not sure but that's what it sounds here.
                                  In the output,you got one set to integer and one to unlimited which is the only right combination as when one gets set to integer,otger has to be in unlimited only.
                                  Still I shall try to search something for this and try to get back.
                                  Aman....
                                  • 14. Re: Password Reuse Max question
                                    Dan A
                                    Yes you are right there is some restriction on using "default" in the same statement but how can one possibly make a change to the profile "default" without using the word "default" twice???

                                    Thanks!
                                    1 2 Previous Next