BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Auditing the Database

Oracle Tips by Burleson

Auditing is a method of recording database activity as part of database security. It allows the DBA to track user activity within the database. The audit records will provide information on who performed what database operation and when it was performed. Records are written to a SYS-owned table named AUD$. The SYS.AUD$ table is commonly referred to as the audit trail.

It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are three reasons for these two statements:

* The AUD$ table, if auditing is used, may grow to a very large size, depending on the audit options selected.

* In some situations, you may want to add a trigger to the AUD$ table to count logins, monitor for specific actions (prior to 8i), or perform other security-related functions (such as implement password-checking functionality prior to version 8). Remember, you can’t add triggers to catalog objects owned by SYS.

* Since the table will grow and shrink and be high activity, it is advisable to move it from the SYSTEM tablespace to avoid fragmentation.

Auditing information is not collected without some impact on performance and database resources. How much of an impact auditing will have on your system depends largely on the type of auditing you enable. For example, setting high-level auditing such as connection activity will not have as much of a performance impact as tracking all SQL statements issued by all users. It is best to start out with high-level auditing and then refine additional auditing as needed.

You can audit all users with the exception of SYS and CONNECT INTERNAL. Auditing can only be performed for users connected directly to the database, not for actions on a remote database.

On some versions of Oracle7 (7.3.2 to 7.3.3) operating system-level auditing of SYS and INTERNAL logins was initiated automatically on install. Auditing at operating system level (EPC_DISABLE=FALSE) results in a small audit file being generated in an operating system-specific directory location for each use of SYS or INTERNAL. This system-level auditing also may result in longer and longer login times for all users as the files process.dat, regid.dat, and so on, in the $ORACLE_

HOME/otrace/admin directory on UNIX, or orant\otrave73\admin on NT, expand in size. This is disabled by dropping the *.dat files in the previously mentioned locations, shutting down the instance, typing otrccref from the command line to reinitiate the files, and then setting the environmental variable EPC_DISABLED to TRUE (EPC_DISABLED=TRUE (Borne shell), setenv EPC_DISABLED TRUE (C shell), and restarting the instance(s).

Auditing should be enabled if the following types of questionable activities are noted:

* Unexplained changes in passwords, tablespace settings, or quotas appear.

* Excessive deadlocks are encountered.

* Records are being read, deleted, or changed without authorization.

There are three types of auditing:

* Statement auditing

* Privilege auditing

* Object auditing

Enabling and Disabling Auditing

The database initialization parameter AUDIT_TRAIL controls the enabling and disabling of auditing. The default setting for this parameter is NONE, which means that no auditing will be performed, regardless of whether or not AUDIT commands are issued. It is important to remember that any auditing statements issued will not be performed if AUDIT_TRAIL=NONE. Unless auditing is enabled in the database parameter initialization file, any auditing options that have been turned on will not create records in the audit trail. Auditing is not completely disabled unless it is set to NONE in the database parameter initialization file.

You must set the database initialization parameter AUDIT_TRAIL to DB or OS in order to enable auditing. The DB setting means the audit trail records are stored in the database in the SYS.AUD$ table. OS will send the audit trail records to an operating system file. The OS setting is operating system-dependent and is not supported on all operating systems.

Auditing can be performed based on whether statements are executed successfully or unsuccessfully. Auditing WHEN SUCCESSFUL inserts records into the audit trail only if the SQL command executes successfully. Auditing WHEN NOT SUCCESSFUL inserts records into the audit trail only when the SQL statement is unsuccessful. If a statement is unsuccessful due to syntax errors, it will not be included in the audit trail. If neither WHEN SUCCESSFUL or WHEN NOT SUCCESSFUL is specified, both successful and unsuccessful actions will be audited.

Auditing can be set BY SESSION or BY ACCESS. When you use the BY SESSION option for statement or privilege auditing, it writes a single record to the audit trail when a connection is made. When the session is disconnected, that record is updated with cumulative information about the session. The information collected includes connection time, disconnection time, and logical and physical I/Os processed. Audit options set BY SESSION for objects insert one record per database object into the audit trail for each session. When you set auditing BY ACCESS, a record is inserted into the audit trail for each statement issued. All auditing of DDL statements will be audited BY ACCESS regardless of whether you have set auditing BY ACCESS or BY SESSION.

The BY USER option allows you to limit auditing to a specific user or list of users. When you do not specify BY USER, all users will be audited.

When you change auditing options, they become active for subsequent sessions, not current sessions. Any existing sessions will continue to use the auditing options in effect when they logged in to the database.

The syntax to enable statement and privilege auditing is:

AUDIT system_priv_list|statement_opt_list
[BY user_list|proxy[ ON BEHALF OF user|ANY]]
[BY SESSION|ACCESS][WHENEVER [NOT]SUCCESSFUL]

The following are the options listed in the syntax diagram for statement auditing:

statement_opt_list
CREATE|ALTER|DROP|TRUNCATE CLUSTER
CREATE|DROP[PUBLIC}DATABASE LINK
NOT EXISTS
CREATE|ALTER|DROP INDEX
CREATE|DROP PROCEDURE|FUNCTION|LIBRARY|PACKAGE|PACKAGE BODY
CREATE|ALTER|SET|DROP ROLE
CREATE|ALTER|DROP ROLLBACK SEGMENT
CREATE|ALTER|DROP SEQUENCE
SESSION
CREATE|DROP [PUBLIC]SYNONYM
SYSTEM AUDIT [NO]AUDIT sql_statements
SYSTEM GRANT
GRANT system_priv_and_roles|REVOKE system_priv_and_roles
CREATE|ALTER|DROP|TRUNCATE|COMMENT ON|DELETE [FROM] TABLE
CREATE|ALTER|DROP TABLESPACE
CREATE|ALTER {ENABLE|DISABLE]|DROP TRIGGER
(And ALTER TABLE ENABLE|DISABLE ALL TRIGGERS)
CREATE||ALTER|DROP TYPE [BODY] (ALTER TYPE BODY N/A)
CREATE|ALTER|DROP USER
CREATE|DROP VIEW
addl_stmt_opt =

GRANT and REVOKE commands on:

For example:

AUDIT DELETE TABLE, INSERT TABLE,
       EXECUTE ANY PROCEDURE
       BY beth
       BY ACCESS
WHENEVER SUCCESSFUL;

The syntax to disable statement and privilege auditing is:

NOAUDIT system_priv_list|statement_opt_list
[BY user_list|proxy[ ON BEHALF OF user|ANY]]
[WHENEVER [NOT]SUCCESSFUL]

For example:

NOAUDIT DELETE TABLE, INSERT TABLE,
EXECUTE ANY PROCEDURE
BY app_user
WHENEVER SUCCESSFUL;

The syntax to enable object auditing is:

AUDIT object_opt_list|ALL
ON [schema.]object_name|DIRECTORY dir_name|DEFAULT
[BY SESSION|ACCESS][WHENEVER [NOT]SUCCESSFUL]

For example:

AUDIT SELECT, INSERT, DELETE
ON scott.dept
WHENEVER NOTSUCCESSFUL;

The syntax to disable object auditing is:

NOAUDIT object_opt_list|ALL
ON [schema.]object_name|DIRECTORY dir_name|DEFAULT
[BY SESSION|ACCESS][WHENEVER [NOT]SUCCESSFUL]

For example:

NOAUDIT SELECT, INSERT, DELETE
ON scott.dept
WHENEVER SUCCESSFUL;

Using Statement Auditing

Statement auditing involves tracking the SQL statements issued by database users. In order to enable or disable auditing on SQL statements, you must have the AUDIT SYSTEM privilege.

In addition to the statement-auditing options shown previously, there are several options that will create audit records for a combination of statements. These options, sometimes referred to as audit shortcuts, are CONNECT, RESOURCE, DBA, and ALL. When these shortcuts are specified, all grants given through the specified role will be audited.

The audit shortcuts are useful for setting up auditing for multiple options with one command. For example:

AUDIT RESOURCE
WHENEVER NOT SUCCESSFUL;

will audit all the commands listed for ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, TYPE, and VIEW for all users when the command does not successfully complete.

Note: Be careful that you do not confuse these shortcuts with the roles named CONNECT, RESOURCE, and DBA. Also note that these shortcuts are provided for compatibility with earlier versions of Oracle and may not be supported in future versions.

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 10g reference poster

Oracle training & performance tuning books