22.3.2 Output patterns at event termination

Multiple audit trails are collected for each event (one audit trail for each event-target object). As a rule, the same contents are output for all items, except for the operation-target object information column of the audit trail table. However, if the result of an event is different for each operation target, such as a utility, different contents are output for each termination code. Examples of audit trail output patterns are described below.

Organization of this subsection
(1) Multiple target objects or target users
(2) SQL that runs internally
(3) Dynamic SQL

(1) Multiple target objects or target users

(a) Multiple target objects

For example, when SELECT * FROM "T1","T2" is executed, two audit trail lines are output, one for object T1 and one for T2, as shown below. In this case, the same termination code is set for both.

...Event subtype...Object name...Termination code...
...SEL...T1...XXX...
...SEL...T2...XXX...

An event, such as routine re-creation or the utility for reorganizing a database in units of schemas, may succeed or fail depending on the object. In this case, a different termination code is output for each object. Table 22-5 lists events that sometimes have multiple target objects.

Table 22-5 Events that sometimes have multiple target objects (event execution units and audit trail record output units)

EventsExecution unitTarget output unitsNumber of output recordsSQL code or termination code
GRANT,
REVOKE
UserUsersNumber of target usersSame value for all event-target objects
GroupGroupsNumber of target groups
RoleRolesNumber of target roles
CREATE CONNECTION SECURITY,
DROP CONNECTION SECURITY
Setting value of the connection security facilitySetting values of the connection security facilityNumber of setting values of the connection security facility
Data manipulation SQLBase tableBase tablesNumber of target base tables
View tableView tablesNumber of target view tables
Table aliasTable aliasesNumber of target table aliases
Foreign tableForeign tablesNumber of target foreign tables
ALTER ROUTINE,
ALTER PROCEDURE,
ALTER TRIGGER
Multiple routines or triggers1Procedures, functions, or triggersOne for each target routineDifferent value for each event-target object
pdexp,
pddefrev
Base tableBase tablesNumber of target base tables
Viewed tableView tablesNumber of target view tables
Table aliasTable aliasesNumber of target table aliases
Stored procedureStored proceduresNumber of target stored procedures
TriggerTriggersNumber of target trigger count
pdrorgSchema,2 base table, or indexBase tablesNumber of target base tables
pdaudbegin,
pdaudend
UnitUnitsNumber of target units
1 If routine information cannot be collected, NULL is entered for the object information and only a single audit trail line is output.
2 In the case of reorganizing in units of schemas, if the table information in the schema cannot be collected, schema information is entered for the object information, and only a single audit trail line is output.
(b) Subqueries

For example, when UPDATE "T1" SET "C1" = (SELECT "C1" FROM "T2" WHERE CODE = '01') WHERE CODE = '01' is executed, information on the accessed objects is output as in (a) above. In this case, audit trails of the actions performed on the objects are output for the event type and subtypes as shown below. The same value is entered for the SQL code in the audit trail at each event's termination.

...Event subtype...Object name...Termination code...
...UPD...T1...XXX...
...SEL...T2...XXX...

(2) SQL that runs internally

In some cases, another SQL is executed as an extension of an executed SQL. If an SQL that is executed as an extension when an event is executed is defined as the audit target, information is collected on the target object of the SQL that is executed as an extension, or on the user. In this case, the event type and subtype of the SQL that is executed as an extension are entered as the event type and subtype of the object or user. Furthermore, for all objects, the same value is entered for the SQL code of each audit trail.

For example, if a table is deleted as an extension of DROP SCHEMA, object information on the table is output as the accessed object, separately from schema information. If the deletion process is terminated by an error, an audit trail is output for each user and each object that has been accessed up to the time of the error.

In procedures and triggers, an SQL is executed automatically as an extension of another SQL. In this case, an event termination audit trail is output for each SQL that is executed as an extension. In the case of a trigger, because the executor switches temporarily to the trigger owner, the trigger owner is entered as the event executor. In the case of a procedure, an event termination audit trail is also output when a CALL statement is terminated. If the procedure is nested, an event termination audit trail is output for each CALL statement. Table 22-6 shows audit trail record output units for trigger and procedure execution.

Table 22-6 Audit trail record output units for trigger and procedure execution

TypeEvent executor (authorization identifier value)Audit trail record output unit
TriggerUser triggerTrigger ownerSQL units in the trigger
System trigger (internal trigger of a referential constraint, etc.)Trigger ownerSQL units in the system trigger
ProcedureProcedure executorSQL units in the procedure + CALL statement

If an error occurs in an SQL in a trigger or procedure, the same SQL record is output as the event results of the SQL at that point in time, the event results of the SQL that became the trigger, and the results of the CALL statement. Table 22-7 shows the error locations in a trigger and the audit trail (SQL code) details. Table 22-8 shows the error locations in a procedure and the audit trail (SQL code) details.

Table 22-7 Error locations in a trigger and audit trail (SQL code) details

BEFORE trigger SQL result
[1]
Trigger SQL result
[2]
AFTER trigger SQL result
[3]
Audit trail (SQL code) details
Audit trail of [1]Audit trail of [2]Audit trail of [3]
Error[Figure][Figure]SQL record of [1]SQL record of [1]N
NormalError[Figure]SQL record of [2]N
NormalErrorSQL record of [3]SQL record of [3]
Normal000
Legend:
[Figure]: Not applicable
N: No audit trail (SQL code) is output.

Table 22-8 Error locations in a procedure and audit trail (SQL code) details

Nesting?Details of event for which audit trail is to be collectedAudit trail (SQL code) details
[1][2][3][4][5]Audit trail of [1]Audit trail of [2]Audit trail of [3]Audit trail of [4]Audit trail of [5]
NoE[Figure][Figure][Figure]ESQL record of [1]NNNSQL record of [1]
Nrm[Figure][Figure][Figure]E0NNNSQL record of [5]
Nrm0NNN0
YesE[Figure][Figure][Figure]ESQL record of [1]NNNSQL record of [1]
NrmEE[Figure]E0SQL record of [2]SQL record of [2]NSQL record of [2]
NrmE[Figure]E00SQL record of [3]NSQL record of [3]
NrmEE000SQL record of [4]SQL record of [4]
NrmE0000SQL record of [5]
Nrm00000
Legend:
[Figure]: Not applicable
E: Error
Nrm: Normal
N: No audit trail (SQL code) is output.
[1]: Result of SQL in a procedure before nesting
[2]: Result of SQL in a procedure after nesting
[3]: Result of a nested CALL statement
[4]: Result of SQL in a procedure before nesting
[5]: Result of a CALL statement
[Figure]

(3) Dynamic SQL

In the case of a dynamic SQL, the entire process from PREPARE to execution is treated as a single SQL, and an event termination audit trail is output for that unit. An event termination audit trail during execution is output when OPEN, CLOSE or EXECUTE is completed. Figure 22-3 shows the flow of a dynamic SQL depending on the type of data manipulation SQL.

Figure 22-3 Flow of a dynamic SQL depending on the type of data manipulation SQL

[Figure]

Explanation
The shaded SQLs are triggers for output of an audit trail. Note that the audit trail collection method for PREPARE, CLOSE and OPEN depends on where an error occurs. Table 22-9 shows whether an audit trail is output depending on the success or failure of an event during dynamic SQL execution.

Table 22-9 Audit trail output depending on the success or failure of an event during dynamic SQL execution

SQL resultCollection specification
Upon successUpon failureBoth
PREPARE successfulOPEN (EXECUTE) successfulCLOSE successfulOutput on CLOSE (EXECUTE)Not outputOutput on CLOSE (EXECUTE)
CLOSE failedNot outputOutput on CLOSE (EXECUTE)Output on CLOSE (EXECUTE)
OPEN (EXECUTE) failedNot outputOutput on OPEN (EXECUTE)
PREPARE failed[Figure]Not outputOutput on PREPARE
EXECUTE IMMEDIATEOutput on EXECUTE IMMEDIATE

Legend:

[Figure]: Not applicable

Note
An event termination audit trail is output when the cursor is closed by executing the CLOSE statement internally without having to execute it explicitly. For the timing, see the section on the CLOSE statement in the manual HiRDB Version 8 SQL Reference.