审计日志数据库设计

每次我需要设计一个新的数据库，我花了相当多的时间 思考如何设置数据库模式来保存审计日志 改变。

关于这个问题，已经有人提出了一些问题，但我不同意这一点 对于所有情况都有一种最佳方法:

我还偶然发现了这个 关于维护数据库更改日志的有趣文章，它试图列出每种方法的优缺点。文章写得很好，信息也很有趣，但这让我的决定更加困难。

我的问题是: 是否有我可以使用的参考资料，也许是一本书或者类似于决策树的东西，我可以根据它们来决定我应该走哪条路 输入变量，比如:

  • 数据库模式的成熟度
  • 如何查询日志
  • 需要重新创建记录的可能性
  • 更重要的是: 写或读性能
  • 要记录的值的性质(字符串、数字、 blobs)
  • 可用存储空间

我所知道的方法是:

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

表例:

  • 身份证
  • Value _ 1
  • Value _ 2
  • Value _ 3
  • Create _ date
  • (修改日期)
  • 由
  • 修改 _ by

主要缺点: 我们丢失了修改的历史记录。提交后不能回滚。

2. 只插入表格

表格示例 :

  • 身份证
  • Value _ 1
  • Value _ 2
  • Value _ 3
  • 来自
  • 到
  • 删除(布尔值)
  • 使用者

主要缺点: 如何保持外键的最新? 需要巨大的空间

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

历史表格例子:

  • 身份证
  • Value _ 1
  • Value _ 2
  • Value _ 3
  • Value _ 4
  • 使用者
  • 删除(布尔值)
  • 时间戳

主要缺点: 需要复制所有已审计的表格。如果模式发生变化，也需要迁移所有日志。

4. 为所有表创建统一的历史记录表

历史表格例子:

  • Table _ name
  • 场地
  • 使用者
  • New _ value
  • 删除(布尔值)
  • 时间戳

主要缺点: 如果需要，我能够轻松地重新创建记录(回滚)吗？New _ value 列需要是一个巨大的字符串，这样它才能支持所有不同的列类型。

97967 次浏览
小开

我不知道任何参考资料，但我肯定有人写了些东西。

然而，如果目的仅仅是为了记录所发生的事情(审计日志最典型的用法) ，那么为什么不简单地保留所有东西:

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

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

小开

如果您正在使用 SQLServer2008，那么您可能应该考虑更改数据捕获。这是2008年的新产品，可以为您节省大量的工作。

小开

我认为没有什么比决策树更好的了。由于一些利弊(或要求)是不能真正计数的。例如，如何度量成熟度？

因此，只需为您的审计日志排列您的业务需求即可。尝试预测这些需求将来会如何变化，并生成您的技术需求。现在你可以比较它的优缺点，选择正确的/最好的选择。

请放心，不管你怎么决定，总会有人认为你做了错误的决定。然而，你做了你的功课，你证明了你的决定。

小开
最佳答案

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

例如，如果有一个名为 机会的表来跟踪销售交易，那么实际上可以创建两个独立的表:

机会
Opportunity _ Content (或类似的东西)

机会表将包含用于唯一标识记录的信息，并且包含用于外键关系的主键。机会 _ 内容表将包含用户可以更改的所有字段，并且您希望为其保留审计跟踪。内容表中的每个记录都将包含自己的 PK 以及修改后的 by 和修改后的 date 数据。机会表将包括对当前版本的引用以及关于主记录最初何时创建和由谁创建的信息。

这里有一个简单的例子:

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 和 Revison 的多列键，前提是 Revison 是一个标识类型。您将使用“修订”列作为 FK。然后通过以下方式连接获取统一记录:

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

可能会有一些错误... 这是我的头顶。不过，它应该会给你一个替代模式的想法。

小开

我们将为博客应用程序创建一个小型示例数据库，需要两个表:

blog: 存储唯一的帖子 ID、标题、内容和已删除的标志。 audit: 存储一组基本的历史更改，包括记录 ID、博客文章 ID、更改类型(NEW、 EDIT 或 DELETE)和更改的日期/时间。 下面的 SQL 创建 blog并为删除的列编制索引:

CREATE TABLE `blog` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` text,
`content` text,
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

下面的 SQL 创建 audit表。对所有列都进行了索引，并为引用 blog.id 的 audit.blog _ id 定义了一个外键。因此，当我们物理删除博客条目时，它的完整审计历史记录也会被删除。

CREATE TABLE `audit` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`blog_id` mediumint(8) unsigned NOT NULL,
`changetype` enum('NEW','EDIT','DELETE') NOT NULL,
`changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `ix_blog_id` (`blog_id`),
KEY `ix_changetype` (`changetype`),
KEY `ix_changetime` (`changetime`),
CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
小开

我使用以下结构:

id  int
user_id int
system_user_id  int
tenant_id   int
db_name varchar
model_name  varchar
model_primary_key   int
model_attributes    text
created_at  timestamp
ip  varchar
session_id  varchar
request_id  varchar
comments    text

到目前为止，在3.62亿条记录、多租户、多数据库中运行良好。

Model _ properties 是最重要的，改变了什么，作为键值格式的 json 字符串。


提交答案