Auditing Architectures - Implementing Database Security and Auditing - page 381

Many regulations require you to maintain auditing information for
many years. Some financial regulations require you to maintain data for
three years, and HIPAA requires you to maintain information for six years.
Internal policies in some financial services organizations even require pres-
ervation of this data for seven years. In all cases, the numbers are huge. A
simple exercise will show you just how bad this can become: say you do 50
million SQL requests per day in your database environments (and many
environments that include many databases do much more than that).
Assume you have to audit 20% of these (including DML, DDL, and
SELECTs on sensitive objects). Assuming (for simplicity) that all days have
the same load, this comes to more than 3.5 billion audit records in one year.
For a sustainable auditing solution, you therefore will need to archive
information. This will also ensure that the response times for reports and
queries are reasonable. Assuming that you store the archived information in
a place and format that is easily accessible for a possible investigation, there
really is no disadvantage to archiving this data, and you should always look
for this feature to exist in an acquired solution or look to implement this
feature in a homegrown solution.
The important attributes you should ensure regarding archiving are as
follows:
Allow for flexible rules that define what to archive, when, and to
where.
Schedule archiving in a way that ensures that your online data is good
enough for all your reporting activities. For example, if you need to
create audit reports and audit trails to present to auditors and informa-
tion security groups, make sure that you do not archive before you cre-
ate these reports. Leave enough slack for supporting regeneration of
reports. For example, if you create audit reports on a monthly basis,
archive data that is three months old to avoid having to restore data in
case someone looks at a report and wants to drill down further.
Archive the produced reports and deliverables, not only the raw audit
trails. In most cases you may need these reports more often than the
raw data.
Archive in a manner that will not create a nightmare when you need
to restore data for an investigation or for regulatory compliance. Cre-
ate a manifest for archived information and index the archived infor-
mation with at least a date range and a specification of the database
server. This is the minimum set of information you will need in order