|Oracle7 Server Administrator's Guide||
Either the database or operating system audit trail can store all audit records generated as the result of statement, privilege, or object auditing.
Your operating system may or may not support database auditing to the operating system audit trail. If this option is available, consider the advantages and disadvantages of using either the database or operating system auditing trail to store database audit records.
Using the database audit trail offers the following advantages:
See Also: Your operating system may also contain an audit trail that stores audit records generated by the operating system auditing facility. However, this facility is operating system-dependent. See your operating system-specific Oracle documentation.
Use the following general guidelines when devising an auditing strategy:
See Also: On most operating systems, the audit trail views are created automatically with the data dictionary. See your operating system-specific Oracle documentation.
For examples of audit information interpretations, see "Viewing Database Audit Trail Information" .
This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.
This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes, and their descriptions.
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.
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.
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.
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 page 21 - 21.
|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|
equivalent to the SESSION option
equivalent to the options ALTER SYSTEM, CLUSTER, DATABASE LINK, PROCEDURE, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, and VIEW
equivalent to the options SYSTEM AUDIT, PUBLIC DATABASE LINK, PUBLIC SYNONYM, ROLE, SYSTEM GRANT, and USER
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.
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|
AUDIT DELETE ANY TABLE
WHENEVER NOT SUCCESSFUL;
Oracle's system privileges are listed beginning .
Table 21 - 4 lists the SQL statements audited by each object option.
|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)|
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.
You can set any auditing option, and specify the following conditions for auditing:
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:
You can set this option selectively for individual users also, as in the next example:
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
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
To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:
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.
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 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 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 SELECT, INSERT, DELETE
Furthermore, to turn off all object audit options on the EMP table, enter the following statement:
Disabling Default Object Audit Options To turn off all default object audit options, enter the following statement:
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.
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.
When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:
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$
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.
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
Note: SYS.AUD$ is the only SYS object that should ever be directly modified.
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
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.
AUDIT ALTER, INDEX, RENAME ON DEFAULT
CREATE TABLE scott.emp . . . ;
CREATE VIEW scott.employee AS SELECT * FROM scott.emp;
AUDIT SESSION BY jward, swilliams;
AUDIT ALTER USER;
AUDIT LOCK TABLE
AUDIT DELETE ON scott.emp
The following statements are subsequently issued by the user JWARD:
ALTER USER tsmith QUOTA 0 ON users;
DROP USER djones;
The following statements are subsequently issued by the user SWILLIAMS:
LOCK TABLE scott.emp IN EXCLUSIVE MODE;
DELETE FROM scott.emp WHERE mgr = 7698;
ALTER TABLE scott.emp ALLOCATE EXTENT (SIZE 100K);
CREATE INDEX scott.ename_index ON scott.emp (ename);
CREATE PROCEDURE scott.fire_employee (empid NUMBER) AS
DELETE FROM scott.emp WHERE empno = empid;
The following sections show the information that can be listed using the audit trail views in the data dictionary.
SELECT * FROM sys.dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- -------------------- ---------- ----------
JWARD SESSION BY SESSION BY SESSION
SWILLIAMS SESSION BY SESSION BY SESSION
LOCK TABLE BY ACCESS NOT SET
Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or BY ACCESS.
SELECT * FROM sys.dba_priv_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- -------------------- ---------- ----------
ALTER USER BY SESSION BY SESSION
SELECT * FROM sys.dba_obj_audit_opts
WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';
OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
------ ----------- --------- --- --- --- --- --- --- --- --- ...
SCOTT EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- ...
SCOTT EMPLOYEE VIEW -/- -/- -/- -/- -/- -/- -/- -/- ...
Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
SELECT * FROM all_def_audit_opts;
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/-
Notice that the view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (see previous example).
SELECT username, obj_name, action_name, ses_actions
SELECT username, logoff_time, logoff_lread, logoff_pread,
USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
---------- --------- ---------- ---------- ---------- ----------
JWARD 02-AUG-91 53 2 24 0
SWILLIAMS 02-AUG-91 3337 256 630 0
Note: In some fields, the Oracle AUDIT command is considered a security audit facility, while triggers can provide a financial audit facility.
When deciding whether to create a trigger to audit database activity, consider the advantages that the standard Oracle database auditing features provide compared to auditing by triggers:
When you should use AFTER row vs. AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also allow the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.
The following trigger audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update. The trigger demonstrates the following:
CREATE TRIGGER audit_employee
AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
/* AUDITPACKAGE is a package with a public package
variable REASON. REASON could be set by the
application by a command such as EXECUTE
AUDITPACKAGE.SET_REASON(reason_string). Note that a
package variable has state for the duration of a
session and that each session has a separate copy of
all package variables. */
IF auditpackage.reason IS NULL THEN
raise_application_error(-20201,'Must specify reason with ',
/* If the above conditional evaluates to TRUE, the
user-specified error number and message is raised,
the trigger stops execution, and the effects of the
triggering statement are rolled back. Otherwise, a
new row is inserted into the pre-defined auditing
table named AUDIT_EMPLOYEE containing the existing
and new values of the EMP table and the reason code
defined by the REASON variable of AUDITPACKAGE. Note
that the "old" values are NULL if triggering
statement is an INSERT and the "new" values are NULL
if the triggering statement is a DELETE. */
INSERT INTO audit_employee VALUES
(:old.ssn, :old.name, :old.job_classification, :old.sal,
:new.ssn, :new.name, :new.job_classification, :new.sal,
auditpackage.reason, user, sysdate );
Optionally, you can also set the reason code back to NULL if you want to force the reason code to be set for every update. The following AFTER statement trigger sets the reason code back to NULL after the triggering statement is executed:
CREATE TRIGGER audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE ON emp
The previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.