Security and Systems Management Newsletter for the IBM i             September 24, 2014 - Vol 4, Issue 16
Security Training from

Security software from Powertech

Skyview Partners

Security Training from The 400 School

Feature Article

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 in the SecureMyi 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.

Read More . .

In This Issue

Featured Article - Track Databse Changes

Security Shorts - Libraries High on *LIBL

Industry News and Calendar

Security Resources

Quick Links

Search Security Site for IBM i and i5/OS

SecureMyi Website

Security Training from The 400 School

SecureMyi Newsletter Home/Archives

Our Newsletter Sponsors

Platinum Sponsor

    The 400 School, Inc

Gold Sponsor


    Skyview Partners, Inc

Silver Sponsor

    Cilasoft Security Solutions

IBM i Security Resources

IBM i Security Videos - SecureMyi

SecureMyi Newsletter Archives

Search Security for IBM i

IBM i Security Ref - 6.1

IBM i Security Ref - 7.1


QAUDJRN Entry Layouts

RedBook - Security Guide IBM i

Open Security Foundation - DataLoss DB

National Vulnerability Database - NIST

PCI Data Security Standard


HIPAA Resources

HITECH Enforcement

CISSP - Certification

Follow SecureMyi on Twitter

Follow SecureMyi on YouTube

Software from Cilasoft

Security software from Powertech
Security news and Events

Live Security Related Webcasts and Training for IBM i

September Events

Coffee with Carol: What's New in V7R2 Security!
with Carol Woodbury

Live Webcast - Presented by Skyview Partners
Wednesday, Sep 24 10:00am CDT
More Information and Register to Attend

Live Hands-On - QAUDJRN Auditing and Forensic Analysis Workshop
with Dan Riehl

Training Workshop - Sep 25-26 - Presented by The 400 School, Inc.
Dan Riehl presents this 2-Day Live Online Hands-on Workshop.
More Information and Register to Attend

Live Hands-On - Expanded Control Language Programming Workshop
with Dan Riehl

Training Workshop - Sep 29-Oct 3 - Presented by The 400 School, Inc.
Dan Riehl presents this 5-Day Live Online Hands-on Workshop.
More Information and Register to Attend

October Events

COMMON 2014 Fall Conference & Expo
October 27 - 29
Hyatt Regency Indianapolis • Indianapolis, Indiana
More Information and Register to Attend

November Events

Live Hands-On - IBM i, iSeries System Administration and Control Workshop
with Dan Riehl

Training Workshop - Nov 3-7 - Presented by The 400 School, Inc.
Dan Riehl presents this 5-Day Live Online Hands-on Workshop.
More Information and Register to Attend

Live Hands-On - Security Audit and Vulnerability Assessment Workshop
      for IBM i, iSeries AS/400 with Dan Riehl

Training Workshop - Nov 11-14 - Presented by The 400 School, Inc.
Dan Riehl presents this 4-Day Live Online Hands-on Workshop.
More Information and Register to Attend

Skyview Partners

Security Training from The 400 School
Skyview Partners

Security Shorts

Danger - Any Libraries Higher than QSYS

By Dan Riehl -

If you, or your software provider, places a library higher than QSYS on the system library list, like ALTQSYS, make sure that the library authority is set to no higher than *PUBLIC AUT(*USE). This will restrict *PUBLIC users from placing new objects into the library. Also make sure that you secure the individual objects in the library with *PUBLIC AUT(*USE) for programs, commands and other static object types, and AUT(*CHANGE) or less for dynamic objects like database files and data areas.

Since we rely heavily on resolving object references using the job's library list, any object in a library ahead of QSYS can override the expected functioning of the operating system and your application software. In this respect, programs and commands can act as a Trojan Horse on your system.

Numerous 3rd party software vendors require a library ahead of QSYS, but do not secure the libraries with *PUBLIC AUT(*USE). Instead, they are mostly installed as *PUBLIC AUT(*CHANGE), or even *PUBLIC AUT(*ALL). Check with your vendor for their solution to the potential vulnerability they have introduced onto your system.

Also check that all libraries in the System Portion of the Library list are *PUBLIC AUT(*USE). An improperly created object in one of these libraries may be used in place of a production object in your application production libraries that are typically in the lower User Portion of the Library List.

Sponsored Links

IBM i, iSeries and AS/400
Security Services from SecureMyi

IT Security and Compliance Group

In Depth Security Assessment of IBM i
Upgrade to QSECURITY level 40 or 50
Forensic Research and Analysis
Audit Assistance and Remediation
Security Training for IT and Audit Staff
Software Selection & Configuration
Security and Systems Programming

Live Training from The 400 School, Inc

Customized IBM i (AS/400) Training -
    Presented Live at your offices

Live Online Hands-On Workshops

Security Training from The 400 School

Send your IBM i Security and Systems Management News and Events!           Send your Questions, Comments, Tips and Stories

Copyright 2014 -, all rights reserved | St Louis MO 63017