CREATE AUDIT (Define the target audit event)

Function

CREATE AUDIT defines the target audit event to be recorded as an audit trail, and its target.

Privileges

Audit-privilege users
These users can execute CREATE AUDIT definition statements.

Format

No.Format
1

CREATE AUDIT
 [AUDITTYPE {PRIVILEGE | EVENT | ANY}]

2

 FOR  operation-type

9

  [selection-option]

3

 [WHENEVER {SUCCESSFUL|UNSUCCESSFUL|ANY}]

Details about items

No.Format
2

operation-type::= {ANY
             | SESSION [{session-type | ANY}]
             | PRIVILEGE [{privilege-operation-type | ANY}]
             | DEFINITION [{object-definition-event-type | ANY}]
             | ACCESS  [{object-operation-event-type | ANY}]
             | UTILITY     [{utility-event-type | ANY}]}

9

selection-option::=ON object-name

object-name::=
{FUNCTION authorization-identifier.routine-identifier
|INDEX authorization-identifier.index-identifier
|LIST authorization-identifier.table-identifier
|PROCEDURE authorization-identifier.routine-identifier
|RDAREA RDAREA-name
|SCHEMA authorization-identifier
|TABLE [authorization-identifier.]table-identifier
|TRIGGER authorization-identifier.trigger-identifier
|TYPE authorization-identifier.data-type-identifier
|VIEW authorization-identifier.table-identifier}

     |SEQUENCE authorization-identifier.sequence-generator-identifier}

4

 session-type::=
  {CONNECT | DISCONNECT | AUTHORIZATION}

5

 privilege-operation-type::=
  {GRANT | REVOKE}

6

 object-definition-event-type::=
  {CREATE |DROP | ALTER}

7

 object-operation-event-type::=
  {SELECT | INSERT | UPDATE | DELETE | PURGE | ASSIGN | CALL | LOCK | NEXT VALUE}

8

 utility-event-type::=
  {PDLOAD | PDRORG | PDEXP | PDCONSTCK}

Operands

1) [AUDITTYPE {PRIVILEGE | EVENT | ANY}]
Specifies whether an audit trail during a privilege check is to be acquired or an audit trail on the final results of an event is to be acquired.
PRIVILEGE
Acquires an audit trail during a privilege check.
EVENT
Acquires an audit trail on the final results of an event.
ANY
Acquires an audit trail on any of the above types.
The PRIVILEGE, EVENT, and ANY operands can be defined and deleted individually. For example, if only ANY is deleted using DROP AUDIT on a given audit event for which PRIVILEGE, EVENT, and ANY are all defined, both the PRIVILEGE and EVENT definitions remain intact (remain subject to auditing).
2) operation-type::= ANY
| SESSION [{session-type | ANY}]
| PRIVILEGE [{privilege-operation-type | ANY}]
| DEFINITION [{object-definition-event-type | ANY}]
| ACCESS [{object-operation-event-type | ANY}]
| UTILITY [{utility-event-type | ANY}]
Specifies the type of operation to be audited. The individual operation types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given condition for which SESSION, PRIVILEGE, and ANY are all defined, both the SESSION and PRIVILEGE definitions remain intact (remain subject to auditing).
ANY
Specifies all operation types as being subject to auditing.
SESSION [{session-type | ANY}]
Specify this operand when session security events are to be made subject to auditing.
ANY makes any session security event subject to auditing. The individual session types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT on a given condition for which CONNECT, AUTHORIZATION, and ANY are defined, both the CONNECT and AUTHORIZATION definitions remain intact (remain subject to auditing).
PRIVILEGE [{privilege-operation-type | ANY}]
Specify this operand when privilege management events are to be made subject to auditing. ANY makes any privilege management event subject to auditing. The individual privilege operation types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given condition for which GRANT, REVOKE, and ANY are defined, both the GRANT and REVOKE definitions remain intact (remain subject to auditing).
DEFINITION [{object-definition-event-type | ANY}]
Specify this operand when definition SQL events are to be made subject to auditing. ANY makes any definition SQL event subject to auditing. The individual object definition event types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given condition for which CREATE, DROP, and ANY are defined, both the CREATE and DROP definitions remain intact (remain subject to auditing).
ACCESS [{object-operation-event-type | ANY}]
Specify this operand when manipulation SQL events are to be made subject to auditing. ANY makes any manipulation SQL event subject to auditing. The individual object operation types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given condition for which SELECT, INSERT, and ANY are defined, both the SELECT and INSERT definitions remain intact (remain subject to auditing).
UTILITY [{utility-event-type | ANY}]
Specify this operand when utility events are to be made subject to auditing. ANY makes any utility event subject to auditing. The individual utility event types and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given condition for which PDLOAD, PDRORG, and ANY are defined, both the PDLOAD and PDRORG definitions remain intact (remain subject to auditing).
3) [WHENEVER {SUCCESSFUL | UNSUCCESSFUL | ANY}]
Specifies whether a given audit event is to be audited, depending on whether the file result of the audit event or a privilege check is successful.
The following table describes the audit trail that is acquired based on the specification in WHENEVER.

Table 3-10 Audit trail that is acquired based on the specification in WHENEVER

WHENEVER
specification
When PRIVILEGE or
ANY is specified in AUDITTYPE
When EVENT or
ANY is specified in AUDITTYPE
SUCCESSFULAudit trail during a privilege check is collected only when the privilege check is successful.Audit trail of the final result of an audit event is collected only when the audit event is successful.
UNSUCCESSFULAudit trail during a privilege check is collected only when the privilege check is unsuccessful.Audit trail of the final result of an audit event is collected only when the audit event is unsuccessful.
ANYAudit trail during a privilege check is collected regardless of whether or not the privilege check is successful.Audit trail of the final result of an audit event is collected regardless of whether or not the audit event is successful.
Some final results of an event can be partially unsuccessful. For a partially unsuccessful event, an audit trail is output regardless of whether SUCCESSFUL, UNSUCCESSFUL, or ANY is specified.
SUCCESSFUL, UNSUCCESSFUL, and ANY are individually defined and deleted. For example, if only ANY is deleted using DROP AUDIT for a given audit event for which SUCCESSFUL, UNSUCCESSFUL, and ANY are defined, both the SUCCESSFUL and UNSUCCESSFUL definitions remain intact (remain subject to auditing).
4) session-type: : = {CONNECT | DISCONNECT | AUTHORIZATION}
Specifies the type of HiRDB session as either the object of the audit, user modification while connected, or the disconnection operation. The following table lists session types and the operations that are generated by the associated audit events.

Table 3-11 Session types and operations generated by the associated audit events

Session typeOperation generated by privilege check audit event (PRIVILEGE specified in AUDITTYPE)Operation generated by audit event acquiring audit trail on final results of an event (EVENT specified in AUDITTYPE)
CONNECTConnection to HiRDBSame as indicated at left
DISCONNECTNoneDisconnection from HiRDB
AUTHORIZATIONExecution of SET SESSION AUTHORIZATION statementSame as indicated at left
5) privilege-operation-type::= {GRANT | REVOKE}
Specify this operand when operations related to privileges are to be subject to auditing. The following table lists privilege operation types and the operations that are generated by the associated audit events.

Table 3-12 Privilege operation types and operations generated by the associated audit events

Privilege operation typeOperation generated by privilege check audit event (PRIVILEGE specified in AUDITTYPE)Operation generated by audit event acquiring audit trail on final results of an event (EVENT specified in AUDITTYPE)
GRANTExecution of GRANTSame as indicated at left
REVOKEExecution of REVOKESame as indicated at left
6) object-definition-event-type::={CREATE | DROP | ALTER}
Specifies creation, deletion, or definition change operations on the object to be monitored. The following table lists object definition event types and the operations that are generated by the associated audit events.

Table 3-13 Object definition event types and operations generated by the associated audit events

Object definition event typeOperation generated by privilege check audit event (PRIVILEGE specified in AUDITTYPE)Operation generated by audit event acquiring audit trail on final results of an event (EVENT specified in AUDITTYPE)
CREATEExecution of the following SQL statements:
  • ALTER PROCEDURE#1
  • ALTER ROUTINE#1
  • ALTER TRIGGER#1
  • ASSIGN LIST statement
  • CREATE CONNECTION SECURITY
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE PROCEDURE
  • CREATE PUBLIC FUNCTION
  • CREATE PUBLIC PROCEDURE
  • CREATE SCHEMA
  • CREATE SEQUENCE
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE TYPE
  • CREATE VIEW
  • CREATE PUBLIC VIEW
  • CALL statement from a UAP#2
Same as indicated at left
DROPExecution of the following SQL statements:
  • DROP CONNECTION SECURITY
  • DROP DATA TYPE
  • DROP FUNCTION
  • DROP INDEX
  • DROP LIST statement
  • DROP PROCEDURE
  • DROP PUBLIC FUNCTION
  • DROP PUBLIC PROCEDURE
  • DROP SCHEMA
  • DROP SEQUENCE
  • DROP TABLE
  • DROP TRIGGER
  • DROP VIEW
  • DROP PUBLIC VIEW
  • REVOKE#3
Same as indicated at left
ALTERExecution of the following SQL statements:
  • ALTER INDEX
  • ALTER PROCEDURE
  • ALTER ROUTINE
  • ALTER TABLE
  • ALTER TRIGGER
  • COMMENT
Same as indicated at left
#1: Internally, CREATE PROCEDURE is executed.
#2: If index information on the procedure being called is invalid, CREATE PROCEDURE is internally executed on each call. In this case, the execution of CREATE PROCEDURE on every call can be suppressed by recreating the SQL object for the procedure that is called by using either ALTER PROCEDURE or ALTER ROUTINE.
#3: If the SELECT privilege from a view base table is deleted, DROP VIEW is internally executed to delete the view table.
The following privilege checks are performed in an object definition event:
  • Schema definition privilege check in an audit event
  • Access privilege check on data manipulation SQL and control SQL statements in an SQL procedure statement either during the creation of a stored procedure or during the re-creation of an SQL object for a stored procedure
  • Access privilege check on data manipulation SQL and control SQL statements in an SQL procedure statement either during the definition of a user-defined type in which a member includes a procedure, or during the re-creation of an SQL object for a user-defined type member
  • Access privilege check on data manipulation SQL and control SQL statements in an SQL procedure statement either during the definition of a trigger or during the re-creation of an SQL object for a trigger
  • Access privilege check on a base table during the definition of a view
7) object-operation-event-type::=
{SELECT|INSERT|UPDATE|DELETE|PURGE|ASSIGN|CALL|LOCK | NEXT VALUE}
This operand specifies an operation on the object to be audited. If an audit target definition for an object operation event is specified, object operations in a procedure and in a trigger SQL statement are also subject to auditing. The following table lists object operation event types and the operations generated by the associated audit events.

Table 3-14 Object operation event types and operations generated by the associated audit events

Object operation event typeOperation generated by privilege check audit event (PRIVILEGE specified in AUDITTYPE)Operation generated by audit event acquiring audit trail on final results of an event (EVENT specified in AUDITTYPE)
SELECT
  • Execution of single-row SELECT statement#
  • Execution of INSERT statement with query specification#
  • Execution of UPDATE statement with subquery specification in a search condition#
  • Execution of DELETE statement with subquery specification in a search condition#
  • Execution of a query on a list#
  • Same as indicated at left
INSERT
  • Execution of INSERT statement#
  • Same as indicated at left
UPDATE
  • Execution of UPDATE statement#
  • Same as indicated at left
DELETE
  • Execution of DELETE statement#
  • Same as indicated at left
PURGE
  • Execution of PURGE TABLE statement#
  • Same as indicated at left
CALL
  • No privilege check events
  • Execution of a procedure by CALL statement#
LOCK
  • Execution of LOCK statement#
  • Same as indicated at left
ASSIGN
  • Execution of ASSIGN LIST statement Format 1
  • Execution of ASSIGN LIST statement Format 1
  • Execution of ASSIGN LIST statement Format 2
NEXT VALUE
  • Execution of NEXT VALUE expression
  • Same as indicated at left
#: Includes dynamic SELECT statements.
Queries in the following SQL statements produce an audit trail if an object operation event type is defined in SELECT:
8) utility-event-type::=
{PDLOAD | PDRORG | PDEXP | PDCONSTCK}
This operand defines a utility event as being subject to auditing. The following table lists utility event types and the operations generated by the associated audit events.

Table 3-15 Utility event types and operations generated by the associated audit events

Utility event typeOperation generated by privilege check audit event (PRIVILEGE specified in AUDITTYPE)Operation generated by audit event acquiring audit trail on final results of an event (EVENT specified in AUDITTYPE)
PDLOADExecution of pdloadSame as indicated at left
PDRORGExecution of pdrorgSame as indicated at left
PDEXPExecution of pdexp or pddefrevSame as indicated at left
PDCONSTCKExecution of pdconstckSame as indicated at left
9) selection-option::=ON object-name
Specify this operand to select an object from which to collect an audit trail. In object-name, specify the object from which you want to collect the audit trail. For details about the naming rules for object names, see 1.1.7 Specification of names.
If a public view, public procedure, or public function is specified in an object, specify PUBLIC for the authorization identifier.
To specify a dictionary table, specify TABLE for the object name, and specify only the table identifier without the authorization identifier. In this case, the object owner column of the dictionary table SQL_AUDITS stores '(Data dictionary)'.

Rules

  1. For details about the security audit facility, see the HiRDB Version 9 System Operation Guide.
  2. Actual recording of an audit trail requires either the setting of the pd_audit operand of the system definition or the execution of the pdaudbegin command.
  3. When the security audit facility is enabled, audit trails from the execution of CREATE AUDIT or DROP AUDIT are always recorded.

Notes

  1. CREATE AUDIT cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. A trail may not always be output when an audit-target event is defined, depending on the combination of the operation type and other operands. When such a definition is specified, the KFPA19680-E message is output. The following table provides details about specifiable combinations.

    Table 3-16 Event type, event subtype, and specifiability of AUDITTYPE

    Event typeEvent subtypeAUDITTYPE specifiability
    PRIVILEGEEVENTANY
    ANY--C#1, #2YC#1, #2
    SESSIONDISCONNECTNYC#2
    ANYC#2YC#2
    Any subtype other than the aboveYYY
    PRIVILEGEAny subtypeYYY
    DEFINITIONAny subtypeYYY
    ACCESSCALLNYC#1
    ANYC#1YC#1
    Any subtype other than the aboveYYY
    UTILITYAny subtypeYYY
Legend:
--: Not applicable
Y: Specifiable
N: Not specifiable (KFPA19680-E message generated)
C: Specifiable with or without audit trail output
#1: Does not output an audit trail for event CALL privilege checks.
#2: Does not output an audit trail for event DISCONNECT privilege checks.

Table 3-17 Event type, event subtype, and specifiability of object name (1/2)

Event typeEvent subtypeFCTNINDEXLISTPRCDRRDSCHM
ANY--CCCCCC
SESSIONAllNNNNNN
PRIVILEGEGRANTNNNNNN
REVOKENNNNNN
ANYNNNNNN
DEFINITIONCREATEYYNYYY
DROPYYNYNY
ALTERYYNYYN
ANYYYNYCC
ACCESSSELECTNNYNNN
INSERTNNNNNN
UPDATENNNNNN
DELETENNNNNN
PURGENNNNNN
ASSIGNNNYNNN
CALLNNNYNN
LOCKNNNNNN
NEXT VALUENNNNNN
ANYNNCCNN
UTILITYPDLOADNNNNNN
PDRORGNNNNNY
PDEXPNNNYNN
PDCONSTCKNNNNNN
ANYNNNCNC
Legend:
FCTN: FUNCTION
PRCDR: PROCEDURE
RD: RDAREA
SCHM: SCHEMA
--: Not applicable
Y: Specifiable
N: Not specifiable (KFPA19680-E message generated)
C: Specifiable with or without audit trail output

Table 3-18 Event type, event subtype, and specifiability of object name (2/2)

Event typeEvent subtypeSVRTBLTRGRTYPVIEWSEQ
ANY--CCCCCC
SESSIONAllNNNNNN
PRIVILEGEGRANTNYNNYN
REVOKENYNNYN
ANYNYNNYN
DEFINITIONCREATEYYYYYY
DROPYYYYYY
ALTERNYYNYN
ANYCYYCYC
ACCESSSELECTNYNNYN
INSERTNYNNYN
UPDATENYNNYN
DELETENYNNYN
PURGENYNNNN
ASSIGNNYNNNN
CALLNNNNNN
LOCKNYNNYN
NEXT VALUENNNNNY
ANYNCNNCC
UTILITYPDLOADNYNNNY
PDRORGNYNNNN
PDEXPNYYNYN
PDCONSTCKNYNNNN
ANYNYCNCC
Legend:
SVR: SERVER
TBL: TABLE
TRGR: TRIGGER
TYP: TYPE
SEQ: SEQUENCE
--: Not applicable
Y: Specifiable
N: Not specifiable (KFPA19680-E message generated)
C: Specifiable with or without audit trail output
  1. When the HiRDB version is upgraded with ANY specified in the AUDITTYPE clause, in FOR <operation-type> and each type, or in the WHENEVER clause, if the number of individual types increases as a result, all of these types are included as audit targets.
    For example, if the version is upgraded with CREATE AUDIT FOR ANY defined and the number of operation types increases as a result, the increased operation types also become audit targets.
  2. You cannot execute the definition of an already defined audit event in a CREATE AUDIT statement. If you attempt to do so, the KFPA11908-E message is output.

Examples

  1. Define privilege checks on all audit events as being subject to auditing.

    CREATE AUDIT FOR ANY WHENEVER ANY

  2. Define privilege checks on connection to HiRDB as being subject to auditing.

    CREATE AUDIT FOR SESSION CONNECT

  3. Define privilege checks on the execution of the GRANT statement as being subject to auditing.

    CREATE AUDIT FOR PRIVILEGE GRANT

  4. Define privilege checks on the creation of an object as being subject to auditing.

    CREATE AUDIT FOR DEFINITION CREATE

  5. Define privilege checks on INSERT as being subject to auditing.

    CREATE AUDIT FOR ACCSESS INSERT

  6. Define all audit events as being subject to auditing.

    CREATE AUDIT AUDITTYPE ANY FOR ANY

  7. Define the termination of any audit event as being subject to auditing.

    CREATE AUDIT AUDITTYPE EVENT FOR ANY

  8. Specify the object from which to acquire an audit trail as table USER1.T1.

     CREATE AUDIT AUDITTYPE EVENT FOR ANY ON TABLE "USER1"."T1"