Auditing Categories - Implementing Database Security and Auditing - page 371

that has 10,000 records in each table. Although this calculation is simplistic
and imprecise, you should not be surprised that if you record old and new
values, after one year your auditing database will be more than 35 times
larger than the database itself.
Therefore, when you contemplate DML audit trails, you should selec-
tively choose which objects and which commands to audit. For example,
you can decide to create audit trails for a subset of the database tables, for a
subset of logins or accounts, and so on. Even more selective is the choice of
which tables and columns to maintain old and new values for.
DML audits are also supported through three main methods, but com-
paring daily (or periodic) snapshots is not an option in this case. The three
methods are using database capabilities, using an external audit system, or
using triggers.
All databases give you some way to implement audit trails for DML
activities. In Oracle, for example, you can use the log miner tool that is
based on the redo log. Because the redo log captures all DML activity
(including the old and new values), log miner can extract this information
and make it available to you. In SQL Server you can use a DOP trace event:

Event ID Event Class Description

DOP Event Occurs before a SELECT, INSERT, or UPDATE state-

ment is executed.

28

Moving on to the second category, external database audit systems sup-
port DML audits based on any filtering criteria, including database object,
user, application, and so on. They also help in capturing and compressing
this information and making it available to reporting frameworks even
when the amount of data is overwhelming. As you'll see in the next chapter
(Section 13.3), some of these tools are also based on mining the redo log (or
transaction log).
Finally, the third option is simply to use your own custom triggers. If
you are not part of a widescale auditing project and just need to create a
DML audit trail for a few objects, adding triggers that write the informa-
tion to a special audit table may be the simplest and quickest thing to help
you move on to your next project.