DB2 10.5 for Linux, UNIX, and Windows

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


>>-CREATE AUDIT POLICY--policy-name--●--CATEGORIES-------------->

   .-,--------------------------------------------------------.   
   V  (1)                                                     |   
>----------+-ALL-----------------------+--STATUS--+-BOTH----+-+-->
           +-AUDIT---------------------+          +-FAILURE-+     
           +-CHECKING------------------+          +-NONE----+     
           +-CONTEXT-------------------+          '-SUCCESS-'     
           |          .-WITHOUT DATA-. |                          
           +-EXECUTE--+--------------+-+                          
           |          '-WITH DATA----' |                          
           +-OBJMAINT------------------+                          
           +-SECMAINT------------------+                          
           +-SYSADMIN------------------+                          
           '-VALIDATE------------------'                          

>--●--ERROR TYPE--+-NORMAL-+--●--------------------------------><
                  '-AUDIT--'

Notes:

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