Audit facility tips and techniques

Archiving the audit records

Benefits

• Performance: Binary format removes audit storage overhead from your database for better responsiveness.
• Cost Savings: Binary storage is significantly cheaper and faster to maintain for long-term retention (e.g., 6-year HIPAA compliance).
• Security: Encrypted logs stored separately with granular access and retention controls.

Drawbacks

• Aggressive audit policies can cause rapid storage growth and increased costs.
• Learning curve for new COS-based workflows and monitoring.
• Requires external conversion process if human-readable DEL format is needed.

This strategic upgrade aligns with cloud-native best practices while improving scalability, security, and cost efficiency.

Error Handling

When you create an audit policy, you should use the error type AUDIT, unless you are just creating a test audit policy. For example, if the error type is set to AUDIT, and an error occurs, such as running out of disk space, then an error is returned. The error condition must be corrected before any more auditable actions can continue. However, if the error type was set to NORMAL, the logging would simply fail and no error is returned to the user. Operation continues as if the error did not happen.

If a problem occurs during archive, such as running out of disk space in the archive path (/ldaphome/db2inst1/db2-audit-logs/), or the archive path does not exist, the archive process fails and an interim log file with the file extension .bk is generated in the audit log data path, for example, db2audit.instance.log.0.20070508172043640941.bk. After the problem is resolved (by allocating sufficient disk space in the archive path, or by creating the archive path) you must move this interim log to the archive path. Then, you can treat it in the same way as a successfully archived log.

If a problem occurs during the process to upload archived audit logs to cloud object storage, verify the credentials and state of the cloud object storage bucket. When the issue has been resolved, any missing DEL files will be uploaded to the bucket along with the current set of archived audit logs.

Policy Configuration Tips

Policy plan

In order to set a proper policy, you should understand your use case and select the categories that will audit the events that you need. Apply the policy to the desired database objects to activate audit.

For example, your company might want a policy for auditing sensitive data and a policy for auditing the activity of users holding DBADM authority. If multiple audit policies are in effect for a statement, all events required to be audited by each of the audit policies are audited (but audited only once). For example, if the database's audit policy requires auditing successful EXECUTE events for a particular table and the user's audit policy requires auditing failures of EXECUTE events for that same table, both successful and failed attempts at accessing that table are audited.

For a specific object, there can only be one audit policy in effect. For example, you cannot have multiple audit policies associated with the same table at the same time.

Avoid auditing all categories and applying this policy across the entire database. This will result in a huge amount of audited events which will take up a lot of space in the cloud object storage bucket.

An audit policy cannot be associated with a view or a typed table. Views that access a table that has an associated audit policy are audited according to the underlying table's policy.

See https://www.ibm.com/docs/en/db2w-as-a-service?topic=guidelines-audit-policies" for more policy examples.

Using CHECKING events

In most cases, when working with CHECKING events, the object type field in the audit record is the object being checked to see if the required privilege or authority is held by the user ID attempting to access the object. For example, if a user attempts to ALTER a table by adding a column, then the CHECKING event audit record indicates the access attempted was ““ALTER”” and the object type being checked was ““TABLE”” (not the column, because it is table privileges that are checked).

However, when the checking involves verifying if a database authority exists to allow a user ID to CREATE or BIND an object, or to DROP an object, then although there is a check against the database, the object type field will specify the object being created, bound, or dropped (rather than the database itself).

When creating an index on a table, the privilege to create an index is required, therefore the CHECKING event audit record has an access attempt type of ““index”” rather than ““create””.

Audit records created for binding a package

When binding a package that already exists, then an OBJMAINT event audit record is created for the DROP of the package and then another OBJMAINT event audit record is created for the CREATE of the new copy of the package.

Using CONTEXT event information after ROLLBACK

Data Definition Language (DDL) might generate OBJMAINT or SECMAINT events that are logged as successful. It is possible however that following the logging of the event, a subsequent error might cause a ROLLBACK to occur. This would leave the object as not created; or the GRANT or REVOKE actions as incomplete. The use of CONTEXT events becomes important in this case. Such CONTEXT event audit records, especially the statement that ends the event, indicates the nature of the completion of the attempted operation.