Trusted context and auditing

Auditing
Security is very important matter nowadays. One of the method to make our data more secure is auditing, monitoring and discovering unexpected or forbidden behavior. For instance - in the previous example - user 'Mary' tried to update the bonus column in 'Employee' table but was rejected. Let's go further - assume that dishonest user 'Mary' was able to overcome the application security control and tried to perform some forbidden activity directly at the database level. Because our well designed solution is protected at the two levels - application and database - this attack was repulsed. But it can be the matter of time that the malicious user devises the second plan to find a security hole and will be successful. The only way to protect our confidential data is to pin down the suspicious behavior as early as possible and react before a mischief is done.
Good news is that DB2 provides very rich set of tools for auditing and security monitoring.

Preparation
Unfortunately the auditing is not enabled by default.  So it is necessary to make some preparations.
1. Create (if not created so far)  tables necessary to keep audit data - there is a db2audit.dll file provided with every DB2 installation.
db2 -tvf {DB2 home directory} /misc/db2audit.ddl
2. Create AUDIT POLICY database object. There are a lot of options what to monitor and what audit granularity is expected. Here is the simple example for monitoring everything.
CREATE AUDIT POLICY BIGBROTHER
CATEGORIES ALL
STATUS BOTH
ERROR TYPE AUDIT;
3. Enable monitoring for roles or users.
AUDIT ROLE SAMPLEFULLACCESS  USING POLICY BIGBROTHER;
AUDIT ROLE SAMPLEREADACCESS  USING POLICY BIGBROTHER;
4. From now on all activities are monitored.
Pave the way for Sherlock Holmes
Several steps are necessary before audit data can be looked through.
1. Make an audit data snapshot.
db2 call "SYSPROC.AUDIT_ARCHIVE(NULL,NULL)"
2. Convert binary data to text CSV file
CALL SYSPROC.AUDIT_DELIM_EXTRACT(NULL, '/home/db2inst1/sqllib/security/auditdata', NULL, null, NULL);
3. Load CSV text files into relational tables created by running db2audit.dll script file

LOAD FROM /home/db2inst1/sqllib/security/auditdata/checking.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO CHECKING;

-- To load the OBJMAINT table, issue the following command:
LOAD FROM /home/db2inst1/sqllib/security/auditdata/objmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO OBJMAINT;

-- To load the SECMAINT table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO SECMAINT;

-- To load the SYSADMIN table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO SYSADMIN;

-- To load the VALIDATE table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO VALIDATE;

-- To load the CONTEXT table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO CONTEXT;

--# To load the EXECUTE table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO EXECUTE;

Description of all audit objects

5. Tables are ready and we can query them.

Example
We want to check if any user belonging to SAMPLEREADACCESS role has tried to update EMPLOYEE table :
This type of event is stored in CHECKING audit table:
So after running query like:

db2 "SELECT TIMESTAMP,SUBSTR(USERID,1,20),SUBSTR(APPNAME,1,20),STATUS  FROM CHECKING WHERE ROLEINHERITED = 'SAMPLEREADACCESS' AND OBJTYPE='TABLE' AND OBJNAME='EMPLOYEE' AND ACCESSATT='0x00000040000000000000000000000000'"

The horrible truth is revealed:

TIMESTAMP                  2                    3                    STATUS     
-------------------------- -------------------- -------------------- -----------
2011-05-27-22.47.38.565086 mary                 db2jcc_application          -551

Our database is under attack and we have to evaluate the whole situation. It is bad news. Good news that by virtue of applying DB2 trusted context and two layers security control this time the attack was repulsed, by virtue of DB2 auditing feature the security hole is unveiled  and we have some time to tighten our security policy before any harm is done.