Audit policy guidelines

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.

The Db2 audit facility generates, and allows you to maintain, an audit trail for a series of predefined database events. In the following description of the categories of events available for auditing, notice that following the name of each category is a one-word keyword used to identify the category type. The following categories of events are available for auditing:

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.

Quick start

Note:

AUDIT_ALL is a predefined policy configured during deployment. This policy will audit all successes and failures for every category of audit records. It is recommended that you create a custom policy that fits your needs. See Audit policies.
AUDIT_UPDATE is a predefined procedure that will extract and load audit records to the AUDIT.* tables.
AUDIT statements require users to possess SECADM authority. To view the data in AUDIT.* tables, users that are given the SELECT privilege to those tables are able to access them.

Important: When an audit policy is enabled and the audit task is scheduled, AUDIT.* tables will continue to accumulate space on your system. You are expected to manage the storage used by the AUDIT.* tables. It is recommended that you periodically export the audit data to store it offline and clean-up the data in the AUDIT.* tables.

To enable AUDIT to capture events in the database:

Capture everything performed by administrators:

AUDIT ROLE DASHDB_ENTERPRISE_ADMIN USING POLICY AUDIT_ALL;

To create a scheduled audit update task to get the latest audit records into the audit tables every 15 minutes (the minimum interval between updates):

CALL SYSPROC.ADMIN_TASK_ADD( 'AUDIT_UPDATE', NULL, NULL, NULL, '*/15 * * * *', 'AUDIT', 'UPDATE', NULL, NULL, 'Periodically update to audit tables' );

To view the audit records from the 8 audit event categories:

select * from AUDIT.AUDIT;

select * from AUDIT.CHECKING;

select * from AUDIT.CONTEXT;

select * from AUDIT.EXCUTE;

select * from AUDIT.OBJMAINT;

select * from AUDIT.SECMAINT;

select * from AUDIT.SYSADMIN;

select * from AUDIT.VALIDATE;

Audit facility configuration

See Audit policies.

To create your own policy:

CREATE AUDIT POLICY policy_name CATEGORIES category or ALL STATUS status ERROR TYPE NORMAL;

See CREATE AUDIT POLICY statement.

Audit policy names

Ensure that the name is unique and its purpose is easily identifiable, for example, AUDIT_SOC2_COMPLIANCE or AUDIT_LOGIN_ONLY. Do not begin the name with SYS 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 control
VALIDATE - Authentication
SECMAINT - 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:

AUDIT database_entity USING POLICY policy_name;

See AUDIT statement.

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;
```
OR
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:

AUDIT database_entity REMOVE POLICY;

See AUDIT statement.

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:

CALL SYSPROC.ADMIN_TASK_ADD( 'AUDIT_UPDATE', NULL, NULL, NULL, '*/20 * * * *', 'AUDIT', 'UPDATE', NULL, NULL, 'Periodically update to audit tables' );

See UNIX cron format for schedule format.

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:

CALL SYSPROC.ADMIN_TASK_REMOVE( 'AUDIT_UPDATE', NULL );

See ADMIN_TASK_REMOVE procedure - Remove scheduled tasks or task status records.

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:

SQL1307N  An error occurred during invocation of the security audit facility.
  Reason Code: "9".

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.