更改日志/审核数据库表的最佳设计？

我需要创建一个数据库表来存储不同的变更日志/审计 (当某物被添加、删除、修改等时)。我不需要存储特别详细的信息，所以我在考虑一些类似的东西:

  • Id (用于事件)
  • 触发它的用户
  • 事件名称
  • 事件描述
  • 事件的时间戳

Am I missing something here? Obviously, I can keep improving the design, although I don't plan on making it complicated (creating other tables for event types or stuff like that is out of the question since it's a complication for my need).

122244 次浏览
小开

有很多方法可以做到这一点。我最喜欢的方法是:

  1. 向源表(要记录的表)添加 mod_user字段。

  2. 创建一个日志表，其中包含要记录的字段，以及一个 log_datetime和 seq_num字段。seq_num是主键。

  3. 在源表上生成一个触发器，该触发器在任何受监视字段发生更改时将当前记录插入到日志表中。

现在你有了所有改变的记录，还有谁做的。

小开

我们还记录新旧值和它们来自的列，以及在审计详细信息表中被审计的表的主键。想想你要审计表干什么？您不仅想知道谁进行了更改以及何时进行了更改，而且当发生不好的更改时，您还需要一种快速的方式将数据放回原处。

在设计时，应该编写代码来恢复数据。当你需要恢复的时候，通常是很匆忙的，最好是已经做好了准备。

小开

我们的桌子上有什么:-

Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name

泛型 id 指向已更新的表中的一行，表名是该表的字符串名称。不是一个好的数据库设计，但是非常实用。我们的所有表都有一个单一的代理键列，因此这样工作得很好。

小开
最佳答案

在我正在进行的项目中，审计日志也是从非常简约的设计开始的，就像你描述的那样:

event ID
event date/time
event type
user ID
description

理念是一样的: 让事情变得简单。

然而，很快就发现这种简约的设计是不够的。典型的审计工作可以归结为这样的问题:

Who the heck created/updated/deleted a record
with ID=X in the table Foo and when?

因此，为了能够快速回答这些问题(使用 SQL) ，我们在审计表中增加了两列

object type (or table name)
object ID

那时我们的审计日志的设计才真正稳定下来(到现在已经有几年了)。

当然，最后一个“改进”将仅适用于具有代理键的表。但你猜怎么着？我们所有值得审核的表都有这样一个密钥！

小开

这里有很多有趣的答案，也有很多类似的问题。根据个人经验，我唯一能补充的是:

  1. 将审计表放在另一个数据库中。理想情况下，您希望与原始数据分离。如果您需要还原数据库，您实际上并不希望还原审计跟踪。

  2. Denormalize as much as reasonably possible. You want the table to have as few dependencies as possible to the original data. The audit table should be simple and lightning fast to retrieve data from. No fancy joins or lookups across other tables to get to the data.

小开

There are several more things you might want to audit, such as table/column names, computer/application from which an update was made, and more.

现在，这取决于您真正需要的详细审计的程度和级别。

我们开始构建自己的基于触发器的审计解决方案，我们希望审计所有内容，并且手边有一个恢复选项。结果证明这太复杂了，所以我们最终使用了基于触发器的第三方工具 ApexSQL 审计来创建我们自己的定制解决方案逆向工程。

Tips:

  • 包括之前/之后的值

  • 包括3-4列用于存储主键(如果是复合键的话)

  • 正如 Robert 已经建议的那样，将数据存储在主数据库之外

  • 花相当多的时间准备报告-特别是那些你可能需要恢复的报告

  • 计划存储主机/应用程序名称——这对于跟踪可疑活动可能非常有用

小开

根据分离原则:

  1. 审核数据表需要与主数据库分开。因为审计数据库可以有很多历史数据，所以从内存利用率的角度来看，将它们分开是有意义的。

  2. Do not use triggers to audit the whole database, because you will end up with a mess of different databases to support. You will have to write one for DB2, SQLServer, Mysql, etc.

小开

在一般情况下，自定义审计(创建各种表)是一个糟糕的选择。可以禁用数据库/表触发器以跳过某些日志活动。自定义审计表可能被篡改。可能发生的异常会导致应用程序崩溃。更不用说设计一个健壮的解决方案的困难了。到目前为止，我在这个讨论中看到了一个非常简单的例子。您需要与当前数据库和任何特权用户(DBA、开发人员)完全分离。 每个主流 RDBMS 都提供了甚至连 DBA 都无法禁用、秘密篡改的审计工具。因此，RDBMS 供应商提供的审计功能必须是第一选择。其他选择是第三方事务日志读取器或自定义日志读取器，它将分解后的信息推送到消息传递系统，最终以某种形式的 AuditDataWarehouse 或实时事件处理程序结束。 总之: 解决方案架构师/“数据架构师”需要参与基于需求的系统定位。仅仅交给开发人员来解决通常是很严肃的事情。


提交答案