Retrieving specific audit journal data by SQL table functions

As part of the latest round of latest Technology Refreshes for IBM i 7.4 and 7.3 are four SQL table functions that allow me to be able to easily get the following information from the system audit journal:

• Authority failures (journal entry type AF)
• Authority changes (CA)
• Ownership changes (OW)
• Password entries (PW)

The syntax for all of these table functions is the same:

01 SELECT * FROM TABLE( 02 SYSTOOLS.AUDIT_JOURNAL_??( 03 STARTING_RECEIVER_NAME => starting-receiver-name, 04 ENDING_RECEIVER_NAME => ending-receiver-name, 05 STARTING_TIMESTAMP => starting-timestamp, 06 ENDING_TIMESTAMP => ending-timestamp, 07 USER_NAME => user-name, 08 JOB => job, 09 PROGRAM => program, 10 STARTING_SEQUENCE => starting-sequence, 11 ENDING_SEQUENCE => ending-sequence) 12 ) ;

When you use these table functions the "??", on line 2, is replaces by the journal entry type. The parameters are the same for every function and these are:

• STARTING_RECEIVER_NAME:  Name of starting journal receiver. Default is CURAVLCHN.
• ENDING_RECEIVER_NAME:  Name of ending journal receiver. Default is *CURRENT.
• STARTING_TIMESTAMP:  Starting timestamp. Default is the equivalent of CURRENT_DATE – 1 DAY
  Note: Starting timestamp and starting sequence cannot be used in the same statement.
• ENDING_TIMESTAMP:  Ending timestamp. Default is the equivalent of CURRENT_TIMESTAMP.
• USER_NAME:  User profile. Default is all.
• JOB:  Job name. Default is all.
• PROGRAM:  Name of program. Default is all.
• STARTING_SEQUENCE:  Sequence number from the journal. Default is *FIRST.
  Note: Cannot be used in conjunction with starting timestamp.
• ENDING_SEQUENCE:  Ending sequence number. Default is *LAST.

While the information you would expect to be returned for each of these journal types is different, there are twenty columns that are common to all of these results from these table functions. I am only going to list those I found useful, for the others refer to IBM's documentation that I have provided a link to at the bottom of this post.

• ENTRY_TIMESTAMP:  Timestamp of when entry was written to the journal.
• SEQUENCE_NUMBER:  Sequence number of the journal entry.
• USER_NAME:  User name.
• QUALIFIED_JOB_NAME:  Full job name.
• PROGRAM_LIBRARY:  Name of the library the program in PROGRAM_NAME is found. If no program name then this will be *NONE.
• PROGRAM_NAME:  Name of the program that caused the journal entry to be written. Will be *NONE if a program does not apply to this entry type.

Do notice that these table functions are found in the library SYSTOOLS.

 

Authority failures (AF)

"AF" journal entries are for authority failures. The can be retrieved using the AUDIT_JOURNAL_AF table function. In my example I want to show all of authority failures that my user profile has done in the past seven days:

01 SELECT ENTRY_TIMESTAMP AS "When", 02 USER_PROFILE_NAME AS "Who", 03 QUALIFIED_JOB_NAME AS "Job", 04 VIOLATION_TYPE AS "V type", 05 VIOLATION_TYPE_DETAIL AS "V detail", 06 OBJECT_LIBRARY AS "Library", 07 OBJECT_NAME AS "Object", 08 OBJECT_TYPE AS "Type" 09 FROM TABLE( 10 SYSTOOLS.AUDIT_JOURNAL_AF( 11 STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS, 12 USER_NAME => 'SIMON') 13 ) ;

Lines 1 – 8: I am only interested in some of the columns from the results. I think their column names explain the data contained within them. And I have given the column headings that, I think, show better in my results.

Lines 9 – 13: The only two parameters I am passing to the table function is the start time, 7 days before this time, and my profile. All of the other parameters will be their default values.

The results show that I have two "AF" journal entries:

When Who Job -------------------------- ----- ------------------------ DDDD-DD-DD 18:18:32.665200 SIMON 711878/QUSER/QZDASOINIT DDDD-DD-DD 19:31:56.044048 SIMON 714440/SIMON/QPADEV0002 V type V detail ------ ---------------------------- A Not authorized to object K Special authority violation Library Object Type ------- --------- ---- QSYS QAUDJRN *JRN <NULL> DSPSECAUD *CMD

A full list of all the violation types you will find in the IBM documentation linked to at the bottom of this post.

 

Authority changes (CA)

The AUDIT_JOURNAL_CA table function returns the journal entries for authority changes, journal type "CA". This is my example statement to list all of the objects I changed the authority on in the past week:

01 SELECT OBJECT_LIBRARY AS "Obj lib", 02 OBJECT_NAME AS "Object", 03 OBJECT_TYPE AS "Obj typ", 04 OBJECT_ATTRIBUTE AS "Obj attr", 05 COMMAND_TYPE AS "Cmd typ", 06 USER_PROFILE_NAME AS "Prf name", 07 OBJECT_EXCLUDE AS "Excl", 08 OBJECT_OPERATIONAL AS "Opr", 09 OBJECT_MANAGEMENT AS "Mgt", 10 OBJECT_EXISTENCE AS "Exist", 11 OBJECT_ALTER AS "Alter", 12 OBJECT_REFERENCE AS "Ref", 13 DATA_READ AS "Read", 14 DATA_ADD AS "Add", 15 DATA_UPDATE AS "Upd", 16 DATA_DELETE AS "Del", 17 DATA_EXECUTE AS "Exe" 18 FROM TABLE( 19 SYSTOOLS.AUDIT_JOURNAL_CA( 20 STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS, 21 USER_NAME => 'SIMON' ) 22 ) ;

Lines 1 – 17: The columns I want to show in my results. I find that the column names do describe well the data that the column contains.

Lines 18 – 22: I am only interested in objects that were changed in the past seven days by myself.

The results show that there are three objects I changed the authority on:

Obj lib Object Obj typ Obj attr Cmd typ ------- --------- ------- -------- ------- MYLIB TESTPGM *PGM RPGLE REPLACE MYLIB TEST *USRSPC <NULL> GRANT MYLIB TESFILE99 *FILE PF-DTA REPLACE Prf name Excl Opr Mgt Exist Alter Ref -------- ---- --- --- ----- ----- --- *PUBLIC YES NO NO NO NO NO *PUBLIC NO YES YES YES YES YES *PUBLIC NO YES NO NO NO NO Read Add Upd Del Exe ---- --- --- --- --- NO NO NO NO NO YES YES YES YES YES YES NO NO NO YES

 

Ownership changes (OW)

The object ownership changes, journal entry "OW", can be seen using the AUDIT_JOURNAL_OW table function.

But before I use this table function I need to change the owner of an object. In this example I am going to use a physical file called TESTFILE99. I can check who owns an object using the OBJECT_PRIVILEGES view:

SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT, OBJTYPE,USER_NAME,OBJ_AUTH,OWNER FROM QSYS2.OBJECT_PRIVILEGES WHERE SYS_DNAME = 'MYLIB' AND SYS_ONAME = 'TESTFILE99' ;

Which shows me I am the object owner:

LIBRARY OBJECT OBJTYPE USER_NAME OBJ_AUTH OWNER ------- ---------- ------- --------- -------- ------ MYLIB TESTFILE99 *FILE *PUBLIC *EXCLUDE SIMON MYLIB TESTFILE99 *FILE SIMON *ALL SIMON

I then change the object owner to be the QPGMR profile:

CHGOBJOWN OBJ(MYLIB/TESTFILE99) OBJTYPE(*FILE) NEWOWN(QPGMR)

When I run the same SQL statement again I can see that the object owner has changed:

LIBRARY OBJECT OBJTYPE USER_NAME OBJ_AUTH OWNER ------- ---------- ------- --------- -------- ------ MYLIB TESTFILE99 *FILE *PUBLIC *EXCLUDE QPGMR MYLIB TESTFILE99 *FILE QPGMR *ALL QPGMR

Using the AUDIT_JOURNAL_OW table function I can see that this change was made:

01 SELECT ENTRY_TIMESTAMP,JOB_USER, 02 OBJECT_LIBRARY,OBJECT_NAME,OBJECT_TYPE, 03 PREVIOUS_OWNER,NEW_OWNER 04 FROM TABLE ( 05 SYSTOOLS.AUDIT_JOURNAL_OW ( 06 STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS) 07 ) 08 WHERE OBJECT_LIBRARY IN ('MYLIB') ;

Lines 1 – 3: The columns that will appear in my results.

Lines 4 – 7: Definition of the table function returning any ownership changes that have happened in the last week.

Line 8: With this where clause I am limiting my results to only those objects in my library.

There has only been one change in my library in that date range:

JOB_ OBJECT_ OBJECT ENTRY_TIMESTAMP USER LIBRARY _NAME -------------------------- ----- ------- ---------- DDDD-DD-DD 14:30:53.361904 SIMON MYLIB TESTFILE99 OBJECT PREVIOUS NEW_ _TYPE _OWNER OWNER ------ -------- ----- *FILE SIMON QPGMR

 

Password entries (PW)

The last of the table functions, AUDIT_JOURNAL_PW, lists all of the password journal entries, "PW".

The SQL statement below will return the user profiles that had issues signing on.

01 SELECT ENTRY_TIMESTAMP AS "Date/time", 02 VIOLATION_TYPE AS "Code", 03 VIOLATION_TYPE_DETAIL AS "Violation", 04 AUDIT_USER_NAME AS "User" 05 FROM TABLE ( 06 SYSTOOLS.AUDIT_JOURNAL_PW ( 07 STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS) 08 ) 09 ORDER BY 3,1 ;

Lines 1 – 4: I am only interested in the date and time someone tried to signon, the type of violation, and the user profile they used.

Lines 5 – 8: Definition of the table function looking for these journal entries from the past week.

Line 9: I am being lazy here with the ORDER BY statement. Rather than giving the column names I have given those columns' positions in the results instead. VIOLATION_TYPE_DETAIL is the third column, and ENTRY_TIMESTAMP is the first.

The results shown below are just a selection of those returned:

Date/time Code Violation User -------------------------- ---- ------------------- ---------- DDDD-DD-DD 15:33:52.311568 R Attempted signon... M********* DDDD-DD-DD 13:04:35.883392 P Password not valid J********* DDDD-DD-DD 14:52:36.206512 U User name not valid B*********

The three dots in the first result show that the value is longer than shown. I did this as the results are limited by the width if the body of this post.

 

Four very useful table functions that I am sure I will use on a regular basis, not just at audit time.

 

You can learn more about this from the IBM website:

• Common columns
• AUDIT_JOURNAL_AF table function
• AUDIT_JOURNAL_CA table function
• AUDIT_JOURNAL_OW table function
• AUDIT_JOURNAL_PW table function

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.