Reverting Changes to Object Authorities Using IBM i Audit Journal Data

WITH AuditData AS (
  SELECT
    ENTRY_TIMESTAMP,
    QUALIFIED_JOB_NAME,
    USER_PROFILE_NAME,
    OBJECT_NAME,
    OBJECT_LIBRARY,
    OBJECT_TYPE,
    TRIM(
      CASE WHEN PREV_OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_OPERATIONAL = 'YES' THEN '*OBJOPR ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_MANAGEMENT = 'YES' THEN '*OBJMGT ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_EXISTENCE = 'YES' THEN '*OBJEXIST ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_ALTER = 'YES' THEN '*OBJALTER ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_REFERENCE = 'YES' THEN '*OBJREF ' ELSE '' END ||
      CASE WHEN PREV_DATA_READ = 'YES' THEN '*READ ' ELSE '' END ||
      CASE WHEN PREV_DATA_ADD = 'YES' THEN '*ADD ' ELSE '' END ||
      CASE WHEN PREV_DATA_UPDATE = 'YES' THEN '*UPD ' ELSE '' END ||
      CASE WHEN PREV_DATA_DELETE = 'YES' THEN '*DLT ' ELSE '' END ||
      CASE WHEN PREV_DATA_EXECUTE = 'YES' THEN '*EXECUTE ' ELSE '' END
    ) AS Previous_Aut
  FROM TABLE (
    SYSTOOLS.AUDIT_JOURNAL_CA(STARTING_TIMESTAMP => '2025-09-23 07:00:00', ENDING_TIMESTAMP => '2025-09-23 07:10:00')
  )
)
SELECT 
  QSYS2.QCMDEXC(
    'GRTOBJAUT OBJ(' || OBJECT_LIBRARY || '/' || OBJECT_NAME || 
    ') OBJTYPE(' || OBJECT_TYPE || 
    ') USER(' || USER_PROFILE_NAME || 
    ') AUT(' || Previous_Aut || ')'
  ) AS CMD_EXEC_RESULT,
  ENTRY_TIMESTAMP,
  QUALIFIED_JOB_NAME,
  USER_PROFILE_NAME,
  OBJECT_NAME,
  OBJECT_LIBRARY,
  OBJECT_TYPE,
  Previous_Aut
FROM AuditData
WHERE Previous_Aut <> '' 
  AND OBJECT_TYPE NOT IN ('*STMF', '*DIR')

• Adjust the Time Frame.
• The SQL in this document executes without a confirmation prompt and will programmatically revert all authority changes within the specified time range to their previous values.
• A '1' on the CMD_EXEC_RESULT column indicates that the GRTOBJAUT completed normally, a '-1' indicates that it failed.
• The SQL procedure described in this document will revert object and data authority changes for a given object within the specified time range. If an object has experienced multiple changes during that period, only the most recent change recorded in the audit journal will be reverted. Earlier changes will not be restored. Users should carefully review the scope and impact of the reversion before executing the procedure.
• This SQL procedure does not revert changes made to an object’s Authorization List or any PGP (Primary Group Profile) associations. It only restores direct object and data authority values recorded in the audit journal. Users should manage Authorization List and PGP changes separately, as they are not captured or processed by this logic.
• This SQL is supported only on IBM i release R730 and above.

WITH AuditData AS (
  SELECT
    ENTRY_TIMESTAMP,
    QUALIFIED_JOB_NAME,
    USER_PROFILE_NAME,
    PATH_NAME,
    OBJECT_TYPE,

    -- Valid OBJAUT values only (no quotes needed)
    TRIM(
      CASE WHEN PREV_OBJECT_EXISTENCE = 'YES' THEN '*OBJEXIST ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_MANAGEMENT = 'YES' THEN '*OBJMGT ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_ALTER = 'YES' THEN '*OBJALTER ' ELSE '' END ||
      CASE WHEN PREV_OBJECT_REFERENCE = 'YES' THEN '*OBJREF ' ELSE '' END
    ) AS Valid_ObjAut,

    -- Mapped Symbolic Data Authority (quoted)
    CASE
      WHEN PREV_DATA_READ = 'YES' AND PREV_DATA_ADD = 'YES' AND PREV_DATA_UPDATE = 'YES' AND PREV_DATA_DELETE = 'YES' AND PREV_DATA_EXECUTE = 'YES' THEN '*RWX'
      WHEN PREV_DATA_READ = 'YES' AND PREV_DATA_EXECUTE = 'YES' THEN '*RX'
      WHEN PREV_DATA_READ = 'YES' AND (PREV_DATA_ADD = 'YES' OR PREV_DATA_UPDATE = 'YES' OR PREV_DATA_DELETE = 'YES') THEN '*RW'
      WHEN (PREV_DATA_ADD = 'YES' OR PREV_DATA_UPDATE = 'YES' OR PREV_DATA_DELETE = 'YES') AND PREV_DATA_EXECUTE = 'YES' THEN '*WX'
      WHEN PREV_DATA_READ = 'YES' THEN '*R'
      WHEN PREV_DATA_ADD = 'YES' OR PREV_DATA_UPDATE = 'YES' OR PREV_DATA_DELETE = 'YES' THEN '*W'
      WHEN PREV_DATA_EXECUTE = 'YES' THEN '*X'
      ELSE '*NONE'
    END AS Mapped_DtaAut

  FROM TABLE (
    SYSTOOLS.AUDIT_JOURNAL_CA(
      STARTING_TIMESTAMP => '2025-09-23 07:00:00',
      ENDING_TIMESTAMP => '2025-09-23 07:10:00'
    )
  )
)
SELECT 
  QSYS2.QCMDEXC(
    'CHGAUT OBJ(''' || PATH_NAME || ''') USER(' || USER_PROFILE_NAME || ')' ||
    CASE WHEN Valid_ObjAut <> '' THEN ' OBJAUT(' || TRIM(Valid_ObjAut) || ')' ELSE '' END ||
    CASE WHEN Mapped_DtaAut <> '*NONE' THEN ' DTAAUT(''' || Mapped_DtaAut || ''')' ELSE '' END
  ) AS CMD_EXEC_RESULT,

  ENTRY_TIMESTAMP,
  QUALIFIED_JOB_NAME,
  USER_PROFILE_NAME,
  Valid_ObjAut,
  Mapped_DtaAut,
  PATH_NAME

FROM AuditData
WHERE (Valid_ObjAut <> '' OR Mapped_DtaAut <> '*NONE')
  AND PATH_NAME LIKE '%/home%'
  AND OBJECT_TYPE IN ('*STMF', '*DIR')

NOTES: 

• Adjust the Time Frame.
• Adjust the PATH_NAME condition in the WHERE clause to specify the directory where the authority changes occurred.
• The SQL in this document executes without a confirmation prompt and will programmatically revert all authority changes within the specified time range to their previous values.
• A '1' on the CMD_EXEC_RESULT column indicates that the CHGAUT completed normally, a '-1' indicates that it failed.
• The SQL procedure described in this document will revert object and data authority changes for a given object within the specified time range. If an object has experienced multiple changes during that period, only the most recent change recorded in the audit journal will be reverted. Earlier changes will not be restored. Users should carefully review the scope and impact of the reversion before executing the procedure.
• This SQL procedure does not revert changes made to an object’s Authorization List or any PGP (Primary Group Profile) associations. It only restores direct object and data authority values recorded in the audit journal. Users should manage authorization list and PGP changes separately, as they are not captured or processed by this logic.
• This SQL is supported only on IBM i release R730 and above.
• This SQL does not restore users that were added to or removed from an object’s authority. The logic implemented by this SQL only reverts changes to authorities for users that already existed on the object’s authority list at the time of the audit journal entry.

 

Sample Results:

 

 

 

Additional Information:

AUDIT_JOURNAL_CA (Authority Changes) table function

 

Disclaimer: This SQL executes system-level changes to authorities and should be used with caution. It is strongly recommended to review and test the statement in a non-production environment before deployment. Improper use may result in unintended access issues or disruption to user workflows.