用于审计日志的数据库设计

 

每次我需要设计一个新的数据库时，我都花了相当长的时间思考如何设置数据库模式以保留更改的审计日志。

这里已经提出了一些问题，但我不同意在所有情况下都有一个最佳方法：

  • 修订版的数据库设计
  • 更新日志审计数据库表的最佳设计
  • 关于捕获审计线索的数据库设计思路

    • 我也偶然发现了这篇关于维护数据库更改日志的有趣文章，该文章试图列出每种方法的优缺点。 它写得非常好，并且有有趣的信息，但它使我的决定变得更加困难。

    我的问题是：有没有可以使用的参考资料，可能是一本书或类似决策树的东西，我可以根据一些输入变量来决定应该采用哪种方式，如：

  • 数据库模式的成熟
  • 如何查询日志
  • 它将需要重新创建记录的可能性
  • 更重要的是：写或阅读表现
  • 正在记录的值的性质（字符串，数字，斑点）
  • 存储空间可用

    • 我知道的方法是：

    1.为创建和修改的日期和用户添加列

    表格示例：

  • ID
  • _1
  • _2
  • VALUE_3
  • 创建日期
  • modifed_date
  • 由...制作
  • modified_by

    • 主要缺点：我们失去了修改的历史。 提交后无法回滚。

    2.只插入表格

    表格示例：

  • ID
  • _1
  • _2
  • VALUE_3
  • 从
  • 至
  • 删除（布尔）
  • 用户

    • 主要缺点：如何让外键保持最新状态？ 需要巨大的空间

    3.为每个表创建一个单独的历史记录表

    历史表格示例：

  • ID
  • _1
  • _2
  • VALUE_3
  • VALUE_4
  • 用户
  • 删除（布尔）
  • 时间戳

    • 主要缺点：需要复制所有审计表。 如果模式更改，则还需要迁移所有日志。

    4.为所有表创建合并历史记录表

    历史表格示例：

  • TABLE_NAME
  • 领域
  • 用户
  • NEW_VALUE
  • 删除（布尔）
  • 时间戳

    • 主要缺点：如果需要，我能否重新创建记录（回滚）？ new_value列需要是一个巨大的字符串，因此它可以支持所有不同的列类型。

    一些维基平台使用的一种方法是将识别数据和正在审核的内容分开。 它增加了复杂性，但是最终会得到完整记录的审计跟踪，而不仅仅是已编辑的字段列表，您必须将其混合起来以便让用户了解旧记录的样子。

    例如，如果您有一个名为Opportunities的表来跟踪销售交易，您实际上可以创建两个单独的表：

    机会
    Opportunities_Content （或类似的东西）

    “ 商机”表中将包含用于唯一标识记录的信息，并将存放您为外键关系引用的主键。 Opportunities_Content表格将包含用户可以更改的所有字段，并且您希望为其保留审计跟踪。 内容表中的每条记录都会包含自己的PK以及修改日期和修改日期数据。 机会表将包括对当前版本的引用以及关于主要记录最初创建时间和人员的信息。

    这里有一个简单的例子： 

    CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

    内容： 

    CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

    我可能会使内容表的PK成为PageID和Revision提供的多列键，如果Revision是标识类型的话。 您将使用修订列作为FK。 然后，您通过JOINing来拉取合并记录，如下所示： 

    SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

    那里可能有一些错误......这是我的头顶。 但它应该给你一个替代模式的想法。

    玩笑

    如果您使用SQL Server 2008，则可能应考虑更改数据捕获。 这是2008年的新功能，可以为您节省大量的工作。

    我不知道有任何参考，但我确定有人写了一些东西。

    但是，如果目的仅仅是记录发生的事情 - 审计日志的最典型用法 - 那么为什么不简单地保留一切： 

    timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue

    大概这是由触发器维持的。

    链接地址: http://www.djcxy.com/p/37455.html

    上一篇: Database design for audit logging

    下一篇: Database localization