'; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 11 - Auditing Database Use and Controlling Resources and Passwords

Using Oracle8

Chapter 11 Auditing Database Use and Controlling Resources and Passwords

Why Audit?
Profiles and System Resources
Combined Resource Limits
Profiles and Password Management
- Creating Password-Management Profile Entries
- Checking for Password Complexity

Control what's audited
Interpret audit results
Control consumption of system resources
Enforce password management

Why Audit?

For an Oracle DBA, auditing is the process of recording what's being done in the database. Audit records can tell you what system privileges are being used and how often, how many users are logging during various periods, how long the average session lasts, what commands are being used against specific tables, and many other related facts. However, you shouldn't treat auditing as an idle gatherer of data-you simply add unnecessary overhead by doing that. You should use auditing when it's the easiest, fastest, and least intrusive means of collecting information that you need to perform your job.

Why can't you audit SYS?

Oracle won't let you audit the SYS userid to keep the database from reaching some type of resource limit that needs the SYS userid to correct. If one or more of the steps required to fix the problem is audited and the problem being addressed is interfering with the system's capability to create audit records successfully, SYS can't complete the necessary corrective steps.

The types of activities you can perform as a DBA where auditing could help include the following:

Preparing database usage reports for your management (how many users connect per day/week, how many queries are issued per month, or how many employee records were added or dropped last week)
Recording failed attempts to break into the database if you suspect hackers
Identifying the busiest tables that may need additional tuning
Investigating suspicious changes to critical tables
Projecting resource consumption by an anticipated increase in user load

Depending on your requirements, you should be able to target exactly what level of auditing you need to invoke. In some cases, you may need to begin by gathering general data before you can identify critical areas that need closer attention.

You may suspect, for example, that the fragmentation in your index tablespace is the result of excessive index creation and deletion over the weekends. You don't know with which tables the indexes are associated because they don't stay in place long enough to find them. You can audit the general activity of index creation in order to identify the specific table or tables involved. You can then audit the indexing activity of the users who have permission to add and drop indexes on these tables. From these detailed audit records, you may be able to identify which user is causing the problem, or at least the specific times the activity is occurring. You can then run a program or log in yourself during these periods to capture data dictionary information about the indexes while they're still in place.

Targeting the problem

If problems can be resolved by carefully honing in on the most specific details from the more general ones, you need to audit only for the exact level of detail you need at each step. This reduces the amount of extraneous data that the audit routines will capture and that you have to filter out to get to what you really need.

Preparing the Audit Trail

When you decide you need to audit, you must configure your database to handle the task. This requires a special entry in your parameter file. The audit task can be activated-and similarly deactivated-only when the database instance is stopped and restarted.

Protect your audit trail

If you're auditing for any reason- particularly if you're suspicious of hackers-you should make sure that you protect your audit trail from potential tampering. If you're using an external file, make sure that it's in a protected directory. If you're using the internal AUD$ table, don't give privileges on the table to any user unnecessarily. Just to make sure that you have a record of any attempts to tamper, audit any activity, DDL or DML, against the audit trail by using the commands you learn in the upcoming sections.

The parameter you need to modify is AUDIT_TRAIL. By default, it has a value of NONE, which disallows any auditing regardless of any other auditing options that may have been set. You have two additional choices of setting for AUDIT_TRAIL-DB or OS-although the values TRUE (corresponding to DB), and FALSE (corresponding to NONE), are also supported for backward compatibility. With AUDIT_TRAIL set to DB, the audit records are written to a database table named AUD$, which is owned by SYS. If set to OS, the audit records are written to an external operating system file. You can control the location of the audit file by setting an additional parameter, AUDIT_FILE_DEST, to identify the full path name of the directory where you want it to be written.

By default, the AUD$ table is built in the SYSTEM tablespace. This can cause problems if you do extensive auditing because it's possible to fill up this tablespace very quickly. The number of audit records created depends on just how many different actions you audit and what level of detail you require. You should be prepared to monitor your audit table or file on regular basis, even if you collect only minimal data, because other users may turn on additional auditing (by design or by accident), which can generate far more data than you were anticipating.

Maintaining the Audit Table

Although you're never supposed to modify the contents or the definition of the tables in the data dictionary (meaning any objects owned by SYS in the SYSTEM tablespace), this restriction is removed from the AUD$ table. You can change its storage parameters for it to grow larger than set by default, or to make better use of the free blocks in its tablespace. You also may need to remove old or other unwanted records to reclaim space. You can even copy records you do want to analyze to some other location (including an external file) and then drop them from the AUD$ table. In fact, doing this on a regularly scheduled basis prevents the table from growing uncontrollably.

Controlling the capability to perform auditing

There are two levels of auditing: on statements and on objects. You can control the auditing of statements by granting only the AUDIT SYSTEM privilege to users whom you want to be able to perform this type of auditing. Object auditing is a little more difficult to control because an object's owner can request that auditing be performed against the use of that object. However, you should be able to prevent any unwanted auditing if you allow objects to be created only by a restricted set of users and protect the schemas where those objects are created by removing connection privileges to them. If the objects do need to be audited, you can grant the AUDIT ANY system privilege to the user(s) responsible for this task and remove it again when the required auditing is established.

You can store records from the AUD$ table a number of ways:

Issue the SQL statement CREATE TABLE audit_day1 AS SELECT * FROM sys.aud$. This will create a table called AUDIT_DAY1 and copy all the records from AUD$ into it.
Run the Export utility as user SYS and select the table AUD$ to export.
Use the SQL*Plus SPOOL command and execute a query against the sys.aud$ table or against an audit view. (See "Reviewing Audit Views" later in this chapter.)

After you save the records you need from AUD$, you can remove the records you no longer need via different commands, depending on what you're trying to accomplish. Use the following command to remove all records from the table:

DELETE FROM sys.aud$;

Execute the following command to remove all records and return the table to its initial extent size:

TRUNCATE TABLE sys.aud$;

Use the following to remove all records associated with the user Kim:

DELETE FROM sys.aud$ WHERE userid = 'KIM';

You can issue the following command to remove all records except those associated with the EMPLOYEES table:

DELETE FROM sys.aud$ WHERE obj$name <> 'EMPLOYEES';
SEE ALSO
Discover the specifics of starting and stopping a database,
An introduction to the parameter file,

Controlling System Auditing

You can audit the use of system privileges or the use of SQL statements used to either administer the database or to manage database objects with a pair of commands. You can also audit the use of DML and DCL statements on specific objects by using a slightly different version of these commands (described in the next section). In both cases, the two commands are AUDIT and NOAUDIT; the former begins an audit and the latter terminates it. Whether you're auditing at the database or object level, the AUDIT command won't generate any audit records unless the instance is running with auditing enabled, as described in the previous section.

The AUDIT command, which begins auditing on system privileges or database administrative commands, has the following syntax:

AUDIT
    [system_privilege] [,...]
    [statement_option] [,...]
    [shortcut_name] [,...]
    [BY [user_name [,...]]]
    [BY [SESSION] [ACCESS]]
    [WHENEVER [NOT] SUCCESSFUL]

AUDIT is the keyword to turn on auditing.
system_privilege [,...] is a system privilege or a comma-separated list of system privileges. You can audit any of the privileges listed in Table 10.1. You must include at least one system privilege if you don't list any statement options or shortcut names.

Default auditing of the SYS user

Although you can't audit the user SYS yourself, Oracle does track the key work done by SYS or using SYS's privileges. This auditing is written to a special file that varies by name and location depending on your operating system. Instance start-up, instance shutdown, and connection as administrator are among the key items audited.

SEE ALSO
Table 10.1 lists privileges on

statement_option [,...] is a statement option or a comma-separated list of statement options from Table 11.1. You must include at least one statement option if you don't list any system privileges or shortcut names.
shortcut_name [,...] is a shortcut name or a comma-separated list of shortcut names from Table 11.2. You must include at least one shortcut name if you don't list any system privileges or statement options.
BY user_name [,...] identifies a user, or a list of users, on whom the auditing of the chosen actions will be performed. All users will be audited if you omit this option.
BY SESSION/ACCESS determines whether all use of an audited action is summarized in a single audit record per user session (BY SESSION), or if a separate audit record will be generated each time the action is performed (BY ACCESS). Audited actions will be summarized at the session level if you omit this option.
WHENEVER [NOT] SUCCESSFUL determines whether only successful (SUCCESSFUL) or unsuccessful (NOT SUCCESSFUL) attempts to use the audited action are recorded. If you omit the WHENEVER option, all actions-successful and unsuccessful-are audited.

Default auditing for DDL statements

Even if you audit at the session level (by default or with the BY SESSION option), you can still generate access-level audit records. This is because all Data Definition Language (DDL) statements, audited as a result of selected system privileges or statement options, will be audited by access.

Table 11.1 Statement options for the AUDIT command

Statement Option:		Audited SQL Statements and Operations:
ALTER SEQUENCE^/1/		ALTER SEQUENCE
ALTER TABLE^/1/		ALTER TABLE
CLUSTER		CREATE CLUSTER, AUDIT CLUSTER, DROP CLUSTER, TRUNCATE CLUSTER
COMMENT TABLE^/1/		COMMENT ON TABLE for tables, views, or snapshots COMMENT ON COLUMN for table columns, view columns, or snapshot columns
DATABASE LINK		CREATE DATABASE LINK, DROP DATABASE LINK
DELETE TABLE^/1/		DELETE FROM tables or views
DIRECTORY		CREATE DIRECTORY, DROP DIRECTORY
EXECUTE PROCEDURE^/1/		Execution of any procedure or function, or access to any variable, library, or cursor inside a package
GRANT DIRECTORY^/1/		GRANT privilege ON directory REVOKE privilege ON directory
GRANT PROCEDURE^/1/		GRANT privilege ON procedure, function, or package REVOKE privilege ON procedure, function, or package
GRANT SEQUENCE^/1/		GRANT privilege ON sequence REVOKE privilege ON sequence
GRANT TABLE^/1/		GRANT privilege ON table, view, or snapshot REVOKE privilege ON table, view, or snapshot
GRANT TYPE^/1/,^/2/		GRANT privilege ON TYPE REVOKE privilege ON TYPE
INDEX		CREATE INDEX, ALTER INDEX, DROP INDEX
INSERT TABLE^/1/		INSERT INTO table or view
LOCK TABLE^/1/		LOCK TABLE table or view
NOT EXISTS		Any SQL statement failures because referenced objects don't exist
PROCEDURE		CREATE FUNCTION, CREATE LIBRARY, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE
PROFILE		CREATE PROFILE, ALTER PROFILE, DROP PROFILE
PUBLIC DATABASE LINK		CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK
PUBLIC SYNONYM		CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM
ROLE		CREATE ROLE, ALTER ROLE, DROP ROLE, SET ROLE
ROLLBACK STATEMENT		CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT
SELECT SEQUENCE		Any statement containing a sequence.CURRVAL or sequence.NEXTVAL phrase, where sequence is the name of an Oracle sequence generator
SELECT TABLE^/1/		SELECT FROM table, view, or snapshot
SEQUENCE		CREATE SEQUENCE, DROP SEQUENCE
SESSION		All database logins
SYNONYM		CREATE SYNONYM, DROP SYNONYM
SYSTEM AUDIT^/3/		AUDIT, NOAUDIT
SYSTEM GRANT^/4/		GRANT, REVOKE
TABLE		CREATE TABLE, DROP TABLE, TRUNCATE TABLE
TABLESPACE		CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE
TRIGGER		CREATE TRIGGER ALTER TRIGGER with ENABLE and DISABLE options DROP TRIGGER ALTER TABLE with ENABLE ALL TRIGGERS and DISABLE ALL TRIGGERS clauses
TYPE^/2/		CREATE TYPE, CREATE TYPE BODY, ALTER TYPE, DROP TYPE, DROP TYPE BODY
UPDATE TABLE^/1/		UPDATE table or view
USER		CREATE USER, ALTER USER, DROP USER
VIEW		CREATE VIEW, DROP VIEW

^/1/ Not included in the ALL shortcut

^/2/ Available only with the Object option

^/3/ When used with system privileges or statement options

^/4/ When used with system privileges and roles

To save you from having to enter a series of related system privileges or statement options in an AUDIT statement, Oracle provides a series of shortcuts. Each of these, when referenced in an AUDIT statement, causes auditing to occur on the related items. Table 11.2 lists these shortcuts and the system privileges and statement options included when you use them in an audit command.

Not all AUDIT options can be named in a single statement

You can mix statement options and shortcuts in the same statement when issuing the AUDIT command, but you can't include most system privileges with statement options or shortcuts. Other than this restriction, you can include as many auditing choices as you want in a single statement. You can even include a shortcut and the statement options covered by the short-cut. Similarly, you can either include as many users as you want or allow the statement to default to all users.

Table 11.2 Shortcuts for system privileges and statement options

Shortcut Name:	System Privilege (P) or Statement Option (O):	Privileges and Options Included:
CONNECT	P	CREATE SESSION
RESOURCE	P P P P P P P P P P	ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE PROCEDURE CREATE ROLLBACK SEGMENT CREATE SEQUENCE CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE VIEW
DBA	P P P P P P O	AUDIT SYSTEM CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE USER SYSTEM GRANT
ALL	O	All statement options listed in Table 11.1, except those noted as not being part of the ALL shortcut
ALL PRIVILEGES	P	All system privileges

Use the NOAUDIT command to cease auditing of the actions you defined with the AUDIT command. The syntax is identical to the AUDIT command except that there's no BY SESSION or BY ACCESS option; the NOAUDIT option turns off whatever option is in effect.

The NOAUDIT command's syntax is as follows:

NOAUDIT
    [system_privilege] [,...]
    [statement_option] [,...]
    [shortcut_name] [,...]
    [BY [user_name [,...]]]
    [WHENEVER [NOT] SUCCESSFUL]

The various options are described in the syntax description for the AUDIT command.

You can use the NOAUDIT command to stop auditing successful or unsuccessful actions if your AUDIT command had enabled both options (the default) when you executed it. However, you can't alter the auditing behavior from successful to unsuccessful with this command; you have to disable the auditing and re-enable it with your preferred option by issuing a new AUDIT command. If, on the other hand, you were auditing only successful or only unsuccessful actions, the NOAUDIT command can turn off the auditing if you issue it with no WHENEVER option.

The NOAUDIT default doesn't necessarily stop all auditing

If your NOAUDIT command doesn't include a list of users, auditing enabled by an AUDIT command also issued without a user list will be terminated. However, any users being audited for the same action(s) for whom you turned on auditing (by naming them in an AUDIT command) won't be affected.

Turning off auditing with the NOAUDIT command won't affect any records already created as a result of the previous AUDIT command, but will prevent any further audit records from being created as a result of the audited actions.

Let's end this section by looking at some examples of how the AUDIT and NOAUDIT commands are used:

The following three commands, in turn, activate the auditing of all connections to the database, the auditing of any successful attempt by Kim to use the ALTER ANY TABLE system privilege, and any attempt by other users to use this privilege:

AUDIT create session;
    AUDIT alter any table BY kim WHENEVER SUCCESSFUL
    AUDIT alter any table;

To audit just unsuccessful connection attempts, you could now issue this command:

NOAUDIT create session WHENEVER SUCCESSFUL;

To audit just successful attempts by all other users (as well as Kim) to use the ALTER ANY TABLE privilege, you could issue this command:

NOAUDIT  alter any table WHENEVER NOT SUCCESSFUL;

To stop the auditing of successful uses of the ALTER ANY TABLE privilege by everyone, you would enter either one of these two statements:

NOAUDIT alter any table;
NOAUDIT alter any table WHENEVER SUCCESSFUL;

However, Kim's successful use of this privilege would still be audited due to the separate AUDIT command issued earlier. To discontinue this auditing, you also need these two commands:

NOAUDIT alter any table BY kim;
NOAUDIT alter any table BY kim WHENEVER SUCCESSFUL;

Controlling Object Auditing

The command that starts auditing activity against specific database objects has the following syntax:

AUDIT
object_option[,...]
    [ALL]
ON
    [[schema.]object_name]
    [DIRECTORY directory_name]
    [DEFAULT]
    [BY [SESSION] [ACCESS]]
    [WHENEVER [NOT] SUCCESSFUL]

Object auditing isn't user-specific

You can't select specific users when auditing on objects. All users performing an audited task on an object will cause audit records to be written, either a session record per connection or one record per executed statement.

AUDIT and ON are the required keywords for the object auditing command.
object_option[,...] is an option or a comma-separated list of options from Table 11.3. You must include at least one object option if you don't use the keyword ALL.
ALL includes all valid object auditing options. It must be included if you don't include any individual options.
schema names the object owner. Your own schema is assumed if it's not included.
object_name is the name of the object on which auditing is to be started, or a synonym for the object. You must include an object name if you don't include the DIRECTORY or the DEFAULT option.
DIRECTORY directory_name identifies the name of an object directory to be audited. You must identify a directory if you don't include an object name or the DEFAULT option.
DEFAULT indicates that you want the auditing options to be applied automatically to all new objects created in the schema. You must include the DEFAULT option if you don't include an object name or the DIRECTORY default.
BY SESSION/ACCESS determines whether all use of an audited action is summarized into a single audit record per user session (BY SESSION), or if a separate audit record will be generated each time the action is performed (BY ACCESS). If you omit this option, audited actions will be summarized at the session level.
WHENEVER [NOT] SUCCESSFUL determines whether only successful (SUCCESSFUL) or unsuccessful (NOT SUCCESSFUL) attempts to use the audited action are recorded. If you omit the WHENEVER option, all actions-successful and unsuccessful-are audited.

Different object auditing options are available, depending on the type of object you're auditing. Table 11.3 identifies which options can be selected for each object type. The optional keyword, ALL, will begin auditing every option that can be audited for the object type, as shown in Table 11.3.

Table 11.3 Object auditing options

Object Option:	Table:	View:	Sequence:	Procedure FunctionPackage:	Snapshot:	Library:	Directory:
ALTER	X		X		X
AUDIT	X	X	X	X	X		X
COMMENT	X	X			X
DELETE	X	X			X
EXECUTE				X		X
GRANT	X	X	X	X	X	X	X
INDEX	X				X
INSERT	X	X			X
LOCK	X	X			X
READ							X
RENAME	X	X		X	X
SELECT	X	X	X		X
UPDATE	X	X			X

Considerations for the DEFAULT audit option

Unlike the other options, DEFAULT applies to the whole database, not just to the schema of the user issuing the command. Therefore, following the successful completion of an AUDIT... DEFAULT command, any object created by any user will begin to be audited if the object audit list in the command included one or more operations valid for that object type. Using the ALL option in an AUDIT...DEFAULT command will cause every possible audit action to be applied to every new object created subsequent to the command being executed.

You can enable object auditing on only one object at a time with an AUDIT command. The object can be a schema object or an object directory in which BFILE objects are stored. Instead of either of these, you can also enable auditing by default via the DEFAULT option. Default auditing does nothing to any existing objects, but will apply the selected audit options to any new object for which the option is valid.

Any auditing that commences as a result of default auditing can be stopped with the appropriate NOAUDIT command. However, the command has to be issued against each individual object. The NOAUDIT...DEFAULT command will prevent further objects from being audited by default, but won't terminate any auditing already being performed.

Special privileges for the AUDIT...DEFAULT option

Due to its far-reaching capabilities, you can't successfully issue the AUDIT...DEFAULT command unless you've been granted the AUDIT SYSTEM privilege. The AUDIT ANY system privilege isn't adequate.

There's a version of the NOAUDIT command for terminating object auditing. Its syntax follows that of the AUDIT command, except that it doesn't include the BY ACCESS/BY SESSION option. Just as with the system privilege and statement option NOAUDIT command, you can stop the auditing of successful or unsuccessful actions on the object actions with the appropriate WHENEVER clause option, or end auditing altogether on the chosen action(s) by completely omitting the WHENEVER clause.

Reviewing Audit Records

The data dictionary contains two types of views related to auditing. The first type identifies which items are being audited; the second type, built on the AUD$ table, shows the audit records from various perspectives. Before you turn auditing on for your database with the AUDIT_TRAIL parameter, or anytime auditing is active, I recommend that you monitor the former type of view (listed in Table 11.4). This will help you identify any auditing that you don't think should occur, thus avoiding unexpected and unnecessary accumulation of audit records.

Scripts to manage audit-related data dictionary views

Oracle provides two SQL scripts to manage the data dictionary views associated with auditing: CATAUDIT.SQL and CATNOAUD.SQL. CATAUDIT.SQL creates the views and is run as part of CATALOG.SQL, which should be run following database creation. CATNOAUD.SQL drops the audit-related views should you not require them. This script won't drop the base auditing table, AUD$, owned by SYS. If you drop the views, you can recreate them later by running CATAUDIT.SQL again. Remember to run CATAUDIT.SQL and CATNOAUD.SQL while logged in as SYS (either directly or through the INTERNAL login).

Table 11.4 Data dictionary views showing current audit options

View Name:	Description:
ALL_DEF_AUDIT_OPTS	Lists the object-auditing options that will be activated by default on all new objects for which they're valid auditing events. This view contains a single-row, one-entry-per-object option in the form of -/-, where either hyphen (-) can be substituted with an S or an A. The first hyphen, S, or A indicates whether the auditing will be performed on successful use of the command, and the second indicates that auditing will be performed on unsuccessful use. A hyphen means this option won't be audited, an S means that the auditing will be by session, and an A means that it will be by access.
AUDIT_ACTIONS	Lists the values from the ACTION# column from the AUD$ table and the related audit action type.
DBA_OBJ_AUDIT_OPTS	Lists the object-auditing options on all objects. This view contains a single entry for each database object, whether it's being audited or not. The rows are structured like the one in the ALL_DEF_AUDIT_OPTS view, with a two-part value under each option (each part containing a hyphen, an S, or an A).
DBA_PRIV_AUDIT_OPTS	Describes the system privileges being audited. There's one entry if the privilege is being audited systemwide, and a separate entry for each individual user being audited.
DBA_STMT_AUDIT_OPTS	Describes the statement options being audited. There's one entry if the privilege is being audited systemwide, and a separate entry for each individual user being audited.
USR_OBJ_AUDIT_OPTIONS	Lists the object auditing options on all objects owned by the user. This view contains a single entry for each object owned by the user, whether it's being audited or not. The rows are structured like those in the DBA_OBJ_AUDIT_OPTIONS view.

The second type of audit-related views provides formatted access to the audit trail. These views are generally more useful for examining the audit results than querying the AUD$ table directly. Of course, you can't use these views, or any SQL-based retrieval method, to examine audit records that you store in an external audit trail file. You must develop your own techniques to report on audit records created when you set the AUDIT_TRAIL parameter to the value OS.

Table 11.5 describes the views built over the entries in the audit trail generated as the result of a selected audit action. The table shows the view name and the nature of auditing that creates the audit records displayed through the view.

Table 11.5 Data dictionary views for querying audit records

View Name:	Audit Action(s) Reported:
DBA_AUDIT_EXISTS	All statements resulting in NOT EXISTS errors
DBA_AUDIT_OBJECT	Any object audit option
DBA_AUDIT_SESSION	All connection attempts
DBA_AUDIT_STATEMENT	All uses of GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands
DBA_AUDIT_TRAIL	All audit trail entries
USER_AUDIT_OBJECT	All statements concerning objects
USER_AUDIT_SESSION	All connections and disconnections for the user
USER_AUDIT_STATEMENT	All uses of GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands by the user
USER_AUDIT_TRAIL	All audit trail entries relevant to the user

Values for audited actions in the AUD$ RETURNCODE column

For the views that show whether the audited action was successful, the RETURNCODE column will contain an Oracle message number: a 0 for success or an exception code from the error message if unsuccessful.

SEE ALSO
Descriptions of the SQL scripts used following database creation to complete the data dictionary table and view definitions,

Profiles and System Resources

To help you control the use of system resources by your database users, Oracle provides you with a special type of database object known as a profile. Every database is built with one profile, called DEFAULT, but you can add as many others as you need. Within each profile, you set limits on various system resources that can be consumed. When more of a resource has been used than the limit allows, Oracle will stop further processing and issue an appropriate error message. Every profile can define different limits for some or all controlled resources. You can choose to leave certain resources with no limitations on their use in any of your profiles.

Each database user is assigned to one profile, and the user's database sessions can be constrained by the limits set in that profile. Profile resource limits won't be enforced, however, unless the database is running in a special mode. You can set this mode with a parameter in your initialization file or with an ALTER SYSTEM command. The parameter, RESOURCE_LIMIT, takes the values TRUE and FALSE; you should set it to TRUE if you want to enforce the resource limits assigned to your profiles. You should issue the following command if RESOURCE_LIMIT is set to FALSE but you want to start enforcing resource limits:

Scope of the ALTER SYSTEM command

The ALTER SYSTEM command won't change the value of the related parameter in your initialization file. If you enable or disable resource checking with this command, therefore, you should also modify your RESOURCE_LIMIT parameter to the desired value. This will ensure that the database will continue to behave as you want it to should the instance need to be restarted for any reason.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

You can, of course, issue the command with the FALSE option to turn off resource checking, whether it was begun with an earlier ALTER SYSTEM command or due to the parameter value being set to TRUE. The ALTER SYSTEM SET RESOURCE_LIMIT command doesn't change the behavior of any active user sessions when the command is issued, so users can still encounter limits on their work even if the command turns off resource checking with the FALSE option.

If you don't need to restrict your users with resource limits, ensure that you run your database with the RESOURCE_LIMIT parameter set to FALSE. Even if your users are all assigned to a profile that allows them unlimited resource usage, Oracle must locate the related profile and store the limits in the user's session data every time a new session starts when your system is running with resource checking enabled. This overhead can be avoided, and if you discover the parameter is set to a wrong value you should temporarily change the database behavior until you can change the parameter and restart the instance. The following command will prevent resource checks from being initiated in any new sessions:

ALTER SYSTEM SET RESOURCE_LIMIT = FALSE;

Combined Resource Limits

You can limit the consumption of different system resources through individual settings in a profile or through a combined usage value. You can even include both limit types within a single profile. You should consider setting up a combined usage limit when your database is running on a system where users are being monitored for their consumption of resources with a standardized unit of measure. Typically, these units-known as "computer resource units," "computer billing units," or "computer service units"-are composed of various amounts of one or more of CPU consumption, memory usage, disk I/O, and connection time.

You can use what Oracle calls composite limits for your profiles. These can reflect the same ratio of resource usage as your system's service units. With them, you can terminate a user's session after it consumes more than a predetermined number of such service units.

Before you can take advantage of composite limits, you must define the components to be included and their respective weightings. You use the ALTER RESOURCE COST command to complete these definitions and you can query the data dictionary view RESOURCE_COST to see the current settings. The command allows you to set a weight for each of the following:

The amount of CPU time used by a session
The session's connect time
The number of Oracle blocks read during a session
The amount of memory dedicated to the session (private SGA)

The ALTER RESOURCE COST command's full syntax is as follows:

ALTER RESOURCE COST
    [CPU_PER_SESSION integer]
    [CONNECT_TIME integer]
    [LOGICAL_READS_PER_SESSION integer]
    [PRIVATE_SGA integer]

integer is the weight assigned to a unit of the resource's consumption, as shown in Table 11.6. You must include at least one resource in the command and as many of the other resources as you need.

Table 11.6 in the following section shows the optional keyword to use in the ALTER RESOURCE COST command for these options and the unit of measure for each one. If you don't include one of the keywords in the command, it will retain its current value (as shown in the RESOURCE_COSTS view). Each resource has an initial value of 0 when the database is created. A composite limit is reached when the sum of the resources, multiplied by their assigned weights, exceeds the value of the limit.

For example, assume you issued the following command and a session has accumulated 10 seconds of CPU time and accessed 2,000 Oracle blocks while being connected for 30 minutes:

ALTER RESOURCE COST
    CPU_PER_SESSION 50
    CONNECT_TIME 10
    LOGICAL_READS_PER_SESSION 100
    PRIVATE_SGA 0;

It will have consumed the number of service units (shown here) toward a composite limit:

50 * 1000 (1/100's seconds CPU time)
  + 10 * 30 (minutes connect time)
  + 100 * 2000 (Oracle blocks accessed)

Calculating composite service units

Using this example, CPU has a weight of 50 and a unit of measure of 1/100 second, so 10 seconds contribute 50×10/(1/100) = 50,000 CPU per session. Connect time has a weight of 10 and a unit of measure of minutes, so 30 minutes contribute 10×30 = 300. Logical reads have a weight of 100 each, so 2,000 blocks accessed contribute 100×2,000 = 200,000. Private SGA has a weight of 0 per byte, so all bytes contribute nothing more to the overall total. The sum of these factors gives the resulting total composite service units: 50,000+300+200,000+0= 250,300.

Creating Profiles

You create a new profile by using the CREATE PROFILE command. This command uses different keywords to set limits for the different resources. Table 11.6 identifies these keywords and lists which resources, plus their units of measure, they control. The table also indicates which of these resources contributes to composite limits, as discussed in the preceding section.

Table 11.6 Resource limits controlled by profiles

Part of Composite System Resource:	Keyword for CREATE PROFILE and ALTER RESOURCE COST Commands:	Unit of Measure:	Part of Composite Limit:
Concurrent sessions	SESSIONS_PER_USER	Number
Session CPU	CPU_PER_SESSION	1/100 second	X
CPU per call	CPU_PER_CALL	1/100 second
Session elapsed time	CONNECT_TIME	Minutes	X
Inactive session	IDLE_TIME	Minutes
Oracle blocks accessed in session	LOGICAL_READS_PER_SESSION	Number	X
Oracle blocks accessed per call	LOGICAL_READS_PER_CALL	Number
Session memory	PRIVATE_SGA	Bytes	X
Service units	COMPOSITE_LIMIT	Number

For those resources that can be limited by session and by call, the session value includes all work done since connecting to the database, and the call value includes only the work done during a single database call, such as a parse, execute, or fetch.

SEE ALSO
Information about the parse, execute, and fetch database calls,

The syntax for the resource management options of the CREATE PROFILE command is as follows:

CREATE PROFILE profile_name LIMIT
    resource_key_word [integer][K|M] [UNLIMITED] [DEFAULT]
[...]

CREATE PROFILE profile_name LIMIT are the keywords to create the profile with the given name.
resource_key_word is a valid keyword from Table 11.6.
integer is the value of the limit assigned to the resource; this must be included if neither the UNLIMITED nor DEFAULT options are included for the resource.
K and M are optional abbreviations for kilobytes and megabytes, respectively, that can be used only with the PRIVATE_SGA keyword.
UNLIMITED removes any limitation on the resource from the profile. This must be included if neither a resource limit value nor the DEFAULT option is included for the resource.
DEFAULT indicates that this resource will be limited by the current value in the DEFAULT profile at the time the profile is invoked. This must be included if neither a resource limit value nor the UNLIMITED option is included for the resource.
[...] indicates that two or more resources can be controlled in a single statement.

Privileges to manage profiles

Profiles aren't members of any schema, so any user with the necessary privileges can issue commands against profiles created by any other user. The system privileges to create profiles, to alter resource limits in existing profiles, and to drop profiles are CREATE PROFILE, ALTER PROFILE, and DROP PROFILE, respectively.

You don't have to include a limit for every resource in a profile when you create it. Any unnamed resource will be treated as though it were assigned the DEFAULT keyword. In other words, any time the profile is used, the values for unassigned resource limits will be taken from the corresponding values in the DEFAULT profile.

Assigning Profiles

To enforce the limits in a profile you've created, you need to assign that profile to one or more users. You can assign a profile by using the PROFILE option of either CREATE USER or ALTER USER. The RESOURCE_LIMIT must also be activated for the profile limits to take any effect, of course, although the profile can be assigned at any time.

A profile must always be part of a user definition

If you don't want a userid to continue to be assigned to one of your own profiles, you must issue the ALTER USER command to reassign it to the default profile, DEFAULT. A userid must always be associated with an existing profile.

When you change the profile assigned to a user with the ALTER USER command, the new profile will become effective the next time someone connects to the database with that userid. However, any currently active session will continue to function under the limits imposed by the profile assigned to the userid at the time they were initiated.

When profile limits are in effect and a user exceeds a limit, the outcome will depend on the specific resource type:

When a session exceeds the session limit on CPU time, connect time, blocks accessed, memory used, or on the composite limit, the transaction being processed at the time will be rolled back and the user will be disconnected from the database.
When a statement exceeds a call limit, the current statement is rolled back but the session and the current transaction remain active.
When a session remains inactive for longer than the idle time limit, any current transaction will be rolled back and the session terminated the next time a statement is attempted.
The database connection is refused when a new session exceeds the number of concurrent sessions allowed by the profile.

Inactive really means inactive to Oracle

For the purposes of the idle session timeout, a session is considered inactive only if no statement is being processed by the server. Unlike some operating system connection time-outs, a long-running statement or query isn't considered inactive, even if the application isn't receiving or displaying any results for more than the idle time limit.

SEE ALSO
The complete syntax of the CREATE USER command,
The complete syntax of the ALTER USER command,

Altering Profiles

You use the ALTER PROFILE command to change one, some, or all of the resource limits defined in a profile. Use exactly the same syntax as that for the CREATE PROFILE command, substituting only the word ALTER for the word CREATE. The only restriction on the ALTER PROFILE command is on the DEFAULT profile-you can't use the DEFAULT keyword when changing one of its resource limits. You can, however, change any of the resource limits in DEFAULT profile to any other valid value.

Any resource not named in an ALTER PROFILE command will retain the current limit's value. Include the resource name with the keyword UNLIMITED to remove a limit from a specific resource.

As when you assign a profile to a user, any current session settings aren't changed when you execute the ALTER PROFILE command. Only new sessions started by users assigned to the altered profile will be accorded its new resource limits.

Watch for unexpected side effects when changing the DEFAULT profile

The user SYS needs to retain the ability to use unlimited resources to complete essential database activities. These include the execution of recursive SQL statements and processing performed by the background processes. SYS uses the UNLIMITED resource limits (set by default in the DEFAULT profile) when resource limits are activated. Therefore, you shouldn't reduce any of these limits unless you've already built an alternate profile with unlimited resources and assigned it to SYS. You also might want to assign this alternate profile to the SYSTEM userid and your primary DBA user accounts.

Dropping Profiles

You can drop any profile except the DEFAULT profile by using the DROP PROFILE command:

DROP PROFILE profile_name [CASCADE]

The CASCADE option must be included if the profile is still assigned to one or more users. As with other changes to profiles, any sessions started with the profile assigned to them will continue to be limited by the profile limits even after it's dropped. The userids assigned to the dropped profile will be assigned to the DEFAULT profile automatically, and any new sessions started by these userids will be constrained by DEFAULT's limits.

Profiles and Password Management

Oracle8 allows you to control your database users' management of their passwords. The password-management features have many capabilities:

Pre-expire new passwords
Expire passwords after a specific period of time
Allow a grace period between password expiration and the deactivation of the userid
Prevent the reuse of a password for a specific period of time
Lock and unlock accounts manually
Force a password to meet certain complexity requirements

The same profiles that you utilize to limit system resource use can be used to provide you with password-management tools. The profiles already contain entries for password-management functions, but these aren't enabled unless you run a special script. This script, UTLPWDMG.SQL, can be found in the same location as the other scripts discussed in Chapter 2 "Creating a Database." To activate the password-management features, execute this script after connecting to Oracle with the SYS userid.

The script has two components: The first changes the password-related entries in the DEFAULT profile and the second builds a password-complexity function in the database. The script also causes the database to begin password-checking activities against all profiles.

Password management is independent of resource management

Although the password-management features are controlled by database profiles, they aren't enabled and disabled the same way the resource limits are controlled by profiles. Resource limit checks are activated by starting the database with the initialization parameter RESOURCE_LIMIT set, or by issuing ALTER SYSTEM to change RESOURCE_LIMIT to TRUE. Checking can be deactivated by changing the value to FALSE in the parameter file or with the ALTER SYSTEM command. Password management is activated by running the UTLPWDMG.SQL script and stays active from that point on. The RESOURCE_ LIMIT value has no impact on password-checking activities.

Creating Password-Management Profile Entries

You use the CREATE PROFILE and ALTER PROFILE commands to set the values in profiles that determine the password characteristics for the users who are assigned to them. The syntax for these commands, when addressing password options, is as follows:

Representing portions of days for password options

You can set values for password options that provide time limits measured in whole days, or in portions of a day. For example, two weeks would be represented by the number 14, whereas one hour could be represented as 1/24 or 0.0416667. The CREATE and ALTER PROFILE commands accept any of these formats for days, integers, fractions, and decimals. However, you can't mix whole numbers and fractions in a single expression. One and a half days would have to be entered as 1.5, not as 1fi. The smallest time increment you can use for a password option is one second, represented by 1/86400 or 0.000015741.

CREATE|ALTER PROFILE profile_name LIMIT
   password_keyword [expression][UNLIMITED][DEFAULT][NULL]
   [...]

CREATE or ALTER will build a new profile or modify an existing one, respectively.
PROFILE profile_name LIMIT are the keywords to identify the profile being processed by the command.
password_keyword is a valid password option keyword from Table 11.7.
expression contains a value valid for the password option, as indicated in Table 11.7. Expressions that represent the numbers of days can contain whole numbers, fractions, or decimal representations of days and partial days.
UNLIMITED removes any limitation on the password from the profile.
DEFAULT indicates that this password option will conform to the current setting in the DEFAULT profile at the time the profile is invoked.
NULL indicates that no password complexity checking is to be performed. This option is valid only with the PASSWORD_VERIFY_FUNCTION password option.
[...] indicates that two or more password options can be controlled in a single statement.

Only one value-expression, UNLIMITED, DEFAULT, or NULL-can be entered for any password option in a given statement. If either password option-PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX-is set to a numeric value, the other must be set to UNLIMITED.

Mixing resource limits and pass-word options

You can issue a single CREATE or ALTER PROFILE command that contains resource limit and password option values.

Table 11.7 Password management options

Function:	Keyword for CREATE or ALTER PROFILE Commands:	Expression Values for CREATE or ALTER PROFILE Commands:
Lock an account after a number of tries to log in	FAILED_LOGIN_ACCOUNTS	An integer
Expire an unchanged password after a number of days	PASSWORD_LIFE_TIME	An integer, decimal, or fractional number of days
Prevent reuse of a password for a number of days	PASSWORD_REUSE_TIME	An integer, decimal, or fractional number of days
Prevent reuse of a password before some number of password changes	PASSWORD_REUSE_MAX	An integer
Keep a password locked for a number of days after consecutive failed login tries	PASSWORD_LOCK_TIME	An integer, decimal, or fractional number of days
Provide a number of days for warnings to be given before locking accounts with expired passwords	PASSWORD_GRACE_TIME	An integer, decimal, or fractional numberof days
Name a function that examines the password for desired characteristics	PASSWORD_VERIFY_FUNCTION	Name of a password complexity function

The password-complexity checking (verify) function is explained in detail in the next section.

When you run the UTLPWDMG.SQL script, you can see the password settings in the DBA_PROFILES data dictionary view. This view lists the profile options and the current values for each defined profile, including DEFAULT. The RESOURCE_TYPE column will have the value PASSWORD for the entries associated with password management, and the value KERNEL for the entries associated with operating system resource limits. The keywords DEFAULT or UNLIMITED will appear in the LIMIT column when a specific value isn't assigned. If the PASSWORD_VERIFY_FUNCTION option was entered as NULL, the value in the DBA_PROFILES view will be UNLIMITED in the LIMIT column.

Considerations for password management

You may want to use different password-management rules for different categories of users, which you can easily accomplish by building different profiles for each group and assigning them to the appropriate userids. You should make sure that any users who remain assigned to the DEFAULT profile won't have inappropriate password management options defined by it. Pay particular attention to the special database userids, SYS and SYSTEM, and to other IDs used by your operations staff or associate DBAs, as well as any userids used for processing batch jobs. If the password options applied to DEFAULT by the UTLPWDMG.SQL script aren't appropriate for these users, you should either alter the DEFAULT profile or build and assign profiles to control their pass-words according to their particular needs.

When assigning profiles to manage passwords, the profile options that are checked are those belonging to the user whose password is being assigned or changed, not those in the profile assigned to the user issuing the command. For example, if the userid SYSTEM has a profile with PASSWORD_REUSE_MAX set to UNLIMITED, a user connected as SYSTEM could issue the following command an infinite number of times without error:

ALTER USER system IDENTIFIED BY manager;

However, if the user SCOTT were assigned to a profile with PASSWORD_REUSE_MAX set to 1, a user logged into the SYSTEM userid with the profile as described couldn't issue the following command more than once successfully:

ALTER USER scott IDENTIFIED BY tiger;

The limit on password reuse set by Scott's profile takes effect, not the limit in the profile assigned to SYSTEM.

Checking for Password Complexity

If you want to force your users' passwords to comply with certain rules, such as a minimum number of characters or at least one digit in the password, you must use a PL/SQL function to perform the requisite checks. Oracle provides VERIFY_FUNCTION as the default function for checking password complexity. The function returns a Boolean value of TRUE if the password passes all the checks, or exits with a return code in the range ORA-20002 to ORA-20004 if the password fails a check. The function is created by the UTLPWDMG.SQL script, which also adds its name to the PASSWORD_VERIFY_FUNCTION option in the DEFAULT profile.

If you want to create your own password-complexity function(s), you should use VERIFY_FUNCTION as a model. You can examine the function's code by querying the DBA_SOURCE data dictionary view or, more simply, by reading the UTLPWDMG.SQL script. (For more information about the PL/SQL language and the creation and management of PL/SQL functions, see Appendix A, "Essential PL/SQL: Understanding Stored Procedures, Triggers, and Packages.") You can activate your password functions as soon as they're created by naming them in the PASSWORD_VERIFY_FUNCTION option of the CREATE of ALTER PROFILE command. Users assigned to the profile will have any new passwords checked by your function.

You may not want to continue to use the Oracle-supplied VERIFY_FUNCTION in the DEFAULT profile because it has some characteristics that you may not find appropriate. For example, the function would disallow the password tiger, which is used in a number of the demonstration scripts that create or use the SCOTT userid. It also disallows the password manager, which is the password expected in other scripts that Oracle requires you to run under the SYSTEM userid. You have three options for discontinuing the use of the default function by DEFAULT:

You can replace the VERIFY_FUNCTION with one of your own and change the DEFAULT profile to identify it:

CREATE FUNCTION my_password_function
   (username VARCHAR2, password VARCHAR2,
   old_password VARCHAR2) RETURN boolean IS ... END;
ALTER PROFILE default
   PASSWORD_VERIFY_FUNCTION
   my_password_function;

You can modify the VERIFY_FUNCTION:

CREATE OR REPLACE FUNCTION
   VERIFY_FUNCTION (username VARCHAR2,
   password VARCHAR2, old_password VARCHAR2)
   RETURN boolean IS ... END;

You can turn off complexity checking for the profile:

ALTER PROFILE default PASSWORD_VERIFY_FUNCTION NULL;

To test the code in a password-complexity function, I recommend that you build and use the following SQL*Plus script:

DECLARE
    status BOOLEAN;
BEGIN
    status := &function_name
              (user, &new_password, &old_password);
END;
/

Testing your own password-complexity functions

If you decide to code your own PL/SQL functions to control password structure and complexity, you should develop a structure to test them. I recommend creating a userid and a profile for developing and testing the code. Also use a script file to hold the PL/SQL CREATE OR REPLACE FUNCTION command. Use the same name for each function while it's in development so that you don't continually have to modify the profile's PASSWORD_ VERIFY_FUNCTION entry. When you're sure that the function is working as you want it, you can copy the test script to a permanent storage location, alter the test name to a production name, and execute the script under the SYS userid. Be particularly careful when building your own password functions to be used with the DEFAULT profile or other profiles assigned to key userids such as SYS, SYSTEM, or your DBA accounts.

You can then execute this script from a SQL*Plus session by providing the name of the function to be checked and testing values for new and old passwords when prompted. You can replace the USER function with the &USER substitution variable if you also want to test the function against different userids. Note that testing the function with this script won't cause any password changes to be stored in the database.

Before completing this discussion of the complexity function, we should briefly discuss the Oracle-supplied VERIFY_FUNCTION function because, if you look at this code, two of the checks can cause some confusion:

Consider the check to guarantee that there's at least one each of the three types of character (letter, digit, and punctuation) somewhere in the password. The code uses string variables to hold the valid characters for each type. The string for checking punctuation contains the standard punctuation marks such as the period (.), comma (,), asterisk (*), and so on. This might lead you to believe that users can now create passwords with such characters embedded in them. You might also assume that any of the three character types can be used anywhere in a password. However, Oracle8 passwords must conform to the standard naming convention for Oracle objects. In other words, they must begin with a letter and contain only letters, digits, underscores (_), dollar signs ($), and pound signs (#). In order to use any other format or any other punctuation character, your users would have to enclose their passwords in double quotation marks (") each time they used them.
The other misleading check compares the old and new passwords. The intent of this check is to ensure that they vary by a certain number of characters. You can execute VERIFY_FUNCTION directly, supplying values for the username and the new and old passwords, to confirm that this check does indeed work as documented in the script.

However, if you look closely at the code, you'll see that this particular check isn't performed if the input value for the old password is a zero-length string. Unfortunately, when the function is executed as part of a password-change command (ALTER USER user_name IDENTIFIED BY password), Oracle doesn't supply the old password because it doesn't know it. Passwords aren't stored in the database directly, but via a one-way encryption algorithm, which means that the current password can't be extracted from its encrypted version. The value for the old password is therefore always sent to the function as a blank string.

The end result, of course, is that the function can't prevent the reuse of the same or similar password. You can overcome part of this limitation by using the PASSWORD_REUSE_MAX option. This can prevent the same password from being used twice in a row, or even from being reused until some defined number of different, intervening passwords have been used. Currently, there's no way to prevent similar passwords from being used right after each other. In addition, you can't code any of your own routines that depend on the value of the old password and have them work outside the test environment.

^/1/	Not included in the ALL shortcut
^/2/	Available only with the Object option
^/3/	When used with system privileges or statement options
^/4/	When used with system privileges and roles