Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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, that user cannot exceed these limits.
See Also:
Oracle9i Database Administrator's Guide for a detailed description and explanation of how to use password management and protection |
To create a profile, you must have CREATE
PROFILE
system privilege.
To specify resource limits for a user, you must:
ALTER
SYSTEM
statement or with the initialization parameter RESOURCE_LIMIT
. (This parameter does not apply to password resources. Password resources are always enabled.)CREATE
PROFILE
statementCREATE
USER
or ALTER
USER
statement
See Also:
|
create_profile::=
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.
Oracle enforces resource limits in the following ways:
CONNECT_TIME
or IDLE_TIME
session resource limit, Oracle rolls back the current transaction and ends the session. When the user process next issues a call, Oracle returns an error.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.
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.
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, the user is subject to the limits on those resources defined by the DEFAULT
profile.
Specify the number of concurrent sessions to which you want to limit the user.
Specify the CPU time limit for a session, expressed in hundredth of seconds.
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
Specify the total elapsed time limit for a session, expressed in minutes.
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.
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
Specify 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 Shared 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. |
Specify the total resource cost for a session, expressed in service units. Oracle calculates the total service units as a weighted sum of CPU_PER_SESSION
, CONNECT_TIME
, LOGICAL_READS_PER_SESSION
, and PRIVATE_SGA
.
See Also:
ALTER RESOURCE COST for information on how to specify the weight for each session resource |
If you specify expr
for any of these parameters, the expression can be of any form except scalar subquery expression.
Specify the number of failed attempts to log in to the user account before the account is locked.
Specify 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.
Specify the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME
to an integer value, then you must set PASSWORD_REUSE_MAX
to UNLIMITED
.
Specify the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX
to an integer value, then you must set PASSWORD_REUSE_TIME
to UNLIMITED
.
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
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.
The PASSWORD_VERIFY_FUNCTION
clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE
PROFILE
statement. Oracle provides a default script, but you can create your own routine or use third-party software instead.
function
, specify the name of the password complexity verification routine.NULL
to indicate that no password verification is performed.PASSWORD_REUSE_TIME
is set to an integer value, PASSWORD_REUSE_MAX
must be set to UNLIMITED
. If PASSWORD_REUSE_MAX
is set to an integer value, PASSWORD_REUSE_TIME
must be set to UNLIMITED
.PASSWORD_REUSE_TIME
and PASSWORD_REUSE_MAX
are set to UNLIMITED
, then Oracle uses neither of these password resources.PASSWORD_REUSE_MAX
is set to DEFAULT
and PASSWORD_REUSE_TIME
is set to UNLIMITED
, then Oracle uses the PASSWORD_REUSE_MAX
value defined in the DEFAULT
profile.PASSWORD_REUSE_TIME
is set to DEFAULT
and PASSWORD_REUSE_MAX
is set to UNLIMITED
, then Oracle uses the PASSWORD_REUSE_TIME
value defined in the DEFAULT
profile.PASSWORD_REUSE_TIME
and PASSWORD_REUSE_MAX
are set to DEFAULT
, then Oracle uses whichever value is defined in the DEFAULT
profile.
The following statement creates the profile new_profile
:
CREATE PROFILE new_profile LIMIT PASSWORD_REUSE_MAX DEFAULT PASSWORD_REUSE_TIME UNLIMITED;
The following statement creates the profile app_user
:
CREATE PROFILE app_user 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 app_user
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 same app_user2
profile with password limits values set:
CREATE PROFILE app_user2 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;
This example uses Oracle's password verification function, verify_function
. Please refer to Oracle9i Database Administrator's Guide for information on using this verification function provided by Oracle or designing your own verification function.