CREATE AUDIT (Define the target audit event)

Function

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::=
{ALIAS authorization-identifier.table-alias
|FOREIGN INDEX authorization-identifier.index-identifier
|FOREIGN TABLE authorization-identifier.table-identifier
|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
|SERVER foreign-server-definition-owner.foreign-server-name
|TABLE [authorization-identifier.]table-identifier
|TRIGGER authorization-identifier.trigger-identifier
|TYPE authorization-identifier.data-type-identifier
|USER MAPPING SERVER foreign-server-name
|VIEW authorization-identifier.table-identifier}

4

 session-type::=
  {CONNECT | 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}

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.
Table 3-10 shows 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 | AUTHORIZATION}
Specifies a change in users who are connected or who are connecting to the HiRDB that is subject to auditing. The following table shows 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
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 shows 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 shows 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 PROCEDURE1
  • ALTER ROUTINE1
  • ALTER TRIGGER1
  • ASSIGN LIST statement
  • CREATE ALIAS
  • CREATE FOREIGN INDEX
  • CREATE FOREIGN TABLE
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE PROCEDURE
  • CREATE SCHEMA
  • CREATE SERVER
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE TYPE
  • CREATE USER MAPPING
  • CREATE VIEW
  • CREATE PUBLIC VIEW
  • CALL statement from a UAP2
Same as indicated at left
DROPExecution of the following SQL statements:
  • DROP ALIAS
  • DROP DATA TYPE
  • DROP FOREIGN INDEX
  • DROP FOREIGN TABLE
  • DROP FUNCTION
  • DROP INDEX
  • DROP LIST statement
  • DROP PROCEDURE
  • DROP SCHEMA
  • DROP SERVER
  • DROP TABLE
  • DROP TRIGGER
  • DROP USER MAPPING
  • DROP VIEW
  • DROP PUBLIC VIEW
  • REVOKE3
Same as indicated at left
ALTERExecution of the following SQL statements:
  • 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}
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 shows 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
* 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 shows 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.
When a public view is specified for the object, specify PUBLIC as 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 8 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 shows details about specifiable combinations.

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

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

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

Event typeEvent subtypeALIASFRGN
INDEX
FRGN
TABLE
FCTNINDEXLISTPRCDR
ANY[Figure]CCCCCCC
SESSIONAllNNNNNNN
PRIVILEGEGRANTNNYNNNN
REVOKENNYNNNN
ANYNNYNNNN
DEFINITIONCREATEYYYYYNY
DROPYYYYYNY
ALTERNNYYYNY
ANYCCYYYNY
ACCESSSELECTYNYNNYN
INSERTYNYNNNN
UPDATEYNYNNNN
DELETEYNYNNNN
PURGEYNNNNNN
ASSIGNNNNNNYN
CALLNNNNNNY
LOCKYNYNNNN
ANYCNCNNCC
UTILITYPDLOADNNNNNNN
PDRORGNNNNNNN
PDEXPYNNNNNY
PDCONSTCKNNNNNNN
ANYCNNNNNC
Legend:
FRGN: FOREIGN
FCTN: FUNCTION
PRCDR: PROCEDURE
[Figure]: 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 subtypeRDSCHMSVRTBLTRGRTYPUSR
MPG
VIEW
ANY[Figure]CCCCCCCC
SESSIONAllNNNNNNNN
PRIVILEGEGRANTNNNYNNNY
REVOKENNNYNNNY
ANYNNNYNNNY
DEFINITIONCREATEYYYYYYYY
DROPNYYYYYYY
ALTERYNNYYNNY
ANYCCCYYCCY
ACCESSSELECTNNNYNNNY
INSERTNNNYNNNY
UPDATENNNYNNNY
DELETENNNYNNNY
PURGENNNYNNNN
ASSIGNNNNYNNNN
CALLNNNNNNNN
LOCKNNNYNNNY
ANYNNNCNNNC
UTILITYPDLOADNNNYNNNN
PDRORGNYNYNNNN
PDEXPNNNYYNNY
PDCONSTCKNNNYNNNN
ANYNCNYCNNC
Legend:
RD: RDAREA
SCHM: SCHEMA
SVR: SERVER
TBL: TABLE
TRGR: TRIGGER
TYP: TYPE
USR MPG: USER MAPPING
[Figure]: 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"