Configuring and enabling auditing using the Db2 audit facility

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

Procedure

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
      list of files and permissions, highlighting sqllib with permissions drwxrwsr
    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"
      CREATE AUDIT POLICY command and results
  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"
      AUDIT DATABASE USING POLICY LOGALL command and results
  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.
      two active log files, .log.1 and .log.0
  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.
      detailed listing of auditlogs and archive directories
    2. Use the AUDIT_LIST_LOGS table function to list all archive logs in the system, including the file paths, file names, and sizes.
      AUDIT_LIST_LOGS command and results
    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
      viewing a sample log file

What to do next

Auditing has now been configured and enabled. For more information about how to examine the generated audit records see Loading and analyzing log data from database tables.