Introduction to the Db2 audit facility for Db2 Warehouse on Cloud
To manage access to your sensitive data, you can use a variety of authentication and access control mechanisms to establish rules and controls for acceptable data access. But, to protect against and discover unknown or unacceptable behaviors, you can monitor data access by using the Db2® audit facility.
Successful monitoring of unwanted data access and subsequent analysis can lead to improvements in the control of data access and the ultimate prevention of malicious or careless unauthorized access to data. The monitoring of application and individual user access, including system administration actions, can provide a historical record of activity on your database systems.
- Audit (AUDIT): Generates records when audit settings are changed or when the audit log is accessed.
- Authorization Checking (CHECKING): Generates records during authorization checking of attempts to access or manipulate Db2 database objects or functions.
- Object Maintenance (OBJMAINT): Generates records when creating or dropping data objects, and when altering certain objects.
- Security Maintenance (SECMAINT): Generates records for the following conditions:
- Granting or revoking object privileges or database authorities
- Granting or revoking security labels or exemptions
- Altering the group authorization, role authorization, or override or restrict attributes of an LBAC security policy
- Granting or revoking the SETSESSIONUSER privilege
- Modifying any of the SYSADM_GROUP, SYSCTRL_GROUP, SYSMAINT_GROUP, or SYSMON_GROUP configuration parameters.
- System Administration (SYSADMIN): Generates records when operations requiring SYSADM, SYSMAINT, or SYSCTRL authority are performed.
- User Validation (VALIDATE): Generates records when authenticating users or retrieving system security information.
- 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 log file. When used with the log's event correlator field, a group of events can be associated back to a single database operation. For example, a query statement for dynamic queries, a package identifier for static queries, or an indicator of the type of operation being performed, such as CONNECT, can provide needed context when analyzing audit results.
- Execute (EXECUTE): Generates records during the execution of SQL statements.
For any of the listed categories, you can audit failures, successes, or both.
Any operations on the database server can generate several records. The actual number of records generated in the audit log depends on the number of categories of events to be recorded as specified by the audit facility configuration. It also depends on whether successes, failures, or both, are audited. For this reason, it is important to be selective of the events to audit.
The records generated from this facility can be viewed from a set of AUDIT tables where each table corresponds to each category described above. The analysis of these records can reveal usage patterns that would identify system misuse. Once identified, actions can be taken to reduce or eliminate such system misuse.
The audit facility provides the ability to audit at the database level. Any member of the administrator group can configure an audit policy to control when such audit information is collected, such as monitoring authorization IDs, database authorities, trusted contexts, or particular tables.
Audit facility configuration
- To create your own policy:
-
See CREATE AUDIT POLICY statement.CREATE AUDIT POLICY policy_name CATEGORIES category or ALL STATUS status ERROR TYPE NORMAL;
- Audit policy names
- Ensure that the name is unique and its purpose is easily identifiable, for example,
AUDIT_SOC2_COMPLIANCE
orAUDIT_LOGIN_ONLY
. Do not begin the name withSYS
as this is reserved for internal system names in the database. - Categories to audit
- The policy determines what categories are to be audited. This can then be applied to other
database objects to determine how the use of those objects is to be audited. There are 8 available
categories to audit. The more categories configured, the more information is audited and accumulates
in the audit buffer taking up compute space. Understanding what is specifically needed for your
purpose is important to prevent overloading your system's compute space. The summary describes each
available category. No other category can be specified if
ALL
is specified as the category option.To comply with most security standards, the recommended categories in the following list will address access control, authentication, and privileged access monitoring. Configuring policies with these categories will ensure that there will be minimal overhead while maintaining security.EXECUTE WITHOUT DATA
- Access controlVALIDATE
- AuthenticationSECMAINT
- Privileged access monitoring
In addition, for each category, both success and failure scenarios should be audited and the error type should only log SQL code errors.STATUS BOTH
ERROR TYPE NORMAL
- To start using your own audit policy:
-
See AUDIT statement.AUDIT database_entity USING POLICY policy_name;
Examples
- To configure a customized audit policy to capture authentication requests (both success and failures) and successfully executed SQL commands and enable it:
- Configure a customized audit policy:
-
CREATE AUDIT POLICY AUDIT_VALIDATE_EXECUTE CATEGORIES VALIDATE STATUS BOTH, EXECUTE STATUS SUCCESS ERROR TYPE NORMAL;
- Database objects to audit
- Auditing the entire database will result in compute space overload. The recommendation is to
identify to which table and associated materialized query table (MQT) to apply a policy.Note: The audit policy that applies to a table does not apply to a materialized query table (MQT) based on that table. It is recommended that if you associate an audit policy with a table, you also associate that policy with any MQT based on that table. The compiler might automatically use an MQT, even though an SQL statement references the base table; however, the audit policy in use for the base table will still be in effect.
Another recommended configuration is to apply a policy to a group or role. This will allow you to monitor which users in which group and/or role performs any unexpected actions. If you choose to apply a policy to the entire database, ensure that the policy does not keep a record of all of the categories.
- Enable the audit policy for a table:
-
AUDIT TABLE CUSTOMTABLE USING POLICY AUDIT_VALIDATE_EXECUTE;
- Enable the audit policy for a group:
-
AUDIT GROUP BLUUSERS USING POLICY AUDIT_VALIDATE_EXECUTE;
- To configure a customized audit policy to capture only authentication requests (both success and failures) and enable it:
- Configure a customized audit policy:
-
CREATE AUDIT POLICY AUDIT_VALIDATE_ONLY CATEGORIES VALIDATE STATUS BOTH ERROR TYPE NORMAL;
- Enable the audit policy for a role:
-
AUDIT ROLE DASHDB_ENTERPRISE_ADMIN USING POLICY AUDIT_VALIDATE_ONLY;
- To view all of the audit policies that have been created:
-
select * from SYSCAT.AUDITPOLICIES;
The following example output is a result of running the preceding SELECT statement:AUDITPOLICYNAME AUDITPOLICYID CREATE_TIME ALTER_TIME AUDITSTATUS CONTEXTSTATUS VALIDATESTATUS CHECKINGSTATUS SECMAINTSTATUS OBJMAINTSTATUS SYSADMINSTATUS EXECUTESTATUS EXECUTEWITHDATA ERRORTYPE REMARKS ---------------------------- ------------- -------------------------- -------------------------- ----------- ------------- -------------- -------------- -------------- -------------- -------------- ------------- --------------- --------- ------- AUDIT_VALIDATE_ONLY 108 2018-07-23-21.00.57.024758 2018-07-23-21.00.57.024758 N N B N N N N N N N - AUDIT_ALL 106 2018-07-23-20.51.18.017062 2018-07-23-20.51.18.017062 B B B B B B B B N N - 2 record(s) selected.
- To determine which audit policies are currently being used:
-
select * from SYSCAT.AUDITUSE;
The following example output is a result of running the preceding SELECT statement:AUDITPOLICYNAME AUDITPOLICYID OBJECTTYPE SUBOBJECTTYPE OBJECTSCHEMA OBJECTNAME AUDITEXCEPTIONENABLED ------------------------- ------------- ---------- ------------- ------------- ----------------- --------------------- AUDIT_VALIDATE_ONLY 108 - CURRENT SERVER N 1 record(s) selected.
- To stop audit on a database entity, the policy must be removed:
-
See AUDIT statement.AUDIT database_entity REMOVE POLICY;
- To stop audit on a group:
-
AUDIT GROUP BLUUSERS REMOVE POLICY;
Tasks
- To create a scheduled task:
- See ADMIN_TASK_ADD procedure - Schedule a new task.
- To create a scheduled audit update task to get the latest audit records into the audit tables every 20 minutes:
-
See UNIX cron format for schedule format.CALL SYSPROC.ADMIN_TASK_ADD( 'AUDIT_UPDATE', NULL, NULL, NULL, '*/20 * * * *', 'AUDIT', 'UPDATE', NULL, NULL, 'Periodically update to audit tables' );
- Schedule frequency
- The frequency of running the predefined task that archives the audit logs was out of the scope of this test, but it is worth noting the recommendations gathered from real-life customer situations. With larger databases, the recommendation is to run the archive task every day for 15 minutes. This allows the database to recover if there are any unexpected performance issues. If the policies are configured as recommended, the audit buffers should be able to contain the workload within that time frame. As the performance tests have shown, if complicated queries run concurrent to the archive of the audit records, there are expected performance issues.
- To modify a scheduled task:
-
See ADMIN_TASK_UPDATE procedure - Update an existing task.
- To modify a scheduled audit update task to get the latest audit records into the audit tables every 20 minutes:
-
CALL SYSPROC.ADMIN_TASK_UPDATE( 'AUDIT_UPDATE', NULL, NULL, NULL, '*/20 * * * *', 'Periodically update to audit tables every 20 minutes' );
- To remove the scheduled audit update task:
-
See ADMIN_TASK_REMOVE procedure - Remove scheduled tasks or task status records.CALL SYSPROC.ADMIN_TASK_REMOVE( 'AUDIT_UPDATE', NULL );
- To monitor the status of the scheduled audit update task:
-
select * from SYSTOOLS.ADMIN_TASK_STATUS;
The following example output is a result of running the preceding SELECT statement:NAME TASKID STATUS AGENT_ID INVOCATION BEGIN_TIME END_TIME SQLCODE SQLSTATE SQLERRMC RC ------------- ------- ---------- ------------ ----------- -------------------------- -------------------------- -------- -------- -------- ----- AUDIT_UPDATE 1 COMPLETE 16433 1 2018-07-23-21.50.00.135211 2018-07-23-21.50.10.584127 0 x'' 0 AUDIT_UPDATE 1 RUNNING 16448 2 2018-07-23-21.55.00.608060 - - - - - 2 record(s) selected.
Procedure
- To load the latest audit records into the AUDIT.* tables immediately:
-
CALL AUDIT.UPDATE();
Note: After running the preceding procedure call, you might encounter the following message:
This message only indicates that there wasn't any activity since the last time that the audit tables were loaded with data. It does not indicate a system error.SQL1307N An error occurred during invocation of the security audit facility. Reason Code: "9".