DB2 auditing - stanislawbartkowski/wikis GitHub Wiki
Also useful: https://www.ibm.com/support/producthub/db2w/docs/content/SSCJDQ/com.ibm.swg.im.dashdb.security.doc/doc/audit_policy_guidelines.html
Auditing is a way to monitor and control how database users or services are accessing database object. Auditing is frequently used by the database administrators or auditors for security reasons, to detect and prevent access to sensitive data and discover any suspicious or not expected behaviour.
DB2 provides a very powerful facility to monitor all aspects of a database or instance activity.
Two kinds of auditing are available: instance level and database level. Instance level audit monitors activities outside databases like creating and dropping databases, databases level audit keeps control over activities related to database objects like tables, views or triggers.
Auditing is not activated by default. Instance level audit is controlled by db2audit tool, a database-level audit is controlled by AUDIT SQL command.
Auditing can be general, all activities are collected, or more granular, an only certain type of events are under control. General auditing is not recommended, the audit log will be flooded with volumes of data and extracting useful information will be problematic.
The lifecycle of the audit is the following:
- DB2 is storing current audit records in audit directory. Database audit records and instance audit are collected separately.
- db2audit archive command moves current audit data to auditarch directory and resets current audit log.
- db2audit extract command transforms audit data to human-readable format. There is also an option to transform audit data into text delimited format and load the data into DB2 tables for further analysis.
- After audit data is analyzed, the data can be removed to free the storage space or archived.
Several remarks
- The audit directory should be monitored. The data can grow rapidly and cause out of space problem.
- The auditing can be stopped and restarted again. Usually, it is not recommended to audit database without analyzing the audit data, it is counterproductive. Turn on auditing if there is a purpose, for instance, company security policy requirement or there is a suspicious of doubtful activity to be discovered and prevented.
db2 "update dbm cfg using AUDIT_BUF_SZ 64"
mkdir audit
mkdir archaudit
db2audit configure datapath /home/db2inst1/audit archivepath /home/db2inst1/archaudit
Verify
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: "/home/db2inst1/audit/"
Audit Archive Path: "/home/db2inst1/archaudit/"
| Command | Description | Example |
|---|---|---|
| db2audit start/stop | Starts and stop instance-level auditing. Does not impact database auditing | |
| db2audit flush | Make sense if AUDIT_BUF_SZ non-zero. Flush audit memory cache | |
| db2audit archive | Moves current audit log file to archive directory, resets current audit log | |
| db2audit configure | Control events to be audited | db2audit configure scope validate status both |
| db2audit extract | Transforms archived audit log files to human-readable format. The command input directory is archaudit | |
| db2audit --help |
db2audit configure scope checking status both
Turn on all checking authorization events.
db2audit configure scope all status both errortype normal
Turn on all instance level audit events. Not recommended.
db2audit configure scope all status none
Turn off all instance level audit.
Two options are available. Creates a single human-readable file or create a series of CVS files ready to be loaded to SQL tables.
db2audit extract file audit.aud from files db2audit.instance.log.0.20201124100008
Transforms archived audit log file to audit.aud text file.
db2audit extract delasc to /tmp/dir from files db2audit.instance.log.0.20201124100008
Transforms archived audit log file into series for CVS files in /tmp/dir directory.
ll /tmp/dir
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 audit.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 auditlobs
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 checking.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 context.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 execute.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 objmaint.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 secmaint.del
-rw-rw-rw-. 1 db2inst1 db2inst1 0 Nov 24 17:24 sysadmin.del
-rw-rw-rw-. 1 db2inst1 db2inst1 829 Nov 24 17:24 validate.del
Monitor all successful and failed attempts to attach to DB2 instance.
db2audit configure scope validate status both
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: "BOTH"
Log context events: "NONE"
Return SQLCA on audit error: "FALSE "
Audit Data Path: "/home/db2inst1/audit/"
Audit Archive Path: "/home/db2inst1/archaudit/"
db2audit start
db2 attach to db2cont user db2inst1
Enter current password for db2inst1:
Instance Attachment Information
Instance server = DB2/LINUXX8664 11.5.5.0
Authorization ID = DB2INST1
Local instance alias = DB2CONT
db2 attach to db2cont user db2inst1
Enter current password for db2inst1:
SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID"). SQLSTATE=08001
db2audit stop
db2audit flush
db2audit archive
Member DB Partition AUD Archived or Interim Log File
Number Number Message
-------- -------------- -------- -------------------------------------------------
0 0 AUD0000I db2audit.instance.log.0.20201124171417
AUD0000I Operation succeeded.
db2audit extract file audit.txt from files db2audit.instance.log.0.20201124171417
vi audit.txt
timestamp=2020-11-24-17.11.57.967182;
category=VALIDATE;
audit event=AUTHENTICATION;
event correlator=1;
event status=0;
userid=db2inst1;
authid=DB2INST1;
execution id=sbartkowski;
application id=192.168.0.242.53946.201124181246;
application name=db2bp;
auth type=SERVER;
plugin name=IBMOSauthserver;
instance name=db2inst1;
hostname=adda9f1176f9;
timestamp=2020-11-24-17.12.01.393534;
category=VALIDATE;
audit event=AUTHENTICATION;
event correlator=1;
event status=-30082;
userid=db2inst1;
execution id=sbartkowski;
application id=192.168.0.242.53948.201124181250;
application name=db2bp;
auth type=SERVER;
plugin name=IBMOSauthserver;
instance name=db2inst1;
hostname=adda9f1176f9;
The first attempt is valid (event status=0), the next one is failed (event status=-30082). Timestamp, application name (db2bp, CLI) and application name including client IP are recorded.
Turn on monitoring of all instance-level object creation.
db2audit configure scope objmaint status both
db2audit start
db2 attach to db2cont user test
Enter current password for test:
Instance Attachment Information
Instance server = DB2/LINUXX8664 11.5.5.0
Authorization ID = TEST
Local instance alias = DB2CONT
db2 create database testdata
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "TEST".
db2audit stop
db2audit flush
db2audit archive
Member DB Partition AUD Archived or Interim Log File
Number Number Message
-------- -------------- -------- -------------------------------------------------
0 0 AUD0000I db2audit.instance.log.0.20201124172822
db2audit extract file audit.txt from files db2audit.instance.log.0.20201124172822
vi audit.txt
timestamp=2020-11-24-17.27.51.466154;
category=OBJMAINT;
audit event=CREATE_OBJECT;
event correlator=3;
event status=-1092;
userid=test;
authid=TEST;
application id=192.168.0.242.54174.201124182832;
application name=db2bp;
object name=TESTDATA;
object type=DATABASE;
instance name=db2inst1;
hostname=adda9f1176f9;
Database-level audit monitors activities regarding database objects. Unlike the instance-level audit, it allows more granular and more specific control.
To activate database-level auditing, two steps are required. Firstly audit policy is created and secondly, the policy is attached to a particular database object.
The database auditing is not impacted by db2audit start/stop command, immediately after connecting database object with policy, audit records are generated.
It is possible to audit all events in the database but it is not recommended, the better practice is to use more granular and sensitive policy.
| Command | Description | Example |
|---|---|---|
| CREATE AUDIT POLICY | Creates audit policy at the database level | db2 "CREATE AUDIT POLICY ALL_AUDIT CATEGORIES ALL STATUS BOTH ERROR TYPE NORMAL" |
| AUDIT | Assigns and activates policy for particular database object | db2 "AUDIT database USING POLICY ALL_AUDIT" |
| SYSCAT.AUDITPOLICIES | Contains all database audit policies | db2 "select * from SYSCAT.AUDITPOLICIES" |
| SYSCAT.AUDITUSE | Contains existing connections between audit policies and object | db2 "select * from SYSCAT.AUDITUSE" |
| AUDIT REMOVE POLICY | Disconnects database object and policy, the audit records are not generated | db2 "AUDIT database REMOVE POLICY" |
| DROP AUDIT POLICY | Removes audit policy | db2 "drop audit policy ALL_AUDIT" |
Turn on all audit policy
db2 "CREATE AUDIT POLICY ALL_AUDIT CATEGORIES ALL STATUS BOTH ERROR TYPE NORMAL"
db2 "AUDIT database USING POLICY ALL_AUDIT"
Perform several activities on the database
db2 "create table x (x int)"
db2 "insert into x value(1)"
db2 "insert into x values(1)"
db2 "select * from x"
Deactivate policy on database
db2 audit database remove policy
Check audit records
db2audit flush
db2audit archive database db2db
Member DB Partition AUD Archived or Interim Log File
Number Number Message
-------- -------------- -------- -------------------------------------------------
0 0 AUD0000I db2audit.db.DB2DB.log.0.20201124203708
db2audit extract file audit.txt from files db2audit.db.DB2DB.log.0.20201124203708
vi audit.txt
timestamp=2020-11-24-20.31.09.291112;
category=CONTEXT;
audit event=EXECUTE_IMMEDIATE;
event correlator=8;
database=DB2DB;
userid=db2inst1;
authid=DB2INST1;
application id=192.168.0.242.34992.201124205833;
application name=db2bp;
package schema=NULLID;
package name=SQLC2P30;
package section=203;
text=create table (x int);
local transaction id=0x1908000000000000;
global transaction id=0x0000000000000000000000000000000000000000;
instance name=db2inst1;
hostname=adda9f1176f9;
..........
timestamp=2020-11-24-20.31.39.577206;
category=CONTEXT;
audit event=EXECUTE_IMMEDIATE;
event correlator=11;
database=DB2DB;
userid=db2inst1;
authid=DB2INST1;
application id=192.168.0.242.34992.201124205833;
application name=db2bp;
package schema=NULLID;
package name=SQLC2P30;
package section=203;
text=insert into x values(1);
local transaction id=0x1c08000000000000;
global transaction id=0x0000000000000000000000000000000000000000;
instance name=db2inst1;
hostname=adda9f1176f9;
Assume having very confidential PERSONS table and monitor all access to this table
db2 "create table persons (ID INT, NAME VARCHAR(100))"
Create policy
db2 "CREATE AUDIT POLICY ALL_STATEMENTS CATEGORIES EXECUTE WITH DATA STATUS BOTH ERROR TYPE AUDIT"
Activate policy on table PERSONS. Important: This policy does not record denied access to the table because of insufficient privileges.
db2 "AUDIT TABLE PERSONS USING POLICY ALL_STATEMENTS"
Execute several statements on PERSONS
db2 "insert into persons values(1,'John')"
db2 "insert into persons values(2,'Adam')"
db2 "select * from persons"
ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 John
2 Adam
2 record(s) selected.
Verify audit records
db2audit flush
db2audit archive database db2db
db2audit extract file audit.txt from files db2audit.db.DB2DB.log.0.20201124205840
vi audit.txt
imestamp=2020-11-24-20.53.26.089493;
category=EXECUTE;
audit event=STATEMENT;
event correlator=29;
event status=0;
database=DB2DB;
userid=db2inst1;
authid=DB2INST1;
session authid=DB2INST1;
application id=192.168.0.242.34992.201124205833;
application name=db2bp;
package schema=DB2INST1;
package name=SQLC2P30;
package section=203;
local transaction id=0x9308000000000000;
global transaction id=0x0000000000000000000000000000000000000000;
uow id=22;
activity id=1;
statement invocation id=0;
statement nesting level=0;
activity type=WRITE_DML;
statement text=insert into persons values(1,'John');
statement isolation level=CS;
Create a policy
db2 "CREATE AUDIT POLICY FAILURE_CHECKING CATEGORIES CHECKING STATUS FAILURE ERROR TYPE AUDIT"
Activate the policy on all database. It is not possible to activate this policy on a single table. The only policy which can be restricted to a single table is EXECUTE.
db2 audit database using policy FAILURE_CHEKING
Not-authorized user test is trying to scan table PERSONS
db2 connect to db2db user test
Enter current password for test:
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.0
SQL authorization ID = TEST
Local database alias = DB2DB
db2 "select * from db2inst1.persons"
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "TEST". Operation: "SELECT". Object: "DB2INST1.PERSONS". SQLSTATE=42501
db2audit flush
db2audit archive database db2db
ember DB Partition AUD Archived or Interim Log File
Number Number Message
-------- -------------- -------- -------------------------------------------------
0 0 AUD0000I db2audit.db.DB2DB.log.0.20201124222049
db2audit extract file audit.txt from files db2audit.db.DB2DB.log.0.20201124222049
vi audit.txt
timestamp=2020-11-24-22.20.21.019591;
category=CHECKING;
audit event=CHECKING_OBJECT;
event correlator=7;
event status=-551;
database=DB2DB;
userid=test;
authid=TEST;
application id=192.168.0.242.36824.201124221401;
application name=db2bp;
package schema=NULLID;
package name=SQLC2P30;
package section=201;
object schema=DB2INST1;
object name=PERSONS;
object type=TABLE;
access approval reason=DENIED;
access attempted=SELECT;
local transaction id=0x4909000000000000;
global transaction id=0x0000000000000000000000000000000000000000;
instance name=db2inst1;
hostname=adda9f1176f9;
access control manager=INTERNAL;
Audit recorded can be loaded into database tables and queried like any other tables.
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0011542.html
Create a separate schema
db2 "create schema audit"
db2 "SET CURRENT SCHEMA='AUDIT'"
db2 -tvf sqllib/misc/db2audit.ddl
db2 "list tables for schema audit"
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
AUDIT AUDIT T 2020-11-24-22.39.13.120373
CHECKING AUDIT T 2020-11-24-22.39.13.766272
CONTEXT AUDIT T 2020-11-24-22.39.15.704910
EXECUTE AUDIT T 2020-11-24-22.39.16.090967
OBJMAINT AUDIT T 2020-11-24-22.39.14.171280
SECMAINT AUDIT T 2020-11-24-22.39.14.544297
SYSADMIN AUDIT T 2020-11-24-22.39.14.923562
VALIDATE AUDIT T 2020-11-24-22.39.15.304340
After schema and tables are created, extract audit records into CVS text file ready to load.
mkdir /tmp/dir
db2audit extract delasc to /tmp/dir from files db2audit.db.DB2DB.log.0.20201124205840
Load files into appropriate tables
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0011543.html
db2 "import from /tmp/dir/audit.del of del replace into audit.audit"
db2 "import from /tmp/dir/checking.del of del replace into audit.checking"
db2 "import from /tmp/dir/validate.del of del replace into audit.validate"
db2 "import from /tmp/dir/sysadmin.del of del replace into audit.sysadmin"
db2 "import from /tmp/dir/objmaint.del of del replace into audit.objmaint"
db2 "import from /tmp/dir/context.del of del replace into audit.context"
db2 "import from /tmp/dir/execute.del of del replace into audit.execute"
db2 "import from /tmp/dir/secmaint.del of del replace into audit.secmaint"
Discover failed attempt to scan the table.
db2 "select timestamp,varchar(userid,10),varchar(objname,10) from audit.checking where status <> 0"
TIMESTAMP 2 3
-------------------------- ---------- ----------
2020-11-24-22.20.21.019591 test PERSONS
1 record(s) selected.