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
>>-ALTER AUDIT POLICY--policy-name------------------------------>
.------------------------------------------------------------------------------------.
| .-,--------------------------------------------------------. |
V (1) V (2) | |
>----+-------CATEGORIES----------+-ALL-----------------------+--STATUS--+-BOTH----+-+-+-+-><
| +-AUDIT---------------------+ +-FAILURE-+ |
| +-CHECKING------------------+ +-NONE----+ |
| +-CONTEXT-------------------+ '-SUCCESS-' |
| | .-WITHOUT DATA-. | |
| +-EXECUTE--+--------------+-+ |
| | '-WITH DATA----' | |
| +-OBJMAINT------------------+ |
| +-SECMAINT------------------+ |
| +-SYSADMIN------------------+ |
| '-VALIDATE------------------' |
'-ERROR TYPE--+-NORMAL-+---------------------------------------------------------'
'-AUDIT--'
Notes:
- Each of the CATEGORIES and ERROR TYPE clauses can be specified
at most once (SQLSTATE 42614).
- 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
DB2 10.5 for Linux, UNIX, and Windows