0615-0619

Previous | Table of Contents | Next

Page 615

Statement-Level Auditing

Auditing that occurs at the statement levelsometimes called the privilege levelhas a wide scope. With this level of auditing, an audit record is written for each specific SQL statement that is issued. It is possible to limit it to a specific usersuch as all CREATE TABLE commands issued by DAVEor to all userssuch as any ALTER TABLE command issued by any user . Depending on how specific the DBA makes this level of auditing, the audit information generated can be substantial.

In the following example, two audit options are set. One option logs CREATE TABLE activity within the database. The other option logs all CREATE SESSION activity done by BETO.

% sqlplus system Password: ........ Connected. SQL> audit create table by access whenever successful; Statement processed. SQL> audit create session by beto by access whenever successful; Statement processed. 

Two important parameters appear in every SQL audit command:

• BY SESSION/BY ACCESS
• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

BY SESSION/BY ACCESS determines how often audit records should be written. In a BY SESSION audit, the database writes a single audit record that sums all the times that an action took place during a given session. In a BY ACCESS audit, the database writes a single audit record for each SQL statement that was issued.

WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL determines the conditions under which the audit records should be written. Audits that are WHENEVER SUCCESSFUL have information written only if they succeed. WHENEVER NOT SUCCESSFUL audits are written only if they do not succeed.

System-level roles can be used to implement auditing, so that only a single SQL statement is required to audit several different operations. Consult the Oracle7 Server Administrator's Guide for more information.

The CLUSTER statement audits

 CREATE CLUSTER ALTER CLUSTER DROP CLUSTER TRUNCATE CLUSTER 

Page 616

The DATABASE LINK statement audits

 CREATE DATABASE LINK DROP DATABASE LINK 

The INDEX statement audits

 CREATE INDEX ALTER INDEX DROP INDEX 

The EXISTS statement indicates a failure because a value currently exists in the database. This is a feature of Trusted Oracle7 only.

The NOT EXISTS statement indicates a failure because database objects do not exist.

The PROCEDURE statement audits

 CREATE FUNCTION CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP PACKAGE DROP PROCEDURE 

The PROFILE statement audits

 CREATE PROFILE ALTER PROFILE DROP PROFILE 

The PUBLIC DATABASE LINK statement audits

 CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK 

The PUBLIC SYNONYM statement audits

 CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM 

The ROLE statement audits

 CREATE ROLE ALTER ROLE 

Page 617

 DROP ROLE SET ROLE 

The ROLLBACK SEGMENT statement audits

 CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT 

The SEQUENCE statement audits

 CREATE SEQUENCE DROP SEQUENCE 

The SESSION statement audits database connections and disconnections.

The SYNONYM statement audits

 CREATE SYNONYM DROP SYNONYM 

The SYSTEM AUDIT statement audits

 AUDIT NOAUDIT 

The SYSTEM GRANT statement audits

 GRANT on system privileges and roles REVOKE on system privileges and roles 

The TABLE statement audits

 CREATE TABLE DROP TABLE TRUNCATE TABLE 

The TABLESPACE statement audits

 CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE 

The TRIGGER statement audits

 CREATE TRIGGER ALTER TRIGGER ENABLE/DISABLE 

Page 618

 DROP TRIGGER ALTER TABLE with the ENABLE/DISABLE option 

The USER statement audits

 CREATE USER ALTER USER DROP USER 

The VIEW statement audits

 CREATE VIEW DROP VIEW 

Object-Level Auditing

It is possible to audit database information at the database object level, which enables you to trap operations done on a specific database object. The syntax is essentially the same as that for a statement level audit:

% sqlplus system Password: ........ Connected. SQL> audit delete on hr.payroll; Statement processed. 

The statement audit specifies a class of statements and, optionally , which user to audit for these statements. The object audit, on the other hand, points to a type of object operation and the name of an object.

The types of object level operations that can be performed are

 ALTER AUDIT COMMENT DELETE EXECUTE GRANT INDEX INSERT LOCK RENAME SELECT UPDATE 

Page 619

These object-level operations can be performed on any of the following types of database objects:

• Tables
• Views
• Sequences
• Stored procedures, functions, or packages
• Snapshots

Consult the Oracle7 Server Administrator's Guide for more information.

To deactivate object- or privilege-level auditing, bounce the database and set AUDIT_TRAIL to NONE or specify the current audit options with the NOAUDIT command. For example:

% sqlplus system Password: ........ Connected. SQL> noaudit all; Statement processed. 

Audit Trail Location

The audit trails from Oracle's AUDIT option can be stored in either the database or the operating system. The location is determined at database startup, based on the value of the INIT.ORA parameter AUDIT_TRAIL.

Database

All audit information stored within the database is stored in the table SYS.AUD$, which by default is stored in the SYSTEM tablespace. You should move this table into a separate tablespace to prevent undue fragmentation of SYSTEM.

There are two methods for performing this operation:

• Before you turn on auditing, extract the SQL for this table creation from sql.bsq; do a describe of the table from SQL*Plus to get the exact sizes of the columns . Drop the table and re-create it in the new tablespace. Make sure that all the relevant grants on the table are made.
• Export the table. Generate a copy of the creation SQL by means of the imp utility. Create the table, and import it with the IGNORE=Y flag set.

In an audited database, it is important to make sure that audit trail information is not erased. To prevent that from happening, the DBA should limit the users who can actually write information to this table to SYS. This is difficult to do, however, if users have been granted the DELETE ANY TABLE system privilege. The following code segment illustrates a simple way to trap users who attempt to modify the SYS.AUD$ table:

% sqlplus system Password: ...... Connected. 

Previous | Table of Contents | Next