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
.-,---------------------------------------.
V (1) |
>>-AUDIT----------+-DATABASE----------------------+-+----------->
+-TABLE--table-name-------------+
+-TRUSTED CONTEXT--context-name-+
+-+-USER--+--authorization-name-+
| +-GROUP-+ |
| '-ROLE--' |
'-+-ACCESSCTRL-----------+------'
+-CREATE_SECURE_OBJECT-+
+-DATAACCESS-----------+
+-DBADM----------------+
+-SECADM---------------+
+-SQLADM---------------+
+-SYSADM---------------+
+-SYSCTRL--------------+
+-SYSMAINT-------------+
+-SYSMON---------------+
'-WLMADM---------------'
>--+-+-USING---+--POLICY--policy-name-+------------------------><
| '-REPLACE-' |
'-REMOVE POLICY--------------------'
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.
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