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