22.9 Narrowing the audit trails

By narrowing the audit trails, you can acquire only specific audit trails.

You can narrow audit trails by defining a condition in the CREATE AUDIT definition SQL statement, and then use DROP AUDIT as needed to drop audit trails.

Organization of this section
(1) Selection items
(2) Available selections
(3) Combinations of selection items
(4) Security audit information buffer
(5) HiRDB operation in the event of an error in the security audit information buffer
(6) Change of security audit information buffer status

(1) Selection items

Table 22-24 lists and describes the selection items that can be specified as audit trail narrowing conditions.

Table 22-24 Selection items that can be specified as audit trail narrowing conditions

Selection itemSpecificationDescriptionRecord items in the corresponding audit trail
Operation typeRequiredNarrows the audit trails to be acquired to audit trails of a specified operation type.
  • Event type
  • Event subtype
Trail typeRequiredNarrows the audit trails to be acquired to audit trails of a specified trail type.
  • Audit trail type
Event success/failureRequiredNarrows the audit trails to be acquired on the basis of event success or failure.
  • Event success or failure
Object*OptionalAcquires audit trails by narrowing the objects that became the target of a specific event.
  • Object owner
  • Object name
  • Object type
*
The following rules apply to object specification:
  • Definition of an audit event can be specified even for a nonexistent object name (so that the trails can be narrowed down when an object is created by CREATE).
  • The definition of an audit event cannot be changed even if the object specified in the audit event definition is deleted by another SQL statement, or if one of its attributes (such as its name) is changed. The following are examples:
    Example 1: An audit event definition is specified for table T1, but the table is renamed from T1 to T2. The specification of T1 remains unchanged in the audit event definition.
    Example 2: An audit event definition is specified for table T1, but table T1 is dropped by DROP TABLE. The audit event definition remains as is.
  • In the case of privilege checking trails, trails are acquired only when the target object for privilege checking matches the target object for the event. If you acquire audit trails by narrowing down by object, Hitachi recommends that you acquire audit trails at the time of event termination. Table 22-25 shows whether or not there is output from privilege checking when trails are narrowed down by object.

    Table 22-25 Whether or not there is output from privilege checking when trails are narrowed by object

    Privilege usedWhether or not there are objectsObjects that can be narrowed
    DBANoNone
    SCHNoNone
    CNTNoNone
    RDAYes (RDA)Objects listed at left
    SELYes (FTB, LST, TBL, VIW)Objects listed at left
    INSYes (FTB, TBL, VIW)Objects listed at left
    DELYes (FTB, TBL, VIW)Objects listed at left
    UPDYes (FTB, TBL, VIW)Objects listed at left
    AUDNoNone*
    SYSYes (AUF, TBL)--
    OWNYes (FID, FNC, FSV, FTB, IDX, PRC, SCH, TBL, TRG, TYP, VIW)Objects listed at left
Legend:
--: If the audit facility is executing, audit trails are always output regardless of the audit event definition.
Note:
For details about the privileges that are used and the symbols for indicating whether or not there is an object, see Table 22-15 Audit trail table column.
*
In the case of an auditor's security event, if the security audit facility is executing, audit trails are always output regardless of the audit event definition.

If you have specified a data dictionary table for the target object, specify the object type, authorization identifier, and table identifier as described in Table 22-26.

Table 22-26 Object type, authorization identifier, and table identifier when a data dictionary table is specified

Operation typeObject typeAuthorization identifierTable identifier
Object operation eventVIEWMASTERTable identifiers of the data dictionary tables, excluding the data dictionary tables used by the system
Utility operation eventTABLEOmitted*Table identifiers of all data dictionary tables
*
Even when the authorization identifier is omitted, '(Data dictionary)' is stored in the object owner column in the data dictionary table SQL_AUDITS.

(2) Available selections

The following selections are available:

To acquire audit trails when the target of an object operation event is the table "USER1"."T1" and the target of the audit is the termination trail of an object definition event, define as follows:

CREATE AUDIT AUDITTYPE EVENT FOR ACCESS ON TABLE "USER1"."T1"
CREATE AUDIT AUDITTYPE EVENT FOR DEFINITION

The following describes the trails that are acquired and the trails that are not when the above audit event is defined:

Trails that are acquired:
  • Event termination trail when the table "USER1"."T1" is searched
  • Event termination trail when the CREATE statement is executed
Trails that are not acquired:
  • Trail when a table other than "USER1"."T1" is searched
  • Trail of a connection event
  • Trail of a utility operation event

(3) Combinations of selection items

Some combinations of selection items serve no purpose. For such a combination of selection items, the executed CREATE AUDIT results in an error.

An example is when the object table "USER1"."T1" is specified in CONNECT for a session security event.

(4) Security audit information buffer

To use the security audit facility, a security audit information buffer is required. Therefore, you must estimate the shared memory requirement for the security audit information buffer.

You can do this by estimating manually the specification value and then specifying the value in the pd_audit_def_buffer_size operand, or by having the system determine the value automatically (omit the pd_audit_def_buffer_size operand). When the system determines the value, a margin is added to the memory size to ensure sufficient space. The memory size required is determined by the number of entries of the object that is the target of the narrowed audit. Thus, to determine the size of memory to allocate, the system both adds the value 100 to the number of entries for the object that has been defined, and also multiplies this number of entries by 1.2. It then applies the higher of the two results as the size of memory to allocate. The following shows the margin value:

Number of entries for an object that has already been defined as the target of a narrowed auditConditionMargin value
0NoneValue for 100 entries for the object that is the target of the narrowed audit
1 or greaterN + 100 > [Figure] 1.2Value for 100 entries for the object that is the target of the narrowed audit
N + 100 [Figure] N[Figure] 1.2Value for N[Figure] 0.2 entries
Legend:
N: Number of entries for the object that has already been defined as the target of the narrowed audit

If the required amount of memory cannot be allocated when the security audit information buffer is created, the actions described in Tables 22-27 and 22-28 must be taken.

Note
If the security audit information buffer cannot store definition information for all audit events, processing may slow down because accesses must be made to the data dictionary table.

 

Table 22-27 HiRDB operation and actions to be taken when security audit information buffer is created (during HiRDB startup)

pd_audit_def_buffer_size operand specificationAllocation of shared memoryHiRDB operationAction
SpecifiedFailureDoes not start. In this case, HiRDB displays the KFPD00031-E message.Take one of the following actions:
  • Increase the OS's shared memory size.
  • Provide free space in the OS's shared memory.
  • Decrease the pd_audit_def_buffer_size operand value.
SuccessStarts. If the definition information for all the audit events is not stored in the security audit information buffer, HiRDB displays the KFPD00032-W message.Because performance may decline, re-estimate the pd_audit_def_buffer_size operand value.
OmittedFailureStarts, but does not create the security audit information buffer. In this case, HiRDB displays the KFPD00032-W message.Because performance may decline, take one of the following actions:
  • Increase the OS's shared memory size.
  • Provide free space in the OS's shared memory.
If neither of the above actions can be taken, specify a value in the pd_audit_def_buffer_size operand that is less than the value determined automatically by the system.
SuccessStarts.No action is required.

Table 22-28 HiRDB operation and actions to be taken when security audit information buffer is created (during HiRDB operation)

pd_audit_def_buffer_size operand specificationOverflow of definition information for audit event in security audit information bufferHiRDB operationAction
SpecifiedYesStores as much definition information for audit events as possible in the security audit information buffer and then resumes processing. In this case HiRDB displays the KFPD00032-W message.Re-estimate the value of the pd_audit_def_buffer_size operand according to the displayed KFPD00032-W message. If no action is taken, performance may decline.
NoStores the definition information for all the audit events in the security audit information buffer and then resumes processing.No action is required.
OmittedYesStores as much definition information for audit events as possible in the security audit information buffer and then resumes processing. In this case HiRDB displays the KFPD00032-W message.Restart HiRDB. The system re-calculates the size and creates a security audit information buffer. If the KFPD00032-W message is displayed when HiRDB restarts, take one of the following actions:
  • Increase the OS's shared memory size.
  • Provide free space in the OS's shared memory.
If neither of the above actions can be taken, specify a value in the pd_audit_def_buffer_size operand that is less than the value determined automatically by the system.
NoStores the definition information for all the audit events in the security audit information buffer and then resumes processing.No action is required.

When the pd_audit_def_buffer_size operand is omitted, the specification value is determined automatically by the system. If definitions of audit events increase during HiRDB operation, the size of the security audit information buffer increases the next time HiRDB starts. This means that the size of the security audit information buffer may increase each time HiRDB is started.

(5) HiRDB operation in the event of an error in the security audit information buffer

This subsection describes HiRDB's operation in the event of an error in the security audit information buffer.

(a) At HiRDB startup

The security audit information buffer is created when HiRDB starts. If an error occurs, the HiRDB operation depends on whether the size of the security audit information buffer is being determined automatically by the system or manually by the user.

Table 22-29 describes the causes of errors during HiRDB startup and the HiRDB operations.

Table 22-29 Causes of errors during HiRDB startup and HiRDB operations

Cause of errorHiRDB operation
pd_audit_def_buffer_size operand is omittedpd_audit_def_buffer_size operand is specified
Area allocation errorShared memory for bufferStarts with size 0Cannot start
Process private memory for dictionary searchStarts with size 0Allocates shared memory and then resumes processing
Communication errorStarts with size 0Allocates shared memory and then resumes processing
Dictionary access errorRollback is not requiredStarts with size 0Allocates shared memory and then resumes processing
Rollback is requiredCannot start*Allocates shared memory and then resumes processing
*
For a HiRDB/Parallel Server, if the error cannot be corrected after the specified number of restart attempts, HiRDB places the front-end server in SUSPEND status.
For a HiRDB/Single Server, HiRDB immediately terminates abnormally.
(b) During HiRDB operation

If an error occurs while HiRDB is checking the definition information for audit events, HiRDB outputs the corresponding audit trail regardless of the definition of audit events.

If an error occurs during SQL execution, HiRDB also outputs audit trails. In this case, an error may also occur when the definition information for an audit event is acquired during output of an audit trail. Table 22-30 describes the combinations of errors, the SQL codes to be set, and whether or not rollback is required. If an error occurs during output of audit trails, HiRDB ignores that error and resumes processing.

Table 22-30 Combinations of errors, SQL codes to be set, and whether or not rollback is required

Status before acquisition of audit event definitionStatus during acquisition of audit event definitionSQL code to be setWhether or not rollback is required
NormalNormal0No
Error requiring rollbackSQL code during acquisition of audit event definitionYes
Error not requiring rollbackSQL code before acquisition of audit event definitionNo
Error requiring rollbackNormalSQL code before acquisition of audit event definitionYes
Error requiring rollbackSQL code before acquisition of audit event definitionYes
Error not requiring rollbackSQL code before acquisition of audit event definitionYes
Error not requiring rollbackNormalSQL code before acquisition of audit event definitionNo
Error requiring rollbackSQL code before acquisition of audit event definitionYes
Error not requiring rollbackSQL code before acquisition of audit event definitionNo

(6) Change of security audit information buffer status

When an event occurs, the status of the security audit information buffer changes, such as from disabled to enabled. The following table shows the changes in the security audit information buffer status when an event occurs.

EventStatus of security audit information buffer
Initial status
(before HiRDB start)
Disabled status
(no information has been set)
Enabled status
(information has been set)
Disabled status
(old information remains)
1234
Completion of HiRDB startup processing[Figure]2------
Access to security audit information buffer--[Figure]3[Figure]3[Figure]3
Change to audit event definition
(execution of CREATE AUDIT or DROP AUDIT)
--[Figure]2[Figure]4[Figure]4
Legend:
[Figure]n: When the event occurs, the security audit information buffer is placed in the status indicated by the number n.
--: Not applicable