Db2 object-level audit policies
With object-level audit policies, you can create your own Db2 audit policies and attach them to specific objects in a database that you created on an RDS for Db2 DB instance, such as tables, users, groups, roles, trusted contexts, and authorities. Amazon RDS records the activity that each policy covers and uploads the audit records to the Amazon S3 bucket that you configured for Db2 audit logging.
Object-level audit policies complement database-level audit logging. Use rdsadmin.configure_db_audit to audit activity across a whole database. Use object-level audit policies to audit only the objects that you need, for example a single table that contains sensitive data, or a single user.
Using object-level audit policies with database-level audit logging
You can use object-level audit policies and database-level audit logging at the same time, in the same database. The two features work together as follows:
-
Amazon RDS manages database-level audit logging –
rdsadmin.configure_db_auditmanages an audit policy namedDBA_AUDIT, which Amazon RDS attaches to theDATABASE,SYSADM, andDBADMobjects. You can't attach object-level audit policies to those objects, and you can't modifyDBA_AUDITwith the object-level audit policy stored procedures. -
All applicable policies are used – When an event is covered by
DBA_AUDITand by an object-level audit policy, Db2 audits the event according to each applicable policy. -
The two features are independent – Calling
rdsadmin.configure_db_auditdoesn't change your object-level audit policies. Calling rdsadmin.disable_db_audit removes only theDBA_AUDITpolicy. Your object-level audit policies remain attached and continue to record activity. -
Removing the option affects both – Removing the
DB2_AUDIToption from your DB instance stops both kinds of auditing. For more information, see Behavior during DB instance and database operations. -
Logs share the same location – Audit records from both features are uploaded to the database-level location in your Amazon S3 bucket. Db2 writes audit records by category, so records for the same category are in the same files whichever policy produced them.
Setting up object-level audit policies
To set up an object-level audit policy, complete the following steps.
Topics
Step 1: Enable Db2 audit logging on the DB instance
Your RDS for Db2 DB instance must be associated with an option group that has the
DB2_AUDIT option. If you haven't done so already, complete steps 1
through 4 in Setting up Db2 audit logging.
You don't need to call rdsadmin.configure_db_audit to use
object-level audit policies.
Step 2: Create an audit policy
Connect to the rdsadmin database using the master username and
master password for your RDS for Db2 DB instance. Then, call the rdsadmin.create_audit_policy stored procedure.
The following example connects to the rdsadmin database and creates
an audit policy named TEST_POLICY in a database named
TESTDB. The policy audits the EXECUTE category with
input data, for both successful and failing events, with an error type of
NORMAL. Replace master_username and
master_password with your own information.
db2 "connect to rdsadmin usermaster_usernameusingmaster_password" db2 "call rdsadmin.create_audit_policy(?, 'TESTDB', 'TEST_POLICY', 'EXECUTE WITH DATA', 'BOTH', 'NORMAL')"
The stored procedure returns the ID of the task that creates the policy.
Value of output parameters -------------------------- Parameter Name : OUT_TASK_ID Parameter Value : 101 Return Status = 0
An audit policy doesn't audit anything until you attach it to an object.
Step 3: Attach the audit policy to an object
Call the rdsadmin.attach_audit_policy stored procedure.
The following example connects to the rdsadmin database and attaches
the TEST_POLICY audit policy to the TESTTABLE table in the
TESTSCHEMA schema of the TESTDB database.
db2 "connect to rdsadmin usermaster_usernameusingmaster_password" db2 "call rdsadmin.attach_audit_policy(?, 'TESTDB', 'TABLE', 'TESTSCHEMA.TESTTABLE', 'TEST_POLICY')"
Step 4: Check the status of the tasks
The object-level audit policy stored procedures run asynchronously. To check that
the policy was created and attached, call rdsadmin.get_task_status. The following example connects to the rdsadmin database and lists
the object-level audit policy tasks for the TESTDB database.
db2 "connect to rdsadmin usermaster_usernameusingmaster_password" db2 "select task_id, task_type, database_name, lifecycle, varchar(bson_to_json(task_input_params), 500) as task_params, cast(task_output as varchar(500)) as task_output from table(rdsadmin.get_task_status(null, 'TESTDB', null)) where task_type like '%AUDIT_POLICY'"
The query produces output similar to the following example:
TASK_ID TASK_TYPE DATABASE_NAME LIFECYCLE ------- ------------------- ------------- --------- 101 CREATE_AUDIT_POLICY TESTDB SUCCESS 102 ATTACH_AUDIT_POLICY TESTDB SUCCESS ... continued ... TASK_PARAMS -------------------------------------------------------------------------------------------- { "STMT" : "CREATE AUDIT POLICY TEST_POLICY CATEGORIES EXECUTE WITH DATA STATUS BOTH ERROR TYPE NORMAL" } { "STMT" : "AUDIT TABLE TESTSCHEMA.TESTTABLE USING POLICY TEST_POLICY" } ... continued ... TASK_OUTPUT --------------------------------------------------- 2026-11-02T20:27:03.029Z Task execution has started. 2026-11-02T20:27:04.285Z Task execution has completed successfully.
If a task fails, LIFECYCLE is ERROR, and
TASK_OUTPUT contains the Db2 SQLSTATE, SQLCODE, and error details.
For more information, see Troubleshooting object-level audit policies.
Step 5: Check the attachment
Connect to the database and query the SYSCAT.AUDITUSE catalog view.
The following example connects to the TESTDB database and shows the
objects that object-level audit policies are attached to. Replace master_username and
master_password with your own information.
db2 "connect to TESTDB usermaster_usernameusingmaster_password" db2 "select auditpolicyname, objecttype, objectschema, objectname from syscat.audituse where auditpolicyname <> 'DBA_AUDIT'"
The query produces output similar to the following example:
AUDITPOLICYNAME OBJECTTYPE OBJECTSCHEMA OBJECTNAME --------------- ---------- ------------ ---------- TEST_POLICY T TESTSCHEMA TESTTABLE
After the attachment is listed, Db2 audits activity on the object according to the audit policy, and Amazon RDS uploads the audit records to your Amazon S3 bucket. For more information, see Viewing audit logs for object-level audit policies.
Managing object-level audit policies
After you set up an object-level audit policy, you can modify it, move it to a different object, or remove it.
Topics
Modifying an object-level audit policy
To change the categories or the error type of an audit policy, call rdsadmin.alter_audit_policy. Each call changes one category, the error
type, or both. Categories that you don't specify keep their current settings. To
audit several categories with one policy, call
rdsadmin.alter_audit_policy once for each additional
category.
Moving an object-level audit policy to a different object
Each object can have only one audit policy attached. To replace the audit policy that's attached to an object, call rdsadmin.detach_audit_policy. Wait for the detach task to complete, as described in Step 4: Check the status of the tasks. Then, call rdsadmin.attach_audit_policy with the new policy.
Removing an object-level audit policy
To remove an audit policy, detach it from every object that it's attached to by calling rdsadmin.detach_audit_policy. Then, call rdsadmin.drop_audit_policy. You can't drop an audit policy that's still attached to an object.
Viewing object-level audit policies and attachments
To view your object-level audit policies, connect to the database that contains them
and query the Db2 catalog views SYSCAT.AUDITPOLICIES and
SYSCAT.AUDITUSE.
The following example connects to the TESTDB database and lists the
object-level audit policies and their settings.
db2 "connect to TESTDB usermaster_usernameusingmaster_password" db2 "select auditpolicyname, auditstatus, checkingstatus, contextstatus, executestatus, executewithdata, objmaintstatus, secmaintstatus, sysadminstatus, validatestatus, errortype from syscat.auditpolicies where auditpolicyname <> 'DBA_AUDIT'"
The query produces output similar to the following example:
AUDITPOLICYNAME AUDITSTATUS CHECKINGSTATUS CONTEXTSTATUS EXECUTESTATUS EXECUTEWITHDATA --------------- ----------- -------------- ------------- ------------- --------------- TEST_POLICY N N N B Y ... continued ... OBJMAINTSTATUS SECMAINTSTATUS SYSADMINSTATUS VALIDATESTATUS ERRORTYPE -------------- -------------- -------------- -------------- --------- N N N N N
The following example connects to the TESTDB database and lists the
objects that object-level audit policies are attached to.
db2 "connect to TESTDB usermaster_usernameusingmaster_password" db2 "select auditpolicyname, objecttype, subobjecttype, objectschema, objectname from syscat.audituse where auditpolicyname <> 'DBA_AUDIT'"
The query produces output similar to the following example:
AUDITPOLICYNAME OBJECTTYPE SUBOBJECTTYPE OBJECTSCHEMA OBJECTNAME --------------- ---------- ------------- ------------ ---------- TEST_POLICY T TESTSCHEMA TESTTABLE TEST_POLICY i U - TESTUSER TEST_POLICY g - DATAACCESS
For information about the values in these catalog views, see SYSCAT.AUDITPOLICIES in the IBM Db2 documentation
Viewing audit logs for object-level audit policies
Amazon RDS uploads audit records for object-level audit policies to the database-level location in your Amazon S3 bucket:
-
Database level logs –
bucket_name/db2-audit-logs/dbi_resource_id/date_time_utc/db_name/
After you attach an audit policy, wait for at least one hour before viewing the audit data in your Amazon S3 bucket. For more information, see Viewing audit logs.
Behavior during DB instance and database operations
The following table describes what happens to object-level audit policies during DB instance and database operations.
| Operation | Behavior |
|---|---|
|
Rebooting, failing over, or modifying the DB instance class or storage |
Audit policies and their attachments are kept, and auditing continues. |
|
Removing the |
Amazon RDS detaches all object-level audit policies in every database.
The audit policies themselves are kept. After you add the
|
|
Restoring a database with rdsadmin.restore_database |
Amazon RDS removes object-level audit policies from the restored database. Create and attach the audit policies again after the restore. |
|
Dropping a table |
Db2 removes the attachment for that table. If you create the table again, attach the audit policy again. |
|
Removing a user with rdsadmin.remove_user |
Audit policies attached to the user stay attached. If you add a user with the same name later, that user is audited. To stop auditing, detach the audit policy. |
|
Calling rdsadmin.disable_db_audit |
No effect on object-level audit policies. |
Limitations for object-level audit policies
The following limitations apply to object-level audit policies:
-
Your DB instance must be associated with an option group that has the
DB2_AUDIToption for all object-level audit policy operations, including detaching and dropping audit policies. -
rdsadmin.create_audit_policyandrdsadmin.alter_audit_policyaccept one category for each call. -
The
ALLcategory includes every Db2 audit category, includingEXECUTEandCONTEXT. This is different fromrdsadmin.configure_db_audit, whereALLdoesn't include theCONTEXT,EXECUTE, orERRORcategories. -
The status for a category is one of
BOTH,SUCCESS,FAILURE, orNONE. To audit input data, use theEXECUTE WITH DATAcategory. Thecategory_settingvalues thatrdsadmin.configure_db_audituses, such asBOTH,WITH, aren't supported. -
Each object can have only one audit policy attached.
-
You can't attach audit policies to the
DATABASE,SYSADM, orDBADMobjects. To audit at the database level, userdsadmin.configure_db_audit. -
You can't create, modify, attach, or drop audit policies named
DBA_AUDITor audit policies with names that start withRDS_DAS_. -
You can't attach audit policies to the users
RDSDB,RDSADMIN, orSECADMUSER, which are used by Amazon RDS. -
Table names must include the schema, in the format
SCHEMA.TABLE. -
Audit policy names and object names must be Db2 ordinary identifiers. They must start with a letter,
@,#, or$, can contain only letters, digits,_,@,#, and$, and can be up to 128 characters. Names are converted to uppercase. Objects with names that require double quotes can't be used with these stored procedures. -
You can't use object-level audit policies in the
rdsadmindatabase. -
Auditing adds work for the database and generates audit records. Some categories, such as
EXECUTE WITH DATA, can generate a large volume of records for frequently used objects. Attach audit policies only to the objects that you need to audit, and test the effect on your workload before you use them in production.
Troubleshooting object-level audit policies
Use the following information to troubleshoot common issues with object-level audit policies.
Calling a stored procedure returns an error
The stored procedures validate their parameters before they create a task. If a parameter isn't valid, the call returns an error and doesn't create a task. For a list of errors, see Object-level audit policy errors.
A task has the ERROR lifecycle
If a task fails, check TASK_OUTPUT in the output of
rdsadmin.get_task_status. The following table lists common
causes.
TASK_OUTPUT contains |
Cause | Solution |
|---|---|---|
|
|
Db2 audit logging isn't enabled on the DB instance, because
the DB instance isn't associated with an option group that has
the |
Add the |
|
|
The object or the audit policy doesn't exist. |
Check the object and policy names. Table names must include the schema. |
|
|
An audit policy with this name already exists. |
Use a different name, or modify the existing policy. |
|
|
The object already has an audit policy attached. |
Detach the audit policy from the object, and then attach the new one. |
|
|
The audit policy is still attached to an object. |
Detach the audit policy from all objects, and then drop it. |
|
|
The statement timed out waiting for a lock, or was chosen as a deadlock victim. |
Call the stored procedure again. |
|
|
An internal error occurred. |
Call the stored procedure again later. If the error continues, contact AWS Support. |
No audit records in the Amazon S3 bucket
If audit records for an object-level audit policy are missing from your Amazon S3 bucket, check the following:
-
The audit policy is attached to the object. For more information, see Viewing object-level audit policies and attachments.
-
The categories and statuses of the audit policy include the activity that you expect. For example, an audit policy that sets the
CHECKINGcategory toFAILUREaudits only failed authorization checks. -
At least one hour has passed since the activity occurred.
For other causes, such as IAM permissions or Amazon S3 bucket settings, see No data in the Amazon S3 bucket.