Search For The Guilty Party

By Rich Loeber

Part of computer security is prevention, and a good access prevention policy along with good controls will go a long way to protect your data from improper access and use. But, people with proper authorization make incorrect data changes that can wreak havoc on a system. At times like that, it is sometimes nice to be able to backtrack and see who did what and when they did it.

Here is an easy way to implement a tracking system on an IBM i database without any significant programming on your part. This technique takes advantage of the IBM i database audit journal features.

First, you have to create a Journal Receiver and a Journal. In this example, we'll set up to track data changes to a file named CUSTMSTR in library MYLIB. Use the following commands to create these objects:

CRTJRNRCV JRNRCV(MYLIB/CUSTMSTR)

CRTJRN JRN(MYLIB/CUSTMSTR) JRNRCV(MYLIB/CUSTMSTR)

Now, you can activate tracing on your physical file by processing the following Start Journal Physical File command:

STRJRNPF FILE(MYLIB/CUSTMSTR) JRN(MYLIB/CUSTMSTR) IMAGES(*BOTH)

At this point, the database journal is active for your file and all activity against that file will be tracked and captured in the Journal Receiver. Now, use your application program to work with your database file. Make some file inquiries and also perform some record changes. When you're done, use the following command to take a look at the file tracking information:

DSPJRN JRN(MYLIB/CUSTMSTR)

You will see several different forms of tracking information displayed. Each time the file is opened, closed, read and updated, an entry will be inserted. The first few entries just track the fact that journaling has been started for the file. After those, you will see the results of the testing transactions that you performed.

To review file changes, look for the audit records with type codes "UB" and "UP". The UB record shows you a record image BEFORE an update was posted and the UP record shows the record image AFTER an update was posted.

In your search for the guilty party, the DSPJRN command leaves a lot to be desired. To give you a better way to look at before/after data changes, you can transfer the DSPJRN information into a database file and then use your favorite database tool to create more usable information.

To create the database for your query tool, run the following form of the DSPJRN command:

DSPJRN JRN(MYLIB/CUSTMSTR) OUTPUT(*OUTFILE) +
OUTFILFMT(*TYPE4) OUTFILE(MYLIB/CUSTMSTRJ)

In this sample, the database version of the Journal now resides in the database file named CUSTMSTRJ. Use your query tool to select records with type codes UB and UP (the field name to select on is JOENTT). If your database file record length is exceptionally long, you may have to parse the record data information to get at what you are looking for, but the report generated should point you to all of the record changes posted to the file and you should be able to sort out who did what and when they did it.

If you prefer an easier way to handle all this, take a look at iFileAudit from Kisco Information Systems. iFileAudit will automate this entire process and show you file updates on a field by field basis telling you all of the who, how, when and where information as you search for the guilty party.

If you have any questions about anything in this tip, just ask me and I'll give you my best shot. My email address is rich at kisco.com. All email will be answered.