50 DB2 Nuggets #34 : Tech Tip - Using db2audit utility

Body

Hello!

Recently I had opportunity to play around db2audit tool. db2audit tool is DBA's best friend when it comes find all the activities going on in database. DB2® database systems provide an audit facility to assist in the detection of unknown or unanticipated access to data. The DB2 audit facility generates and permits the maintenance of an audit trail for a series of predefined database events.

For more information about db2audit command, refer Information Center

db2audit can be divided in 5 main steps as :

1> Configure

2> Start

3> Archive

4> Extract

5> Stop (optional)

Today, I will share the steps I followed to work with db2audit.

1> Configure db2audit.

1.a> Before configuring db2audit, when I checked the status of my db2audit utility with db2audit describe, this is how it looked:

c:\temp>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.

It is pretty much self explanatory. It says, current audit is not active and non of the events are being audited.

1.b> Now I went ahead and configured db2audit:

c:\temp>db2audit configure scope all status both errortype audit

AUD0000I  Operation succeeded.

I used the simplest form, I configured audit with scope 'all' and errortype 'audit', i.e I would like my audit log to capture all the events with errortype 'audit'. You can further refer db2audit command's 'Audit Configuration' section, to find what all events can be monitored. We can choose the path where audit data can get generated and where we would like archived audit logs to be stored, in configure command. You can use 'datapath' and 'archivepath' options for this respectively. I did not specify them, resulting audit logs and archive logs getting created in default location. For Windows it is C:\ProgramData\IBM\DB2\<DB2COPY_NAME>\<Instance_Name>\security\auditdata and for Linux it is sqllib/security/auditdata. I conducted my test on Windows 7, so default audit log files, and default archived audit logs went to C:\ProgramData\IBM\DB2\DB2COPY2\DB2_01\security\auditdata

2> Start db2audit

Once db2audit is configured to capture the required events, you can start it.

c:\temp>db2audit start

AUD0000I  Operation succeeded.

This will generate a file called 'db2audit.instance.log.0' in your datapath. db2audit start works independent to instance start. You do not need instance recycle to start or stop db2audit.

db2audit describe should show us that db2audit is started and what all events it is capturing:

c:\temp>db2audit describe
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: "TRUE "
Audit Data Path: ""
Audit Archive Path: ""

AUD0000I  Operation succeeded.

3> Generate audit data

Our db2audit is actively capturing all the events. I purposely tried logging to database with wrong password to check what all things we can find from audit log:

c:\temp>db2 connect to sample user db2admin using wrongpw
SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID").  SQLSTATE=08001

c:\temp>db2audit flush

AUD0000I  Operation succeeded.

db2audit flush, will flush in memory audit data to on disk audit log.

if you check your audit log, you can see below two files created there now:

4> Archive db2audit log:

You can refer to section 'Audit Log Archive' of db2audit command for detailed syntax.

This parameter moves the current audit log for either an individual database or the instance to a new location for archiving and later extraction. The current timestamp will be appended to the filename. All records that are currently being written to the audit log will complete before the log is archived to ensure full records are not split apart. All records that are created while the archive is in progress will be written to the current audit log, and not the archived log, once the archive has finished.

I executed archive to archive audit logs related to sample database.

c:\temp>db2audit archive database sample

-------- -------------- -------- -----------------------------------------------
--
       0              0 AUD0000I db2audit.db.SAMPLE.log.0.20140617134939


AUD0000I  Operation succeeded.

After archiving, your files will look like this:

With any subsequent command which can create audit data for sample database, db2audit.db.SAMPLE.log.0 will be recreated.

5>  Extract information from archived audit log in readable format:

The audit records in the archived audit log file are stored in a raw format. You must extract all the audit records from it into a text file, which can then be analyzed. You also have the option to extract audit records into delimited ASCII files, which can then be loaded into DB2 relational tables for analysis and querying.

5.a> Extracting to delimited ASCII files

To extract records from the db2audit.log file into delimited ASCII files that you can then load into DB2 relational tables, I used following command:

c:\temp>db2audit extract delasc delimiter ; from files db2audit.db.SAMPLE.log.0.
20140617134939

AUD0000I  Operation succeeded.

The extracted audit records are placed in a delimited ASCII format suitable for loading into DB2 database relational tables. The output is placed in separate files, one for each category. In addition, the file auditlobs will also be created to hold any lobs that are included in the audit data. The filenames are:

• audit.del
• checking.del
• objmaint.del
• secmaint.del
• sysadmin.del
• validate.del
• context.del
• execute.del
• auditlobs

Later you can create tables to load these delimited files for easy analysis. You can refer this for complete table creation process.

5.b> Extracting to text file

c:\temp>db2audit extract file out.txt from files db2audit.db.SAMPLE.log.0.201406
17134939

AUD0000I  Operation succeeded.

Contents of out.txt are like this:

timestamp=2014-06-17-13.46.01.988000;
  category=CONTEXT;
  audit event=CONNECT;
  event correlator=2;
  database=SAMPLE;
  userid=db2admin;
  application id=*LOCAL.DB2_01.140617184601;
  application name=db2bp.exe;
 
timestamp=2014-06-17-13.46.01.993000;
  category=VALIDATE;
  audit event=AUTHENTICATION;
  event correlator=2;
  event status=-30082;
  database=SAMPLE;
  userid=db2admin;
  execution id=SGTHORVE;
  application id=*LOCAL.DB2_01.140617184602;
  application name=db2bp.exe;
  auth type=SERVER;
  plugin name=IBMOSauthserver;

This clearly shows us, execution id SGTHORVE tried to login to connect to database sample using userid db2admin, which failed with -30082 error.

6>Stop db2audit

c:\temp>db2audit stop

AUD0000I  Operation succeeded.

C:\Program Files\IBM\SQLLIB_01\BIN>db2audit describe
DB2 AUDIT SETTINGS:

Audit active: "FALSE "
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: "TRUE "
Audit Data Path: ""
Audit Archive Path: ""

AUD0000I  Operation succeeded.
 

---

This is good developerworks article, which I used. Hope you enjoy your db2audit more now.

Thanks for reading!

-Swati Thorve