Forensic Analysis - Easily Track Changes to your Database

By Dan Riehl

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 Forensic Analysis Using QAUDJRN, Part 1: Auditing and Reporting the Use of CL Commands and Forensic Analysis Using QAUDJRN, Part 2: User Activity and Forensic Analysis Part 3: Access to Sensitive Objects from the SecureMyi Security Newsletter.

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.

To ensure that the database journal is created for detailed forensic analysis, use the CRTJRN command as shown here. If the journal already exists, use the CHGJRN command to change the RCVSIZOPT and MINENTDTA parameters as shown.

CRTJRN JRN(Library-Name/Journal-Name) 
    JRNRCV(Library-Name/Receiver-Name) 
    RCVSIZOPT(*RMVINTENT *MAXOPT3) 
    MINENTDTA(*NONE)

Turning ON Journaling for your Database Files

To track changes to a database file (i.e., add, change, and delete record events), the file must be configured for journaling. To start the journaling process for a file, use this command:

STRJRNPF FILE(PAYLIB/PAYROLL)
    JRN(JRNLIB/PRODJRN)
    IMAGES(*BOTH)
    OMTJRNE(*OPNCLO)

In this example, you start journaling the PAYROLL file with IMAGES(*BOTH), which specifies that the system will record both before and after images of changed records. You also omit file OPEN and CLOSE operations with the parameter OMTJRNE(*OPNCLO). You don't need these optional journal entries to analyze database record changes. As soon as the STRJRNPF command is run, the record-level activity (i.e., add, change, delete record events) will start being collected into the journal.

Extracting and Reporting on Forensic Data from the Journal

Once you've started the journaling process for a file, you can easily report on the database record changes, additions, and deletions by using the Compare Journal Image (CMPJRNIMG) command. As long as you haven't minimized the journal data and are journaling *BOTH before and after images of record changes, you can know what record characters were changed, the old and new value of the characters, who made the change, and when and how the change was made. CMPJRNIMG is the IBM-supplied CL command to extract and report journaled database record–level changes, record additions and deletions. A prerequisite to using the command is that you must be journaling the database file with *BOTH "Before" and "After" images, as shown earlier in the STRJRNPF command. You also can't use the CMPJRNIMG command on journals that have minimized entry-specific data (e.g. CRTJRN . . . (MINENTDTA(*FILE)).

To list record changes, additions and deletions, you use the command:

CMPJRNIMG FILE(Library-name/File-name)

The command has several other parameters that let you limit the scope of the record changes you want to view. For example, you can select to view changes only by a particular user, changes only to a particular record number, or changes only for a given date/time range.

When you run the CMPJRNIMG command, the spooled file QPCMPIMG is created and contains the report of record adds, changes and deletes. Figure 1 below shows a snippet of that report. You can see in the header that the relative record number RRN 1 was updated(UP) by user BOB, who was using the workstation device QPADEV0008. Bob was running program QDZTD00001, which is the program used by IBM's Data File Utility (DFU) program. You also see the date and time of the change.

Figure 1 - Output of CMPJRNIMG

Sequence  
 Number  Type  RRN Job        User   Number Program    Profile  Date     Time 
      6   UP     1 QPADEV0008 BOB    027329 QDZTD00001 BOB     11/21/13  9:09:41
 
 Before image . . . . . :  'Thomas                        Phil 
 After image  . . . . . :  'Brinker                       Phil 
 Change . . . . . . . . :   *******   

 Before image . . . . . :  '          191 Beacon St. Boston, MA 02215 
 After image  . . . . . :  '          191 Beacon St. Boston, MA 02215 
 Change . . . . . . . . :   

 Before image . . . . . :  '                                  ' 
 After image  . . . . . :  '                                  '

In the body of the entry, you notice that the "Before image" of the record is listed, and the "After image". Asterisks indicate which bytes have been changed. If we were to look at the record layout, we'd see that the positions changed represent the last name of the payroll employee from "Thomas" to "Brinker." Since there are no more asterisks, we know that the other fields in the record were not changed.

With the CMPJRNIMG command and correct database file journaling in place, you have the basis of your own simple reporting tool on database changes.

Commercial Software Packages for Advanced Options

There are several commercial software packages for reporting on database changes. These packages are typically feature rich and provide advanced forensic capabilities. Good commercial packages will support numerous reporting options, such as eMail, Text Message, Excel, PDF, SYSLOG and spooled files. They will also support real-time alerts for critical changes and be able to handle advanced selection criteria, such as, "send me a text message alert when someone changes a price in the price file by more than 10 percent" or "send me an alert when an IT staff member changes a record in any production file."

If your requirements go beyond the simple use of CMPJRNIMG, I encourage you to evaluate some of these commercial products against your requirements and pricing constraints.

About the Author

Dan Riehl is the Editor of the SecureMyi Security Newsletter and a Security Specialist for
the IT Security and Compliance Group

Dan performs IBM i security assessments and provides security consulting, remediation, forensic evaluations, and other customized security services for his clients. He also provides training in all aspects of IBM i security and other technical areas through The 400 School, Inc.

Dan Riehl on LinkedIn