Configuring and enabling auditing using the Db2 audit facility

These steps describe how to configure and enable auditing of Db2 Big SQL activity on a system using the Db2 audit facility.

Follow the steps below to configure and enable auditing.

1. Create an audit policy
   1. Identify the categories of audit records that you want to have generated. For a complete list of audit record categories and their associated event types see Categories of audit records. Three particularly important audit categories are Authorization Checking (CHECKING), User Validation (VALIDATE), and Execute (EXECUTE).
   2. Configure the locations on the file system where you want the audit logs to be stored as well as archived.
      For a detailed discussion see Storage and analysis of audit logs in the IBM® Db2® Version 11.1 Knowledge Center. Note the following recommendations:

      • The data path for an active log should be on a high speed local disk.
      • The path should support the $m syntax. This will be substituted by the member number so that you can have a unique path for each member.
      • The archive path should be on a shared directory to enable extracting and loading, though this is not required.

      In the example below, the db2audit command is used to configure the directory where the audit logs will be written and archived.

      db2audit configure datapath /home/bigsql/auditlogs archivepath /home/bigsql/archive

      
   3. Create an audit policy to cover the categories of audit records identified in step 1.a. For details about how to create an audit policy see CREATE AUDIT POLICY.
      For example, the command below will create policy LOGALL, which will log all categories of audit events.

      db2 "CREATE AUDIT POLICY LOGALL CATEGORIES ALL STATUS BOTH ERROR TYPE
      AUDIT"

      
2. Enable auditing
   1. Use the AUDIT statement to enable auditing on the entity of interest. For details about using the statement see AUDIT statement.
      In the example below, the policy LOGALL is used to enable logging for the database.

      db2 "AUDIT DATABASE USING POLICY LOGALL"

      
3. Start generating audit logs
   1. As a result of the commands above, logs are now generated for all of the events configured through the CREATE AUDIT POLICY statement, on all of the entities being audited as configured through the AUDIT statement.
   2. The screen capture below shows that there are two active log files: .log.1 and .log.0. One log is for the Big SQL head node process and the other is for the Big SQL worker process. In this example, both the head node and the worker process are running on a single VM.
      
4. Archive logs
   1. As these logs are generated, you should periodically archive the logs so that they can be extracted downstream for further analysis. This also helps to keep the active log files of manageable size.
      The Audit routines and procedures are available for you to archive and extract audit logs. The following command uses the AUDIT_ARCHIVE procedure and table function to archive the logs from the default log directory to the default archive directory.

      db2 "CALL SYSPROC.AUDIT_ARCHIVE(NULL, NULL)"

      Each archive is tagged with a timestamp indicating when it was created. As well, the fresh set of log files are updated with new timestamps and collect information for events occuring since the logs were last archived. Logs should be archived on a periodic basis.

      
   2. Use the AUDIT_LIST_LOGS table function to list all archive logs in the system, including the file paths, file names, and sizes.
      
   3. Optional: You may want to copy the archived logs from the local system to HDFS.
      For example:

      hdfs dfs -put /home/bigsql/archive/db2audit.db.BIGSQL.log.0.20150421113652 
      /home/bigsql/archive/db2audit.db.BIGSQL.log.1.20150421113652 /user/bigsql/auditlogs