my notes...

PROFILES - Controlling resource use:

Pre-requisites : For Oracle to enforce resource limit set in profiles set the following initialization parameter in init.ora : RESOURCE_LIMIT = TRUE

or change the same dynamically :

ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE;

There are three aspects of resource usage & limitations to consider in setting up profiles:

• Individual Resource Limit : session level

• session_per_user

• cpu_per_session

• logical_reads_per_session

• idle_time

• connect_time

• private_sga

Consequences of call-level limit exceeding: If a user exceeds the session level resource limit, the user gets and error & the session is terminated automatically.

• Individual Resource Limit : call level

  • logical_reads_per_call

  • cpu_per_call

    Consequences of call-level limit exceeding: If a user exceeds the call-level limits assigned to him/her, the SQL statement that produced the error is terminated, any transaction changes made only by the offending statement is rolled back, previous statement remain intact, and the user remains connected to Oracle.

• Composite limits & Resource cost: in come cases you may need more flexibility than setting individual limits. In such case a composite limit may be specified. Resource cost is an arbitrary number that reflects the relative value of that resource based on the host machine capabilites. It only applies to cpu_per_session, logical_reads_per_session, connect_time & private_sga resources.

  Setting resource cost:

  ALTER RESOURCE COST
  CPU_PER_SESSION 10
  LOGICAL_READS_PER_SESSION 2
  PRIVATE_SGA 6
  CONNECT_TIME 1;

Assigning composite limit : Once the resource cost is set, the composite limits can assigned to the users. Composite limits restricts database use by specifying a limit on how much a host machine resource can be used per session. Each time a session uses a resource, Oracle tallies the total resource use for that session. When the session hits the composite limit, the session is terminated.

ALTER PROFILE app_developr
COMPOSITE_LIMIT 500;

Note : Oracle 8i also introduced the concept of resource consumer groups. It uses built in PL/SQL procedures & functions to control the use of host machine resources by users.

Administering profiles:

CREATE PROFILE app_developr LIMIT

SESSION_PER_USER1 CPU_PER_SESSION 10000 CPU_PER_CALL 20 CONNECT_TIME 240 IDLE_TIME 20 LOGICAL_READS_PER_SESSION 50000 LOGICAL_READS_PER_CALL 400 PRIVATE_SGA 1024;

• Any resources not explicitly assigned limits while creating the profile will be assigned default value for that limit from the DEFAULT profile. Thus changes in the DEFAULT profile can be use to make changes to other profiles in the system.

• Note : In DEFAULT profile, all resource_limits are set to unlimited by default.

SESSION_PER_USER	Maximum no. of concurrent session opened by a user.
CPU_PER_SESSION	Maximum allowed CPU time in 1/100th second per session.
CPU_PER_CALL	Maximum allowed CPU time in 1/100th second per individual operation.
CONNECT_TIME	Total number of minutes a user can be connected to database.
IDLE_TIME	Idle time in minutes before the session is timed out
LOGICAL_READS_PER_SESSION	Maximum number of disk I/O block reads per session in support of user's processing.
LOGICAL_READS_PER_CALL	Maximum number of disk I/O block reads per session in support of user's processing.
PRIVATE_SGA	Used only when MTS is used. The amount of private memory in KB or MB that can be allocated to a user for private storage.

Assigning profiles to users :

CREATE USER usr2
IDENTIFIED BY usr2_pwd
DEFAULT TABLESPACE usrtab
TEMPORARY TABLESPACE tmp1
QUOTA 10M ON usr1
PROFILE app_developr;

ALTER USER usr2
PROFILE app_developr;

Altering & dropping profiles:

ALTER PROFILE app_developr
CPU_PER_SESSION UNLIMITED;

Note: Any option of the profile can be changed anytime. However the effect of the same will be enforced only after the user logs out & logs back in.

DROP PROFILE app_developr CASCADE;

Note: Use CASCADE if the profile is assigned to users. In that case the profile will be deleted & the users assigned back to the default profile. The DEFAULT profile can never be dropped.

Note: Use audit session command to get users resource limit usage information. It includes information on connect_time, logical_reads_per_session & logical_reads_per_call.

Creating profiles with Composite Limit Set: Refer to Profiles -> Composite limits & Resource cost section above.

PROFILES – Password Administration :

Though not required, the DBA can run rdbms/admin/utlpwdmg.sql script as SYS to support the functionality of password management. It defines a password verification function and assigns default password management settings to the DEFAULT profile. This script is reproduced at : utlpwdmg.sql

Password-management Resource limits in DEFAULT Profile:

FAILED_LOGIN_ATTEMPTS	No. of unsuccessful login attempts before the a/c gets locked
PASSWORD_LIFE_TIME	Number of days a password remain active
PASSWORD_REUSE_TIME	Number of days before a password can be reused
PASSWORD_REUSE_MAX	Number of times a password must be changed before the old password can be reused
PASSWORD_LOCK_TIME	Number of days the a/c remain locked after the user exceeds failed_login_attempts
PASSWORD_GRACE_TIME	Number of days within which the expired password must be changed or else Oracle locks the a/c permanently.
PASSWORD_VERIFY_FUNCTION	The function for password complexity verification.

ALTER PROFILE default LIMIT

FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME unlimited PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 30 PASSWORD_REUSE_MAX 0 PASSWORD_VERIFY_FUNCTION my_password_verify;

A simple password complexity function :

CREATE OR REPLACE FUNCTION my_password_verify (

x_user IN VARCHAR2 x_new_pwd IN VARCHAR2 x_old_pwd IN VARCHAR2

) RETURN BOOLEAN IS

BEGIN

IF LENGTH(x_new_pwd)<6> RAISE APPLICATION_ERROR(-20001,'New password must be 6 character or more long.'); ELSEIF x_new_pwd=x_user THEN RAISE APPLICATION_ERROR(-20002,'New password can't be same as user name.'); ELSEIF x_new_pwd=x_old_pwd RAISE APPLICATION_ERROR(-20003,'New password can't be same as old password.'); ELSE RETURN(TRUE); END IF

END;

Defining the function in the default profile:

ALTER PROFILE default

PASSWORD_VERIFY_FUNCTION= my_password_verify;

Views :

DBA_PROFILES	Specific information about resource usage parameters specified with each profile.
RESOURCE_COST	Identifies each resources in the database and their corresponding cost as defined by the DBA. Cost determine a resource's relative importance of use.
USER_RESOURCE_LIMITS	System resource limits for individual users as determined by the profiles assigned to them.
DBA_USERS	Information about the profiles assigned to the users, current account status, lock date, password expiry date.