Saturday, August 12, 2006

Profiles

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:

    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.

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;


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.


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google