1 2 Previous Next 17 Replies Latest reply: Jul 8, 2014 10:05 AM by JustinCave RSS

    Standard profile settings

      Hi all,

       

      11g

      aix

       

      I want to set limits to all parameters in our prod dba default PROFILE. I mean here all profiles in DBA_PROFILEs in which the DEFAULT profile is one of them.

      Can you help me what is the normal, or standard settings of limits please?

       

      Default profile is:

      DEFAULTPASSWORD_GRACE_TIMEPASSWORD7
      DEFAULTPASSWORD_LOCK_TIMEPASSWORD1
      DEFAULTPASSWORD_VERIFY_FUNCTIONPASSWORDNULL
      DEFAULTPASSWORD_REUSE_MAXPASSWORDUNLIMITED
      DEFAULTPASSWORD_REUSE_TIMEPASSWORDUNLIMITED
      DEFAULTPASSWORD_LIFE_TIMEPASSWORD180
      DEFAULTFAILED_LOGIN_ATTEMPTSPASSWORD10
      DEFAULTPRIVATE_SGAKERNELUNLIMITED
      DEFAULTCONNECT_TIMEKERNELUNLIMITED
      DEFAULTIDLE_TIMEKERNELUNLIMITED
      DEFAULTLOGICAL_READS_PER_CALLKERNELUNLIMITED
      DEFAULTLOGICAL_READS_PER_SESSIONKERNELUNLIMITED
      DEFAULTCPU_PER_CALLKERNELUNLIMITED
      DEFAULTCPU_PER_SESSIONKERNELUNLIMITED
      DEFAULTSESSIONS_PER_USERKERNELUNLIMITED
      DEFAULTCOMPOSITE_LIMITKERNELUNLIMITED

       

      Thanks all,

      mk

       

      Message was edited by: MariaKarpa(MK)

        • 1. Re: Standard profile settings
          JustinCave

          I'd be pretty concerned about anyone that was proclaiming a "normal" or "standard" setting.  The right setting is heavily dependent on your organization, your database, your other password policies, the work your application(s) need to perform, etc.  It makes little sense to talk about a "standard" setting.  If every other password in your organization expires after 60 days, an expiration of 180 days probably doesn't make sense.  If every other password expires annually, a setting of 30 days makes no sense,  If your applications are OLTP, you can probably set lower *_PER_CALL settings, should you want to, than applications doing DSS type queries. 

           

          What problem(s) are you trying to solve?

           

          Justin

          • 2. Re: Standard profile settings

            Thanks Justin!

            • 3. Re: Standard profile settings
              JudWilliford

              You mentioned that this is the profile to be applied to just the DBA accounts, presumably individual accounts that you want your DBAs to use so you know who did what.  If that's the case, here are some considerations:

               

              KERNEL limits:  For a DBA, I wouldn't set these at all.   Keep at unlimited.

               

              PASSWORD limits:  Two cases--

               

              CENTRALIZED CREDENTIALS 

               

              I would expect your organization already has some kind of centralized credentials management for individuals.  This provides a central place to enforce password strength and change frequency.  In that case, you would either not be storing the passwords at the database level (e.g., authenticate with Kerberos, SSL, or EUS) or you would be using your IdM or other credentials management system to push the passwords from the central source.  In either of those cases, most of the PASSWORD settings are not relevant.

               

              However, if the password is being pushed from a central source, you would want to activate the FAILED_LOGIN_ATTEMPTS, LOCK_TIME, and VERIFY_FUNCTION.  FAILED_LOGIN_ATTEMPTS=10 seems pretty reasonable, though I have seen it specified lower for compliance purposes; whatever your controls dictate is what you go with.  LOCK_TIME=1 is a whole day with the account locked (after too many failed attempts); that's probably unacceptable.  Use a value of 1/24 to get it down to an hour.  Otherwise, your DBAs will be bugging each other to remove the lock and/or opting out of using their individual accounts and going with SYSTEM or / AS SYSDBA.   VERIFY_FUNCTION=null means they don't have to meet any strength requirements, which in a centralized credentials environment they shouldn't be doing at the local database--HOWEVER! you actually don't want them changing their password at all locally if the password is being pushed.  So I have written a function that fails anytime they try to change the password locally.  You can even add a URL to the error message, pointing them to the central password management location on your intranet.

               

              LOCALLY MANAGED CREDENTIALS

               

              In this case, you should set all the PASSWORD limits.  FAILED_LOGIN_ATTEMPTS and LOCK_TIME as above.  In this case, VERIFY_FUNCTION should enforce your company's password strength rules.  REUSE_MAX and REUSE_TIME is really up to you, but remember they can drive up storage requirements if you have a lot of accounts for which you are having to keep extensive password history locally.  LIFE_TIME is how you control the password change frequency, so as Justin says you should set that per company policy.

              • 4. Re: Standard profile settings

                Thanks all,

                 

                Is it possible to set the PROFILE to this?

                 

                ALTER PROFILE default LIMIT

                COMPOSITE_LIMITUNLIMITED
                CONNECT_TIMEUNLIMITED
                CPU_PER_CALLUNLIMITED
                CPU_PER_SESSIONUNLIMITED
                FAILED_LOGIN_ATTEMPTSUNLIMITED
                IDLE_TIMEUNLIMITED
                LOGICAL_READS_PER_CALLUNLIMITED
                LOGICAL_READS_PER_SESSIONUNLIMITED
                PASSWORD_GRACE_TIMEUNLIMITED
                PASSWORD_LIFE_TIMEUNLIMITED
                PASSWORD_LOCK_TIMEUNLIMITED
                PASSWORD_REUSE_MAXUNLIMITED
                PASSWORD_REUSE_TIMEUNLIMITED
                PASSWORD_VERIFY_FUNCTIONUNLIMITED
                PRIVATE_SGAUNLIMITED
                SESSIONS_PER_USERUNLIMITED;

                 

                or this ?

                 

                ALTER PROFILE default LIMIT

                COMPOSITE_LIMITDEFAULT
                CONNECT_TIMEDEFAULT
                CPU_PER_CALLDEFAULT
                CPU_PER_SESSIONDEFAULT
                FAILED_LOGIN_ATTEMPTSDEFAULT
                IDLE_TIMEDEFAULT
                LOGICAL_READS_PER_CALLDEFAULT
                LOGICAL_READS_PER_SESSIONDEFAULT
                PASSWORD_GRACE_TIMEDEFAULT
                PASSWORD_LIFE_TIMEDEFAULT
                PASSWORD_LOCK_TIMEDEFAULT
                PASSWORD_REUSE_MAXDEFAULT
                PASSWORD_REUSE_TIMEDEFAULT
                PASSWORD_VERIFY_FUNCTIONDEFAULT
                PRIVATE_SGADEFAULT
                SESSIONS_PER_USERDEFAULT;

                 

                Thanks all,

                mk

                • 5. Re: Standard profile settings

                  Hi all,

                   

                  Which one is invalid ? Thanks.

                   

                  SQL> ALTER PROFILE default LIMIT

                    2  COMPOSITE_LIMIT                            default

                    3  CONNECT_TIME                               default

                    4  CPU_PER_CALL                               default

                    5  CPU_PER_SESSION                            default

                    6  FAILED_LOGIN_ATTEMPTS                      default

                    7  IDLE_TIME                                  default

                    8  LOGICAL_READS_PER_CALL                     default

                    9  LOGICAL_READS_PER_SESSION                  default

                  10  PASSWORD_GRACE_TIME                         default

                  11  PASSWORD_LIFE_TIME                          default

                  12  PASSWORD_LOCK_TIME                          default

                  13  PASSWORD_REUSE_MAX                          default

                  14  PASSWORD_REUSE_TIME                         default

                  15  PASSWORD_VERIFY_FUNCTION                    default

                  16  PRIVATE_SGA                                 default

                  17  SESSIONS_PER_USER                           default;

                  ALTER PROFILE default LIMIT

                  *

                  ERROR at line 1:

                  ORA-02377: invalid resource limit

                  • 6. Re: Standard profile settings

                    Hi Jud,

                     

                    ****You mentioned that this is the profile to be applied to just the DBA accounts.


                    Actually No I am referring to ALL profiles in DBA_PROFILES.


                    Thanks,

                    Mk

                    • 7. Re: Standard profile settings
                      JudWilliford

                      You can't use DEFAULT as a limit setting on the DEFAULT profile, as it is the source for all default limit values.  DEFAULT as a limit value only works on NON-default profiles.  It is saying, "use the limit value found in the DEFAULT profile."

                      • 8. Re: Standard profile settings
                        andrewmy

                        You haven't explained why you want to change those settings. It's hard to give advice when there is no requirement or objective. What is it about DBA access that you are you trying to control? You realize that DBAs can typically just go in and change those settings back to whatever they were, right?

                        • 9. Re: Standard profile settings

                          Thanks,  I am not controlling dba but rather ordinary users using the DEFAULT profile. I am just doing test on DEV only to understand how it profiles work.

                          Which is line here has error:

                           

                          ALTER PROFILE default LIMIT

                          COMPOSITE_LIMITUNLIMITED
                          CONNECT_TIMEUNLIMITED
                          CPU_PER_CALLUNLIMITED
                          CPU_PER_SESSIONUNLIMITED
                          FAILED_LOGIN_ATTEMPTSUNLIMITED
                          IDLE_TIMEUNLIMITED
                          LOGICAL_READS_PER_CALLUNLIMITED
                          LOGICAL_READS_PER_SESSIONUNLIMITED
                          PASSWORD_GRACE_TIMEUNLIMITED
                          PASSWORD_LIFE_TIMEUNLIMITED
                          PASSWORD_LOCK_TIMEUNLIMITED
                          PASSWORD_REUSE_MAXUNLIMITED
                          PASSWORD_REUSE_TIMEUNLIMITED
                          PASSWORD_VERIFY_FUNCTIONUNLIMITED
                          PRIVATE_SGAUNLIMITED
                          SESSIONS_PER_USERUNLIMITED;

                           

                          or this ?

                           

                          ALTER PROFILE default LIMIT

                          COMPOSITE_LIMITDEFAULT
                          CONNECT_TIMEDEFAULT
                          CPU_PER_CALLDEFAULT
                          CPU_PER_SESSIONDEFAULT
                          FAILED_LOGIN_ATTEMPTSDEFAULT
                          IDLE_TIMEDEFAULT
                          LOGICAL_READS_PER_CALLDEFAULT
                          LOGICAL_READS_PER_SESSIONDEFAULT
                          PASSWORD_GRACE_TIMEDEFAULT
                          PASSWORD_LIFE_TIMEDEFAULT
                          PASSWORD_LOCK_TIMEDEFAULT
                          PASSWORD_REUSE_MAXDEFAULT
                          PASSWORD_REUSE_TIMEDEFAULT
                          PASSWORD_VERIFY_FUNCTIONDEFAULT
                          PRIVATE_SGADEFAULT
                          SESSIONS_PER_USERDEFAULT;

                           

                           

                          Both command throws same error

                           

                          ALTER PROFILE default LIMIT

                          *

                          ERROR at line 1:

                          ORA-02377: invalid resource limit

                           

                          Thanks,

                          • 10. Re: Standard profile settings
                            kcrrga

                            Default profile is used when your user is not assigned to a profile. You should be able to  create a profile and assign all required values to it. But remember you should assign the profile to the user who need to assign the limits to.

                             

                            Also, when you are creating new users you should be assigning the correct profile to the user. DBA_PROFILE is data dictionary views and doesn't relate to any DBA's :-)

                             

                            Go through the below link for more understanding.

                             

                            CREATE PROFILE

                            • 11. Re: Standard profile settings
                              JudWilliford

                              Hi Maria, I think the error you are getting is on the PASSWORD_VERIFY_FUNCTION.  You can't set it to UNLIMITED!  It has to either be NULL or the name of a function that can serve to verify password formation rules.  AND (as mentioned previously) you can't use DEFAULT for it on the DEFAULT profile.  You could only say PASSWORD_VERIFY_FUNCTION = DEFAULT on a non-default profile, in which case you are telling that profile to use the same setting for this as used in the DEFAULT profile.

                              • 12. Re: Standard profile settings

                                Thanks Sirs, it worked

                                 

                                SQL> ALTER PROFILE default LIMIT

                                  2  COMPOSITE_LIMIT    UNLIMITED

                                  3  CONNECT_TIME       UNLIMITED

                                  4  CPU_PER_CALL       UNLIMITED

                                  5  CPU_PER_SESSION    UNLIMITED

                                  6  FAILED_LOGIN_ATTEMPTS      UNLIMITED

                                  7  IDLE_TIME  UNLIMITED

                                  8  LOGICAL_READS_PER_CALL     UNLIMITED

                                  9  LOGICAL_READS_PER_SESSION  UNLIMITED

                                10  PASSWORD_GRACE_TIME        UNLIMITED

                                11  PASSWORD_LIFE_TIME UNLIMITED

                                12  PASSWORD_LOCK_TIME UNLIMITED

                                13  PASSWORD_REUSE_MAX UNLIMITED

                                14  PASSWORD_REUSE_TIME        UNLIMITED

                                15  PASSWORD_VERIFY_FUNCTION   null

                                16  PRIVATE_SGA        UNLIMITED

                                17  SESSIONS_PER_USER  UNLIMITED;

                                 

                                 

                                Profile altered.

                                 

                                 

                                How do I limit non-priority batch jobs to very low resources of CPU and MEMORY?

                                 

                                 

                                Thanks

                                • 13. Re: Standard profile settings
                                  JustinCave

                                  Probably not using profiles.  Unless your goal is to kill the batch job if one of its queries/ sessions has consumed too many resources.  That would normally end up being quite counterproductive, though.

                                   

                                  Assuming you have the enterprise edition, you'd use Resource Manager.  Of course, Resource Manager would only work to limit CPU at the point that the server was out of spare CPU-- if there is any idle CPU, the low-priority jobs would get it if they asked for it.

                                   

                                  Justin

                                  • 14. Re: Standard profile settings

                                    Thanks Sir,

                                     

                                    I read the Resource Manager thing and its very complicated to implement, plus it is warning that wrong setup of this will make your Prod go crazy and hang?

                                     

                                    Can you give me steps to do it right?

                                    1 2 Previous Next