AUDIT statement

The AUDIT statement determines the audit policy that is to be used for a particular database or database object at the current server. Whenever the object is in use, it is audited according to that policy.

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

Notes:

¹ Each clause (with the same object name, if applicable) can be specified at most once (SQLSTATE 42713).

Description

ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, SECADM, SQLADM, SYSADM, SYSCTRL, SYSMAINT, SYSMON, or WLMADM

Specifies that an audit policy is to be associated with or removed from the specified authority. All auditable events that are initiated by a user who holds the specified authority, even if that authority is not required for the event, will be audited according to the associated audit policy.

DATABASE

Specifies that an audit policy is to be associated with or removed from the database at the current server. All auditable events that occur within the database are audited according to the associated audit policy.

TABLE table-name

Specifies that an audit policy is to be associated with or removed from table-name. The table-name must identify a table, materialized query table (MQT), or nickname that exists at the current server (SQLSTATE 42704). It cannot be a view, a catalog table, a created temporary table, a declared temporary table (SQLSTATE 42995), or a typed table (SQLSTATE 42997). Only EXECUTE category audit events, with or without data, will be generated when the table is accessed, even if the policy indicates that other categories should be audited.

TRUSTED CONTEXT context-name

Specifies that an audit policy is to be associated with or removed from context-name. The context-name must identify a trusted context that exists at the current server (SQLSTATE 42704). All auditable events that happen within the trusted connection defined by the trusted context context-name will be audited according to the associated audit policy.

USER authorization-name

Specifies that an audit policy is to be associated with or removed from the user with authorization ID authorization-name. All auditable events that are initiated by authorization-name will be audited according to the associated audit policy.

GROUP authorization-name

Specifies that an audit policy is to be associated with or removed from the group with authorization ID authorization-name. All auditable events that are initiated by users who are members of authorization-name will be audited according to the associated audit policy. If user membership in a group cannot be determined, the policy will not apply to that user.

ROLE authorization-name

Specifies that an audit policy is to be associated with or removed from the role with authorization ID authorization-name. The authorization-name must identify a role that exists at the current server (SQLSTATE 42704). All auditable events that are initiated by users who are members of authorization-name will be audited according to the associated audit policy. Indirect role membership through other roles or groups is valid.

USING, REMOVE, or REPLACE

Specifies whether the audit policy should be used, removed, or replaced for the specified object.

USING: Specifies that the audit policy is to be used for the specified object. An existing audit policy must not already be defined for the object (SQLSTATE 5U041). If an audit policy already exists, it must be removed or replaced.
REMOVE: Specifies that the audit policy is to be removed from the specified object. Use of the object will no longer be audited according to the audit policy. The association is deleted from the catalog when the audit policy is removed from the object.
REPLACE: Specifies that the audit policy is to replace an existing audit policy for the specified object. This combines both REMOVE and USING options into one step to ensure that there is no period of time in which an audit policy does not apply to the specified object. If a policy was not in use for the specified object, REPLACE is equivalent to USING.

POLICY policy-name

Specifies the audit policy that is to be used to determine audit settings. The policy-name must identify an existing audit policy at the current server (SQLSTATE 42704).

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.
An object can be associated with no more than one policy (SQLSTATE 5U042).

Notes

Packages that are dependent on the table that is being audited become invalid when an audit policy is added or removed from that table.
Changes are written to the catalog, but do not take effect until after a COMMIT statement executes.
Changes do not take effect until the next unit of work that references the object to which the audit policy applies. For example, if the audit policy is in use for the database, no current units of work will begin auditing according to the policy until after a COMMIT or a ROLLBACK statement completes.
Views accessing a table that is associated with an audit policy are audited according to the underlying table's policy.
The audit policy that applies to a table does not apply to a materialized query table (MQT) based on that table. It is recommended that if you associate an audit policy with a table, you also associate that policy with any MQT based on that table. The compiler might automatically use an MQT, even though an SQL statement references the base table; however, the audit policy in use for the base table will still be in effect.
When a switch user operation is performed within a trusted context, all audit policies are re-evaluated according to the new user, and no policies from the old user are used for the current session. This applies specifically to audit policies associated directly with the user, the user's group or role memberships, and the user's authorities. For example, if the current session was audited because the previous user was a member of an audited role, and the switched-to user is not a member of that role, that policy no longer applies to the session.
When a SET SESSION USER statement is executed, the audit policies associated with the original user (and that user's group and role memberships and authorities) are combined with the policies that are associated with the user specified in the SET SESSION USER statement. The audit policies associated with the original user are still in effect, as are the policies for the user specified in the SET SESSION USER statement. If multiple SET SESSION USER statements are issued within a session, only the audit policies associated with the original user and the current user are considered.
If the object with which an audit policy is associated is dropped, the association to the audit policy is removed from the catalog and no longer exists. If that object is recreated at some later time, the object will not be audited according to the policy that was associated with it when the object was dropped.
When multiple objects are specified, the policy applies to each of them individually. The policy applies to the individual objects within the statement, and not their intersection. It is not possible to combine policies with an "AND" type configuration.

Examples

Example 1: Use the audit policy DBAUDPRF to determine the audit settings for the database at the current server.
```
   AUDIT DATABASE USING POLICY DBAUDPRF
```
Example 2: Remove the audit policy from the EMPLOYEE table.
```
   AUDIT TABLE EMPLOYEE REMOVE POLICY
```
Example 3: Use the audit policy POWERUSERS to determine the audit settings for the authorities SYSADM, DBADM, and SECADM, as well as the group DBAS.
```
   AUDIT SYSADM, DBADM, SECADM, GROUP DBAS USING POLICY POWERUSERS
```
Example 4: Replace the audit policy for the role TELLER with the new policy TELLERPRF.
```
   AUDIT ROLE TELLER REPLACE POLICY TELLERPRF
```

Example 5: Specifying multiple objects

AUDIT TABLE EMPLOYEE, ROLE TELLER USING POLICY TABLEAUDIT

The above statement is equivalent to these separate statements:

AUDIT TABLE EMPLOYEE USING POLICY TABLEAUDIT
AUDIT ROLE TELLER USING POLICY TABLEAUDIT