 |
| Security Audit |
Easter is coming up, a slow day due to vacation and Spring Break, time to try out some, in advance, of the chocolate Easter eggs. Gosh! They are gone! Who took them? It must have been someone with insight. Let's take a look at the audit logs...
When the DB2 10.5 Cancun Release (Fixpack 4) was announced
I mentioned that db2audit records can be transferred to syslog now and I wanted to test it. The command
db2audit is used to configure parts of the DB2 audit infrastructure, to archive audit logs, and to extract information from the archived logs. The
"extract" option now features a destination "syslog" (from the command syntax):
Audit Extraction
.-file--output-file---------------------------------------------------.
|--+---------------------------------------------------------------------+-->
+-delasc--+---------------------------+--+-----------------+----------+
| '-delimiter--load-delimiter-' '-to--delasc-path-' |
'-syslog--facility.priority--+-----------+--+-----------------------+-'
'-tag--word-' '-splitrecordsize--byte-'
While the option "file" would store the formatted audit logs in a regular text file, choosing "delasc" would split the log data across several delimited text files, ready for postprocessing in the database. The new option "syslog" can be used to hand over the audit data to the system logger facility. Depending which logger is used and how it is set up it could mean storing the audit records in local message files or sending them over to a central hub for analysis (e.g., by
IBM Operations Analytics or Splunk).
DB2 Setup
In order to find the one trying to steal the Easter eggs the audit system would need to be active prior to any attempt. The DB2 audit infrastructure is started with "db2audit start", basic settings can be changed with "
db2audit configure". For my tests I left everything set to failure-only logging and changed the archive path to "/tmp". Using the "describe" option, here is how the configuration looked like:
[hloeser@mymachine ~]$ db2audit describe
DB2 AUDIT SETTINGS:
Audit active: "TRUE "
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: "FAILURE"
Return SQLCA on audit error: "FALSE "
Audit Data Path: ""
Audit Archive Path: "/tmp/"
It is also a good idea to use a buffer to hold audit records. The
audit_buf_sz controls its size:
db2 update dbm cfg using audit_buf_sz 40
The next step in my setup was to
create an audit policy in my test database:
create audit policy execfail categories execute status failure,checking status failure, context status failure error type normal
Creating a policy does not mean it is used. The
AUDIT statement takes care of it:
audit sysadm,dbadm,dataaccess,user hloeser using policy execfail
Syslog Setup
The above concludes the DB2 portion of the test setup. Next is the optional step of telling the system logger where to place the received DB2 audit data.
The DB2 Knowledge Center has some basic information about how to configure the system error and event log (syslog). Without any changes it is possible to dump the audit data to, e.g., "/var/log/messages". I wanted the records go to a separate file. Because my system has rsyslog installed, I needed to edit (as root) the file "/etc/rsyslog.conf". Adding the following line causes all "user"-related records to be written to "user_messages.log" in the directory "/var/log/db2":
user.* /var/log/db2/user_messages.log
It is important to create that directory and file (I used "mkdir" and "touch"), then to restart the syslog facility.
DB2 Audit Logs to Syslog
Once done with the setup I connected to my test database and executed several SQL statements, including a "select * from eastereggs" (a non-existing table). Then I deemed my system ready for moving a first batch of audit records over to syslog. If a buffer for the DB2 audit data is used, it needs to be flushed:
db2audit flush
Thereafter, all the current
audit logs need to be archived. This can be done for both the instance and for databases. The following archives the logs for my test database and writes the file to the configured archive path (or the default path if none is specified):
db2audit archive database hltest
After all the configuration and preparation, we are finally at the really interesting part, the new extract option. Using "syslog" as destination and the category "user" with the priority level "info", the audit logs are handed over to the system error and event logger:
db2audit extract syslog user.info from files /tmp/db2audit.*
Did the logs really make its way over from DB2 to the system infrastructure? Here is my successful test:
[hloeser@mymachine ~]$ sudo grep -i easter /var/log/db2/user_messages.log Apr 2 13:32:10 mymachine db2audit: timestamp=2015-04-02-13.31.09.089507; category=CONTEXT; audit event=PREPARE; event correlator=40; database=HLTEST; userid=hloeser; authid=HLOESER; application id=*LOCAL.hloeser.150402095529; application name=db2bp; package schema=NULLID; package name=SQLC2K26; package section=201; text=select * from eastereggs; local transaction id=0x3266020000000000; global transaction id=0x0000000000000000000000000000000000000000; instance name=hloeser; hostname=mymachine;
Happy Easter and hopefully some chocolate eggs are left for you!
