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

policy-block

Notes:

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

Description

ADD EXCEPTION FOR TRUSTED CONTEXTcontext-name: Specifies that an audit exception is to be associated with context-name. The context-name must identify a trusted context that exists at the current server and the audit exception exists (SQLCODE 20584, SQLSTATE 428IG). All auditable events that happen within the trusted connection defined by the trusted context context-name will not be audited.

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.

REMOVE EXCEPTION FOR TRUSTED CONTEXTcontext-name: Specifies that an audit exception is to be removed from being associated with context-name. The context-name must identify a trusted context that exists at the current server and the audit exception exists (SQLCODE 20584, SQLSTATE 428IG). All auditable events that happen within the trusted connection defined by the trusted context context-name will be audited according to the associated policy. Note that when a trusted context is dropped, audit exception is also removed.

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).
Only tables in the SYSTEM tenant catalogs can be the target of the AUDIT TABLE statement.
Audit exceptions take precedence over audit policies
Audit exception cannot be added for an object (e.g. trusted context) that does not exist
If the object (e.g. trusted context) for which the exception was created is dropped, the audit exception is also dropped

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

Example 6: Specifying a global audit exception
```
AUDIT TABLE EMPLOYEE USING POLICY TABLEAUDIT
AUDIT ADD EXCEPTION FOR TRUSTED CONTEXT T1
```
The above statement will result in the table EMPLOYEE being audited for all non-trusted connections and not being audited for connections based on the trusted context T1. Note that a global audit exception applies to all objects used within connections based on the trusted context T1.

Example 7: Removing a global audit exception

AUDIT REMOVE EXCEPTION FOR TRUSTED CONTEXT T1