Monitoring Changes to Db2 Data with SIEM Agent

Overview

These instructions describe a sample setup of SIEM Agent that enable monitoring access and modifications to Db2 database files.

Example Scenario

This example uses the following scenario:

  1. We will monitor access to a physical file that contains the company’s payroll.

  2. The file is journaled.

  3. The file contains the following fields: EMPLOYEE; SALARY; SSN; DEPARTMENT.

Configuration Overview

When a file is journaled, each modification to the file causes a journal entry to be created that describes the modification. The journal entry type reflects the type of action that was performed on the file, and the journal entry fields describe details of the action, such as the data that was inserted, or the job that performed the action. SIEM Agent allows you to monitor the journal and convert the journal entries into events that are sent out.

Setup

  1. In SIEM Agent, add a new Event Source as follows:

    1. Name:                   PAYROLL

    2. Description:        Monitors changes to the Payroll file

    3. Type:                     *JRN

    4. Facility:                 4             (example)

    5. Active:                  1

    6. Default Output: Add one or more Outputs

    7. Journal: Configure Object, Library, and ASP Group to the journal that is used to journal access to the Payroll file.

  1. Add Event Descriptions

    1. F MC (Member added)

      1. Add Event Description

        1. Journal Code:     F, Entry Type: MC

        2. Description:        Member added

        3. Active:                  1

        4. Event Class ID:  *NAME

        5. Severity:              5 (example)

        6. Class:                   FIL (example)

      2. Add Field

        1. Name:                  MEMBER

        2. Description:        Name of new member

        3. Subtype:              0

        4. Offset:                  20, Length: 10, Data Type: A

        5. CCSID:                   0

      3. Define Event Text

        1. In the FMC Event Description, change the Event Text as follows:

          1. Reason: Member added

          2. Message: &*CURUSR& added file member &MEMBER& to the Payroll file

    2. Repeat the above steps, but with the differences noted here:

      1. F MD = Member deleted

        1. Fields: (none)

        2. Event Text

          1. Reason: Member deleted

          2. Message: &*CURUSR& deleted file member &*JRNMBR& from the Payroll file

      2. F OP = File opened

        1. Fields: (none)

        2. Event Text

          1. Reason: File opened

          2. Message: &*CURUSR& opened the Payroll file

      3. R DL = Record deleted

        1. Fields: (none)

        2. Event Text

          1. Reason: Record deleted

          2. Message: &*CURUSR& deleted record number &*COUNTRRN& from the Payroll file.

      4. R PT = Record added

        1. Fields:

          1. Add Fields specific to the fields (columns) that exist in the PAYROLL file and that you want to use in the Event Text. In this example, add one field to the configuration for each of the file fields EMPLOYEE, SALARY, SSN and DEPARTMENT.

          2. Set Subtype to 0.

          3. You can use the Display File Fields (DSPFFD) command to display the fields that exist in the file. Note that in SIEM Agent, field offsets start at 0. The DSPFFD output shows offsets (“buffer position”) as starting from 1. If DSPFFD shows a field as starting at buffer position 100, the Field in SIEM Agent has to be configured as having offset 100–1 = 99.

        2. Event Text

          1. Reason: Record added

          2. Message: Create a message text template using the file-specific Fields that you defined. For example: &*CURUSR& added a record to Payroll. Values are: Employee name-&EMPLOYEE &Salary-&SALARY& SSN-&SSN& Department-&DEPARTMENT&

      5. R PX = Record added using a Relative Record Number

        1. Copy this from the R PT Event Description so you can reuse the Fields defined there, and just update the Entry Type and the Description. Journal entry types R PT and R PX are the two varieties of journal entry for “record added” Events.

      6. R UB = Record updated—pre-update values

        1. Copy this from the R PT Event Description so you can reuse the Fields defined there.

        2. Event Text:

          1. Reason: Record updated

          2. Message: &*CURUSR& changed Payroll for Employee &EMPLOYEE&. Values before update were: Salary-&SALARY& SSN-&SSN& Department-&DEPARTMENT&

      7. R UP = Record updated—contains post-update values

        1. Copy this from the R UB Event Description so you can reuse the Fields defined there.

        2. Event Text

          1. Reason: Record updated

          2. Message: &*CURUSR& changed Payroll for Employee &EMPLOYEE&. Values after update are: Salary-&SALARY& SSN-&SSN& Department-&DEPARTMENT&

Monitoring Read Access to Files

The default settings for journaling a file will create entries only when the file is modified, but not when it is read. If you want to additionally set up monitoring for read accesses to a file, perform the following additional steps.

  1. Configure the journaling of the file to include read accesses:

    CHGJRNOBJ OBJ((YOURLIB/YOURFILE *FILE)) ATR(*OMTJRNE) OMTJRNE(*NONE)

  2. Note that if the same job repeatedly opens and closes the same file, only the first few file-opens for that file and job will be logged in the journal. This is normal Db2 behavior.

General Notes

  1. Even though you enter the name of Event Descriptions in two fields, for example; “F MC”, in the list of Event Descriptions, the name is displayed as a single field, for example; “FMC”.

  2. This example only covers a subset of journal codes. For production usage you should configure additional journal codes to reflect activities such as Clear Member, etc. Each Event Description for monitoring a *JRN Event Source must correspond to an IBM i journal code. The journal codes are documented here: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzaki/rzakijournalfinderall2.htm.

  3. To define Fields that correspond to file fields with a data type other than Character/Alphanumeric, see the following help topic: Making fields for Journal Entry Formats.

  4. If multiple files are journaled to the same journal, you can use Rules to distinguish between Events from different files and only send out Events for particular files, or to customize Event Text to reflect the file name.

  5. To be able to output the pre-update changes, the file journaling will need to be configured to include pre-change data. Use the following command:

    CHGJRNOBJ OBJ((YOURLIB/YOURFILE *FILE)) ATR(*IMAGES) IMAGES(*BOTH)

  6. If you have a requirement for creating reports of database modifications, Powertech Database Monitor for IBM i offers powerful ways to monitor database activity and create reports of it.