Featured Database Articles

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

Database administrators have both an Instance level and a Database level auditing approach with IBM DB2 9.5 and 9.7. Understanding auditing at the database level requires a little more work than the instance level. Rebecca Bond shares some tips that will start you on your way with database level auditing.

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:

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: