IBM i Global

 View Only

  • 1.  leave a log trail when reading physical files on IBMi

    Posted Mon April 22, 2024 10:53 AM

    The customer wants to keep track of who read which field of which record and when, for audit purposes.
    Customers use COBOL on IBMi, and in some cases do not use SQL.

    1. Is it possible to use the audit journal or DB journal to check which records were viewed by which users?
    2. 2. Is it possible to use the audit journal or DB journal to check who viewed which fields of which records?

    If the audit journal or DB journal cannot record, is there a way to obtain logs using IBM's SW Solution or ISV Solution?

    thank you.



    ------------------------------
    Akemi Kamiogi
    ------------------------------


  • 2.  RE: leave a log trail when reading physical files on IBMi

    Posted Mon April 22, 2024 11:02 AM

    Hi Akemi,

    In the journals normally you have the information about changed data, not about the read operations.

    In the audit journal you can record that a file has been read (ZR journal entries, but not the exact record.

    Also, before implement something like this is important to understand the use of the file.

    What is "view a record"? because if you have a batch process that reads all the records (but nobody views the records) this can generate a large amount of audit data. Those records has been read, not modified but nobody viewed them.

     

    In some cases, a trigger can be an option to record the viewed records.

     

    There is some software also from ISV's that work on this with different approaches.

     

    Best Regards

     

    -------------------------

    Andreu Rul

    CTO

    Telf: +34 663 817 144

     

    Interfaz de usuario gráfica, Aplicación Descripción generada automáticamente

     

    signature_256849821

     




    Original Message


  • 3.  RE: leave a log trail when reading physical files on IBMi

    Posted Mon April 22, 2024 03:51 PM

    Trigger was the clue

    ADDPFTRG FILE(MYLIB/MYFILE) TRGTIME(*BEFORE)      
             TRGEVENT(*READ) PGM(TRIGGERLIB/MYTRIGGER)

    Basically you could log every row read using this.

    You have to write the program TRIGGERLIB/MYTRIGGER.

    Creating trigger programs - IBM Documentation

    In theory a trigger program could cast an error is someone is reading something they shouldn't.  However many legacy operations often assume they'll never be an error on a read, or, if there is an error, they assume the error must be some specific condition (like key not found).  These won't tolerate a trigger program casting an error.

    See also:  Row and column access control (RCAC)

    https://www.ibm.com/docs/en/i/7.5?topic=administration-row-column-access-control-rcac



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 4.  RE: leave a log trail when reading physical files on IBMi

    Posted Tue April 23, 2024 10:38 AM

    I'd be very cautious about the usage of Read Triggers.  Not only do they add the performance overhead of a program call to each read operation, their presence on a physical file or table forces the Classic Query Engine to be used instead of the SQL Query Engine.



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 5.  RE: leave a log trail when reading physical files on IBMi

    Posted Fri August 16, 2024 10:18 AM

    I support this out of experience. 

    Akemi, 

    A good way to look for is your FULL OPENS Stats in PEX / PDI & Table Read Stats in System Tables to check per interval to give you some good sense of what you are dealing with aswell. 

    (hopefully it's within thresholds but a good "6 sense feeling" it will give you not to continue down that path)



    ------------------------------
    Marius le Roux
    Owner
    MLR Consulting
    ------------------------------

    Original Message


  • 6.  RE: leave a log trail when reading physical files on IBMi

    Posted Mon April 22, 2024 08:42 PM
      |   view attached

    Dear Akemi

    With the  principle of preventing an issue is better than treating it, you may want to ask your customer whether it would be more sensible to prevent some users (based on their group profile would be more convenient) from reading some data records of a file based on the value(s) of the field(s) rather than keeping a read log (using a trigger).  If your customer agrees this is a better way to go, then Row And Column Access Control (RCAC) in DB2 for i (available since release 7.2) is the tool you should use. 

    There is a Redpaper on RCAC you can study:  Row and Column Access Support in IBM DB2 for i at  https://redbooks.ibm.com/abstracts/redp5110.html      

    Also keep in mind that using trigger (Robert's suggestion) and RCAC will increase the workload to the system according to the frequency of data access to the file(s). 

    I attach an additional technical presentation on RCAC herewith so you can find more details on how to use it. Let us know when you have more question.



    ------------------------------
    Satid S
    ------------------------------

    Attachment(s)

    pdf
    DB2i-RCAC-May2018.pdf   2.86 MB 1 version
    Original Message


  • 7.  RE: leave a log trail when reading physical files on IBMi

    Posted Tue April 23, 2024 04:47 AM

    Thank you for your answer.
    Is there a way to obtain the "Read" access audit log for each field of the read record?



    ------------------------------
    Akemi Kamiogi
    ------------------------------

    Original Message


  • 8.  RE: leave a log trail when reading physical files on IBMi

    Posted Tue April 23, 2024 05:17 AM

    I totally agree with Satid - preventing an issue is better than treating it. RCAC allow you to control data access at record level (row control) and mask field (access column). 

    Nicolae



    ------------------------------
    Nicolae Chirea
    System Architect
    Seidor Iberia
    Madrid/Spain
    ------------------------------

    Original Message