Replaying past database activities
Replaying past database activity is possible if all required data, logs and information is available. This reference topic shows how a SECADM might replay past database activity via example.
Description
At some point, company auditors might want to analyze the activities of a particular user that occurred in the past. The SECADM can use the backup database images, coupled with the backup logs, and audit logs to reconstitute the database in question and replay the activity the auditors want to analyze. Suppose the activities of a particular user that occurred on April 19, 2006 are in question, the following example shows the flow of how a SECADM would help the auditors carry out their analysis.
Example
- The SECADM would issue the AUDIT_LIST_LOGS to find all available
audit logs from April 2006.
SELECT FILE FROM TABLE(SYSPROC.AUDIT_LIST_LOGS('/auditarchive')) AS T WHERE FILE LIKE 'db2audit.db.sample.log.0.200604%' FILENAME --------------------------------------- ... db2audit.db.sample.log.0.20060418235612 db2audit.db.sample.log.0.20060419234937 db2audit.db.sample.log.0.20060420235128
- From this output, the SECADM observes that the necessary logs should be in the db2audit.db.sample.log.20060419234937 file. The log was taken at the end of the business day on April 19, 2006.
- This is used as input to the SYSPROC.AUDIT_DELIM_EXTRACT stored
procedure. The arguments passed into the procedure are:
- character delimiter (default),
- output path,
- path to the archived audit logs,
- the filename filter to determine what files are extracted from,
- the status for each category to be extracted, in this case the only category is EXECUTE.
CALL SYSPROC.AUDIT_DELIM_EXTRACT( '', '', '/auditarchive', 'db2audit.db.sample.log.0.20060419234937', 'category execute' )
- The audit data is now in delimited files. The SECADM will load
the audit data from the EXECUTE category into the AUDITDATA.EXECUTE
table. The table can be created by executing the following:
db2 CONNECT TO sample db2 SET CURRENT SCHEMA AUDITDATA db2 -tvf sqllib/misc/db2audit.ddl
- Next, load the data from execute.del to the AUDITDATA.EXECUTE
table. The do this run the following command:
db2 LOAD FROM FILE execute.del OF DEL MODIFIED BY LOBSINFILE INSERT INTO AUDITDATA.EXECUTE
- The SECADM now has all the audit data in the audit tables located
within the AUDITDATA schema. This data can now be analyzed to find
the particular statement the auditors are interested in.Note: Even though the auditors are only interested in a single SQL statement, multiple statements from the unit of work may need to be examined in case they have any impact on the statement of interest.
- In order to replay the statement, the following actions must be
taken:
- The exact statement issued must be determined from the audit record.
- The user who issued the statement must be determined from the audit record.
- The exact permissions of the user at the time they issued the statement must be re-created, including any LBAC protection.
- The compilation environment must be reproduced, by using the compilation environment column in the audit record in combination with the SET COMPILATION ENVIRONMENT statement.
- The exact state of the database at the time the statement was issued must be re-created.
Note: So as not to disturb the production system, any restore of the database and replay of the statement should be done on a secondary database system. - The SECADM would need to roll forward to the time the statement will start executing. The
statement local start time (local_start_time) is part of the EXECUTE audit record. Using the
following EXECUTE audit record as an
example:
The rollforward statement would look like this:timestamp=2006-04-10-13.20.51.029203; category=EXECUTE; audit event=STATEMENT; event correlator=1; event status=0; database=SAMPLE; userid=smith; authid=SMITH; session authid=SMITH; application id=*LOCAL.prodrig.060410172044; application name=myapp; package schema=NULLID; package name=SQLC2F0A; package section=201; uow id=2; activity id=3; statement invocation id=0; statement nesting level=0; statement text=SELECT * FROM DEPARTMENT WHERE DEPTNO = ? AND DEPTNAME = ?; statement isolation level=CS; compilation environment= isolation level=CS query optimization=5 degree=1 sqlrules=DB2 refresh age=+00000000000000.000000 schema=SMITH maintained table type=SYSTEM resolution timestamp=2006-04-10-13.20.51.000000 federated asynchrony=0; value index=0; value type=CHAR; value data=C01; value index=1; value type=VARCHAR; value index=INFORMATION CENTER; local_start_time=2006-04-10-13.20.51.021507;
ROLLFORWARD DATABASE sample TO 2006-04-10-13.20.51.021507 USING LOCAL TIME AND COMPLETE
- The compilation environment needs to be set as well. The compilation
environment variable can be set by the SET COMPILATION ENVIRONMENT
statement. The SECADM, running as the user who issued the statement,
can now replay the statement as found in statement text with any input
variables that are provided in the statement value data elements.
Here is a sample program in C embedded SQL that will set the COMPILATION
ENVIRONMENT and replay the SELECT statement the auditors want to analyze:
EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS BLOB(1M) hv_blob; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE c1 CURSOR FOR SELECT COMPENVDESC FROM AUDITDATA.EXECUTE TIMESAMP= '2006-04-10-13.20.51.029203'; EXEC SQL DECLARE c2 CURSOR FOR SELECT * FROM DEPARTMENT WHERE DEPTNO = 'C01' AND DEPTNAME = 'INFORMATION CENTER'; EXEC SQL OPEN c1; EXEC SQL FETCH c1 INTO :hv_blob; EXEC SQL SET COMPILATION ENVIRONMENT :hv_blob; EXEC SQL OPEN c2; .... EXEC SQL CLOSE c1; EXEC SQL CLOSE c2;