Oracle Profile

CREATE PROFILE
Purpose

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.

Prerequisites
To create a profile, you must have the CREATE PROFILE system privilege.
To specify resource limits for a user, you must:

Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.
Create a profile that defines the limits using the CREATE PROFILE statement
Assign the profile to the user using the CREATE USER or ALTER USER statement

resource_parameters
SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.

CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.

CPU_PER_CALL.
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths .of seconds.

CONNECT_TIME.
Specify the total elapsed time limit for a session, expressed in minutes.

IDLE_TIME.
Specify the permitted periods of continuous inactive time during a session, expressed in minutes.

LOGICAL_READS_PER_SESSION.
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.

LOGICAL_READS_PER_CALL.
Specify the permitted the number of data blocks read for a call to process a SQL statement .

PRIVATE_SGA.
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

COMPOSITE_LIMIT
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.

password_parameters
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).

FAILED_LOGIN_ATTEMPTS.
Specify the number of failed attempts to log in to the user account before the account is locked.

PASSWORD_LIFE_TIME.
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.

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.

PASSWORD_LOCK_TIME.
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.

PASSWORD_GRACE_TIME
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.

Examples.

Creating a Profile: Example
The following statement creates the profile new_profile:
CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 30;

Setting Profile Resource Limits: Example
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;

Alter user scott profile app_user;
Setting Profile Password Limits: Example
The following statement creates the 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 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10

Posted in: Oracle