How to Enable Security Auditing
By default, security auditing is disabled. You must enable security auditing by setting the security_auditing configuration parameter. In addition, you must specify the level of auditing using the ENABLE SECURITY_AUDIT statement.
To enable security auditing follow these steps:
1. In CBF, select Security, Configure, Auditing.
The Configure Security Auditing screen appears.
2. Scroll to security_auditing. Select Edit to toggle the setting to ON.
3. (Optional) Tab to the Audit log files and use the Edit function to change the location and names of the security audit log files.
4. Connect to the master database iidbdb as the installation owner or security administrator.
5. Issue statements similar to the following to enable the level of security auditing.
• To enable security auditing on all operations by all users, installation wide:
ENABLE SECURITY_AUDIT ALL
• To enable query text auditing by a specific user:
ALTER USER username WITH SECURITY_AUDIT=(QUERY_TEXT)
How to Verify Security Auditing Levels
You can verify security auditing levels by querying the appropriate system catalog.
To check that security auditing was enabled on all operations by all users, installation wide, follow these steps:
1. Log on as the installation owner.
2. Connect to master database iidbdb.
Query the system catalog iisecurity_state by issuing the following command:
SELECT STATE FROM iisecurity_state
WHERE NAME = 'All';
The value returned should be E (enabled).
To check that query text auditing was enabled by a specific user, follow these steps:
3. Log on as the installation owner.
4. Connect to master database iidbdb.
5. Query the system catalog iiusers by issuing the following command:
SELECT AUDIT_QUERY_TEXT FROM iiusers
WHERE USER_NAME = ‘username’;
The value returned should be Y.