Feature Article

Forensic Analysis - Easily Track Changes to your Database

By Dan Riehl - SecureMyi.com

Who added that new vendor to the vendor file? Who changed that purchase order dollar amount? And who changed that employee's hourly pay? When did it happen, and how was it accomplished? In this next article in the forensic analysis series, I examine how to audit and report on database changes. I examine the topic of DB2 for i database journaling and how you can extract meaningful data out of a database journal for your forensic analysis.

Auditing Revisited

I encourage you to review the previous articles in this series in the SecureMyi Newsletter.

• Forensic Analysis Using QAUDJRN, Part 1: Auditing/Reporting the Use of CL Commands
• Forensic Analysis Using QAUDJRN, Part 2: User Activity
• Forensic Analysis Using QAUDJRN, Part 3: Access to Sensitive Objects

And Now, Detecting Database Changes

IBM i provides two methods for collecting information on database record changes, additions, and deletions. You can write your own database trigger programs (customized for each required file), or you can use the database record-level journaling support built into the operating system. Given the complexities of database record changes when commitment control is active (i.e., with Commit and Rollback operations), trigger programs can be devilishly difficult to write correctly. In addition, the database record audit trail created by an inhouse-written trigger program may be considered "suspect" by your compliance auditors. IBM i database journaling can provide an audit trail of each record add, change, and delete operation made for the journaled file. Database journals have long been a trusted accounting of record-level activity. However, the journaling environment must be configured in a manner consistent with your ability to extract the important information about record additions, changes, and deletions.

Creating the Journal for Forensic Analysis

Several enhancements to the database journaling support have been made over the last several releases of the operating system. Some of these enhancements relate to the size and content of database journal entries.

When you want to perform forensic analysis of journaled database record changes, you do NOT want to use "skinny headers" as described in the IBM Redbook Journaling – Journal Receiver Diet tip 2: Consider using skinny headers. You implement skinny headers by shrinking the size of the journal entry headers, which can eliminate information such as the job name, user name, and program name that made the change. You can implement skinny headers by specifying on the CRTJRN or CHGJRN command a receiver size option of RCVSIZOPT(*MINFIXLEN) or by using the Fixed Length Data (FIXLENDTA) parameter to specify a value, such as FIXLENDTA(*USR). When a record is added to, changed, or deleted from a database file, you want to be able to report on ALL pertinent information, including the job, user and program used to make the change. Therefore, you do NOT want to use skinny headers.

Another enhancement in journaling support lets you specify that the journal-entry–specific data be minimized. To shrink the size of the entry-specific data, you use the CRTJRN/CHGJRN parameter MINENTDTA. To reiterate: When you need to be able to perform forensic analysis of the journal entry data, you need all the available journal-entry–specific data to be intact. Therefore, the MINENTDTA parameter needs to be specified as *NONE, meaning that complete entry-specific data will be present in each journal entry.

Read More . .