ALTER AUDIT POLICY statement

The ALTER AUDIT POLICY statement modifies the definition of an audit policy at the current server.

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 diagramALTER AUDIT POLICYpolicy-name1CATEGORIES,2ALLAUDITCHECKINGCONTEXTEXECUTEWITHOUT DATAWITH DATAOBJMAINTSECMAINTSYSADMINVALIDATESTATUSBOTHFAILURENONESUCCESSERROR TYPENORMALAUDIT
Notes:
  • 1 Each of the CATEGORIES and ERROR TYPE clauses can be specified at most once (SQLSTATE 42614).
  • 2 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
Identifies the audit policy that is to be altered. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The name must uniquely identify an existing audit policy at the current server (SQLSTATE 42704).
CATEGORIES
A list of one or more audit categories for which a new status value is specified. If ALL is not specified, the STATUS of any category that is not explicitly specified remains unchanged.
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.
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 DROP (TRUSTED CONTEXT) if the role or trusted context 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.
  • If the audit policy that is being altered is currently associated with a database object, the changes do not take effect until the next unit of work for the application that is affected by the change. For example, if the audit policy is in use for the database, no current units of work will see the change to the policy until after a COMMIT or a ROLLBACK statement for that unit of work completes.

Example

Alter the SECMAINT, CHECKING, and VALIDATE categories of an audit policy named DBAUDPRF to audit both successes and failures.
   ALTER AUDIT POLICY DBAUDPRF
     CATEGORIES SECMAINT STATUS BOTH,
                CHECKING STATUS BOTH,
                VALIDATE STATUS BOTH