Security_audit Option
The SECURITY_AUDIT option on the WITH clause specifies the auditing level.
This option has the following format:
SECURITY_AUDIT = (table_audit_opt {, table_audit_opt})
table_audit_opt
Specifies the level of security, as follows:
TABLE
(Default) Implements table-level security auditing on general operations (for example create, drop, modify, insert, or delete) performed on the table.
[NO]ROW
Implements row-level security auditing on operations performed on individual rows, such as insert, delete, update, or select. If NOROW is specified, the row-level security auditing is not implemented.
For example, an SQL DELETE statement that deleted 500 rows from a table with both table and row auditing generates the following audit events:
• One table-delete audit event, indicating the user issued a delete against the table.
• 500 row-delete audit events, indicating which rows were deleted.
Note: Either TABLE and ROW or TABLE and NOROW auditing can be specified. If NOROW is specified, row-level auditing is not performed. If either clause is omitted, the default installation row auditing is used. The default can be either ROW or NOROW depending on how your installation is configured.
WITH SECURITY_AUDIT_KEY Clause
The WITH SECURITY_AUDIT_KEY clause allows the user to specify an optional attribute to be written to the audit log to assist row or table auditing. For example, an employee number can be used as the security audit key:
CREATE TABLE employee (name CHAR(60), emp_no INTEGER)
WITH SECURITY_AUDIT = (TABLE, ROW),
SECURITY_AUDIT_KEY = (emp_no);
If no user-specified attribute is given and the table has row-level auditing, a new hidden attribute, _ii_sec_tabkey of type TABLE_KEY SYSTEM_MAINTAINED is created for the table to be used as the row audit key. Although any user attribute can be used for the security audit key (SECURITY_AUDIT_KEY clause), we recommend that a short, distinctive value be used (such as a social security ID), allowing the user to uniquely identify the row when reviewing the security audit log. If an attribute longer than 256 bytes is specified for the security audit key, only the first 256 bytes are written to the security audit log.