审计跟踪 – 数据库中数据变更的跟踪

引言

许多企业应用程序的常见需求是记录数据库中的数据更改——什么数据发生了更改，谁更改了它们以及何时更改（审计日志）。博客作者就这个老问题发表了许多文章，但在关系数据库中如何实现这一点，只有少数通用方法。

莎士比亚式的假设——数据库管理员的难题

这是一个从未发生过的虚构故事

朱丽叶·凯普莱特遇到了保罗·史密斯；他们相爱并于2009年8月25日结婚。朱丽叶回到工作岗位后，她要求数据库管理员更改公司数据库中的姓名。管理员对这项额外工作不太满意，但他还是为她做了。

朱丽叶和保罗的婚姻并不顺利，朱丽叶在短时间后与保罗离婚。很快朱丽叶感到孤独，她遇到了罗密欧·蒙太古，当然，疯狂地爱上了他。顺理成章地，又安排了一场婚礼，朱丽叶要求数据库管理员在2010年1月10日再次更改她的姓名。

朱丽叶所有爱情关系的悲剧在她发现她的丈夫罗密欧是个赌徒，花光了他们所有的钱时结束了。她再次离婚，并于2010年7月21日将姓名改为朱丽叶·辛格尔顿，希望不再结婚。不用说，数据库管理员几乎要自杀了，因为他的数据库不允许更改姓名超过两次。

你会建议数据库管理员在他的下一世做得更好吗？

问题描述

让我们总结一下数据库管理员被要求对朱丽叶的个人数据做了什么

• 朱丽叶·凯普莱特于2005年3月1日入职
• 姓名 朱丽叶·凯普莱特 于2009年8月25日更改为 朱丽叶·史密斯
• 姓名 朱丽叶·史密斯 于2010年1月10日更改为 朱丽叶·蒙太古
• 姓名 朱丽叶·蒙太古 于2010年7月21日更改为 朱丽叶·辛格尔顿

解决方案一：回滚过期数据

在数据库中保留历史数据最简单的解决方案是在更新之前创建一个重复的记录。之后，我们可以更新请求的数据。在追踪朱丽叶姓名的案例中，我们在数据库表中得到这些数据

id	名称	创建日期	创建人
124	朱丽叶·凯普莱特	2005-03-01	管理员
124	朱丽叶·史密斯	2009-08-25	管理员
124	朱丽叶·蒙太古	2010-01-10	管理员
124	朱丽叶·辛格尔顿	2010-07-21	管理员

在真实的数据库模式中，id 是主键，必须是唯一的。因此，我们需要一个额外的列来包含来自哪个行ID的数据被回滚的信息。此外，有一个表示记录状态的列会很有帮助。由于情况变得更加复杂，这里我展示了数据在数据库中是如何演变的

朱丽叶·凯普莱特于2005年3月1日入职。

id	原始ID	状态	名称	创建日期	创建人
124	124	active	朱丽叶·凯普莱特	2005-03-01	管理员

姓名 朱丽叶·凯普莱特 于2009年8月25日更改为 朱丽叶·史密斯。

id	原始ID	状态	名称	创建日期	创建人
124	124	active	朱丽叶·史密斯	2009-08-25	管理员
254	124	历史记录	朱丽叶·凯普莱特	2005-03-01	管理员

姓名 朱丽叶·史密斯 于2010年1月10日更改为 朱丽叶·蒙太古。

id	原始ID	状态	名称	创建日期	创建人
124	124	active	朱丽叶·蒙太古	2010-01-10	管理员
254	124	历史记录	朱丽叶·凯普莱特	2005-03-01	管理员
347	124	历史记录	朱丽叶·史密斯	2009-08-25	管理员

姓名 朱丽叶·蒙太古 于2010年7月21日更改为 朱丽叶·辛格尔顿。

id	原始ID	状态	名称	创建日期	创建人
124	124	active	朱丽叶·辛格尔顿	2010-07-21	管理员
254	124	历史记录	朱丽叶·凯普莱特	2005-03-01	管理员
347	124	历史记录	朱丽叶·史密斯	2009-08-25	管理员
489	124	历史记录	朱丽叶·蒙太古	2010-01-10	管理员

你可以看到，对记录 id=124 的每次更改都会触发

1. 创建一个 status=history 的重复行，以及
2. 将列名更新为请求的值，并将 created_date 设置为当前日期。

当活动记录将被“删除”时，只有状态标志的值从 active 变为 history。

优点

基于回滚旧数据来实现审计日志很简单。你不需要额外的表。维护也很简单。如果你决定删除旧数据，那只是一条SQL语句的事情。

缺点

但是如果你需要在更多表中实现审计日志呢？你需要添加列 original_id、status、created_date 和 created_by。并且你必须实现前面介绍的回滚逻辑，无论是直接在数据库中（通常通过表触发器）还是在应用程序中。

问题可能在于，当数据更改时，整个记录都会被复制，即也包括未更改的数据。这会导致数据重复，如果更改频繁发生，数据库磁盘空间可能会显著增加。例如，如果表 persons 有一个包含照片的二进制数据的 photo 列，那么每当朱丽叶的姓名更改时，整个记录（包括照片）都会被复制到回滚记录中。

另一个缺点是，支持审计日志的每个表的复杂性都会增加。你必须始终牢记，检索记录并不简单。你总是必须使用带有条件的 SELECT 子句

SELECT * FROM persons WHERE status='active'

当你需要回滚特定记录的历史数据时，你必须将历史记录中的所有列复制到活动记录中。如果只是将状态值从“history”切换到“active”会更容易，但这会破坏如果该记录在其他表中被引用时的引用完整性。

解决方案二：专用数据追踪表

另一种方法是基于一个单独的审计日志表，该表专门用于记录来自所有具有审计日志功能的表的数据更改

id	table	列	行	更改日期	更改人	旧值	新值
1241	人员	名称	124	2005-03-01	管理员	朱丽叶·凯普莱特	朱丽叶·凯普莱特
1654	人员	名称	124	2009-08-25	管理员	朱丽叶·凯普莱特	朱丽叶·史密斯
2547	人员	名称	124	2010-01-10	管理员	朱丽叶·史密斯	朱丽叶·蒙太古
3645	人员	名称	124	2010-07-21	管理员	朱丽叶·蒙太古	朱丽叶·辛格尔顿

优点

这个解决方案更好地分离了关注点。所有历史数据只有一个共同的数据存储。此外，你只需为所有应支持审计日志功能的表实现一次审计日志功能。

与之前的解决方案相比，当记录更改时，只记录更改的数据。因此，预计会稍微快一些，但我没有做任何测试，所以无法精确证明。真正的优势在于，存储数据更改所需的磁盘空间更少。当你对日志表进行规范化并只放入“表”和“列”的整数引用时，你可以改进表结构。

你只需通过查询即可轻松检索用户活动

SELECT * FROM audit_log WHERE changed_by='admin'

缺点

所有数据更改都记录在一个公共表中，因此存储在那里的旧值和新值必须是某种通用类型。例如，在 Microsoft SQL 中，它可以是“sql_variant”。或者你可以在将值存储到日志表之前将其转换为“varchar”。

回滚是一个相当难以实现的任务。如果你想将数据回滚到2010年3月3日，你必须使用查询

SELECT [table], [column], old_value, new_value FROM audit_log WHERE _
  [table]='persons' AND row=124 and changed_date > '2010-03-01' ORDER BY changed_date

然后你遍历结果集并构建动态查询，因为表和列信息都在结果中。与之前的解决方案相比，它更复杂。幸运的是，该功能只需实现一次，然后就可以在整个应用程序中重用。

历史

• 2010年8月30日：首次发布