使用系统周期时序表跟踪审计信息的方案

Db2 可以为您追踪一些基本的审计信息。 Db2 可以追踪数据修改的时间、修改者以及修改数据的SQL操作。

为了追踪数据修改的时间，请将您的表定义为系统周期临时表。当系统周期时间表中的数据被修改时，关于修改的信息会记录在关联的历史表中。

为了追踪修改数据的SQL语句的执行者和内容，您可以使用非确定性生成表达式列。这些列可以包含有助于审计的值，例如数据修改时当前SQLID特殊寄存器的值。您可以使用适当的 CREATE TABLE 或 ALTER TABLE 语法定义生成的表达式列的多个变体。生成表达式列的每个变化都会产生不同类型的生成值。

在以下场景中，创建了一个系统周期时间表，其中包含非确定性生成表达式列，用于跟踪审计信息。

假设您发布以下语句来创建一个名为STT的系统周期性临时表：

CREATE TABLE STT (balance INT, 
                  user_id VARCHAR(128) GENERATED ALWAYS AS  ( SESSION_USER ) ,  
                  op_code CHAR(1) 
                              GENERATED ALWAYS AS ( DATA CHANGE OPERATION )  
                  ... SYSTEM PERIOD (SYS_START, SYS_END));

user_id列用于存储修改数据的人员。此列定义为非确定性生成表达式列，在数据更改操作时包含 SESSION_USER 特殊寄存器的值。

op_code列用于存储修改数据的SQL操作。此列也被定义为非确定性生成表达式列。

假设您随后发出以下语句，为STT创建历史表，并将该历史表与STT关联：

CREATE TABLE STT_HISTORY (balance INT, user_id VARCHAR(128) , op_code CHAR(1) ... );

ALTER TABLE STT ADD VERSIONING 
                     USE HISTORY TABLE STT_HISTORY     ON DELETE ADD EXTRA ROW;

在ALTER TABLE语句中，ON DELETE ADD EXTRA ROW子句表示当从STT中删除一行时，将在历史表中插入一行。历史表中的这一额外行用于在删除操作时保存非确定性生成表达式列（user_id和op_code）的值。

现在，让我们来考虑一下修改STT表会发生什么。为简单起见，在此场景中，日期值代替时间戳用于期间列。

假设2010年6月15日，用户KWAN发布以下语句，在STT中插入一行：

 INSERT INTO STT (balance) VALUES (1)

插入后，表格包含以下数据。

表 1. 插入后的数据
表	数据（余额、用户ID、操作代码、系统开始时间、系统结束时间）
STT	`(1, 'KWAN', ‘I’, 2010-06-15, 9999-12-30)`
STT_HISTORY	空

后来，在2011年12月1日，用户HAAS发表了以下声明，对这一行进行了更新：

   UPDATE STT SET balance = balance + 9;

更新后，表格中的数据如下：

表 2. 更新后的数据
表	数据
STT	`(10, 'HAAS', 'U', 2011-12-01, 9999-12-30)`
STT_HISTORY	`row 1 (1, 'KWAN', 'I', 2010-06-15, 2011-12-01)`

2013年12月20日，用户THOMPSON发布以下声明，要求删除该行：

   DELETE FROM STT;

删除后，表格中的数据如下：

表 3. 删除后的数据
表	数据
STT	空
STT_HISTORY	`row 1 (1, 'KWAN', 'I', 2010-06-15, 2011-12-01) row 2 (10, 'HAAS', 'U', 2011-12-01, 2013-12-20) row 3 (10, 'THOMPSON', 'D', 2013-12-20, 2013-12-20)`

STT_HISTORY中的行包含以下信息：更改开始

行 1: 第1行记录了HAAS发布的更新语句所导致的历史变化，并反映了HAAS发布更新语句之前系统时间表中该行的值：用户KWAN于2010年6月15日发布了插入语句（‘I’），将 balance=1 设置为。这一行一直有效，直到2011年12月1日，即用户HAAS发布更新声明取代KWAN插入声明的日期。
行 2: 第二行记录了THOMPSON发出的删除语句所导致的历史，并反映了THOMPSON发出删除语句之前系统周期时间表中该行的值：用户HAAS于2011年12月1日发出更新语句（‘U’），将 balance=10 设置为。此行有效至2013年12月20日，即汤普森发布删除此行的声明的日期。
第 3 行: 因为在系统周期临时表的定义中指定了ON DELETE ADD EXTRA ROW子句，所以添加了第3行，用于记录删除操作本身的信息。第3行表示，THOMPSON于2013年12月20日发布了删除声明（“D”），而 balance=10 在删除该行时。

第二行和第三行在用户数据（余额栏的数值）上完全相同。区别在于审计列：新生成的表达式列记录了操作发起者和该行代表的变更操作。

带有显式或隐式 FOR SYSTEM_TIME 时段规格的 SELECT 语句可以透明地访问历史数据（或当前数据和历史数据的组合）。对于此类查询，历史记录表中的第三行不会出现在查询结果中。