Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Managing Resources with Profiles

Oracle Tips by Burleson
 

You can establish limits on the system resources consumed by setting up profiles with defined limits on resources. Profiles are very useful in large, complex organizations with many users. It allows you, the DBA, to regulate the amount of resources consumed by each database user by creating and assigning profiles to users. Under Oracle8, password attributes were added to the profile capability.

Creation of Profiles

Profiles comprise a named set of resource limits. By default, when you create users, they are given the default profile, which provides unlimited use of all resources.

The syntax to create a profile follows:

 CREATE PROFILE profile LIMIT resource_parameters|password_parameters;
Resource_parameters: 
 [SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
 [CPU_PER_SESSION n|UNLIMITED|DEFAULT]    
 [CPU_PER_CALL n|UNLIMITED|DEFAULT]           
 [CONNECT_TIME              n|UNLIMITED|DEFAULT]
 [IDLE_TIME                 n|UNLIMITED|DEFAULT]
 [LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT] 
 [LOGICAL_READS_PER_CALL    n|UNLIMITED|DEFAULT]
 [COMPOSITE_LIMIT           n|UNLIMITED|DEFAULT]
 [PRIVATE_SGA               n [K|M]|UNLIMITED|DEFAULT]

Password_parameters (Oracle8 and above):

 [FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT]
 [PASSWORD_LIFE_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_MAX    expr|UNLIMITED|DEFAULT]
 [PASSWORD_LOCK_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_GRACE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]

Restrictions on password parameters are as follows:

* expr must resolve to either an integer value or an integer number of days.

* If 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.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of these password resources.

* If 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.

* If 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.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever value is defined in the DEFAULT profile.

For example:

CREATE PROFILE  enduser  LIMIT
CPU_PER_SESSION   60000
LOGICAL_READS_PER_SESSION   1000
CONNECT_TIME   30
PRIVATE_SGA   102400
CPU_PER_CALL   UNLIMITED
COMPOSITE LIMIT   60000000
FAILED_LOGIN_ATTEMPTS   3
PASSWORD_LIFE_TIME   90
PASSWORD_REUSE_TIME   180
PASSWORD_LOCK_TIME   3
PASSWORD_GRACE_TIME   3
PASSWORD_VERIFY_FUNCTION ;

You can assign a profile to a user when you create the user or by altering the user. The syntax to alter the profile for a user is:

ALTER USER PROFILE profile; 

       For example:

ALTER USER scott 
PROFILE appuser;

You must have the CREATE PROFILE system privilege to create a profile. To alter a profile you must be the creator of the profile or have the ALTER PROFILE system privilege. To assign a profile to a user, you must have the CREATE USER or ALTER USER system privilege.

Profiles and Resource Limits

The default cost assigned to a resource is unlimited, but you can also assign a composite cost to each profile.. By setting resource limits, you can prevent users from performing operations that will tie up the system, and prevent other users from performing operations. You can use resource limits for security, to ensure that users log off the system, so as not to leave the session connected for long periods of time.

The system resource limits can be enforced at the session level, the call level, or both. The session level is calculated from the time the user logs in to the database until the user exits. The call level applies to each SQL command issued. Session-level limits are enforced for each connection. When a session level limit is exceeded, only the last SQL command issued is rolled back; no further work can be performed until a commit, rollback, or exit is performed. Table 9.3 lists the system resources that can be regulated at the session level.

Note: If you use parallel query option (PQO), the resources will be applied to each new session, not accumulated over all of the sessions used by a parallel operation.

Table 9.3 Resources Regulated at the Session Level

SYSTEM RESOURCE

DEFINITION

CPU_PER_SESSION

Total CPU time in hundreds of seconds

SESSIONS_PER_USER

Number of concurrent sessions for a user

CONNECT_TIME

Allowed connection time in minutes

IDLE_TIME

Inactive time on the server in minutes

LOGICAL_READS_PER_SESSION

Number of data blocks read, including both physical and logical reads from memory and disk

PRIVATE_SGA

Bytes of SGA used in a database with the multithreaded server (in KB or MB)

Note: You can combine the CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a COMPOSITE LIMIT.

Call-level limits are enforced during the execution of each SQL statement. When a call-level limit is exceeded, the last SQL command issued is rolled back. All the previous statements issued continue to be valid, and the user can continue to execute other SQL statements. The following system resources can be regulated at the call level:

* CPU_PER_CALL, for the CPU time for the SQL statement

* LOGICAL_READS_PER_CALL ,for the number of data blocks read for the SQL statement

The assignment of a cost to a resource can be performed with the ALTER RESOURCE COST command. Resource limits that you set explicitly for a user take precedence over the resource costs in an assigned profile. The command-line syntax for this command is:

ALTER RESOURCE COST
[CPU_PER_SESSION n|UNLIMITED|DEFAULT]
[CONNECT_TIME n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]
[PRIVATE_SGA  n [K|M]|UNLIMITED|DEFAULT];

For example:

ALTER RESOURCE COST CONNECT_TIME 100;

The use of resource limits is set in the database initialization parameter RESOURCE_LIMIT=TRUE. By default, this parameter is set to FALSE. This parameter can be changed interactively with an ALTER SYSTEM command.

The DBA_PROFILES view provides information on all the profiles and the resource limits for each profile. The RESOURCE_COST view shows the unit cost associated with each resource. Each user can find information on his or her resources and limits in the USER_RESOURCE_LIMITS view.

Altering Profiles

Provided you have the CREATE PROFILE or ALTER PROFILE system privilege, you can alter any profile, including the Oracle-created DEFAULT profile. You can alter a profile to change the cost assigned to each resource. The syntax to alter a profile follows:

ALTER PROFILE profile LIMIT resource_parameters|password_parameters;

Resource_parameters: 

 [SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
 [CPU_PER_SESSION   n|UNLIMITED|DEFAULT]    
 [CPU_PER_CALL      n|UNLIMITED|DEFAULT]           
 [CONNECT_TIME      n|UNLIMITED|DEFAULT]
 [IDLE_TIME         n|UNLIMITED|DEFAULT]
 [LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT] 
 [LOGICAL_READS_PER_CALL    n|UNLIMITED|DEFAULT]
 [COMPOSITE_LIMIT           n|UNLIMITED|DEFAULT]
 [PRIVATE_SGA               n [K|M|UNLIMITED|DEFAULT]]

Password_parameters (Oracle8 and above):

 [FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT]
 [PASSWORD_LIFE_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_MAX    expr|UNLIMITED|DEFAULT]
 [PASSWORD_LOCK_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_GRACE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]

For example:

ALTER PROFILE enduser LIMIT
CPU_PER_SESSION 60000
LOGICAL_READS_PER_SESSION 1000
CONNECT_TIME 60
PRIVATE_SGA 102400
CPU_PER_CALL UNLIMITED
COMPOSITE LIMIT 60000000;

To disable a profile during a session, you must have the ALTER SYSTEM privilege. A limit that you set for the session overrides the previous limit set by the profile. To reset the profile to the limit originally set by the database, set the limit to DEFAULT.

ALTER SYSTEM SET RESOURCE_LIMIT =  TRUE|FALSE;

For example:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE ;

Profiles and Passwords

The capability to control password expiry and password complexity and validity was added to Oracle8. The capability to control passwords is contained within the purview of the profile. Table 9.4 lists the password control attributes in a profile and their definitions.

Table 9.4 Password Control Attributes in a Profile

Attribute

Description

FAILED_LOGIN_ATTEMPTS

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

 PASSWORD_LIFE_TIME

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

 PASSWORD_REUSE_TIME

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

 PASSWORD_REUSE_MAX

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

 PASSWORD_LOCK_TIME

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

PASSWORD_GRACE_TIME

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

PASSWORD_VERIFY_FUNCTION

Allows a PL/SQL password complexity verification script to 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 is the name of the password complexity verification routine. NULL indicates that no password verification is performed. 

Oracle also provides a template PL/SQL procedure for use in creating your own password complexity and verification function. The sample PL/SQL procedure is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql on UNIX and in x:\orant\ rdbms\admin\utlpwdmg.sql on NT. Any script you use must follow the general template shown in Listing 9.1.

LISTING 9.1 Required function input and return variables.

CREATE OR REPLACE FUNCTION <function_name>
(username varchar2,
 password varchar2,
 old_password varchar2)
  RETURN boolean IS
BEGIN

RETURN <boolean value>;
END;

Other than the required input and return variables, the password verification function can be as simple or as complex as you desire.

 

See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter