DB2 Version 9.7 for Linux, UNIX, and Windows

Setting up security to manage database auditing in upgraded databases

Security administrator (SECADM) authority is now required to configure and manage database auditing using SQL statements, the SYSADM authority is not a requirement anymore. Grant SECADM authority to the users who manage the database auditing in upgraded databases.

About this task

Database and instance-level auditing are separate since DB2® Version 9.5. You can configure database auditing only by using DDL statements. You can continue to use the db2audit command to configure instance auditing.

When you upgrade an instance, the audit configuration file is converted to the DB2 Version 9.7 format.

When you upgrade a database, the instance-level configuration settings for auditing are used to create an audit policy in the database. If the audit facility is enabled at the instance level, the audit policy is associated with the upgraded database to enable auditing. Otherwise, the audit policy is not associated. These actions ensure that you observe the same audit behavior on your database after upgrading to DB2 Version 9.7.

Restrictions

You only have to perform this procedure if you upgraded your DB2 server from DB2 Version 9.1 or DB2 UDB Version 8.

Before you begin

To grant SECADM authority, you must have SECADM authority. If there is no user authorization ID with SECADM authority in your existing databases, the user upgrading the database is granted SECADM authority during upgrade.
To run the db2audit command, you must have SYSADM authority.

Procedure

To setup security to manage database auditing in upgraded databases:

Grant SECADM authority to the users who manage the audit facility by using the GRANT command. The following sample commands show how to grant SECADM authority to a user:
```
db2 CONNECT TO SAMPLE
db2 GRANT SECADM ON DATABASE TO USER user-id
```
Verify that the DB2AUDIT_CFG_MIGR audit policy was created for your databases during upgrade by querying the SYSCAT.AUDITPOLICIES system catalog view. The following sample query determines whether this audit policy was created:
```
db2 "SELECT * FROM SYSCAT.AUDITPOLICIES A 
            WHERE A.AUDITPOLICYNAME = 'DB2AUDIT_CFG_MIGR'"
```
If the DB2AUDIT_CFG_MIGR audit policy was not created during upgrade, create it using the CREATE AUDIT POLICY statement.
Verify that the DB2AUDIT_CFG_MIGR audit policy was associated with the upgraded databases by querying the SYSCAT.AUDITUSE system catalog view. The following sample query determines whether the audit policy was associated with the SAMPLE database:
```
db2 "SELECT * FROM SYSCAT.AUDITUSE U 
            WHERE U.OBJECTNAME = 'SAMPLE'"
```
If the database upgrade fails to associate the DB2AUDIT_CFG_MIGR audit policy to the upgraded databases, use the AUDIT statement to associate this policy to your database.
Optional: If you want to extract all audit records from the original audit log file that you had before upgrade and place the contents into a new audit log file in the new default location, run the db2audit command with the extract parameter. The original audit log file from a previous release remains in the same location that it was in prior to database upgrade.
The default location for the audit logs in DB2 Version 9.7 is:
- INSTHOME/sqllib/security/auditdata on Linux and UNIX operating systems, where INSTHOME is the instance home directory.
- INSTHOME\security\auditdata on Windows operating systems
where INSTHOME is the instance home directory that stores user data and instance directories.

What to do next

Now, you can use the following DDL statements to manage database auditing:

CREATE AUDIT POLICY
ALTER AUDIT POLICY
AUDIT