The Db2 audit facility

 

The Db2 audit facility provides information to detect any unknown or unanticipated access to data by generating and maintaining an audit trail for a series of predefined database events. The information generated from this facility is kept in an audit log file and analysis of these audit log files can reveal usage patterns that could identify system misuse. Once an unknown or unanticipated access has been identified, actions can be taken to reduce or eliminate such access.

The audit facility provides the ability to audit at both instance and database levels, independently recording all instance- and database-level activities with separate logs for each. Users with SYSADM authority can use the db2audit tool to configure the audit at instance level as well as to control audit information. You can also use the db2audit tool to archive or extract both instance and database audit logs to and from archive locations.

Db2 provides a set of stored procedures to archive audit logs, locate logs of interest, and extract data into delimited files for analysis. The procedures include:

SYSPROC.AUDIT_ARCHIVE()

SYSPROC.AUDIT_LIST_LOGS()

SYSPROC.AUDIT_DELIM_EXTRACT()

Users with SECADM authority can grant EXECUTE privileges on these stored procedures to another user, enabling the security administrator to delegate these tasks.

Audit policies:

 The security administrator can create audit policies to control what is audited within a database. The following objects can have an audit policy defined on them:

The entire database: All auditable events that occur within the database are audited.

Tables: All DML and XQUERY access to the tables, MQTs, and nicknames are audited. The only event category applicable to tables is EXECUTE.

Trusted contexts: All auditable events that happen within a trusted connection defined by the particular trusted context are audited.

Authorization IDs representing users, groups, and roles: All auditable events that are initiated by the specified user are audited.

Authorities: All auditable events that are initiated by a user that holds the specified authority such as SYSADM, SECADM, DBADM, SQLADM, WLMADM, ACCESSCTRL, DATAACCESS, SYSCTRL, SYSMAINT, and SYSMON are audited.

If you want to audit all SQL statements accessing the HR.EMPLOYEES table you can do so by creating a security policy and associating the policy to the respective table: 

CREATE AUDIT POLICY dataaccess_policy CATEGORIES EXECUTE STATUS BOTH ERROR TYPE AUDIT;

DB20000I  The SQL command completed successfully.

AUDIT TABLE hr.employees USING POLICY dataaccess_policy;

DB20000I  The SQL command completed successfully.

If you want to audit all the activities performed by the SYSADM or DBADM authorities, you can create a policy similar to the following:

 

CREATE AUDIT POLICY admin_policy CATEGORIES EXECUTE STATUS BOTH, SYSADMIN STATUS BOTH ERROR TYPE AUDIT;

DB20000I  The SQL command completed successfully.

 

AUDIT SYSADM, DBADM USING POLICY admin_policy;

DB20000I  The SQL command completed successfully.

 

The db2audit tool command:

The db2audit tool command can be used to perform the following actions within the audit facility:

Start or stop recording auditable events at database instance level. However, the database level events will always be captured even when recording has been stopped.

Configure the behavior of the audit facility at the instance level.

Select the categories of auditable events to be recorded at the instance level.

Request a description of the current audit configuration for the instance.

Flush any pending audit records from the instance and write them to the audit log.

Archive audit records from the current audit log for either the instance or a database under the instance.

Extract audit records from an archived audit log by formatting and copying them to a flat file or ASCII-delimited file. Extraction is done in preparation for the analysis of log records.

At the instance level, a user with SYSADM authority can start or stop the audit facility by using the db2audit command:

db2audit start

AUD0000I  Operation succeeded.

db2audit stop

AUD0000I  Operation succeeded. 

To configure the Db2 audit facility, execute the db2audit command:

 db2audit configure scope all status both datapath "/db/home/db2inst1/audit"

If you want to view the current instance-level audit settings and status, use the db2audit command with the describe clause:

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: "FALSE "

Audit Data Path: "/db/home/db2inst1/audit/"

Audit Archive Path: ""

AUD0000I  Operation succeeded.

Once the command is executed, you can see audit file at the specified data path location:

/db/home/db2inst1/audit

ls -ltr

total 24

-rw------- 1 db2inst1 db2inst1  8911 Jun 15 20:24 db2audit.instance.log.0

-rw------- 1 db2inst1 db2inst1 10944 Jun 15 20:25 db2audit.db.SAMPLE.log.0

If you want to archive audit logs, you can do so by executing the db2audit command with an archive command option:

db2audit archive database sample to /db/home/db2inst1/auditarchivelogs

Member   DB Partition   AUD      Archived or Interim Log File

Number   Number         Message

-------- -------------- -------- -------------------------------------------------

       0              0 AUD0000I db2audit.db.SAMPLE.log.0.20180615205645

 

AUD0000I  Operation succeeded.

If you want to convert the archive audit log to a readable format, use the following command:

db2audit extract delasc to /db/home/db2inst1/auditformat from files /db/home/db2inst1/auditarchivelogs/db2audit.db.SAMPLE.log.0.20180615205645

AUD0000I  Operation succeeded.

To see all the converted delimited ASCII files and a sample output file, use the following:

/db/home/db2inst1/auditformat

==> ls -ltr

total 16

-rw-rw-rw- 1 db2inst1 db2inst1    0 Jun 15 20:57 secmaint.del

-rw-rw-rw- 1 db2inst1 db2inst1    0 Jun 15 20:57 objmaint.del

-rw-rw-rw- 1 db2inst1 db2inst1    0 Jun 15 20:57 checking.del

-rw-rw-rw- 1 db2inst1 db2inst1    0 Jun 15 20:57 auditlobs

-rw-rw-rw- 1 db2inst1 db2inst1    0 Jun 15 20:57 audit.del

-rw-rw-rw- 1 db2inst1 db2inst1  464 Jun 15 20:57 validate.del

-rw-rw-rw- 1 db2inst1 db2inst1  194 Jun 15 20:57 sysadmin.del

-rw-rw-rw- 1 db2inst1 db2inst1 2714 Jun 15 20:57 execute.del

-rw-rw-rw- 1 db2inst1 db2inst1  370 Jun 15 20:57 context.del

For example, if you look in the sysadmin event file, you will see records pertaining to SYSADM activity, such as initiating the database backup.

==> cat sysadmin.del

"2018-06-15-20.54.27.950604","SYSADMIN","BACKUP_DB",3,-2413,"SAMPLE","db2inst1","DB2INST1",,,"*LOCAL.db2inst1.180616005425","db2bp",,,,,,,,,,,,,,,,"db2inst1","db2demo.ibm.com"

Using the execute event file, you can see the COMMIT and ROLLBACK statement executions on database SAMPLE.

==> cat execute.del

"2018-06-15-20.54.24.709403","EXECUTE","COMMIT",12,0,"SAMPLE","db2inst1","DB2INST1","DB2INST1",,,"*LOCAL.db2inst1.180616002444","db2bp",,," ",,,,,"NULLID","SQLC2O27",0,,"*z","",,,,,"OTHER",,,,,,,,,,,,,"db2inst1"," db2demo.ibm.com "