ITPub博客

首页 > 数据库 > Oracle > Create Profile 摘自官当

Create Profile 摘自官当

原创 Oracle 作者:yewushang 时间:2014-02-09 20:11:30 0 删除 编辑
前一篇文章http://blog.itpub.net/29477587/viewspace-1078536/写到profile。记录下limit的含义以备使用。
摘自10g 官当。


点击(此处)折叠或打开

  1. Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.

  2. profile
  3. Specify the name of the profile to be created. Use profiles to limit the database resources available to a user for a single call or a single session.
  4. Oracle Database enforces resource limits in the following ways:
  5. If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.
  6. If a user attempts to perform an operation that exceeds the limit for other session resources, then the database aborts the operation, rolls back the current statement, and immediately returns an error. The user can then commit or roll back the current transaction, and must then end the session.
  7. If a user attempts to perform an operation that exceeds the limit for a single call, then the database aborts the operation, rolls back the current statement, and returns an error, leaving the current transaction intact.
  8. UNLIMITED
  9. When specified with a resource parameter, UNLIMITED indicates that a user assigned this profile can use an unlimited amount of this resource. When specified with a password parameter, UNLIMITED indicates that no limit has been set for the parameter.

  10. DEFAULT
  11. Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement.
  12. Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, then the user is subject to the limits on those resources defined by the DEFAULT profile.
  13. resource_parameters
  14. SESSIONS_PER_USER
  15. Specify the number of concurrent sessions to which you want to limit the user.
  16. CPU_PER_SESSION
  17. Specify the CPU time limit for a session, expressed in hundredth of seconds.
  18. CPU_PER_CALL
  19. Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
  20. CONNECT_TIME
  21. Specify the total elapsed time limit for a session, expressed in minutes.
  22. IDLE_TIME
  23. Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
  24. LOGICAL_READS_PER_SESSION
  25. Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
  26. LOGICAL_READS_PER_CALL
  27. Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
  28. PRIVATE_SGA
  29. Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA).
  30. COMPOSITE_LIMIT
  31. Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
  32. password_parameters
  33. Use the following clauses to set password parameters. Parameters that set lengths of time are interpreted in number of days. For testing purposes you can specify minutes (n/1440) or even seconds (n/86400).
  34. FAILED_LOGIN_ATTEMPTS
  35. Specify the number of failed attempts to log in to the user account before the account is locked.
  36. PASSWORD_LIFE_TIME
  37. Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.
  38. PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
  39. 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.
  40. 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.
  41. 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.
  42. If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
  43. 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 ofUNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
  44. If you set both of these parameters to UNLIMITED, then the database ignores both of them.
  45. PASSWORD_LOCK_TIME
  46. Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
  47. PASSWORD_GRACE_TIME
  48. Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
  49. PASSWORD_VERIFY_FUNCTION
  50. The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement. Oracle Database provides a default script, but you can create your own routine or use third-party software instead.
  51. For function, specify the name of the password complexity verification routine.
  52. Specify NULL to indicate that no password verification is performed.
  53. If you specify expr for any of the password parameters, the expression can be of any form except scalar subquery expression.

  54. Examples
  55. Creating a Profile: Example
  56. The following statement creates the profile new_profile:
  57. CREATE PROFILE new_profile
  58.   LIMIT PASSWORD_REUSE_MAX 10
  59.         PASSWORD_REUSE_TIME 30;
  60. Setting Profile Resource Limits: Example
  61. The following statement creates the profile app_user:
  62. CREATE PROFILE app_user LIMIT
  63.    SESSIONS_PER_USER UNLIMITED
  64.    CPU_PER_SESSION UNLIMITED
  65.    CPU_PER_CALL 3000
  66.    CONNECT_TIME 45
  67.    LOGICAL_READS_PER_SESSION DEFAULT
  68.    LOGICAL_READS_PER_CALL 1000
  69.    PRIVATE_SGA 15K
  70.    COMPOSITE_LIMIT 5000000;

  71. If you assign the app_user profile to a user, the user is subject to the following limits in subsequent sessions:
  72. The user can have any number of concurrent sessions.
  73. In a single session, the user can consume an unlimited amount of CPU time.
  74. A single call made by the user cannot consume more than 30 seconds of CPU time.
  75. A single session cannot last for more than 45 minutes.
  76. In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
  77. A single call made by the user cannot read more than 1000 data blocks from memory and disk.
  78. A single session cannot allocate more than 15 kilobytes of memory in the SGA.
  79. In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.
  80. Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.
  81. Setting Profile Password Limits: Example
  82. The following statement creates the app_user2 profile with password limits values set:
  83. CREATE PROFILE app_user2 LIMIT
  84.    FAILED_LOGIN_ATTEMPTS 5
  85.    PASSWORD_LIFE_TIME 60
  86.    PASSWORD_REUSE_TIME 60
  87.    PASSWORD_REUSE_MAX 5
  88.    PASSWORD_VERIFY_FUNCTION verify_function
  89.    PASSWORD_LOCK_TIME 1/24
  90.    PASSWORD_GRACE_TIME 10;



仅供学习。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1078541/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-09

  • 博文量
    53
  • 访问量
    275918