|
 |
|
Oracle Concepts -
Alerting Profiles
Oracle Tips by Burleson Consulting |
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
In Oracle8 the ability to control password
expiry and password complexity and validity was added to Oracle. The
capability to control passwords is contained within the purview of the
profile. Table 7.17 lists the password control attributes in a profile
and their definitions.
Table 7.17: 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 example PL/SQL procedure is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
on UNIX and in x:\orant\rdbms\admin\utlpwdmg.sql on NT. Other than the
required input and return variables the password verification function
can be as simple, or as complex, as you desire.
Managing CPU Utilization for in Oracle8i
In Oracle diverse groups of users may look at
the database to find information important to their group. While we
like to believe everyone is equal, face it, if the CEO wants a report
his needs come over and above Joe Clerk's needs for a different
report. In earlier releases of Oracle you could use profiles to
restrict specific types of resources but this was unwieldy and
produced unpredictable results. New in Oracle8i is the concept
of Oracle resource groups. A resource group specification allows you
to specify that a specific group of database users can only use a
certain percentage of the CPU resources on the system. A resource plan
must be developed that defines the various levels within the
application and their percentage of CPU resources in a waterfall type
structure where each subsequent levels percentages are based on the
previous levels.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher
here.
|
