Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Security -- Did they, or Didn't they?

December 26, 2009, 9:43 am
Posted by bond in General

Did they, or didn't they?  Part 1
(With db2audit you CAN know for sure.) 

If you haven't used db2audit yet due to concerns about performance or perceptions that the setup options are inflexible, I would encourage you to re-consider enabling it when you make the move to DB2 9.5 or 9.7.

Ok, I admit, DB2 auditing prior to 9.5 was a bit of a challenge.  The granularity of the setup wasn't robust enough for most shops.  But all that has changed now.  So, if you are asking the question, "DB2 Audit, what have you done for me lately?", the answer is "A LOT".   

But, as with all technical tasks,  before you begin to use DB2 auditing, you will want to do some analysis and set up work.  I would encourage you to start at the beginning (which means, leave production alone until you are confident you understand any potential impact), learn about the set up options (read my blog for tips and review IBM's documentation), and then take it one step at a time.

In most shops, users who hold SECADM authority will be the ones who are primarily responsbile for auditing, although that work can be delegated (see my earlier blog posts for more info on how do do that).

One approach I like is to set up a separate database for the storage of audit information.  This "auditing storage" database can then be "locked down" so that only those with SECADM authority have access.

Once the database is created (using the RESTRICTIVE keyword, of course), you will want to grant some authorities to the SECADM and build the audit tables to store your audit information.  I named my database "lockit" and my SECADM is going to be "locksmith".  I have decided I want to name the schema "audit" and I'm using DB2 roles to faciliate setup.  We will also need to do some housekeeping.

Here are my commands (this is just a "minimal" example, you will want to modify these commands and steps for your environments):

$> db2 "connect to lockit"
$> db2 "create role lockrole"
$> db2 "grant secadm on database to role lockrole"
$> db2 "grant role lockrole to locksmith"
$> db2 "create schema audit"
$> db2 "grant createin, alterin on schema audit to role lockrole"
$> db2 "grant usage on workload sysdefaultuserworkload to role lockrole"
$> db2 "create bufferpool auditbp pagesize 8192"
$> db2 "create large tablespace auditspace pagesize 8192 managed by automatic storage autoresize yes bufferpool auditbp dropped table recovery on"
$> db2 "grant createtab on database to role lockrole"
$> db2 terminate

Ok, initial set up work is complete.  Now it's time to connect to the new database using the locksmith id.

$> db2 "connect to lockit user locksmith"
$> cd <instancehome>/sqllib/misc
$> db2 set current schema audit
$> db2 -tvf db2audit.ddl

What have we accomplished?  There is a new database to hold audit information, named lockit.  The user locksmith via it's role (lockrole) is a SECADM on the database. We've done some minimal housekeeping tasks so that we can move forward. There are tables created in the "audit" schema now and they are ready to receive new auditing data loads.  We have made progress.  Now we are ready for next steps. 

I think it's time to celebrate our success with a 5 mile jog.  Care to join me?

Stay tuned for more exciting adventures as the DB2 Locksmith reveals some of her favorite things about DB2 auditing. 

A BIG THANKS to Paul Bird from DB2 Development. He read the blog and reminded me that the db2audit changes that I am blogging about first appeared in DB2 9.5, not 9.7 as I originally posted.


db2locksmith at