Replaying past database activities

Example

1. 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

2. 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.
3. 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' )

4. 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

5. 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

6. 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.
7. 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.
8. 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:

   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;

   The rollforward statement would look like this:

   ROLLFORWARD DATABASE sample 
   TO 2006-04-10-13.20.51.021507 
   USING LOCAL TIME AND COMPLETE

9. 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;