CREATE AUDIT POLICY statement

The CREATE AUDIT POLICY statement defines an auditing policy at the current server. The policy determines what categories are to be audited; it can then be applied to other database objects to determine how the use of those objects is to be audited.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include SECADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramCREATE AUDIT POLICYpolicy-name●CATEGORIES,1ALLAUDITCHECKINGCONTEXTEXECUTEWITHOUT DATAWITH DATAOBJMAINTSECMAINTSYSADMINVALIDATESTATUSBOTHFAILURENONESUCCESS●ERROR TYPE NORMALAUDIT ●
Notes:
  • 1 Each category can be specified at most once (SQLSTATE 42614), and no other category can be specified if ALL is specified (SQLSTATE 42601).

Description

policy-name
Names the audit policy. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The policy-name must not identify an audit policy already described in the catalog (SQLSTATE 42710). The name must not begin with the characters 'SYS' (SQLSTATE 42939).
CATEGORIES
A list of one or more audit categories for which a status is specified. If ALL is not specified, the STATUS of any category that is not explicitly specified is set to NONE.
ALL
Sets all categories to the same status. The EXECUTE category is WITHOUT DATA.
AUDIT
Generates records when audit settings are changed or when the audit log is accessed.
CHECKING
Generates records during authorization checking of attempts to access or manipulate database objects or functions.
CONTEXT
Generates records to show the operation context when a database operation is performed.
EXECUTE
Generates records to show the execution of SQL statements.
WITHOUT DATA or WITH DATA
Specifies whether or not input data values provided for any host variables and parameter markers should be logged as part of the EXECUTE category.
WITHOUT DATA
Input data values provided for any host variables and parameter markers are not logged as part of the EXECUTE category. WITHOUT DATA is the default.
WITH DATA
Input data values provided for any host variables and parameter markers are logged as part of the EXECUTE category. Not all input values are logged; specifically, LOB, LONG, XML, and structured type parameters appear as the null value. Date, time, and timestamp fields are logged in ISO format. The input data values are converted to the database code page before being logged. If code page conversion fails, no errors are returned and the unconverted data is logged.
OBJMAINT
Generates records when data objects are created or dropped.
SECMAINT
Generates records when object privileges, database privileges, or DBADM authority is granted or revoked. Records are also generated when the database manager security configuration parameters sysadm_group, sysctrl_group, or sysmaint_group are modified.
SYSADMIN
Generates records when operations requiring SYSADM, SYSMAINT, or SYSCTRL authority are performed.
VALIDATE
Generates records when users are authenticated or when system security information related to a user is retrieved.
STATUS
Specifies a status for the specified category.
BOTH
Successful and failing events will be audited.
FAILURE
Only failing events will be audited.
SUCCESS
Only successful events will be audited.
NONE
No events in this category will be audited.
ERROR TYPE
Specifies whether audit errors are to be returned or ignored.
NORMAL
Any errors generated by the audit are ignored and only the SQLCODEs for errors associated with the operation being performed are returned to the application.
AUDIT
All errors, including errors occurring within the audit facility itself, are returned to the application.

Rules

  • An AUDIT-exclusive SQL statement must be followed by a COMMIT or ROLLBACK statement (SQLSTATE 5U021). AUDIT-exclusive SQL statements are:
    • AUDIT
    • CREATE AUDIT POLICY, ALTER AUDIT POLICY, or DROP (AUDIT POLICY)
    • DROP (ROLE or TRUSTED CONTEXT if it is associated with an audit policy)
  • An AUDIT-exclusive SQL statement cannot be issued within a global transaction (SQLSTATE 51041) such as, for example, an XA transaction.

Notes

  • Only one uncommitted AUDIT-exclusive SQL statement is allowed at a time across all database partitions. If an uncommitted AUDIT-exclusive SQL statement is executing, subsequent AUDIT-exclusive SQL statements wait until the current AUDIT-exclusive SQL statement commits or rolls back.
  • Changes are written to the system catalog, but do not take effect until they are committed, even for the connection that issues the statement.

Example

Create an audit policy to audit successes and failures for the AUDIT and OBJMAINT categories; only failures for the SECMAINT, CHECKING, and VALIDATE categories, and no events for the other categories.
   CREATE AUDIT POLICY DBAUDPRF
     CATEGORIES AUDIT    STATUS BOTH,
                SECMAINT STATUS FAILURE,
                OBJMAINT STATUS BOTH,
                CHECKING STATUS FAILURE,
                VALIDATE STATUS FAILURE
     ERROR TYPE NORMAL