Database Auditing in IBM DB2 9.5 & 9.7: How do I know...

I was searching for a new book for my Kindle recently and found one that made me think about the whole topic of "knowing". From a security standpoint, "knowing" is important. If you don't know something, then how can you be expected to support or secure it? Without "knowing", you are much like a two year old child; you need someone to protect you from yourself as you attempt to navigate the unfamiliar. That does not seem like a good approach to database security.

About the same time, I got an email from a DBA asking me about auditing approaches for an IBM DB2 9.5 database. Well, before I knew it, the two thoughts were rumbling around together in my brain and they eventually started to stick together. 'Auditing and Knowing' became my 'peanut butter and chocolate' replacement of the moment. Do you want to combine "Auditing" and "Knowing" too? If so, read on.

So, how can you know about IBM DB2 auditing?

Remember that we have both an Instance level and a Database level auditing approach with DB2 9.5 and 9.7. This split between Instance and Database auditing also means that both the SYSADM and SECADM have individual auditing responsibilities. This is helpful as we apply Separation of Duties to our auditing tasks. Of course, this also means that as we perform our "audit knowing investigation steps", we need to involve both the SYSADM and the SECADM to get a full picture. Ideally, these two high level authorities will not be held by the same person. Since we have the ability to enhance our security posture by separating these responsibilities for auditing, if at all possible, we should do so.

INSTANCE:

The SYSADM will check the instance level settings by attaching to the instance and issuing the following:

$> db2audit describe

If you worked with auditing prior to DB2 9.5, you are probably familiar with this command. With it, you can determine if instance level auditing is "on" and if so, what events are being captured and what types of information is being generated into the audit logs. The output for this instance is:

DB2 AUDIT SETTINGS:

Audit active: "TRUE "
Log audit events: "BOTH"
Log checking events: "BOTH"
Log object maintenance events: "BOTH"
Log security maintenance events: "BOTH"
Log system administrator events: "BOTH"
Log validate events: "BOTH"
Log context events: "BOTH"
Return SQLCA on audit error: "FALSE "
Audit Data Path: ""
Audit Archive Path: ""

In this case, we can see that at the INSTANCE level, auditing is currently active and that the SYSADM has set up auditing for ALL categories for BOTH SUCCESS and FAILURE. We also discover that audit errors (The value for "Return SQLCA on audit error") will not be returned to the calling application. Just by looking at this output, we can tell that neither the active or archived audit logs have been moved from their defaults. (We may want to discuss that with the SYSADM and try to move those logs to other locations for enhanced performance.) We can learn a significant amount of information just from this one screen of output.

We are on the path to "knowing", but we have only half of the information at this point.

DATABASE:

At the database level, auditing is the responsibility of the SECADM. Database level audit policies are created, assigned, managed, changed and maintained by the SECADM. One way for the SECADM to review or discover which database level auditing policies are in place is simply to write some SQL queries. There are two database SYSCAT views, SYSCAT.AUDITUSE and SYSCAT.AUDITPOLICIES, which are specific to database level auditing.

SYSCAT.AUDITUSE holds information on audit policies for non-database objects, such as USERS, GROUPS, ROLES or AUTHORITIES.

Queries to SYSCAT.AUDITUSE can give us information on the object associated with the audit policy. For example, a query to SYSCAT.AUDITUSE, which is performing a select on the OBJECTTYPE column, will show either a BLANK value or one of:

T Table
S MQT
g Authority
i Authorization ID
x Trusted Context

If the OBJECTTYPE returned value is BLANK, then the policy is associated with the Database itself. If the OBJECTTYPE column returns a value of ' i ' (indicating the audit policy is associated with an authorization id), the SYSCAT.AUDITUSE column, SUBOBJECTTYPE, can be used to determine if the Authorization ID is associated with a GROUP, ROLE or USER. Of course, SYSCAT.AUDITUSE also holds the obvious information on AUDITPOLICYNAME, AUDITPOLICYID, OBJECTSCHEMA and OBJECTNAME, which are necessary to identify the policy.

Another database level investigative step is to query SYSCAT.AUDITPOLICIES. Here you can start to get solid information on the database level policies themselves.

In looking at the columns in this view, you will notice that the AUDITSTATUS, CONTEXTSTATUS, VALIDATESTATUS, CHECKINGSTATUS, SECMAINTSTATUS, OBJMAINTSTATUS, SYSADMINSTATUS, EXECUTESTATUS columns all are similar. Each ' *STATUS ' column, in turn, refers to a specific audit category (CONTEXT, VALIDATE, CHECKING, SECMAINT, OBJMAINT, SYSADMIN, EXECUTE).

For each of the audit categories, we can determine a status. For example, if our query shows that VALIDATESTATUS is ' N ', we know that this particular audit policy is not capturing audit events for the VALIDATE category. The possible return values for each of these ' * STATUS ' columns are Both (B), Failure (F), None (N), Success (S). If the value returned is ' B ', then both successful and failing events are being audited for this audit policy category.

The EXECUTEWITHDATA column only has two possible return values, 'N' or 'Y', to indicate if the EXECUTE auditing category is set to also capture any available values for host variables and parameter markers.

The ERRORTYPE column will tell us if this audit policy is returning all errors to the application, including audit errors (A) or only returning normal errors (N) to the calling application.

As you can start to see, "knowing" about auditing at the database level requires a little more work than "knowing" about auditing at the instance level. There is no "audit describe" command at this level, but SQL, our old friend, can be effectively used to gather all the knowledge we need.

Know Enough?

If some of these terms are new and unfamiliar to you, that is understandable. Working with database level auditing requires a lot more "knowing" than can be covered in this article. To get more acquainted with DB2 auditing, consider visiting these sites:

My Blogs (I frequently blog on auditing and include syntax)

Secure DB2
DB2 Security
IBM DB2 Information Center (Great current information on DB2 Auditing steps)
IDUG Solutions Journal Article "Using DB2 V9.5 Auditing to Aid Law Enforcement in Cybercrime Investigations"

Conferences

If you are lucky enough to get to go to IBM's Information on Demand Conference, there are always some strong DB2 Security presentations which typically include DB2 Auditing sessions.

Register before August 31, 2010 for the deeply discounted early bird rate at: https://www-01.ibm.com/software/data/2010-conference/ (promo code G10RBOND)

SHOUT OUT

Thanks to Ragh Makena, a DB2 DBA who values database security. He sent the email that inspired me to write this article.

» See All Articles by Columnist Rebecca Bond