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's an easy way to implement a tracking system on an OS/400 database -- without any significant programming on your part. This technique takes advantage of the OS/400 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 Query/400 (or any other database tool that you have in your toolbox) to create more useable 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.
Rich Loeber is president of Kisco Information Systems Inc., in Saranac Lake, NY. The company is a provider of various security products for the iSeries market.
