Oracle7 Server Administrator's Guide
Managing Audit Trail Information
This section describes various aspects of managing audit trail information, and includes the following topics:
Depending on the events audited and the auditing options set, the audit trail records can contain different types of information. The following information is always included in each audit trail record, provided that the information is meaningful to the particular audit action:
- name of the object accessed
- operation performed or attempted
- completion code of the operation
- system privileges (including MAC privileges for Trusted Oracle7) used
- label of the user session (for Trusted Oracle7 only)
- label of the object accessed (for Trusted Oracle7 only)
Audit trail records written to the operating system audit trail contain some encodings that are not readable. These can be decoded as follows:
Action Code | This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions. |
Privileges Used | This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes, and their descriptions. |
Completion Code | This describes the result of the attempted operation. Successful operations return a value of zero, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. |
See Also: Error codes are listed in the Oracle7 Server Messages manual.
Events Audited by Default
Regardless of whether database auditing is enabled, the Oracle Server will always audit certain database-related actions into the operating system audit trail. These events include the following:
instance startup | An audit record is generated that details the OS user starting the instance, his terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is audited into the OS audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup further prevents an administrator from restarting a database with database auditing disabled so that they are able to perform unaudited actions. |
instance shutdown | An audit record is generated that details the OS user shutting down the instance, her terminal identifier, the date and time stamp. |
connections to the
database with administrator privileges | An audit record is generated that details the OS user connecting to Oracle as SYSOPER or SYSDBA, to provide accountability of users with administrator privileges. |
On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.
Setting Auditing Options
Depending on the auditing options set, audit records can contain different types of information. However, all auditing options generate the following information:
- the user that executed the audited statement
- the action code (a number) that indicates the audited statement executed by the user
- the object or objects referenced in the audited statement
- the date and time that the audited statement was executed
The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an UPDATE statement is audited. However, this specialized type of auditing can be performed on DML statements involving tables by using database triggers.
Oracle allows you to set audit options at three levels:
| statement audits based on the type of a SQL statement, such as any SQL statement on a table (which records each CREATE, TRUNCATE, and DROP TABLE statement) |
| privilege audits use of a particular system privilege, such as CREATE TABLE |
| object audits specific statements on specific objects, such as ALTER TABLE on the EMP table |
See Also: For examples of trigger usage for this specialized type of auditing, see .
Statement Audit Options
Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in Table 21 - 1.
Option
| SQL Statements Audited
|
ALTER SYSTEM
| ALTER SYSTEM
|
CLUSTER
| CREATE CLUSTER
ALTER CLUSTER
TRUNCATE CLUSTER
DROP CLUSTER
|
DATABASE LINK
| CREATE DATABASE LINK
DROP DATABASE LINK
|
INDEX
| CREATE INDEX
ALTER INDEX
DROP INDEX
|
NOT EXISTS
| All SQL statements that return an Oracle error because the specified structure or object does not exist
|
PROCEDURE
| CREATE [OR REPLACE] FUNCTION
CREATE [OR REPLACE] PACKAGE
CREATE [OR REPLACE] PACKAGE BODY
CREATE [OR REPLACE] PROCEDURE
DROP PACKAGE
DROP PROCEDURE
|
PUBLIC DATABASE LINK
| CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
|
PUBLIC SYNONYM
| CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
|
ROLE
| CREATE ROLE
ALTER ROLE
SET ROLE
DROP ROLE
|
ROLLBACK SEGMENT
| CREATE ROLLBACK SEGMENT
ALTER DROPBACK SEGMENT`DROP ROLLBACK SEGMENT
|
SEQUENCE
| CREATE SEQUENCE
DROP SEQUENCE
|
SESSION
| Connects and Disconnects
|
SYNONYM
| CREATE SYNONYM
DROP SYNONYM
|
SYSTEM AUDIT
| AUDIT
NO AUDIT
|
SYSTEM GRANT
| GRANT system privileges/role
TO user/role
REVOKE system privileges/role
FROM user/role
|
TABLE
| CREATE TABLE
ALTER TABLE
DROP TABLE
|
TABLESPACE
| CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
|
TRIGGER
| CREATE TRIGGER
ALTER TRIGGER ENABLE or DISABLE
ALTER TABLE with
ENABLE, DISABLE, and DROP clauses
|
USER
| CREATE USER
ALTER USER
DROP USER
|
VIEW
| CREATE [OR REPLACE] VIEW
DROP VIEW
|
Table 21 - 1. Statement Auditing Options
Shortcuts for Statement Audit Options Shortcuts are provided so that you can specify several related statement options with one word.
Shortcuts are not statement options themselves; rather, they are ways of specifying sets of related statement options with one word in AUDIT and NOAUDIT statements.
CONNECT | equivalent to the SESSION option |
RESOURCE | equivalent to the options ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, and VIEW |
DBA | equivalent to the options SYSTEM AUDIT, PUBLIC DATABASE LINK, PUBLIC SYNONYM, ROLE, SYSTEM GRANT, and USER |
ALL | equivalent to all options in Table 21 - 1, including the NOT EXISTS option |
Warning: Do not confuse the shortcuts CONNECT, RESOURCE, and DBA with the predefined roles of the same names.
Auditing Connections and Disconnections
The SESSION statement option (and CONNECT shortcut) is unique because it does not generate an audit record when a particular type of statement is issued; this option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session.
Table 21 - 2 lists additional audit options not covered by any of the above shortcuts.
Object Option
| SQL Statements Audited
|
ALTER SEQUENCE
| ALTER SEQUENCE sequence
|
ALTER TABLE
| ALTER TABLE table
|
COMMENT TABLE
| COMMENT ON table, view,
snapshot, column
|
DELETE TABLE
| DELETE FROM table, view
|
EXECUTE PROCEDURE
| Calls to procedures and functions
|
GRANT PROCEDURE
| GRANT privilege ON procedure REVOKE privilege ON sequence
|
GRANT TABLE
| GRANT privilege ON table, view, snapshot REVOKE privilege ON table, view, snapshot
|
INSERT TABLE
| INSERT INTO table view
|
LOCK TABLE
| LOCK TABLE table, view
|
SELECT SEQUENCE
| Reference to a sequence
|
SELECT TABLE
| SELECT . . .FROM table, view, snapshot
|
UPDATE TABLE
| UPDATE table, view
|
Table 21 - 2. Statement Auditing Options
Privilege Audit Options
Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you would use a statement similar to the following example:
AUDIT DELETE ANY TABLE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
Oracle's system privileges are listed beginning .
Object Audit Options
Table 21 - 3 lists valid object audit options and the schema object types for which each option is available.
Object Option
| Table
| View
| Sequence
| Procedure1
|
ALTER
| _/
|
| _/
|
|
AUDIT
| _/
| _/
| _/
| _/
|
COMMENT
| _/
| _/
|
|
|
DELETE
| _/
| _/
|
|
|
EXECUTE
|
|
|
| _/
|
GRANT
| _/
| _/
| _/
| _/
|
INDEX
| _/
|
|
|
|
INSERT
| _/
| _/
|
|
|
LOCK
| _/
| _/
|
|
|
RENAME
| _/
| _/
|
| _/
|
SELECT
| _/
| _/2
| _/
|
|
UPDATE
| _/
| _/
|
|
|
Table 21 - 3. Object Audit Options
1 "Procedure" refers to stand-alone stored procedures and functions, and packages.
2 The SELECT option may also be used for snapshots.
Table 21 - 4 lists the SQL statements audited by each object option.
Object Option
| Table
|
ALTER
| ALTER object (table or sequence)
|
AUDIT
| AUDIT (Form II) object
|
COMMENT
| COMMENT object (table or view)
|
DELETE
| DELETE FROM object (table or view)
|
EXECUTE
| EXECUTE object (procedure1)
|
GRANT
| GRANT (Form II) privilege ON object
|
INDEX
| CREATE INDEX ON object (tables only)
|
INSERT
| INSERT INTO object (table, view, or procedure)
|
LOCK
| LOCK object (table or view)
|
RENAME
| RENAME object (table, view, or procedure1)
|
SELECT
| SELECT . . .FROM object (table, view, snapshot)
|
UPDATE
| UPDATE object (table or view)
|
Table 21 - 4. SQL Statement Audited by Database Object Audit
Options
1 Procedure refers to stand-alone stored procedures and functions, and packages.
Shortcut for Object Audit Options The ALL shortcut can be used to specify all available object audit options for a schema object. This shortcut is not an option itself; rather, it is a way of specifying all object audit options with one word in AUDIT and NOAUDIT statements.
Enabling Audit Options
The SQL command AUDIT turns on statement and privilege audit options, and object audit options. Audit statements that set statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options. The SQL command AUDIT turns on audit options. To use it to set statement and privilege options, you must have the AUDIT SYSTEM privilege. To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.
You can set any auditing option, and specify the following conditions for auditing:
- WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL
A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options; existing sessions will continue using the audit options in place at session creation.
Warning: The AUDIT command only turns auditing options on; it does not enable auditing as a whole. To turn auditing on and control whether Oracle generates audit records based on the audit options currently set, set the parameter AUDIT_TRAIL in the database's parameter file.
The following examples illustrate the use of the AUDIT command.
See Also: For a complete description of the AUDIT command, see the Oracle7 Server SQL Reference.
For more information about enabling and disabling auditing, see "Enabling and Disabling Database Auditing" .
Enabling Statement Privilege Auditing To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION (the default and only value for this option), enter the following statement:
AUDIT SESSION;
You can set this option selectively for individual users also, as in the next example:
AUDIT SESSION
BY scott, lori;
To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:
AUDIT DELETE ANY TABLE;
To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE ANY PROCEDURE system privilege, by all database users, BY ACCESS, enter the following statement:
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE ANY PROCEDURE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.
Enabling Object Auditing To audit all successful and unsuccessful DELETE statements on the EMP table, BY SESSION (the default value), enter the following statement:
AUDIT DELETE ON emp;
To audit all successful SELECT, INSERT, and DELETE statements on the DEPT table owned by user JWARD, BY ACCESS, enter the following statement:
AUDIT SELECT, INSERT, DELETE
ON jward.dept
BY ACCESS
WHENEVER SUCCESSFUL;
To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:
AUDIT SELECT
ON DEFAULT
WHENEVER NOT SUCCESSFUL;
A user can set any object audit option for the objects contained in the user's schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing options; normally, the security administrator is the only user granted this system privilege.
Disabling Audit Options
The NOAUDIT command turns off the various audit options of Oracle. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.
You can use a NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and non-successful cases.
The BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT command; audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.
The following examples illustrate the use of the NOAUDIT command.
Warning: The NOAUDIT command only turns auditing options off; it does not disable auditing as a whole. To turn auditing off and stop Oracle from generating audit records, even though you have audit options currently set, set the parameter AUDIT_TRAIL in the database's parameter file.
See Also: For a complete syntax listing of the NOAUDIT command, see the Oracle7 Server SQL Reference.
Also see "Enabling and Disabling Database Auditing" .
Disabling Statement and Privilege Auditing The following statements turn off the corresponding audit options:
NOAUDIT session;
NOAUDIT session BY scott, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE ANY PROCEDURE;
The following statements turn off all statement (system) and privilege audit options:
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.
Disabling Object Auditing The following statements turn off the corresponding auditing options:
NOAUDIT DELETE
ON emp;
NOAUDIT SELECT, INSERT, DELETE
ON jward.dept;
Furthermore, to turn off all object audit options on the EMP table, enter the following statement:
NOAUDIT ALL
ON emp;
Disabling Default Object Audit Options To turn off all default object audit options, enter the following statement:
NOAUDIT ALL
ON DEFAULT;
Note that all schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.
To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.
Enabling and Disabling Database Auditing
Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate and store audit records in the audit trail unless database auditing is enabled. The security administrator is normally responsible for this operation.
Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database's parameter file. The parameter can be set to the following values:
| DB enables database auditing and directs all audit records to the database audit trail |
| OS enables database auditing and directs all audit records to the operating system audit trail |
| NONE disables auditing (This value is the default.) |
Once you have edited the parameter file, restart the database instance to enable or disable database auditing as intended.
See Also: For more information about editing parameter files, see the Oracle7 Server Reference.
Controlling the Growth and Size of the Audit Trail
If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.
When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:
- the number of audit options turned on
- the frequency of execution of audited statements
To control the growth of the audit trail, you can use the following methods:
- Enable and disable database auditing. If it is enabled, audit records are generated and stored in the audit trail; if it is disabled, audit records are not generated.
- Be very selective about the audit options that are turned on. If more selective auditing is performed, useless or unnecessary audit information is not generated and stored in the audit trail.
- Tightly control the ability to perform object auditing. This can be done two different ways:
- A security administrator owns all objects and the AUDIT ANY system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.
- All objects are contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not granted to the corresponding user) and the security administrator is the only user granted the AUDIT ANY system privilege.
In both scenarios, object auditing is controlled entirely by the security administrator.
The maximum size of the database audit trail (SYS.AUD$ table) is predetermined during database creation. By default, up to 99 extents, each 10K in size, can be allocated for this table.
See Also: If you are directing audit records to the operating system audit trail, see your operating system-specific Oracle documentation for more information about managing the operating system audit trail.
After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.
For example, to delete all audit records from the audit trail, enter the following statement:
DELETE FROM sys.aud$;
Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table EMP, enter the following statement:
DELETE FROM sys.aud$
WHERE obj$name='EMP';
If audit trail information must be archived for historical purposes, the security administrator can copy the relevant records to a normal database table (for example, using "INSERT INTO table SELECT ... FROM sys.aud$ ...") or export the audit trail table to an operating system file.
Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS has granted DELETE privilege on SYS.AUD$ can delete records from the database audit trail.
Note: If the audit trail is completely full and connections are being audited (that is, if the SESSION option is set), typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, the security administrator must connect as SYS (operations by SYS are not audited) and make space available in the audit trail.
See Also: For information about exporting tables, see the Oracle7 Server Utilities guide.
Reducing the Size of the Audit Trail
As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.
If the database audit trail has many extents allocated for it, but many of them are not being used, the space allocated to the database audit trail can be reduced using the following steps:
To Reduce the Size of the Audit Trail
1. If you want to save information currently in the audit trail, copy it to another database table or export it using the EXPORT utility.
2. Connect as with administrator privileges.
3. Truncate SYS.AUD$ using the TRUNCATE command.
4. Reload archived audit trail records generated from Step 1.
The new version of SYS.AUD$ is allocated only as many extents that are necessary to contain current audit trail records.
Note: SYS.AUD$ is the only SYS object that should ever be directly modified.
Protecting the Audit Trail
When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.
To protect the database audit trail from unauthorized deletions, grant the DELETE ANY TABLE system privilege to security administrators only.
To audit changes made to the database audit trail, use the following statement:
AUDIT INSERT, UPDATE, DELETE
ON sys.aud$
BY ACCESS;
Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use. As a final measure of protecting the audit trail, any operation performed while connected with administrator privileges is audited in the operating system audit trail, if available.
See Also: For more information about the availability of an operating system audit trail and possible uses, see your operating system-specific Oracle documentation.