|
 |
|
Oracle Tips by Burleson Consulting |
Auditing by Database
Triggers
Enabling auditing options may not always be
sufficient to evaluate suspicious activity within your database.
When you enable auditing, Oracle places records in the SYS.AUD$
table in accordance with the auditing options that you have
specified. One limitation to this type of auditing is that SYS.AUD$
does not provide you with value-based information. You need to write
triggers to record the before and after values on a per-row
basis.
Auditing with Oracle supports DML and DDL
statements on objects and structures. Triggers support DML
statements issued against objects, and can be used to record the
actual values before and after the statement.
In some facilities, audit commands are
considered security audit utilities, while triggers are referred to
as financial auditing. This is because triggers can provide a method
to track actual changes to values in a table. Although, similar to
the AUDIT command, you can use triggers to record information, you
should customize your auditing by using triggers only when you need
more detailed audit information.
AFTER triggers are normally used to avoid
unnecessary statement generation for actions that fail due to
integrity constraints. AFTER triggers are executed only after all
integrity constraints have been checked. AFTER ROW triggers provide
value-based auditing for each row of the tables and support the use
of “reason codes.” A reason for the statement or transaction, along
with the user, sysdate, and old and new values, can be inserted into
another table for auditing purposes.
Oracle auditing can be used for successful
and unsuccessful actions, as well as connections, disconnections,
and session I/O activities. With auditing, you can decide if the
actions should be BY ACCESS or BY SESSION. Triggers can only audit
successful actions against the table on which they are created. If
auditing is being performed using a trigger, any rollback or
unsuccessful action will not be recorded.
Auditing provides an easy, error-free method
to tracking, with all the audit records stored in one place.
Triggers are more difficult to create and maintain.
See Code Depot
www.dba-oracle.com/oracle_scripts.htm |
