22.8 Audit trail table columns

Table 22-15 shows the columns of the audit trail table.

Table 22-15 Audit trail table columns

Column nameColumn contentColumn data typeNULL value specification
USER_NAMEAuthorization identifier of the person who executed the audited event.
For a command or utility executed by the HiRDB administrator or a general OS user, the OS user name is output.
MVARCHAR(30)No
EXEC_DATEDate the event was executed.
Event execution date means the date according to the standard time at which the server that issued the audit trail output request requests output of an audit trail to the audit trail buffer.
DATENo
EXEC_TIMETime the event was executed.
Event execution time means the time according to the standard time at which the server that issued the audit trail output request requests output of an audit trail to the audit trail buffer.
TIMENo
EXEC_TIME_MICROTime the event was executed (microseconds).
Event execution time means the time according to the standard time at which the server that issued the audit trail output request requests output of an audit trail to the audit trail buffer.
INTEGERNo
EVENT_TYPEEvent type. For details about the event types and subtypes, see (1) Event types and subtypes.CHAR(3)No
EVENT_SUBTYPEEvent subtype. For details about the event types and subtypes, see (1) Event types and subtypes.CHAR(3) No
EVENT_RESULTResult of event execution; this item flags whether or not privilege checking was successful:
S: Privilege checking or event succeeded.
F: Privilege checking or event failed.
U: Part of the event failed.
CHAR(1)No
USED_PRIVILEGEPrivilege used when the event executed. When the event is terminated, three consecutive blank symbols ([Figure]) are output.
AUD: Audit privilege
CNT: CONNECT privilege
DBA: DBA privilege
DEL: DELETE privilege
INS: INSERT privilege
OWN: Owner
RDA: RDAREA usage privilege
SCH: Schema definition privilege
SEL: SELECT privilege
SYS: HiRDB administrator
UPD: UPDATE privilege
CHAR(3) No
UAP_NAMEName of the UAP that issued the event.1
If the UAP name is fewer than 30 characters, it is padded with trailing blank symbols ([Figure]).
In all other cases, the NULL value is output.
VARCHAR(30)Yes
SERVICE_NAMEName of the service requested by the UAP that issued the event. If an OpenTP1 UAP, this is the name of the service requested by SPP or MHP.
If not an OpenTP1 UAP, 31 consecutive asterisks (*) are output.
In all other cases, the NULL value is output.
VARCHAR(31)Yes
IP_ADDRESSIP address that issued the event. The NULL value is output when the IP address cannot be identified.VARCHAR(63)Yes
PROCESS_IDProcess ID that issued the event. The NULL value is output when the process ID cannot be identified.
For a command or utility, the process ID of the executed command is output.
At the start of audit trail overwriting by the system, the process ID of the audit trail daemon is output.
In all other cases, the process ID of the single server or front-end server that connected is output.
INTEGERYes
THREAD_IDThread ID that issued the event. The NULL value is output when the thread ID cannot be identified.INTEGERYes
HOST_NAMEName of the host that received the event. The host name specified in the -x option of the pdunit operand is output. If a system switchover facility is used, the host name of the primary system is output.
The NULL value is output when the name of the host that received the event cannot be identified.
VARCHAR(32)Yes
UNIT_NAMEUnit identifier that received the event. The unit identifier specified in the -u option of the pdunit operand is output.
The NULL value is output when the unit identifier that received the event cannot be identified.
CHAR(4) Yes
SERVER_NAMEName of the server that received the event. For a HiRDB/Single Server, this is the name of the single server; for a HiRDB/Parallel Server, this is the name of the front-end server. This is output when the server name can be identified. The server name specified in the -s option of the pdstart operand is output.
The NULL value is output when the name of the server that received the event cannot be identified.
VARCHAR(8)Yes
CONNECT_NUMBERConnection number of the event issuer. The NULL value is output when the connection number cannot be identified.INTEGERYes
SQL_NUMBEREvent SQL number. The NULL value is output when the event SQL number cannot be identified.INTEGERYes
OBJECT_SCHEMAObject information detail2Owner of the object for which the event privilege is being checked. The NULL value is output when the object owner cannot be identified.MVARCHAR(30)Yes
OBJECT_NAMEName of the object for which the event privilege is being checked. The NULL value is output when the object name cannot be identified.MVARCHAR(30)Yes
OBJECT_TYPEType of object for which the event privilege is being checked. The NULL value is output when the object type cannot be identified.
ALS: Alias
AUF: Audit trail file
FID: Foreign index
FNC: Function
FSV: Foreign server
FTB: Foreign table
IDX: Index
LST: List
PRC: Procedure
RDA: RDAREA
SCH: Schema
TBL: Table
TRG: Trigger
TYP: Data type
USM: User Mapping
VIW: View table
CHAR(3) Yes
PRIVILEGE_TYPEPrivilege added, deleted, or modified by the event. The NULL value is output when the privilege cannot be identified.
AUD: Audit privilege
CNT: CONNECT privilege
DBA: DBA privilege
DEL: DELETE privilege
INS: INSERT privilege
RDA: RDAREA usage privilege
SCH: Schema definition privilege
SEL: SELECT privilege
UPD: UPDATE privilege
CHAR(3)Yes
PRIVILEGE_SCHEMAUser authorization identifier added, deleted, or modified by the event. Or, the authorization identifier of the user who was the target of the event.
The NULL value is output when the authorization identifier cannot be identified.
  • GRANT or REVOKE: Authorization identifier, group ID, or role name to which a privilege was added, from which a privilege was deleted, or whose privilege was modified.
  • Transition to or release from consecutive certification failure account lock state: Authorization identifier that was locked or released.
  • Transition to or release from password-invalid account lock state: Authorization identifier that was locked or released.
MVARCHAR(30)Yes
SECURITY_OPERANDValues of the security audit facility operands. These are the operand values at HiRDB startup. For all cases other than startup, the NULL value is output.
For details about the operand values for the security audit facility, see (2) Operand values for the security audit facility.
VARCHAR(256)Yes
AUDIT_TRAIL_TYPEAudit trail type. A value is output to differentiate between an audit trail showing an event result and an audit trail showing the result of privilege checking during event execution.
Privilege checking: NULL
Event termination: E
CHAR(1) Yes
SQL_CODESQL code or termination code. When an audited event is terminated, an SQL code or termination code is output for the SQL event or utility (or command). For privilege checking, the NULL value is output.
This code indicates the event success or failure value that is set when the event terminates. For details about each code in the audit record entry and whether or not the event is successful, see (3) SQL code or termination code indicating event success or failure.
INTEGERYes
FROM_AUDFILE_NAMEAudit trail file name at the swapping source. The NULL value is output when the audit trail cannot be identified.MVARCHAR(30)Yes
TO_AUDFILE_NAMEAudit trail file name at the swapping destination. The NULL value is output when the audit trail cannot be identified.MVARCHAR(30)Yes
SECURITY_PARM_TYPEModification type related to the connection security facility.3CHAR(4)Yes
BEFORE_SECURITY_PARMSetting value of the connection security facility before change.3
If the setting value is fewer than 10 characters, it is padded for output with trailing blanks (setting value + blanks = 10 characters).
CHAR(10)Yes
AFTER_SECURITY_PARMSetting value of the connection security facility after change.3
If the setting value is fewer than 10 characters, it is padded for output with trailing blanks (setting value + blanks = 10 characters).
CHAR(10)Yes
AUDIT_TABLE_OPTIONAudit trail table option. This item is not output for privilege checking. It is output in the following cases:
  • The event-target object is an audit trail table.
  • The event-target object is a view table created using an audit trail table as the base table.
  • The event-target object is a list created using an audit trail table as the base table.
Y: Manipulation-target object is an audit trail table.
V: Manipulation-target object is a view table created using an audit trail table as the base table.
L: Manipulation-target object is a list created using an audit trail table as the base table.
NULL value: For cases other than those listed above or for privilege checking
For details about whether or not audit trail table options are output, see (5) Whether or not audit trail table options are output.
CHAR(1)Yes
ACCESS_COUNTNumber of rows searched, inserted, updated, and deleted by the user for an object (base table, view table, foreign table, table alias, or list). The access count that can be acquired is from 0 to 2147483647​. If acquisition of the access count fails, the NULL value is output.
For details about the access count, see (6) Details about the access count.
INTEGERYes
Notes
  • For a column for which the NULL value is specified, NULL is set for the NOT NULL constraint. For a column for which the NULL value is not specified, NOT NULL is set for the NOT NULL constraint.
  • When version upgrading changes the column structure of an audit trail table and data prior to version upgrading is registered in the audit trail table after version upgrading, the NULL value is set in the newly added columns of the existing rows.
1 To use a UAP name as a search key, specify CAST to convert the attribute to CHAR(30) and specify the result in the conditional expression.
Example:

  SELECT * FROM SQL_AUDIT_TRAIL
      WHERE UAP_NAME=CAST('UAP11' AS CHAR(30));

2 During privilege checking:
The object that is the target of privilege checking is output. However, when the privilege of the executor is checked, nothing is output because there is no object that is checked.
Examples:
  • Target object when the access privilege to table T1 is checked: Table T1
  • Target object when whether or not the executor has the DBA privilege is checked: NULL value
At event termination:
The object that is the target of the event is output. If no object is specified as the event target, table is output as the object type.
Example:
  • Target object when a table is searched: The searched table
3 A modification type is output in the following cases:
  • When a setting value of the connection security facility is changed
  • When a password is registered or modified
For details about the information that is recorded, see (4) Information that is recorded when the connection security facility is used.
Organization of this section
(1) Event types and subtypes
(2) Operand values for the security audit facility
(3) SQL code or termination code indicating event success or failure
(4) Information that is recorded when the connection security facility is used
(5) Whether or not audit trail table options are output
(6) Details about the access count

(1) Event types and subtypes

Table 22-16 provides details about the event types and subtypes.

Table 22-16 Event types and subtypes

Audit eventEvent type valueEvent subtype valueEvent
System administrator security eventsSYSSTRHiRDB startup
STPHiRDB termination
MODAuditor registration or audit trail table creation (pdmod command)
ARMAudit trail file deletion (pdaudrm command)
ABGAudit trail collection startup (pdaudbegin command, HiRDB startup)
AENAudit trail collection termination (pdaudend command, HiRDB termination)
OVWStart of audit trail file overwriting
CLKTransition to consecutive certification failure account lock state
CULRelease of consecutive certification failure account lock state
PLKTransition to password-invalid account lock state
PULRelease of password-invalid account lock state
SPRModification of an operand related to the connection security facility
ULKExecution of the pdacnlck command
Auditor security eventsAUDALDData load the audit trail table (pdload command)
ASWAudit trail file swap (pdaudswap command)
CRTAudit event definition (CREATE AUDIT)
DRPAudit event deletion (DROP AUDIT)
GRTAuditor password change (GRANT AUDIT)
Session security eventsSESCNTConnection to HiRDB (CONNECT statement)
ATHUser modification (SET SESSION AUTHORIZATION statement)
Privilege management eventsPRVGRTUser privilege addition (GRANT statement)
Password change (adds information indicating a password change to the set modification type for the connection security facility)
RVKUser privilege deletion (REVOKE statement)
Object definition eventsDEFCRTObject definition (CREATE type SQL)
DRPObject deletion (DROP type SQL)
ALTObject modification (ALTER type SQL)
Object manipulation eventsACSSELTable retrieval (SELECT statement)
INSTable row insertion (INSERT statement)
UPDTable row modification (UPDATE statement)
DELDeletion of a row from a table (DELETE statement)
PRGDeletion of all rows from a table (PURGE TABLE statement)
CALExecution of a stored procedure (CALL statement)
LCKTable lock control (LOCK TABLE statement)
ASNList creation (ASSIGN LIST statement)
Utility manipulation eventUTLLODDatabase load utility (pdload command)
ORGDatabase reorganization utility (pdrorg command)
EXPDictionary import/export utility (pdexp command)
pddefrev command
CSTIntegrity check utility (pdconstck command)

(2) Operand values for the security audit facility

Table 22-17 lists the operand values related to the security audit facility. Figure 22-7 shows the data format used when operand values are recorded.

Table 22-17 Values of security audit facility operands

OperandData typeStored value
pd_auditCHAR(1)Y or N
pd_aud_file_nameVARCHAR(167)Name of HiRDB file system area used for audit trail files
pd_aud_max_generation_sizeVARCHAR(4)1-5240
pd_aud_max_generation_numVARCHAR(3)2-200
pd_aud_no_standby_file_oprVARCHAR(10)down or forcewrite
pd_aud_async_buff_sizeVARCHAR(7)0, 4096-6553600​
pd_aud_async_buff_countVARCHAR(4)1-6500
pd_aud_file_wrn_pntVARCHAR(6)0-100 and 0-99

Figure 22-7 Data format for output of security audit facility operand values

[Figure]

(3) SQL code or termination code indicating event success or failure

Table 22-18 provides details about the SQL codes or termination codes that indicate event success or failure.

Table 22-18 SQL code or termination code indicating event success or failure

EventSQL code or termination code valueMeaningEvent success or failureRemarks
Various SQLs0 or greaterSuccessS[Figure]
NegativeFailureF
pdstart0SuccessS[Figure]
4SuccessS
8FailureFAn audit trail cannot be collected in the following cases:
  • Failure occurred before the audit trail daemon started.
  • Failure of the pdstart command caused the unit to terminate abnormally.
pdstop0SuccessSIf a failure occurred after the audit trail daemon had terminated, a successful audit trail is output.
4SuccessS[Figure]
8FailureF
pdmod (auditor registration, audit trail creation)0SuccessS[Figure]
8FailureF
pdaudrm0SuccessS[Figure]
8FailureF
pdaudbegin0SuccessS[Figure]
4Partial failureU
8FailureFAn audit trail cannot be collected in the following cases:
  • All generations of audit trail files have been used.
  • The pdaudbegin command failed because there is no generation of audit trail files that is in unload wait status.
pdaudend0SuccessS[Figure]
8FailureF
pdacunlck0SuccessS[Figure]
8FailureF
pdaudswap0SuccessSDuring overwriting of the audit trail file, the names of the audit trail files at the swapping source and swapping destination are output.
8FailureFDuring overwriting of the audit trail file, the name of the audit trail file at the swapping source is output. The NULL value is output for the audit trail file name at the swapping destination.
pdload0SuccessS[Figure]
4SuccessS
8FailureF
pddefrev0SuccessS[Figure]
4SuccessS
8FailureF
12FailureF
pdrorg0SuccessS[Figure]
4SuccessS
8FailureF
pdexp0SuccessS[Figure]
4SuccessS
8FailureF
12FailureF
pdconstck0SuccessS[Figure]
4SuccessSEnd of warning (there is an integrity violation)
8FailureF[Figure]

Legend:

[Figure]: Not applicable

(4) Information that is recorded when the connection security facility is used

Table 22-19 provides the information that is recorded when the connection security facility is used.

Table 22-19 Information that is output when the connection security facility is used

FacilityEvent typeEvent subtypeModification typeValue that is output after modification
Permitted number of consecutive certification failuresSYSSPRECNT1-10 or NULL value
Account lock periodLCKM10-44640, UNLIMITED, or NULL value
Minimum number of bytes for a passwordMINL1-15 or NULL value
Authorization identifier specification prohibitionUSIDRESTRICT, UNRESTRICT, or NULL value
Single character-type specification prohibitionSMLN
Password change*PRV or AUDGRTCPWDNULL value
Note
The NULL value is output in the following cases:
  • For a value that is to be output before or after a change, no value is found in the corresponding column in the SQL_SYSPARAMS dictionary table (for example, when a new setting is specified).
  • For a NULL value
  • When a value cannot be identified.
During an advance check of password character string restrictions, an audit trail is collected for each modification type. However, the NULL value is output for the value that is to be output before or after the change.
*
HiRDB cannot determine from the event type, event subtype, and SQL code only whether the password was actually changed during execution of a GRANT statement. Only when a registered user password is changed during execution of a GRANT statement is CPWD set as the modification type. Table 22-20 shows the modification types that are output when a password is changed.

Table 22-20 Modification types that are output when a password is changed

Does a password exist for the registered user?GRANT CONNECT, DBA, or AUDIT execution resultModification type
Password exists.Password changeCPWD
Password deletionCPWD
No password changeNULL value
Password does not exist.Changes to "Password exists."CPWD
Retains "Password does not exist."NULL value

Note

Users and passwords referred to here are those managed by HiRDB.

(5) Whether or not audit trail table options are output

Table 22-21 shows whether or not audit trail table options are output.

Table 22-21 Audit trail table option output

Execution resultFailure causeIs audit trail table option output?
Success[Figure]Output
Failure*The target object does not exist.Not output (NULL value)
An error occurred because the target object is an audit trail table.Output
Error other than one listed aboveOutput (not output when information cannot be collected)

Legend:

[Figure]: Not applicable

* To determine whether a table is an audit trail table when no audit trail table option is output, check the combination of the object owner and object name in the audit record entry, for example.

(6) Details about the access count

Table 22-22 provides the details of the access count.

Table 22-22 Details about the access count

Access methodAccess count
SQLSELECTNumber of rows accessed (fetched until CLOSE was successful or failed) by the user on the result set using the SELECT statement. For block transfer, the number of rows in a block is treated as the access count.
If multiple tables are specified for a query, the same access count is used for all the applicable tables. The access count is set to the NULL value in either of the following cases:
  • Table is specified in a subquery (for details, see Table 22-23)
  • Table is specified in the right-hand operand in the EXCEPT [ALL] set operation (for an example, see Figure 22-13)
INSERTNumber of rows inserted
UPDATENumber of rows updated
DELETENumber of rows deleted
UtilitypdloadData load count per table
pdrorgUnload count per table and reload count per table
pdconstckTotal number of key values resulting in limitation violation

Table 22-23 Access count by subquery

Specified location in tableAccess countRemarks
Scalar subqueryWhere a value expression is permittedNULL valueFor an example, see Figure 22-14
Row subqueryWhere a row value constructor is permittedNULL valueFor an example, see Figure 22-15
SET clause in UPDATE statementNULL valueFor an example, see Figure 22-16
Table subqueryRight-hand term of IN predicateNULL valueFor an example, see Figure 22-17
Right-hand term of qualified predicateNULL valueFor an example, see Figure 22-18
EXISTS predicateNULL valueFor an example, see Figure 22-19
Derived table in FROM clauseNon-NULL value*For an example, see Figure 22-20
*
If the result of searching the derived table in the FROM clause is not returned directly to the user, the access count is the NULL value (for an example, see Figure 22-21).

Figures 22-8 through 22-21 show output examples of the access count when the access method is SELECT.

Figure 22-8 Output example of access count (part 1)

[Figure]

Figure 22-9 Output example of access count (part 2)

[Figure]

Figure 22-10 Output example of access count (part 3)

[Figure]

Note: The actual table is the same as in Figure 22-9.

Figure 22-11 Output example of access count (part 4)

[Figure]

Note: The actual table is the same as in Figure 22-9.

Figure 22-12 Output example of access count (part 5)

[Figure]

Figure 22-13 Output example of access count (part 6)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-14 Output example of access count (part 7)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-15 Output example of access count (part 8)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-16 Output example of access count (part 9)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-17 Output example of access count (part 10)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-18 Output example of access count (part 11)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-19 Output example of access count (part 12)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-20 Output example of access count (part 13)

[Figure]

Note: The actual table is the same as in Figure 22-12.

Figure 22-21 Output example of access count (part 14)

[Figure]

Note: The actual table is the same as in Figure 22-12.