Auditing DB2 type Databases

This is a set of steps to set up the basis for auditing for DB2 types databases. SYSADM will control this aspect. We will then follow on to use Database level auditing using SQL so that the SECADM has this control through Policies and Profiles. You can use instance level, but it is not very controllable nor performant. While you can run auditing at an istance level, it is best to control at Database level. You can actually get to table level auditing.
Database level auditing is all stored in the database in the system catalog tables, so it starts when the database activates, is backed up with the database and restored with the database.
however we will use the configure function of db2audit

Here is a knowledge center article on the topic of db2audit as well.

Setting it up

Using db2audit

This is a set of steps to set up the basis for auditing for DB2 types databases. SYSADM will control this aspect. We will then follow on to use Database level auditing using SQL so that the SECADM has this control through Policies and Profiles. You can use instance level, but it is not very controllable nor performant. It is best to control at Database level, then you can actually get to table level auditing.
Database level auditing is all stored in the database in the system catalog tables, so it starts when the database activates and is backed up with the database and restored with the database.

1. Login to the Openshift cluster Example:

     oc login --token=sha256~Hxk5vF3JbWyhGuxsnmHKaJcG6dHc8bOEDBhiGLCkRZI --server=https://c100-e.us-east.containers.cloud.ibm.com:30156
   

2. Set the project or namespace, in my case it is zen. This lets you not need to use -n on every command.

     Toms-MBP:cpd352 tjm$ oc project zen
     Now using project "zen" on server "https://c100-e.us-east.containers.cloud.ibm.com:30156".
   

3. This will list the available head nodes of the db2u based databases which you have provisioned in your Cloud Pak for Data platform.

     Toms-MBP:/ tjm$ oc get po --selector name=dashmpp-head-0
     NAME                               READY  STATUS   RESTARTS  AGE
     c-db2oltp-1610989723055231-db2u-0  1/1    Running  0         10d
     c-db2wh-1614550695007498-db2u-0     1/1    Running  0         10d
   

4. Exec into the pod

     Toms-MBP:/ tjm$ oc exec -ti c-db2wh-1614550695007498-db2u-0  bash
   

   • Other option is to use remote shell

       oc rsh c-db2wh-1614550695007498-db2u-0
     

5. Become db2inst1 This will give you the environment to access and excute these commands

   [db2uadm@c-db2wh-1614550695007498-db2u-0  /]$ su - db2inst1
   Last login: Thu Jan 28 21:58:11 UTC 2021 on pts/3
   [db2inst1@c-db2wh-1614550695007498-db2u-0  - Db2U db2inst1]$
   

6. Change directories to cd sqllib/adm

     [db2inst1@c-db2wh-1614550695007498-db2u-0  - Db2U db2inst1]$ cd sqllib/adm
   

7. Verify that db2audit exists and you have done everything so far correctly.

     [db2inst1@c-db2wh-1614550695007498-db2u-0  - Db2U adm]$ ls db2audit
     db2audit
   

8. There are only two commands you should use with db2audit. These are describe and configure. If you use others like, Audit active: “TRUE” you will end up with instance level auditing which we do not want.
   • To understand what is configured run db2audit describe

       [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ 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.
     

9. Notice the the Audit Data Path and Archive Path are empty. We need to configure these otherwise there will be no logs. Later we will choose between synchronous and asynchronous logging. This is where the output from that decision is going to go.
   Since these are pods, you want to write the logs out to a Persistant Volume which is located in /mnt in the pod.
10. The next command is how you will configure db2audit to change the Paths we mentioned before. This command is db2audit configure. Lets configure the datapath and archivepath
11. Configure datapath
    • To configure the location for the active audit files issue the following command. This directory, similar to tran logs, should not have much competition as you may get performance contention. I am picking the /mnt/backup which maps to a separate Persistent Volume. db2audit configure datapath your chosen path
    • First make the directory, otherwise you will get a permission error. mkdir /mnt/backup/security
    • Now issue the configure command: db2audit configure datapath /mnt/backup/security

       [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2audit configure datapath /mnt/backup/security
        AUD0000I  Operation succeeded.
      

12. Configure archivepath
    • To configure the location for the active audit files issue the following command. This directory we care less about contention and performance. In this example, I am still writing to the same directory, but you may choose a different path /mnt/backup which maps to a separate Persistent Volume. db2audit configure archivepath your chosen path
    • First make the directory, otherwise you will get a permission error. mkdir /mnt/backup/archive
    • Now issue the configure command: db2audit configure archivepath /mnt/backup/archive

       [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2audit configure datapath /mnt/backup/archive
        AUD0000I  Operation succeeded.
      

13. Lets see if the changes have taken place using db2audit describe

      [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ 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: "/mnt/backup/security/"
     Audit Archive Path: "/mnt/backup/archive/"
      AUD0000I  Operation succeeded.
    

For more information on db2audit, visit the IBM Knowledge Center

Synchronous or Asynchronous log writing that is the question

As of now you have set up where the logs will be written, next is to set up how they are written. By default, there is no database buffer when writing out logs to the datapath or active audit log. Let’s call this a synchronous write, which has performance implications as it is writing directly to the path. You can set up an audit buffer, which will right in memory while older records are being written to the datapath. Obviously asynchronous will be more performant, but if you have a database crash are you willing to potentially lose that memory buffered audit logs. 99% of the time, someone will ask who accessed the database or what was happening at the time of the crash. Do your own cost benefit analysis on which one you choose. I will show you how to configure both.

1. By default synchronous is set up with a buffersize of 0. You can also set this by issuing: db2 update dbm cfg using audit_buf_sz 0

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 update dbm cfg using audit_buf_sz 0
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
   

2. If you are looking to use a audit buffer, which is considered asynchronous for test or you have a reason to not write directly, you can set up a buffer sized at 64 or 128 depending on the dynamic SQL. You can also set this by issuing: db2 update dbm cfg using audit_buf_sz 64

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 update dbm cfg using audit_buf_sz 64
     DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
   

   
   NOTE: These changes are persisted and will not revert on a pod restart.
   For more information on audit_buf_sz, visit the IBM Knowledge Center

Database level Auditing

This product hub link on DB2 Wareshouse auditing is very helpful

Main Objects that can be audited

Objects	Description
Database	All events dealing with connections at a database level
Table	Only Events you can or should used is Execute. SQL statements, rows returned, executable environment. You can get this with or without data. Execute is expensive, not as expensive as Context, as there is a lot of logging that will come from it. Context has too much data. You can audit Untyped tables, Nicknames, and Material Query Tables (MQT) but you can’t audit views. Remember you are auditing an object. If you have 2 tables (with policies) that are part of the MQT, the MQT is not audited unless it has a policy. Note: If you drop a table that is audited, and you recreate the table, you have to apply the policy again to that table.
Authorities	You can assign a policy to multiple authorities.
Users, Groups and Roles	You can assign a policy to any of these to audit actions by these objects.
Trusted Context	You can and should monitor trusted applications.

For more information on Audit Object types, visit the IBM Knowledge Center

Main Events that can be audited

Database event types that can be audited

Event type	Description
Audit (AUDIT)	Generates records when audit settings are changed or when the audit log is accessed
User validation (VALIDATE)	Generates records when authenticating users or retrieving system security information
Authorization checking (CHECKING)	Generates records during authorization checking of attempts to access or manipulate DB2 objects or functions
Object maintenance (OBJMAINT)	Generates records when creating or dropping data objects
Security maintenance (SECMAINT)	Generates records when granting or revoking object or database privileges or DBADM authority Records are also generated when the database manager security configuration parameters SYSADM_GROUP, SYSCTRL_GROUP, or SYSMAINT_GROUP are modified
System administration (SYSADMIN)	Generates records when operations requiring SYSADM, SYSMAINT, or SYSCTRL authority are performed
Operation execution (EXECUTE)	Generates records to accurately track the SQL statements that are issued by a user. Options are with data or with out data. This operation can be expensive
Operation context (CONTEXT)	Generates records to show the operation context when a database operation is performed. This category allows for better interpretation of the audit records. When used with the log’s event correlator field, a group of events can be traced back to a single database operation that can provide needed context when analyzing audit results.</br>This operation can be very expensive.

For more information on Audit Events, visit the IBM Knowledge Center

Creating policies

You can create many different policies with different intents and use cases. Maybe you want to audit a table or a role then you would write up a policy for this to be applied to the table or role. When you think about a policy, there is one policy to many objects.
Note: I am assuming that autocommit is set to true. If not you will be to commit each statement for them to take hold. If it is set to Autocommit=true then once the cursor returns, it’s enabled and working.

1. You will need a list of databases db2 LIST ACTIVE DATABASES
2. I have a database called CP4DEMO. Connect to the database: db2 connect to cp4demo

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 connect to cp4demo
     Database Connection Information
      Database server        = DB2/LINUXX8664 11.5.5.0
      SQL authorization ID   = DB2INST1
      Local database alias   = CP4DEMO
   

3. Create a policy. In my case, it is a generic policy called DBAUDPRF, yours may be more specific to the usecase of the business. db2 create audit policy DBAUDPRF CATEGORIES AUDIT STATUS BOTH, SECMAINT STATUS FAILURE, OBJMAINT STATUS BOTH, CHECKING STATUS FAILURE, VALIDATE STATUS FAILURE ERROR TYPE NORMAL

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 create audit policy DBAUDPRF CATEGORIES AUDIT STATUS BOTH, SECMAINT STATUS FAILURE, OBJMAINT STATUS BOTH, CHECKING STATUS FAILURE, VALIDATE STATUS FAILURE ERROR TYPE NORMAL
     DB20000I  The SQL command completed successfully.
   

For more information on Audit Policies, visit the IBM Knowledge Center

Reviewing Policies

1. All Audit policies are stored in SYSCAT&& schema and in a table called **AUDITPOLICIES. To review the policies you can execute this SQL. db2 "select * from SYSCAT.AUDITPOLICIES"
2. Results are not pretty, but you can see that the DBAUDPRF policy is listed when created, altered etc. I have shorten this to protect the innocent.

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 "select * from SYSCAT.AUDITPOLICIES"
   AUDITPOLICYNAME AUDITPOLICYID CREATE_TIME  ALTER_TIME AUDITSTATUS CONTEXTSTATUS VALIDATESTATUS CHECKINGSTATUS SECMAINTSTATUS OBJMAINTSTATUS SYSADMINSTATUS EXECUTESTATUS EXECUTEWITHDATA ERRORTYPE REMARKS
   --------------- ----------  ----------- ---------- ---------- ------------ ------------ --------- --------
   ------- -------- --------- ------------ ----------
   DBAUDPRF  100 2021-03-11-15.33.26.803714 2021-03-11-15.33.26.803714 B           N             F              F              F              B              N              N             N               N         -                    
   1 record(s) selected.
   

Applying Policies

An object (DATABASE, ROLE, GROUP, TABLE) can only have one policy. How you have build out your database structure with GROUPS and ROLES will allow you to have a USER who is in a ROLE and a GROUP or multiple GROUPS have many policies applied to the user, because the USER doesn’t have a policy applied directly to the id, instead the GROUP or ROLE has the POLICY applied.
Example: Maybe I create a SENSITIVE_DATA_PRF and I want to apply this many object. The command to apply this to multiple objects uses a comma to separate the arguments: db2 audit authority DBADM, role teller, role accounting, role marketing, role support using policy SENSITIVE_DATA_PRF
We have created a policy, verified it exists in the Database, now we want to apply it to the database. This is done using the AUDIT command. db2 audit database using policy \<policy\>

1. Assuming you are still connected to the database, tell the audit facilty that you are going to audit this database using a certain policy. I apply DBAUDPRF policy to the CP4DEMO database using this command: db2 audit database using policy DBAUDPRF

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 audit database using policy DBAUDPRF
     DB20000I  The SQL command completed successfully.
   

2. If this was for say a role of TELLER you can add/replace using the following. db2 audit role teller using TELLERPRF or db2 audit role teller replace TELLERPRF

Deeper information on Audit Policies

Reviewing policies applied.

1. All Applied Audit policies are stored in SYSCAT schema and in a table called AUDITUSE. To review the policies you can execute this SQL. db2 "select * from SYSCAT.AUDITUSE"
2. Results are not pretty, but you can see that the DBAUDPRF is listed under AUDITPOLICYNAME and applied to the OBJECTNAME CURRENT SERVER, altered etc. I have shorten this to protect the innocent.

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U adm]$ db2 "select * from SYSCAT.AUDITUSE"     
    AUDITPOLICYNAME  AUDITPOLICYID OBJECTTYPE SUBOBJECTTYPE OBJECTSCHEMA   OBJECTNAME      AUDITEXCEPTIONENABLED
    ---------------  -------------  ---------- ------------- ------------- -------------  ------------------
    DBAUDPRF         100                                         -        CURRENT SERVER       N                    
     1 record(s) selected.
   

Removing policies objects

1. I have applied a policy and now want to remove it. Database is slightly different as you have one policy applied at the Database level and you can apply finer grained policies to roles and tables. To remove the applied policy from the database the command is db2 audit database remove policy.
2. If you had created a policy for a table for example the command would be db2 audit table employee remove policy

Managing Storage and Logs

Archiving the Active logs

1. Archive out of the active file to the archive directory. call sysproc.audit_archive('', -2)
   • Frst argument is the location of the output. You have set a default earlier in the db2audit configure, so you can make this NULL or change it if you want call sysproc.audit_archive('/auditarchive', -2)
   • Second argument is either -1 for current database members or NULL or -2 for all database members.

       [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ db2 "call SYSPROC.AUDIT_ARCHIVE(NULL, NULL)"  
       Result set 1
       --------------
       DBPARTITIONNUM PATH                       FILE                                SQLCODE     SQLSTATE  SQLERRMC   MEMBER
       -------------- -------------------- ---------------------------------------- ----------   --------  --------- ------
       0             /mnt/backup/archive/  db2audit.db.CP4DEMO.log.0.20210312125805      0           -        -          0
       1 record(s) selected.
       Return Status = 0
     

2. Verify that is exists in the correct directory

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ ls -l /mnt/backup/archive/
   total 12
   -rw-------. 1 db2inst1 db2iadm1 8712 Mar 12 12:58 db2audit.db.CP4DEMO.log.0.20210312125805  
   

   For more information on AUDIT_ARCHIVE, visit the IBM Knowledge Center

Prepare them for use

1. Let’s parse out the archive logs into more specific logs that can be used later when we load them into a database. The following command will extract out all records where the file name has a date of 20210312. call sysproc.audit_delim_extract(NULL, NULL, NULL, '%20210311%', ' ') It will produce the following files. Files produced:
   • audit.del
   • checking.del
   • objmaint.del
   • sysadmin.del
   • validate.del
   • context.del
   • execute.del
   • auditlobs

       [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ db2 "call sysproc.audit_delim_extract(NULL, NULL, NULL, '%20210312%', '')"
      Return Status = 0
     

2. Let’s see what the output looks like:

     [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ ls /mnt/backup/archive/
     audit.del  auditlobs  checking.del  context.del  db2audit.db.CP4DEMO.log.0.20210312125805  execute.del  objmaint.del  secmaint.del  sysadmin.del  validate.del
   

3. If you wanted to move these to a place of safe keeping you can use the scp command to copy the files from this cluster to another persistent store. Then delete the copied files freeing up this space for future usage. You will want to do these periodically so you do not run out of volume space. The mount I choose is also where you Database backups will be stored.

For more information on AUDIT_DELIM_EXTRACT, visit the IBM Knowledge Center

Automating Archiving and Delimiting the archive files

Setting up the Administrative Task Scheduler

1. Set the DB2_ATS_ENABLE registry variable to YES, TRUE, 1, or ON.
   For example:
   db2set DB2_ATS_ENABLE=YES

Creating Scheduled Archive Task

1. Create an ADMIN_TASK using SYSPROC.ADMIN_TASK_ADD called DAILY AUDIT ARCHIVE which triggers the Stored Procedure AUDIT_ARCHIVE with no specific arguments at 2PM ET execute this command: db2 "CALL SYSPROC.ADMIN_TASK_ADD( 'DAILY AUDIT ARCHIVE', CURRENT_TIMESTAMP, NULL, NULL, '00 19 * * *', 'SYSPROC', 'AUDIT_ARCHIVE','VALUES("NULL","NULL")', NULL, NULL )"
2. At 2PM each day, a new audit file archive will be created:

   [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ ls -l /mnt/backup/archive/db2audit*
   -rw-------. 1 db2inst1 db2iadm1   8712 Mar 17 19:00 /mnt/backup/archive/db2audit.db.CP4DEMO.log.0.20210317190000
   -rw-------. 1 db2inst1 db2iadm1 348393 Mar 18 19:00 /mnt/backup/archive/db2audit.db.CP4DEMO.log.0.20210318190000
   

Creating Scheduled Archive Task

1. Create an ADMIN_TASK using SYSPROC.ADMIN_TASK_ADD called DAILY AUDIT DELIMIT which triggers the Stored Procedure AUDIT_DELIM_EXTRACT with no specific arguments at 2PM ET execute this command: db2 "CALL SYSPROC.ADMIN_TASK_ADD( 'DAILY AUDIT DELIMIT', CURRENT_TIMESTAMP, NULL, NULL, '20 19 * * *', 'SYSPROC', 'AUDIT_DELIM_EXTRACT','VALUES("NULL", "NULL", "NULL", "'%20210319%'", "''")', NULL, NULL )"
2. At 2:20PM each day, a new audit file archive will be created in

   [db2inst1@c-db2wh-1614550695007498-db2u-0 - Db2U db2inst1]$ ls -l /mnt/backup/archive/db2audit*
   -rw-------. 1 db2inst1 db2iadm1   8712 Mar 17 19:00 /mnt/backup/archive/db2audit.db.CP4DEMO.log.0.20210317190000
   -rw-------. 1 db2inst1 db2iadm1 348393 Mar 18 19:00 /mnt/backup/archive/db2audit.db.CP4DEMO.log.0.20210318190000
   

db2 "call sysproc.audit_delim_extract(NULL, NULL, NULL, '%20210312%', '')"

Storing Records for Analysis

Create Audit Database

1. Create an audit database with audit schema. When you create this database the pagesize should be greater that 8k probably best set to 32768.
   Best not to be in the same production database as it would be extra load on the system. You probably also want to give SECADM load privileges so that the SYSADM doesn’t need to load for the security person. This would be isolated for just security audit analytics.
   • Create schema db2 create schema audit
   • Set the schema so you don’t have to type it over and over again, db2 set current schema="AUDIT"
   • create tables Use the db2audit.ddl in the sqllib/misc directory to create the database tables. If the database was not created with 32K pagesize then you will want to create a tablespace with a pagesize=32768, then add in tablespace name to each CREATE TABLE statement in db2audit.ddl. – db2 +o -tf sqllib/misc/db2audit.ddl

For more information on Creating Audit Database, visit the IBM Knowledge Center

Loading Data in the Audit Database

1. Load files created from the audit_delim_extact stored proc (create DB with tablespace pages at 32K) For more information on Loading Data into Audit Database, visit the IBM Knowledge Center