Best Practices

1. Regular Monitoring

• Schedule periodic reviews of authority changes
• Alert on critical changes (e.g., *ALL authority granted, *EXCLUDE applied)
• Archive audit data for long-term compliance
• Monitor authorization list changes

2. Filtering Strategies

-- Focus on recent changes
WHERE ENTRY_TIMESTAMP > CURRENT_TIMESTAMP - 7 DAYS

-- Monitor specific users
WHERE USER_NAME = 'CRITICALUSER'

-- Track authority increases
WHERE OBJECT_AUTHORITY > PREV_OBJECT_AUTHORITY

-- Monitor production libraries
WHERE LIBRARY_NAME IN ('PRODLIB', 'SECLIB')

-- Track access revocations
WHERE OBJECT_AUTHORITY = '*EXCLUDE'

3. Security Considerations

• Restrict access to audit journal queries
• Log who runs audit reports
• Implement alerts for unauthorized authority changes
• Document baseline authority settings
• Review authorization list memberships

4. Performance Optimization

For large audit journals:

-- Use specific time ranges
STARTING_TIMESTAMP => '2024-01-01 00:00:00',
ENDING_TIMESTAMP => '2024-01-31 23:59:59'

-- Limit result sets
FETCH FIRST 1000 ROWS ONLY

-- Filter by specific objects or users
WHERE OBJECT_NAME = 'CRITICALFILE'
  AND USER_NAME IN ('USER1', 'USER2')

Advanced Queries

Example 1: Track All Authority Escalations

Identify when users receive increased authorities:

WITH AUTHORITY_CALC AS (
    SELECT ENTRY_TIMESTAMP,
           JOB_USER,
           USER_NAME,
           COALESCE(OBJECT_NAME, PATH_NAME) AS OBJECT_IDENTIFIER,
           COALESCE(OBJECT_LIBRARY, 'IFS') AS LOCATION,
           CASE
               WHEN PREV_OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN PREV_OBJECT_MANAGEMENT = 'YES' AND PREV_OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN PREV_DATA_ADD = 'YES' AND PREV_DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN PREV_OBJECT_OPERATIONAL = 'YES' AND PREV_DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS PREV_OBJECT_AUTHORITY,
           CASE
               WHEN OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN OBJECT_MANAGEMENT = 'YES' AND OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN DATA_ADD = 'YES' AND DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN OBJECT_OPERATIONAL = 'YES' AND DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS OBJECT_AUTHORITY,
           PREV_OBJECT_EXCLUDE,
           OBJECT_EXCLUDE
    FROM TABLE(
        SYSTOOLS.AUDIT_JOURNAL_CA(
            STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
        )
    ) AS CA
)
SELECT ENTRY_TIMESTAMP,
       JOB_USER AS GRANTED_BY,
       USER_NAME AS GRANTED_TO,
       OBJECT_IDENTIFIER,
       LOCATION,
       PREV_OBJECT_AUTHORITY,
       OBJECT_AUTHORITY,
       CASE
           WHEN PREV_OBJECT_EXCLUDE = 'YES' AND OBJECT_EXCLUDE = 'NO' THEN 'Access Granted'
           WHEN PREV_OBJECT_AUTHORITY = '*USE' AND OBJECT_AUTHORITY = '*CHANGE' THEN 'Elevated to Modify'
           WHEN PREV_OBJECT_AUTHORITY = '*USE' AND OBJECT_AUTHORITY = '*ALL' THEN 'Elevated to Full Control'
           WHEN PREV_OBJECT_AUTHORITY = '*CHANGE' AND OBJECT_AUTHORITY = '*ALL' THEN 'Elevated to Full Control'
           ELSE 'Authority Increased'
       END AS ESCALATION_TYPE
FROM AUTHORITY_CALC
WHERE OBJECT_AUTHORITY > PREV_OBJECT_AUTHORITY
   OR (PREV_OBJECT_EXCLUDE = 'YES' AND OBJECT_EXCLUDE = 'NO')
ORDER BY ENTRY_TIMESTAMP DESC;

Example 2: Monitor Critical Object Authority Changes

Track authority changes on production objects:

WITH AUTHORITY_CALC AS (
    SELECT ENTRY_TIMESTAMP,
           JOB_USER,
           USER_NAME,
           OBJECT_NAME,
           OBJECT_LIBRARY,
           OBJECT_TYPE,
           CASE
               WHEN PREV_OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN PREV_OBJECT_MANAGEMENT = 'YES' AND PREV_OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN PREV_DATA_ADD = 'YES' AND PREV_DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN PREV_OBJECT_OPERATIONAL = 'YES' AND PREV_DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS PREV_OBJECT_AUTHORITY,
           CASE
               WHEN OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN OBJECT_MANAGEMENT = 'YES' AND OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN DATA_ADD = 'YES' AND DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN OBJECT_OPERATIONAL = 'YES' AND DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS OBJECT_AUTHORITY
    FROM TABLE(
        SYSTOOLS.AUDIT_JOURNAL_CA(
            STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS
        )
    ) AS CA
    WHERE OBJECT_LIBRARY IN ('PRODLIB', 'APPLIB', 'DATALIB', 'SECLIB')
)
SELECT ENTRY_TIMESTAMP,
       JOB_USER,
       USER_NAME,
       OBJECT_NAME,
       OBJECT_LIBRARY AS LIBRARY_NAME,
       OBJECT_TYPE,
       PREV_OBJECT_AUTHORITY,
       OBJECT_AUTHORITY,
       CASE
           WHEN OBJECT_AUTHORITY = '*ALL' THEN '⚠️ CRITICAL - Full Control Granted'
           WHEN OBJECT_AUTHORITY = '*EXCLUDE' THEN '⚠️ WARNING - Access Revoked'
           WHEN PREV_OBJECT_AUTHORITY = '*ALL' THEN '⚠️ NOTICE - Full Control Removed'
           ELSE 'Authority Modified'
       END AS SECURITY_IMPACT
FROM AUTHORITY_CALC
ORDER BY
    CASE
        WHEN OBJECT_AUTHORITY = '*ALL' THEN 1
        WHEN OBJECT_AUTHORITY = '*EXCLUDE' THEN 2
        ELSE 3
    END,
    ENTRY_TIMESTAMP DESC;

Example 3: Authority Change Summary Report

Generate a summary of authority changes by user:

WITH AUTHORITY_CALC AS (
    SELECT USER_NAME,
           JOB_USER,
           CASE
               WHEN PREV_OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN PREV_OBJECT_MANAGEMENT = 'YES' AND PREV_OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN PREV_DATA_ADD = 'YES' AND PREV_DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN PREV_OBJECT_OPERATIONAL = 'YES' AND PREV_DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS PREV_OBJECT_AUTHORITY,
           CASE
               WHEN OBJECT_EXCLUDE = 'YES' THEN '*EXCLUDE'
               WHEN OBJECT_MANAGEMENT = 'YES' AND OBJECT_EXISTENCE = 'YES' THEN '*ALL'
               WHEN DATA_ADD = 'YES' AND DATA_UPDATE = 'YES' THEN '*CHANGE'
               WHEN OBJECT_OPERATIONAL = 'YES' AND DATA_READ = 'YES' THEN '*USE'
               ELSE '*USER_DEF'
           END AS OBJECT_AUTHORITY
    FROM TABLE(
        SYSTOOLS.AUDIT_JOURNAL_CA(
            STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
        )
    ) AS CA
)
SELECT USER_NAME,
       COUNT(*) AS TOTAL_CHANGES,
       SUM(CASE WHEN OBJECT_AUTHORITY = '*ALL' THEN 1 ELSE 0 END) AS FULL_CONTROL_GRANTED,
       SUM(CASE WHEN OBJECT_AUTHORITY = '*EXCLUDE' THEN 1 ELSE 0 END) AS ACCESS_REVOKED,
       SUM(CASE WHEN OBJECT_AUTHORITY > PREV_OBJECT_AUTHORITY THEN 1 ELSE 0 END) AS AUTHORITY_INCREASED,
       SUM(CASE WHEN OBJECT_AUTHORITY < PREV_OBJECT_AUTHORITY THEN 1 ELSE 0 END) AS AUTHORITY_DECREASED,
       COUNT(DISTINCT JOB_USER) AS MODIFIED_BY_USERS
FROM AUTHORITY_CALC
GROUP BY USER_NAME
HAVING COUNT(*) > 0
ORDER BY TOTAL_CHANGES DESC;

Troubleshooting

Issue: No Results Returned

Possible Causes:

1. Auditing Not Configured: Verify QAUDCTL and QAUDLVL system values
2. No Changes Made: Confirm authority changes were actually made during the time period
3. Insufficient Authority: Verify you have required journal authorities
4. Incorrect Time Range: Expand the STARTING_TIMESTAMP and ENDING_TIMESTAMP

Issue: Performance Concerns

Solutions:

1. Narrow Time Range: Use specific timestamps rather than broad ranges
2. Add Filters: Include WHERE clauses for specific objects or users
3. Use FETCH FIRST: Limit result sets with FETCH FIRST n ROWS ONLY
4. Index Considerations: Ensure appropriate indexes exist on filtered columns

Additional Resources

• IBM i Services: AUDIT_JOURNAL_CA Table Function
• IBM i Security Auditing
• IBM i Object Authority
• DSPSECAUD Command
• CHGSECAUD Command

Summary

The SYSTOOLS.AUDIT_JOURNAL_CA table function provides powerful capabilities for tracking authority changes on IBM i systems:

• Library Objects: Monitor authority changes for traditional IBM i objects (*FILE, *PGM, etc.)
• IFS Objects: Track authority modifications for files and directories in the Integrated File System
• Authority Mapping: Understand the specific object and data authorities granted or revoked
• Security Compliance: Maintain comprehensive audit trails of permission changes
• Incident Response: Quickly identify when and by whom authorities were modified

By implementing these queries and best practices, you can maintain robust security monitoring and ensure compliance with access control requirements on your IBM i systems.