Auditing Categories - Implementing Database Security and Auditing - page 370

many specific objects and commands that you can audit on. These were
listed in Chapter 8. For example, Table 8.1 shows commands using an Ora-
cle-centric replication scheme, Figures 8.6 and 8.7 show DB2 objects
related to replication, and Figures 8.13 to 8.15 show SQL Server objects
related to replication.

12.9 Audit changes to sensitive data

Auditing of DML activity is another common requirement, especially in
scenarios such as a Sarbanes-Oxley project where accuracy of financial
information is the main event. Data change audit trails are common in
almost all major auditing initiatives.
A related auditing requirement that sometimes comes up (although it is
not as common as the need to audit the DML activity) involves full record-
ing of the old and the new value per DML activity. For example, you may
need to create an audit trail for the column of an employee table in which
yearly bonuses are stored. In this case you may have two different require-
ments. The first may be to fully record any update to these values and for
each update record the user who performed the update, which client was
used, which application was used, when it was done, and what the actual
SQL statement was. A second requirement may be to record all of the
above information but also record what the value was before the update
and what the value was after the update. This is not always the same thing
because I can give myself 50% more of a bonus by using a command such
as the following:

UPDATE EMP SET BONUS = BONUS * 1.5

DML audit trails and recording old and new values are an important
type of audit that you will probably need to include in your bag of tricks.
However, you have to be careful with this category and realize that these
audits should be done selectively. In some cases, people are overzealous
about this type of audit trail and for the sake of simplicity think about acti-
vating it for every DML operation. While this is technically possible, the
amount of data produced can be large, and you should make sure that your
auditing infrastructure can manage this load, especially when you include
the old and new values. As an example, suppose that you have 1 million
DML transactions per day, and assume for simplicity that each transaction
updates a single value, that you have 100 tables in the database each one
with 10 values that may be updated, and that you start out with a database