Oracle Profiles

Profiles helps DBA to control usage of database resources by user sessions.

Database profiles provide a means for administrators to control and restrict usage of various database and operating system resources on a per user session level and also as a policy to manage passwords. The below are various resources that can be controlled with profiles.

COMPOSITE_LIMIT

This controls total resource cost for a session. It is expressed as service units. Service units are calculated as weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION and PRIVATE_SGA. The formula that Oracle uses to arrive at COMPOSITE_LIMIT can be specified using ALTER RESOURCE COST statement. If a user session exceeds the COMPOSITE_LIMIT, the user session is terminated with error.

How to alter various resource weights with ALTER RESOURCE COST statement.

ALTER RESOURCE COST CPU_PER_SESSION <value>;
ALTER RESOURCE COST CONNECT_TIME <value>;
ALTER RESOURCE COST LOGICAL_READS_PER_SESSION <value>;
ALTER RESOURCE COST PRIVATE_SGA <value>;
Example
ALTER RESOURCE COST CPU_PER_SESSION 100 CONNECT_TIME 1;
Note: Both 100 and 1 are weights for the respective resource.

The weight is derived as CPU_PER_SESSION * 100 + CONNECT_TIME * 1

CPU_PER_SESSION and CONNECT_TIME are values set in profile for the user.

Since LOGICAL_READS_PER_SESSION & PRIVATE_SGA are not included in ALTER statement, these two parameters are not used in arriving the total resource cost.

Database multiplies the weights with each resource CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION & PRIVATE_SGA; The product is expressed in unit called service units To arrive at the composite limit, database sums up all the product. The sum is expressed in unit called service unit.

Consider the below statement.

ALTER RESOURCE COST LOGICAL_READS_PER_SESSION 10 CONNECT_TIME 0;

The new COMPOSITE_LIMIT will be :
LOGICAL_READS_PER_SESSION * 10 + CONNECT_TIME * 0 + CPU_PER_SESSION * 100

The weight for CPU_PER_SESSION is taken from previous weight, new weight for CONNECT_TIME from current setting and the new weight for LOGICAL_READS_PER_SESSION.

CONNECT_TIME

This is the total elapsed time limit for a user session. If the value for this parameter is 20, then the user session expires after twentyth minute. It is expressed in minutes.

CPU_PER_CALL

This is the time limit for each operation parse, execute and fetch. It is expressed in hundredth of a second.

CPU_PER_SESSION

This is the CPU time limit set for a session which is expressed in hundredth of a second.

FAILED_LOGIN_ATTEMPTS (Account Locking)

This limit locks the account if the user attemts to enter incorrect password for the value set to this parameter. That is, if the FAILED_LOGIN_ATTEMPTS is set to 3, then user can enter incorrect password two times. If the password provided for the third time is incorrect, the account is locked, hence further logins denied until the account is unlocked. If the password provided for the third time is correct, the user logs into the database. Default is 10 attempts.

IDLE_TIME

This limit expires the user session if the session is idle for the value set to this parameter. For example, if IDLE_TIME is set to 10, the user session can be idle for 10 minutes (600 seconds) after which the used has to re-login. Session that run long queries that cross this time limit are not expired. The unit is minutes.

LOGICAL_READS_PER_CALL

This parameter sets the limit for number of data blocks that can be read by a SQL. Default is UNLIMITED.

LOGICAL_READS_PER_SESSION

This parameter sets the limit for number of data blocks that can be read by all SQLs for a session. This value includes both disk blocks and memory blocks. Default is UNLIMITED.

PASSWORD_GRACE_TIME (Password Aging and Expiration)

This parameter is used to issue warnings to user about password expiry. If PASSWORD_LIFE_TIME is set to 10 days, and PASSWORD_GRACE_TIME is 3, the warning "ORA-28002: the password will expire within 3 days" appear after 7 days for the remaining 3 days. If the password is not changed, the user account locks preventing further connection to database. Default grace time is 7 days.

PASSWORD_LIFE_TIME (Password Aging and Expiration)

Denotes the duration in days the same password can be used. The user is required to change the password with the days specified by this parameter or the account is locked. Default is 180 days.

PASSWORD_LOCK_TIME (Account Locking)

Specifies the number of days the account remain locked when an incorrect password is provided more than FAILED_LOGIN_ATTEMPTS. For example, if FAILED_LOGIN_ATTEMPTS is 3 and PASSWORD_LOCK_TIME is 1, then a user tried to connect by incorrect password more than 3 times, the account is locked for 1 day, after which the account is unlocked automatically. Default is 10 days. LOCK_DATE column of DBA_USERS is of interest for further understanding of PASSWORD_LOCK_TIME.

PASSWORD_REUSE_MAX & PASSWORD_REUSE_TIME (Password History)

These two parameters decides when a password that was used earlier can be used now. If a user wants to use a password that he used earlier, then he must change passwords value set in PASSWORD_REUSE_MAX and should also be more than days specified in PASSWORD_REUSE_TIME. These two parameters are used in conjunction. For example, if PASSWORD_REUSE_TIME is set to 90 days and PASSWORD_REUSE_MAX is set to 3 times, then, to use a password that a user is using now, he must change the password 3 times within 90 days. If either of the parameters is set to UNLIMITED, the user can never reuse the password. For example, if the password is "abc123", the user can never use "abc123" as his future password. If he value of either parameter is DEFAULT, then the value of that parameter set in DEFAULT profile will be used. If the value for the parameter is UNLIMITED, then the user can never reuse the password. If the value is UNLIMITED for both the parameters, then both the parameters are ignored by database.

PASSWORD_VERIFY_FUNCTION (Password Complexity Verification)

Eables to specify what PL/SQL function should be used to verify the complexity of the password. Complexity enforces how the password needs to be constructed, the inclusion of special characters, digits and password lengths. The default password function is provided by $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. A value of NULL does not use any function to determine the complexity. To alter the password complexity verify function: alter profile <profile_name> limit password_verify_function <routine_name>; To disable password complexity verify function: alter profile <profile_name> limit password_verify_function null; The password complexity function can be a custom made function. In such case, the following prototype must be followed : CREATE OR REPLACE FUNCTION <verify_function_name> (username varchar2, password varchar2, old_password varchar2) RETURN boolean

PRIVATE_SGA

Specifies the amount of space a user can take in shared pool.

SESSIONS_PER_USER

Specifies the number of concurrent sessions a particular user can connect to database. For example, if SESSION_PER_USER is set to 3, a user cannot more than 3 different connections.

How to create profile

create profile <profile name>
limit
<resource parameter 1> <value 1>
<resource parameter 2> <value 2>
<resource parameter n> <value n>
...
<password parameter 1> <value 1>
<password parameter 2> <value 2>
<password parameter n> <value n>;

Example

create profile sample_profile
limit
sessions_per_user  5
private_sga        500K;

How to assign profiles to an existing user

alter user <username> profile <profile name>;

How to assign profiles to new user

create user <username> identified by <password> profile <profile name>;

How to check the profiles of users

select USERNAME, PROFILE from dba_users;
Comments