How I can now answer auditor’s questions – Leveraging DB2’s Audit facility

Last week, I had a series of conversations with an internal auditor (at my work). I was asked a seemingly simple question. Is it possible to find out “when (date and time) was a database role granted to a given user”? At the time of the department’s audit, we had not leveraged DB2’s audit feature yet. However, I am now equipped to answer such questions in future. In this blog post, I show how DB2’s audit facility (db2audit) could be leveraged to answer this question. Treat this as a proof of concept using a simple example.

My first step was to find out if DB2’s catalog objects SYSCAT.ROLEAUTH or SYSIBM.SYSROLEAUTH have a ‘grant_time’ type of column of timestamp datatype. My hope was based on the fact that there are ‘create_time’, ‘alter_time’ columns in SYSCAT.TABLES. I quickly realized catalog objects do not hold such information. I started to pursue db2audit route.

My experience with dbaudit in this blog post is on DB2 10.1 fp2 on AIX 7.1

High Level Steps:

From a high level, the process has 5 steps

1) Configure data and archive paths for db2audit logs (one time)

2) Create tables to save audit data (one time)

3) Start db2audit, create audit policy and activate the policy (one time for a specific type of policy)

4) Extract data from audit logs and dump into audit tables created in step 2 (ongoing)

5) Analyze data (ongoing)

Before you start:

As SYSADM, the ‘describe’ option in db2audit summarizes the current info about db2audit facility.

$ db2audit describe
 DB2 AUDIT SETTINGS:
Audit active: "FALSE "
Log audit events: "FAILURE"
Log checking events: "FAILURE"
Log object maintenance events: "FAILURE"
Log security maintenance events: "FAILURE"
Log system administrator events: "FAILURE"
Log validate events: "FAILURE"
Log context events: "NONE"
Return SQLCA on audit error: "FALSE "
Audit Data Path: ""
Audit Archive Path: ""
AUD0000I Operation succeeded.

Audit active: “FALSE” simply states that DB2’s audit feature is currently down. This is the default. As shown above, you would notice “FAILURE’ or “NONE” or “FALSE” as the status for auditing on all events because nothing has been done yet. Please refer to this link for further information about ‘db2audit’ command.

Step 1: Configure data and archive paths for db2audit logs

Pay attention to the NULL paths in the above output of ‘db2adutl describe’. The goal is to set a path for both audit’s data and archive logs for db2audit to write its logs to these paths.

$ db2audit configure scope audit status both archivepath /db2home/maint/db2audit_poc/db2audit_archive_logs
AUD0000I Operation succeeded.
$ db2audit configure scope audit status both datapath /db2home/maint/db2audit_poc/db2audit_data_logs
AUD0000I Operation succeeded.

If you re-run the db2audit describe command, you would notice that paths are no longer NULL.

$ db2audit describe | grep Path
Audit Data Path: "/db2home/maint/db2audit_poc/db2audit_data_logs/"
Audit Archive Path: "/db2home/maint/db2audit_poc/db2audit_archive_logs/"

One point to note is that the full path must exist. In other words, directory(ies) have to be created before setting the paths.

You would get an error similar to the one shown below if the path does not exist.

AUD0003N I/O error on accessing "/db2home/maint/db2audit_poc/db2audit_archive_logs", make sure the directory/file exists and has the right permission.
AUD0001N Operation failed.

Step 2: Create tables to save audit data

DDL to create audit tables is readily available when you install DB2 software. It is located at <instance home>/sqllib/misc/db2audit.ddl where <instance home> is the instance owner user ID’s home directory. I copied this file to my working directory and added the following 2 lines to the top of the DDL.

CREATE SCHEMA AUDIT;

SET SCHEMA AUDIT;

I also modified the DDL to create tables in a dedicated tablespace (AUDIT_TBSP) of 8K pagesize (Because of wider rows, some of the rows in audit tables would not fit in 4K page and so attempt to create audit tables in 4K pagesize tablespace failed). Please note that in order to create 8K pagesize tablespace, there has to be at least one bufferpool of at least 8K pagesize.

After making the above mentioned modifications, run the DDL as below:

db2 -tvf db2audit.ddl | tee db2audit.ddl.log

You should notice that 8 audit tables got created.

db2 "select char(tabschema, 20) as schema, char(tabname, 20) as table, tbspace from syscat.tables where tabschema ='AUDIT' with ur"
SCHEMA TABLE TBSPACE
-------------------- -------------------- ------------------------------
AUDIT AUDIT AUDIT_TBSP
AUDIT CHECKING AUDIT_TBSP
AUDIT OBJMAINT AUDIT_TBSP
AUDIT SECMAINT AUDIT_TBSP
AUDIT SYSADMIN AUDIT_TBSP
AUDIT VALIDATE AUDIT_TBSP
AUDIT CONTEXT AUDIT_TBSP
AUDIT EXECUTE AUDIT_TBSP
8 record(s) selected

Step 3: Start db2audit, create audit policy and audit database using policy

$ db2audit start
AUD0000I Operation succeeded.
$ db2audit describe | grep -i active
Audit active: "TRUE "

“TRUE” indicates that DB2’s auditing facility is up and running.

Please note that ‘create audit policy’ command requires SECADM authority which is granted to instance owner by default.

$db2 "create audit policy db_sample_secmaint_policy categories secmaint status both error type audit"
DB20000I The SQL command completed successfully.
$db2 "select char(AUDITPOLICYNAME, 40) as POLICY_NAME, create_time from syscat.auditpolicies with ur"
POLICY_NAME CREATE_TIME
---------------------------------------- --------------------------
DB_SAMPLE_SECMAINT_POLICY 2014-05-30-15.02.45.819852
1 record(s) selected.

db_sample_secmaint_policy is the policy name I chose. ‘sample’ is the database name.

Our original goal was to keep track of and to document database permission changes. The category ‘secmaint’  fits this requirement. There are various types of audit policies that could be created at database level using “create audit policy” command. Please refer to http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0050607.html for more details.

Next step is to ‘activate’ the policy. Here is how you do it:

db2 "audit database using policy DB_SAMPLE_SECMAINT_POLICY"
DB20000I The SQL command completed successfully.
db2 "select char(AUDITPOLICYNAME,40) as POLICY_NAME, OBJECTTYPE from syscat.audituse"
POLICY_NAME OBJECTTYPE
---------------------------------------- ----------
DB_SAMPLE_SECMAINT_POLICY
1 record(s) selected.

OBJECTTYPE in the above output is blank space (white space). From documentation on the view SYSCAT.AUDITUSE found at http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0050611.html, objecttype would be blank for database level auditing.

For more clarity, the above SQL could be re-written as shown below to check the status:

db2 "select char(AUDITPOLICYNAME,40) as POLICY_NAME, case OBJECTTYPE when ' ' then 'DATABASE' end OBJECTTYPE from syscat.audituse"
POLICY_NAME OBJECTTYPE
---------------------------------------- ----------
DB_SAMPLE_SECMAINT_POLICY DATABASE
1 record(s) selected.

If you want to stop auditing facility, here is how you do it:

$ db2audit stop
AUD0000I Operation succeeded.
$ db2audit describe | grep -i active
Audit active: "FALSE "

Step 4: Extract data from audit logs and dump into audit tables created in step 2

Before we extract data from db2audit facility, let us create a role and assign a role to a user so that db2audit can pick up this activity. Here is how to do it:

$db2 "create role readonly"
DB20000I The SQL command completed successfully.
$db2 "grant role readonly to user xyz1"
DB20000I The SQL command completed successfully.

Next steps are to force any pending audit records (using 'flush' option) to be written to the audit log followed by actual 'archiving' of audit data into a log file.

In future blog posts, I will share how to automate this step. For now, below is how you could extract data from audit logs.

$ db2audit flush
AUD0000I Operation succeeded.
$db2audit archive database sample
Member DB Partition AUD Archived or Interim Log File
Number Number Message
-------- -------------- -------- -------------------------------------------------
 0 0 AUD0000I db2audit.db.SAMPLE.log.0.20140530152200
AUD0000I Operation succeeded.

#/db2home/maint/db2audit_poc/db2audit_archive_logs $ ls -ltr
-rw——- 1 db2inst2 db2iadm2 8382 May 30 15:22 db2audit.db.SAMPLE.log.0.20140530152200

Extracting Data from audit logs:

$db2audit extract file `date +"%Y-%m-%d-%H-%M"`_db_sample_db2audit.txt from path /db2home/maint/db2audit_poc/db2audit_archive_logs/ files db2audit.db.SAMPLE.log.0.20140530152200
 AUD0000I Operation succeeded.

I used `date` in the custom filename in order to have the current date as prefix.

We are now ready to look at audit data generated by db2audit facility. It is clear from the following output that DB2INST2 granted the ROLE called ‘READONLY’ to user ‘XYZ1’ in database SAMPLE at 2014-05-30-15.21.37.258238

$ cat 2014-05-30-15-28_db_sample_db2audit.txt
 timestamp=2014-05-30-15.21.37.258238;
 category=SECMAINT;
 audit event=GRANT;
 event correlator=69;
 event status=0;
 database=SAMPLE;
 userid=db2inst2;
 authid=DB2INST2;
 application id=*LOCAL.db2inst2.140530190320;
 application name=db2bp;
 package schema=NULLID;
 package name=SQLC2J24;
 package section=0;
 object name=READONLY;
 object type=ROLE;
 grantor=DB2INST2;
 grantee=XYZ1;
 grantee type=USER;
 privilege=MEMBERSHIP;
 local transaction id=0x000000000000039d;
 global transaction id=0x0000000000000000000000000000000000000000;
 grantor type=USER;

One alternative approach to using ‘db2audit extract’ option is to take advantage of DB2’s built-in stored procedure SYSPROC.AUDIT_DELIM_EXTRACT to extract audit information into a delimited text after logs are archived.

$CALL SYSPROC.AUDIT_DELIM_EXTRACT('','/db2home/maint/db2audit_poc/extract','/db2home/maint/db2audit_poc/db2audit_archive_logs','%2014%','')
Return Status = 0

In the above call to stored procedure, ‘db2home/maint/db2audit_poc/extract’ is the destination path and ‘db2home/maint/db2audit_poc/db2audit_archive_logs’ is the source path (of db2audit’s archive logs)

Such a call to SYSPROC.AUDIT_DELIM_EXTRACT dumps all audit data into delimited files.

/db2home/maint/db2audit_poc/extract $ ls -ltr *del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 validate.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 sysadmin.del
-rw-r--r-- 1 db2inst2 db2iadm2 291 May 30 16:45 secmaint.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 objmaint.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 execute.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 context.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 checking.del
-rw-r--r-- 1 db2inst2 db2iadm2 0 May 30 16:45 audit.del

Next step would be to load (or Import) the file secmaint.del into the relevant audit table.

db2 "IMPORT FROM secmaint.del of del insert into audit.secmaint"
SQL3109N The utility is beginning to load data from file "secmaint.del".
SQL3110N The utility has completed processing. "1" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "1" rows were processed from the input file. "1" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1

Step 5: Analyze data

The final step involves analyzing data, in this case, in AUDIT.SECMAINT table.

db2 "SELECT TIMESTAMP, CATEGORY, EVENT, STATUS, DATABASE, USERID, AUTHID, GRANTOR, GRANTEE, GRANTEETYPE, ACCESSTYPE 
GRANTORTYPE,OBJTYPE, OBJNAME FROM AUDIT.SECMAINT"
TIMESTAMP CATEGORY EVENT STATUS DATABASE USERID AUTHID GRANTOR GRANTEE GRANTEETYPE GRANTORTYPE OBJTYPE OBJNAME
-------------------------- -------- ----- ------ -------- -------- -------- -------- ------- ----------- ----------- ------- --------
2014-05-30-15.21.37.258238 SECMAINT GRANT 0 SAMPLE db2inst2 DB2INST2 DB2INST2 XYZ1 USER NULL ROLE READONLY

Our problem statement was “Is it possible to find out “when (date and time) was a database role granted to a given user”? From the above output, it is clear that in database SAMPLE, userid db2inst2 granted user ‘XYZ1’ role called “READONLY” at 2014-05-30-15.21.37.258238.

db2audit equips DBAs with lot more options which I did not get a chance to explore yet. I am hopeful I will share more details in future blog posts.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s