Start of change

AUDIT_JOURNAL_PW table function

The AUDIT_JOURNAL_PW table function returns rows from the audit journal that contain information from the PW (Password) journal entries.

Every audit journal table function shares a common authorization requirement and a common set of parameters. These are described in AUDIT JOURNAL table function common information.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. AUDIT_JOURNAL_PW table function
Column Name Data Type Description
The first columns returned by this table function are from the common audit journal entry header. See Common columns returned from the audit journal entry header for the column definitions. After the common columns are the following columns that describe the entry specific data for the PW audit journal entry.
VIOLATION_TYPE CHAR(1) The type of violation.
A
APPC bind failure.
C
User authentication with the CHKPWD command failed.
D
Service tools user ID name not valid (QSYCHGDS API, CRTSSTUSR, CHGSSTUSR, DLTSSTUSR commands).
E
Service tools user ID password not valid (QSYCHGDS API, CRTSSTUSR, CHGSSTUSR, DLTSSTUSR commands).
P
Password not valid.
Q
Attempted signon (user authentication) failed because user profile is disabled.
R
Attempted signon (user authentication) failed because password was expired.
S
SQL Decryption password is not valid.
U
User name not valid.
X
Service tools user ID is disabled.
Y
Service tools user ID not valid (service tools interface).
Z
Service tools user ID password not valid (service tools interface).
VIOLATION_TYPE_DETAIL VARCHAR(200) Descriptive text that corresponds to the violation type.
AUDIT_USER_NAME VARCHAR(10) The job user name or the service tools user ID name.

Contains the null value if no user name is available.

DEVICE_NAME VARCHAR(40) The name of the device or communications device on which the password or user ID was entered.

Contains the null value when VIOLATION_TYPE is D, E, X, Y, or Z or if the device name is not available.

INTERFACE_NAME VARCHAR(40) The name of the interface being used.

Contains the null value when VIOLATION_TYPE is not D, E, X, Y, or Z or if the interface name is not available.

REMOTE_LOCATION VARCHAR(8) Name of the remote location for the APPC bind.

Contains the null value when VIOLATION_TYPE is not A.

LOCAL_LOCATION VARCHAR(8) Name of the local location for the APPC bind.

Contains the null value when VIOLATION_TYPE is not A.

NETWORK_ID VARCHAR(8) Network ID for the APPC bind.

Contains the null value when VIOLATION_TYPE is not A.

DECRYPT_HOST_VARIABLE VARCHAR(3) Whether the user attempted to decrypt data in a host variable.
NO
The user did not attempt to decrypt data in a host variable.
YES
The user attempted to decrypt data in a host variable.

Contains the null value if VIOLATION_TYPE is not S.

DECRYPT_OBJECT_LIBRARY VARCHAR(10) The library that contains OBJECT_NAME.

Contains the null value if VIOLATION_TYPE is not S or if there is no library name.

DECRYPT_OBJECT_NAME VARCHAR(10) The name of the object being decrypted.

Contains the null value if VIOLATION_TYPE is not S or if there is no object name.

DECRYPT_OBJECT_TYPE VARCHAR(8) The type of the object.

Contains the null value if VIOLATION_TYPE is not S or if there is no object.

DECRYPT_OBJECT_ASP_NAME VARCHAR(10) The name of the ASP device where OBJECT_NAME resides.

Contains the null value if VIOLATION_TYPE is not S or if there is no object name.

DECRYPT_OBJECT_ASP_NUMBER INTEGER The number of the ASP device where OBJECT_NAME resides.

Contains the null value if VIOLATION_TYPE is not S or if there is no object name.

Example

  • For all the password audit journal entries from yesterday and today, list the number of each type of audit violation.
    SELECT VIOLATION_TYPE CONCAT ' - ' CONCAT VIOLATION_TYPE_DETAIL, 
           COUNT(*) AS VIOLATION_COUNT
      FROM TABLE (
          SYSTOOLS.AUDIT_JOURNAL_PW ( )
        )
      GROUP BY VIOLATION_TYPE CONCAT ' - ' CONCAT VIOLATION_TYPE_DETAIL
      ORDER BY 2 DESC;
End of change