IBM i Security

Track Restored User-Profile Authorities on IBM i with SQL

Use SYSTOOLS.AUDIT_JOURNAL_RU to verify IBM i user-profile authority restoration, detect partial restores, preserve recovery evidence, and build a controlled post-restore review workflow.

Restoring a user profile does not, by itself, prove that every private authority associated with that profile was successfully restored. SYSTOOLS.AUDIT_JOURNAL_RU exposes the IBM i RU audit entries that record whether all—or only some—authorities were restored for a user profile.

That makes the service useful after:

• disaster-recovery exercises
• system migrations
• profile recovery
• hardware or partition replacement
• environment rebuilds
• security-data restoration
• production recovery events

The questions are straightforward:

Which user profile was processed?
Which library and object type were involved?
Were all authorities restored?
Who initiated the restore?
Which job performed the work?
Where is the original audit evidence?

RSTUSRPRF and RSTAUT are different steps

A common source of confusion is treating profile restoration and authority restoration as one operation.

RSTUSRPRF restores user profiles and the security information associated with the saved profiles.

RSTAUT restores authorities by using the authority reference tables created during save operations.

A typical recovery sequence can include:

Restore user profiles
Restore application and system objects
Restore private authorities
Validate the restored security model

The exact recovery sequence depends on the save strategy, media, ASP structure, file systems, and recovery scenario.

Do not use one successful command as proof that the entire security recovery is complete.

What the RU entry records

The RU audit-journal entry means:

Restore Authority for User Profile

The entry-specific data includes:

ENTRY_TYPE
USER_PROFILE
LIBRARY_NAME
OBJECT_TYPE
AUTHORITY_RESTORED

For ENTRY_TYPE:

A   Restoring authority to user profiles

For AUTHORITY_RESTORED:

A   All authorities were restored
S   Some authorities were not restored

The distinction between A and S is the core operational value of this service.

A completed RSTAUT command is not the same as:

Every authority restored successfully

Start with the basic query

SELECT *
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 DAY
    )
)
ORDER BY
    ENTRY_TIMESTAMP DESC;

The service returns the RU-specific fields together with the standard audit-journal header.

Review recent authority restoration

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS RESTORE_RUN_BY,
    QUALIFIED_JOB_NAME,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    USER_PROFILE,
    LIBRARY_NAME,
    OBJECT_TYPE,
    AUTHORITY_RESTORED,
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 7 DAYS
    )
)
ORDER BY
    ENTRY_TIMESTAMP DESC,
    SEQUENCE_NUMBER DESC;

This creates a compact post-restore review showing:

• effective profile performing the operation
• qualified job
• program context
• profile whose authorities were restored
• library and object type
• completion indicator
• receiver and sequence

Find partial authority restores

The highest-priority query is:

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS RESTORE_RUN_BY,
    QUALIFIED_JOB_NAME,
    USER_PROFILE,
    LIBRARY_NAME,
    OBJECT_TYPE,
    AUTHORITY_RESTORED,
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 30 DAYS
    )
)
WHERE AUTHORITY_RESTORED = 'S'
ORDER BY
    ENTRY_TIMESTAMP DESC,
    USER_PROFILE,
    LIBRARY_NAME,
    OBJECT_TYPE;

S means that some authorities were not restored for the profile.

It does not, by itself, identify every failed object or explain the cause.

Use the audit entry as the starting point for deeper review.

Summarize the result by user profile

SELECT
    USER_PROFILE,
    COUNT(*) AS RU_ENTRY_COUNT,
    SUM(
        CASE
            WHEN AUTHORITY_RESTORED = 'A'
              THEN 1
            ELSE 0
        END
    ) AS COMPLETE_ENTRY_COUNT,
    SUM(
        CASE
            WHEN AUTHORITY_RESTORED = 'S'
              THEN 1
            ELSE 0
        END
    ) AS PARTIAL_ENTRY_COUNT,
    MIN(ENTRY_TIMESTAMP) AS FIRST_ENTRY,
    MAX(ENTRY_TIMESTAMP) AS LAST_ENTRY
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 30 DAYS
    )
)
GROUP BY
    USER_PROFILE
ORDER BY
    PARTIAL_ENTRY_COUNT DESC,
    USER_PROFILE;

This is useful when a recovery processes many profiles.

A profile with one or more S results requires investigation.

Review by library and object type

SELECT
    LIBRARY_NAME,
    OBJECT_TYPE,
    COUNT(*) AS ENTRY_COUNT,
    SUM(
        CASE
            WHEN AUTHORITY_RESTORED = 'S'
              THEN 1
            ELSE 0
        END
    ) AS PARTIAL_ENTRY_COUNT
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 30 DAYS
    )
)
GROUP BY
    LIBRARY_NAME,
    OBJECT_TYPE
ORDER BY
    PARTIAL_ENTRY_COUNT DESC,
    ENTRY_COUNT DESC;

This can highlight patterns such as:

• one library was unavailable
• one object type had repeated failures
• an ASP was not varied on
• an application library was restored after RSTAUT
• required objects did not exist yet
• an object was damaged or otherwise unavailable

The SQL identifies the pattern.

The job log usually provides the detailed diagnostic explanation.

Why authorities may not restore

IBM documents failure situations such as:

The object does not exist
The object is damaged
The object was unavailable
A required ASP was not available
The restore sequence was incomplete
A function check occurred
The authority reference information is no longer available

A partial result should be investigated rather than accepted as normal recovery noise.

Use the RSTAUT job logs

The RU audit entry tells you whether the profile-level restoration was complete or partial.

The RSTAUT job log can identify the object-level failure.

Important messages include:

CPF3736
CPF3845
CPC3706
CPI3821

CPF3736

This diagnostic can identify an individual object for which private authority was not restored.

The message can indicate that the object:

• does not exist
• is damaged
• was unavailable when authority was being granted

CPF3845

This message reports that some authorities were not restored for a user profile.

It can include:

• how many authorities were restored
• how many were not restored
• the prestart job used for the work
• the job containing further diagnostics

CPC3706

This completion message indicates that private authorities were restored for the profile.

CPI3821

When restoring authority for all profiles, this status message reports progress as authority reference tables are processed.

Do not rely only on a final command-completion message.

Review diagnostic and completion messages from the main job and any restore prestart jobs.

Restricted versus nonrestricted state

RSTAUT can be run in either restricted or nonrestricted state.

The operational evidence differs.

Restricted state

The work is more likely to be represented in one primary job log.

Nonrestricted state

RSTAUT can use prestarted jobs.

The individual object-level diagnostics may be in those prestarted job logs rather than the main RSTAUT job log.

That means a review process should preserve:

The submitting job log
Every named restore prestart job log
RU audit entries
Recovery command output
Recovery timestamps

A clean main job log does not necessarily mean every prestart job was clean.

Do not run RCLSTG between RSTUSRPRF and RSTAUT

IBM explicitly warns against running:

RCLSTG

between:

RSTUSRPRF

and:

RSTAUT

RCLSTG deletes authority reference tables.

Those tables are required by RSTAUT to restore authorities.

This is an important recovery-runbook control.

Restore authorities after the objects exist

Authorities can only be restored successfully when the target objects are available.

If RSTAUT runs before all required application objects are restored, some private authorities may fail.

IBM recommends restoring authorities late in the recovery process.

When additional restore operations are performed after RSTAUT, you may need to restore security data and run authority restoration again, depending on the recovery sequence.

The correct order is not just a performance consideration.

It determines whether the security model can be reconstructed.

RSTAUT can be rerun

IBM documents that RSTAUT can be rerun before another RSTUSRPRF or RCLSTG operation.

A rerun can attempt to restore authorities that previously failed, for example after:

• restoring a missing object
• varying on an ASP
• correcting object damage
• making a library available
• resolving a restore problem

Do not rerun blindly.

First preserve the failed evidence and identify why the first attempt was incomplete.

Compare expected profiles with RU results

Create an expected recovery scope:

CREATE TABLE RECOVERY.EXPECTED_PROFILE
(
    RECOVERY_ID       VARCHAR(40) NOT NULL,
    USER_PROFILE      VARCHAR(10) NOT NULL,
    REQUIRED          CHAR(1) NOT NULL,
    BUSINESS_OWNER    VARCHAR(128),
    TECHNICAL_OWNER   VARCHAR(128),
    PRIMARY KEY
    (
        RECOVERY_ID,
        USER_PROFILE
    )
);

Then compare expected profiles with observed RU entries:

WITH RU_RESULTS AS
(
    SELECT
        USER_PROFILE,
        MAX(
            CASE
                WHEN AUTHORITY_RESTORED = 'S'
                  THEN 1
                ELSE 0
            END
        ) AS HAS_PARTIAL_RESTORE,
        MAX(ENTRY_TIMESTAMP)
            AS MOST_RECENT_ENTRY
    FROM TABLE(
        SYSTOOLS.AUDIT_JOURNAL_RU(
            STARTING_TIMESTAMP =>
                :RECOVERY_START_TIMESTAMP,
            ENDING_TIMESTAMP =>
                :RECOVERY_END_TIMESTAMP
        )
    )
    GROUP BY
        USER_PROFILE
)
SELECT
    E.USER_PROFILE,
    E.REQUIRED,
    E.BUSINESS_OWNER,
    E.TECHNICAL_OWNER,
    R.MOST_RECENT_ENTRY,
    CASE
        WHEN R.USER_PROFILE IS NULL
          THEN 'NO RU EVIDENCE'
        WHEN R.HAS_PARTIAL_RESTORE = 1
          THEN 'PARTIAL'
        ELSE 'COMPLETE'
    END AS VALIDATION_STATUS
FROM RECOVERY.EXPECTED_PROFILE AS E
LEFT JOIN RU_RESULTS AS R
  ON R.USER_PROFILE = E.USER_PROFILE
WHERE E.RECOVERY_ID = :RECOVERY_ID
ORDER BY
    VALIDATION_STATUS DESC,
    E.USER_PROFILE;

No RU evidence can mean:

• the profile was not included
• auditing was not active
• the time window is wrong
• the required receiver is unavailable
• the operation did not produce the expected entry
• the recovery process did not reach that step

Do not automatically classify missing evidence as successful.

Track a recovery window

Create a control record:

CREATE TABLE RECOVERY.RECOVERY_RUN
(
    RECOVERY_ID            VARCHAR(40) PRIMARY KEY,
    SOURCE_SYSTEM          VARCHAR(8),
    TARGET_SYSTEM          VARCHAR(8),
    RECOVERY_TYPE          VARCHAR(30) NOT NULL,
    START_TIMESTAMP        TIMESTAMP NOT NULL,
    END_TIMESTAMP          TIMESTAMP,
    RSTUSRPRF_JOB          VARCHAR(28),
    RSTAUT_JOB             VARCHAR(28),
    RUNBOOK_VERSION        VARCHAR(40),
    CHANGE_REFERENCE       VARCHAR(128),
    STATUS                 VARCHAR(20) NOT NULL,
    REVIEWED_BY            VARCHAR(128),
    REVIEWED_TIMESTAMP     TIMESTAMP,
    REVIEW_NOTES           VARCHAR(2048)
);

Examples of RECOVERY_TYPE:

DR TEST
PRODUCTION RECOVERY
SYSTEM MIGRATION
PROFILE RECOVERY
ENVIRONMENT BUILD

Linking RU evidence to a defined recovery run prevents unrelated restore activity from being mixed into the report.

Preserve the audit evidence

A protected evidence table can contain:

CREATE TABLE RECOVERY.RU_AUDIT_EVIDENCE
(
    RECOVERY_ID            VARCHAR(40) NOT NULL,
    SOURCE_SYSTEM          VARCHAR(8) NOT NULL,
    RECEIVER_LIBRARY       VARCHAR(10) NOT NULL,
    RECEIVER_NAME          VARCHAR(10) NOT NULL,
    SEQUENCE_NUMBER        DECIMAL(21, 0) NOT NULL,
    ENTRY_TIMESTAMP        TIMESTAMP NOT NULL,
    RESTORE_RUN_BY         VARCHAR(10),
    QUALIFIED_JOB_NAME     VARCHAR(28),
    PROGRAM_LIBRARY        VARCHAR(10),
    PROGRAM_NAME           VARCHAR(10),
    USER_PROFILE           VARCHAR(10),
    LIBRARY_NAME           VARCHAR(10),
    OBJECT_TYPE            VARCHAR(8),
    AUTHORITY_RESTORED     CHAR(1),
    REVIEW_STATUS          VARCHAR(20) NOT NULL,
    ISSUE_REFERENCE        VARCHAR(128),
    REVIEW_NOTES           VARCHAR(2048),
    PRIMARY KEY
    (
        SOURCE_SYSTEM,
        RECEIVER_LIBRARY,
        RECEIVER_NAME,
        SEQUENCE_NUMBER
    )
);

Useful review statuses include:

NEW
COMPLETE
PARTIAL
INVESTIGATING
OBJECT MISSING
ASP UNAVAILABLE
RERUN REQUIRED
RERUN COMPLETE
RISK ACCEPTED

Use a durable extraction position

A scheduled audit collector should store:

System name
Journal library
Journal name
Receiver library
Receiver name
Sequence number
Entry timestamp
Collector name
Collection status

Do not store only SEQUENCE_NUMBER.

Journal sequence numbers can be reset.

Receiver identity provides the durable source location needed for restart and duplicate prevention.

Common audit-function filters

Like other IBM audit-journal table functions, the service supports common filters for areas such as:

Starting and ending timestamps
Starting and ending receivers
Starting and ending sequences
User
Job
Program

Use timestamp filtering for interactive investigation.

Use receiver-and-sequence boundaries for repeatable collection.

Do not specify both a starting timestamp and starting sequence in the same call.

Filter by the operator

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS RESTORE_RUN_BY,
    QUALIFIED_JOB_NAME,
    USER_PROFILE,
    LIBRARY_NAME,
    OBJECT_TYPE,
    AUTHORITY_RESTORED
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_RU(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 7 DAYS,
        USER_NAME =>
            'RECOVERY'
    )
)
ORDER BY
    ENTRY_TIMESTAMP DESC;

USER_NAME in the common audit header identifies the effective user running when the entry was written.

USER_PROFILE identifies the profile whose authority was being restored.

Do not confuse the two.

Validate sensitive profiles first

Maintain a recovery-critical profile list:

CREATE TABLE SECURITY.RECOVERY_CRITICAL_PROFILE
(
    USER_PROFILE        VARCHAR(10) PRIMARY KEY,
    PROFILE_CLASS       VARCHAR(20) NOT NULL,
    BUSINESS_OWNER      VARCHAR(128),
    REVIEW_PRIORITY     INTEGER NOT NULL
);

Prioritize:

• application service profiles
• scheduler profiles
• integration profiles
• database owners
• deployment profiles
• operators
• profiles associated with regulated data
• profiles required during startup and batch processing

A profile does not need *ALLOBJ to be operationally critical.

Check operational behavior after authority restoration

An AUTHORITY_RESTORED = 'A' result means the authority restoration represented by that RU entry completed.

It does not prove that:

• the source save contained the intended authority model
• every expected object existed
• group relationships are correct
• authorization-list membership is correct
• ownership is correct
• adopted-authority programs are correct
• public authority is appropriate
• application jobs can run successfully
• the target system matches the source policy

Post-recovery validation should also include:

Application startup
Critical batch jobs
API and integration profiles
Object ownership
Authorization lists
Private authority
Primary groups
Adopted authority
IFS permissions
Database row and column controls

Correlate with related audit entry types

A complete restore-security review may also use:

CP   User profile changes and restores
OR   Object restored
RA   Authority change for restored object
RJ   Job description restored with a user profile
RO   Ownership change for restored object
RP   Program that adopts authority restored
RU   User-profile authority restored
RZ   Primary group change for restored object

AUDIT_JOURNAL_RU is focused on authority restoration for user profiles.

It should be one part of a larger recovery-evidence package.

Auditing must already be active

The function reads evidence from:

QSYS/QAUDJRN

For save-and-restore security activity, the audit configuration must include the relevant auditing level, commonly associated with:

*SAVRST

Do not alter audit policy casually just to make one query return rows.

Confirm:

QAUDCTL
QAUDLVL
QAUDLVL2
QAUDJRN receiver retention
audit failure handling

A query cannot recreate an entry that was never collected or whose receiver has been deleted.

Required authority

Audit-journal table functions share common authority requirements.

The caller needs appropriate access to:

QSYS/QAUDJRN
The journal library
Every requested receiver
The receiver libraries

Use a controlled audit-reporting profile.

Do not grant broad special authority solely to simplify reporting.

The extracted evidence should be protected because it reveals:

• recovery activity
• profile names
• library and object-type scope
• administrative jobs
• restore completeness
• security-control weaknesses

Receiver retention matters

Do not delete audit receivers based only on age.

Retention should account for:

• DR-test sign-off
• production recovery
• audit-review completion
• incident investigation
• regulatory obligations
• legal hold
• SIEM forwarding
• durable extraction checkpoints

Expose a retention-safe-through receiver for every audit consumer.

Suggested recovery sign-off

A recovery should not be marked complete until:

All expected user profiles were restored
RSTAUT was executed at the correct stage
RU evidence was collected
All S results were investigated
Restore prestart job logs were preserved
Missing objects or unavailable ASPs were resolved
Required reruns were completed
Critical application jobs were tested
Security owners reviewed the results
Audit receivers were retained

This converts authority restoration from an assumed step into a measurable recovery control.

Release requirement

SYSTOOLS.AUDIT_JOURNAL_RU is delivered with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

It is part of the IBM i 7.6 TR2 and IBM i 7.5 TR8 service updates.

Confirm the installed Db2 Group PTF before using the function.

Final takeaway

RSTAUT completing is not enough evidence for recovery sign-off.

SYSTOOLS.AUDIT_JOURNAL_RU exposes the result that matters:

A   All authorities restored
S   Some authorities not restored

Use the SQL service to identify the affected profile, library, object type, job, operator, receiver, and sequence.

Then use the RSTAUT job logs to find the object-level cause.

A defensible recovery process should:

Preserve the RU evidence
Investigate every partial result
Restore missing or unavailable objects
Rerun authority restoration when appropriate
Validate critical application behavior
Retain the audit receivers
Obtain security-owner sign-off

Authority restoration should be verified—not assumed.