Enabling replay of past activities

As part of a comprehensive security policy, a company can require the ability to retroactively go back a set number of years and analyze the effects of any particular request against certain tables in their database.

Before you begin

A company must institute a policy of archiving their weekly backups and associated log files such that they can reconstitute the database for any chosen moment in time.

About this task

To allow, at any future time, the replay and analysis of any request against the relevant, restored database, sufficient database audit information must be captured about every request made against the database. This requirement can cover both static and dynamic SQL statements. The EXECUTE category, when logged WITH DATA contains the necessary information to replay past SQL statements, assuming that the data in the database is restored to the state it was when the statement was issued.

Restrictions

The following authority and privileges are required:

SECADM authority is required to create the audit policies,
EXECUTE privilege is required for the audit routines and procedures.

Procedure

To enable replay of past activities, as the SECADM:

Create an audit policy that audits the EXECUTE category and apply this policy to the database.

CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA 
   STATUS BOTH ERROR TYPE AUDIT
COMMIT
AUDIT DATABASE USING POLICY STATEMENTS
COMMIT

Regularly archive the audit log to create an archive copy.
To archive the audit log, run the following command on a regular basis, specifying the path to the archive directory and -2 to indicate the archive should be run on all members:
```
CALL SYSPROC.AUDIT_ARCHIVE( '/auditarchive', -2 )
```
Check that the audit log files were created.
These archived files will then be kept for the number of years specified by the company's business policy.
To check the audit log files run:
```
SELECT FILE FROM SESSION.AUDIT_ARCHIVE_RESULTS
```

Results

Your environment is now set up so data and information is archived to allow future replay of logged database activity.

What to do next

For information about how you can use the information logged in this task to replay database activity, see the Related reference.