Temporal versioning for Db2 security-related catalog tables

Insert behavior

During the operations listed in the previous table, if an INSERT operation into a history table fails, the SQL statement or command that triggered the INSERT operation will also fail. For example, if an SQL REVOKE statement triggered an INSERT into a catalog history table, and the INSERT operation fails, the REVOKE statement will also fail.

In addition to the operations that are listed in the previous table, the CATMAINT utility options SCHEMA SWITCH and OWNER FROM can also add entries to the catalog history tables based on the objects that are being updated.

The TEMPORAL_LOGICAL_TRANSACTION_TIME global variable, which allows applications to control the scope of temporal logical transactions, is ignored for updates to catalog history tables.

Enabling the temporal relationship

The temporal relationship between a history table and its associated catalog table must be enabled before the history table can be used to record historical information. To enable temporal support, issue the ALTER TABLE statement with the ADD VERSIONING clause and specify the corresponding history table.

ALTER TABLE SYSIBM.SYSUSERAUTH
  ADD VERSIONING
  USE HISTORY TABLE SYSIBM.SYSUSERAUTH_H;

Disabling the temporal relationship

If the temporal relationship between a catalog table and its associated history table later needs to be removed, issue the ALTER TABLE statement with the DROP VERSIONING clause on the catalog table. The following example disables the temporal relationship on SYSIBM.SYSUSERAUTH:

ALTER TABLE SYSIBM.SYSUSERAUTH
  DROP VERSIONING;

Auditing the REVOKE privilege on a table

The following scenario demonstrates how to obtain records about the SELECT privileges that were in place for a table at a point in time in the past.

1. A security administrator enables temporal support on the SYSIBM.SYSTABAUTH catalog table and also adds the ON DELETE ADD EXTRA ROW clause to audit the IDs that revoke privileges.

   ALTER TABLE SYSIBM.SYSTABAUTH
      ADD VERSIONING
      USE HISTORY TABLE SYSIBM.SYSTABAUTH_H
      ON DELETE ADD EXTRA ROW;

2. A user then grants the SELECT privilege on the CUSTOMER.INVOCE table to the SELGRP01 user ID. This GRANT statement is issued on 2023/04/10.
3. The user then revokes this SELECT privilege from user SELGRP01. This REVOKE statement is issued on 2023/05/10. Db2 13 removes the corresponding GRANT record from SYSTABAUTH and adds an entry in SYSTABAUTH_H.
4. Because temporal support was enabled on SYSTABAUTH, an auditor is able to determine the privileges that were in place for CUSTOMER.INVOICE for a specific time period by issuing the following query:

   SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, GEN_SESSION_USER
   FROM SYSIBM.SYSTABAUTH
   FOR SYSTEM_TIME BETWEEN ‘2023-01-01-00.00.00.000000000000’
                       AND ‘2023-06-01-00.00.00.000000000000'
   WHERE TCREATOR = ‘CUSTOMER’ AND TTNAME = ‘INVOICE’
   AND GRANTEETYPE <> ‘P’;

Deleting records from a history table

Based on the data retention policies of your organization, the history tables will likely need to be cleaned out on a regular basis. The following example shows that how a security administrator can delete all records from the SYSIBM.SYSTABAUTH_H table by using the REORG utility. In this example, SYSTABAUTH_H resides in the DSNDB06.SYSTSTBH table space, and all records with an end timestamp of greater than six months are deleted.

REORG TABLESPACE DSNDB06.SYSTSTBH
SHRLEVEL CHANGE COPYDDN SYSCOPY
DISCARD FROM TABLE SYSIBM.SYSTABAUTH_H
WHEN (SYS_END < CURRENT TIMESTAMP - 6 MONTHS)