Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 20 of 20
To create a profile. A profile is a set of limits on database resources. If you assign the profile to a user, that user cannot exceed these limits.
You must have CREATE
PROFILE
system privilege.
To specify resource limits for a user, you must:
ALTER
SYSTEM
statement (see "ALTER SYSTEM") or with the initialization parameter RESOURCE_LIMIT
. (This parameter does not apply to password resources. Password resources are always enabled.)
CREATE
PROFILE
statement.
CREATE
USER
or ALTER
USER
statement (see "CREATE USER" and "ALTER USER").
profile |
is 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. |
|
|
Oracle enforces resource limits in the following ways:
|
|
|
Notes:
|
|
|
When specified with a resource parameter, indicates that a user assigned this profile can use an unlimited amount of this resource. When specified with a password parameter, indicates that no limit has been set for the parameter. |
|
|
omits a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the |
|
|
Any user who is not explicitly assigned a profile is subject to the limits defined in the |
|
resource_parameters |
|
|
|
limits a user to integer concurrent sessions. |
|
|
limits the CPU time for a session, expressed in hundredth of seconds. |
|
|
limits the CPU time for a call (a parse, execute, or fetch), expressed in hundredths of seconds. |
|
|
limits the total elapsed time of a session, expressed in minutes. |
|
|
limits periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. |
|
|
specifies the number of data blocks read in a session, including blocks read from memory and disk. |
|
|
specifies the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). |
|
|
specifies the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes. Use K or M to specify this limit in kilobytes or megabytes. |
|
|
Note: This limit applies only if you are using multi-threaded server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas. |
|
|
specifies the total resources cost for a session, expressed in service units. Oracle calculates the total service units as a weighted sum of For information on how to specify the weight for each session resource, see "ALTER RESOURCE COST". |
|
password_parameters |
For a detailed description and explanation of how to use password management and protection, see Oracle8i Administrator's Guide. |
|
|
specifies the number of failed attempts to log in to the user account before the account is locked. |
|
|
limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected. |
|
|
specifies the number of days before which a password cannot be reused. If you set |
|
|
specifies the number of password changes required before the current password can be reused. If you set |
|
|
specifies the number of days an account will be locked after the specified number of consecutive failed login attempts. |
|
|
specifies 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. |
|
|
allows a PL/SQL password complexity verification script to be passed as an argument to the |
|
|
function |
is the name of the password complexity verification routine. |
|
|
indicates that no password verification is performed. |
Restrictions on password parameters:
|
The following statement creates the profile SYSTEM_MANAGER
:
CREATE PROFILE system_manager LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE SGA 15K COMPOSITE_LIMIT 5000000;
If you then assign the SYSTEM_MANAGER
profile to a user, the user is subject to the following limits in subsequent sessions:
DEFAULT
profile.
ALTER RESOURCE COST
statement.
SYSTEM_MANAGER
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.
The following statement creates the profile PROF
:
CREATE PROFILE prof LIMIT PASSWORD_REUSE_MAX DEFAULT PASSWORD_REUSE_TIME UNLIMITED;
The following statement creates profile MYPROFILE
with password profile limits values set:
CREATE PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_LOCK_TIME 1/24 PASSWORD_GRACE_TIME 10;
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|