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.
Follow the steps below to configure and enable auditing.
Create an audit policy
- 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).
- 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.
db2audit configure datapath /home/bigsql/auditlogs archivepath /home/bigsql/archive
- 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"
- 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"
- Use the AUDIT statement to enable auditing on the entity of interest. For details about using the statement see AUDIT statement.
- Start generating audit logs
- 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.
- 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.
- Archive logs
- 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.
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.
db2 "CALL SYSPROC.AUDIT_ARCHIVE(NULL, NULL)"
- Use the AUDIT_LIST_LOGS table function to list all archive logs in the
system, including the file paths, file names, and sizes.
- 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
- 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.
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.