Monitoring data access by using audit policies in Data Virtualization

Monitoring data access with the Db2 audit facility in Data Virtualization

Last updated: Nov 26, 2024

To manage access to your sensitive data, you can use authentication and access control mechanisms to establish rules and controls for 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.

You can use the Db2 audit facility to generate and maintain an audit trail for a series of predefined database events. For each category of events that is available for auditing, a one-word keyword is used to identify the category type after the name of the category. 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 that require 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. 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. The analysis of these records can reveal usage patterns that can identify system misuse. When misuse is identified, actions can be taken to reduce or eliminate the 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

AUDIT_ALL is a predefined policy that is configured during deployment. This policy audits all successes and failures for every category of audit records. It is recommended that you create a custom policy that fits your needs.
AUDIT_UPDATE is a predefined procedure that extracts and loads 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 accumulate space on your system. You must manage the storage used by the AUDIT.* tables. You must periodically export the audit data to store it offline and clean up the data in the AUDIT.* tables.

To enable AUDIT to capture all events in the service for each role

AUDIT ROLE DV_ADMIN USING POLICY AUDIT_ALL;
AUDIT ROLE DV_ENGINEER USING POLICY AUDIT_ALL;
AUDIT ROLE DV_STEWARD USING POLICY AUDIT_ALL;
AUDIT ROLE DV_WORKER 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;

To learn more, use the following links:

Create an audit policy
Start using your audit policy
Configure a custom audit policy
View all of the audit policies that have been created
Check which audit policies are currently being used
Stop audit on a database entity
Stop audit on a group
Create a scheduled task
Modify a scheduled task
Monitor the status of the scheduled audit update task
Load the most recent audit records into the AUDIT.* tables immediately

For more information, see Audit policies.

Create an audit policy

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

For more information, 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 policy can be applied to other database objects to determine how the use of those objects is to be audited. There are eight available categories to audit. The more categories that are configured, the more information is audited and accumulates in the audit buffer taking up compute space. Understanding what is needed for your purpose is important to prevent overloading your system's compute space. The summary describes each available category. If ALL is specified as the category option, no other category can be specified.

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

Start using your audit policy

AUDIT database_entity USING POLICY policy_name;

For more information, see AUDIT statement.

Configure a custom audit policy

You can configure a customized audit policy to capture authentication requests and successfully executed SQL commands and enable it.

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. You can use this configuration to monitor which users in which group and role perform 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;

You can also configure a customized audit policy to capture only authentication requests (both success and failures) and enable it.

CREATE AUDIT POLICY AUDIT_VALIDATE_ONLY CATEGORIES VALIDATE STATUS BOTH ERROR TYPE NORMAL;

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.

Check 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.

Stop audit on a database entity

To stop an audit on a database entity, the policy must be removed.

AUDIT database_entity REMOVE POLICY;

For more information, see AUDIT statement.

Stop audit on a group

AUDIT GROUP BLUUSERS REMOVE POLICY;

Create a scheduled task

For more information, 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' );

For more information, 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 that are 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 timeframe. As the performance tests have shown, if complicated queries run concurrent to the archive of the audit records, there are expected performance issues.

Modify a scheduled task

For more information, 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 );

For more information, see ADMIN_TASK_REMOVE procedure - Remove scheduled tasks or task status records.

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.

Load the most recent audit records into the AUDIT.* tables immediately

CALL AUDIT.UPDATE();

Note: After you run this procedure call, you might encounter the following message:

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

This message 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.