Microsoft SQL 的审计跟踪生成器

• 下载源码 - 1.9 KB

引言

出于法律义务（例如萨班斯-奥克斯利法案或FDA法规）的规定，一些公司必须保留其数据的审计日志。但我认为它对于调试目的也非常有用。它可以让你了解数据库在任何时间点的状态。

在本文中，我将解释我首选的实现审计日志的方法。接下来，我将介绍一个脚本，用于在数据库中自动实现审计日志。

带影子表和触发器的审计日志

那么如何实现审计日志呢？有不同的方法。我首选的方法是为数据库中的每个表使用一个影子表。每当在表中插入、更新或删除记录时，该记录也会插入到相应的影子表中。为了将记录也插入到影子表中，我使用原始表上的触发器，当发生某些操作时，这些触发器会触发。你在网上看到的关于影子表的其他名称可能还有存档表或审计表。

让我们用一个简单的例子来弄清楚这一点。

左边是包含三个列的名为Users的表结构。我称之为基表。右边是该表的影子表。影子表包含Users表中的所有列，以及一些额外的列

• AuditId：这是影子表的主键。它是一个标识字段。
• AuditAction：这是一个单字母代码，用于指示操作的类型。值分别为I、U或D，分别表示插入、更新和删除。
• AuditDate：操作发生的时间和日期。默认值设置为getdate()，这是一个返回当前日期和时间的 SQL 函数。
• AuditUser：执行操作的用户。默认值设置为suser_sname()，这是一个返回当前连接用户的用户名的 SQL 函数。
• AuditApp：使用的应用程序。默认值设置为(('App=('+rtrim(isnull(app_name(),'')))+') ')。这允许你了解是哪个应用程序修改了数据，例如App=(Microsoft SQL Server Management Studio Express)。

为了填充影子表，我在Users表上定义了触发器。我们需要三个触发器：一个用于插入，一个用于更新，一个用于删除。下面显示了插入操作的代码。更新和删除的操作也类似。如果你不熟悉触发器，请参阅SquaredRomi的SQL Server 2000 触发器简介。

CREATE TRIGGER tr_users_Insert ON dbo.users
FOR INSERT AS INSERT INTO users_shadow(UserId,FirstName,LastName,AuditAction)
SELECT UserId,FirstName,LastName,'I' FROM Inserted

触发器填充的列仅限于基表的数据列（userid、FirstName 和LastName）以及AuditAction 列。影子表中的所有其他列（AuditId、AuditDate、AuditUser 和AuditApp）都由其默认值定义填充。

那么这种方法的优缺点是什么？让我们从优点开始

• 它完全将当前数据与审计日志分开。旧值不再存储在基表中，而是存储在影子表中。没有软删除，即已删除的记录被标记为已删除，而不是真正删除。
• 它可以轻松地在现有数据库上实现。如果你最初没有考虑审计日志，可以稍后添加。你只需要在基表上添加触发器并创建影子表。无需更改与数据库交互的存储过程或应用程序。
• 它总是触发。例如，如果你通过 Enterprise Manager 连接到数据库并手动修改数据，触发器会触发，并且影子表会相应地更新。

该方法也有一些缺点

• 整个记录会被复制到影子表，包括未更改的列。在我们的例子中，如果你更改了基表用户的firstname，lastname也会被复制到影子表中，即使它没有改变。因此，影子表会占用比实际需要更多的空间。
• 触发器不能用于所有列数据类型。Text、Ntext和Image 不受支持。原因是它们本身不存储在记录中。记录只包含指向数据的指针。在 SQL 2005 中，时间戳也不受支持。
• 表的数量翻倍，尽管我个人认为这不算一个缺点。
• 审计日志是在表级别而不是操作级别。如果在应用程序的单个保存操作期间更新了数据库中的多个表，那么在不同表上发生的事务之间没有链接。唯一将它们联系起来的是它们（几乎）在同一时间由同一用户发生。

审计日志生成器脚本

如果你的数据库中有 50 个表，使用上述方法添加审计日志意味着再添加 50 个表并创建 150 个触发器。这就是我创建审计日志生成器的原因。它可以节省时间并避免拼写错误。请参阅本文顶部的链接下载代码。

审计日志生成器是作为存储过程编写的。因此，你不需要任何其他工具。RichardRoe 使用codesmith 作为生成器，但这需要你先购买该工具。

该存储过程接受四个参数

• @TableName：你想为其添加审计日志的表的名称，例如users
• @Owner：表的所有者。默认值为dbo
• @AuditNameExtention：你希望为影子表名称添加的扩展名。例如，如果将其设置为_shadow，则users的审计表将被命名为users_shadow。默认值为_shadow
• @DropAuditTable：一个位值，指定是否可以删除影子表。如果为1，则现有审计表将被删除并重新创建。当然，你将丢失其中的所有数据。这在开发阶段特别有用，但你可能也想在生产系统上执行此操作。默认值为0。

该存储过程将通过查询 SQL Server 的系统表来发现原始表中的列。这些系统表由 SQL Server 本身用来存储表的结构。下面显示了获取有关表的所有信息的查询。有关 SQL Server 系统表的完整概述，请参阅Microsoft MSDN 网站。

SELECT b.name, c.name as TypeName, b.length, _
    b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId

下图显示了我们为Users表运行此查询的结果。

存储过程的其余部分使用游标遍历此查询的结果，并在字符串中动态构建 SQL 语句，以创建影子表并向原始表添加触发器。然后使用EXEC命令执行这些语句。我将不详细介绍，因为它很简单。

使用脚本

该脚本是一个存储过程，因此使用它意味着调用该存储过程。最简单的形式是，你只需要设置@TableName 参数，因为所有其他参数都指定了默认值。可以在查询窗口中执行以下语句。

EXECUTE GenerateAudittrail 'Users'

以下示例显示了指定所有参数值时的情况。

EXECUTE GenerateAudittrail 'Users', 'dbo','_shadow', 0

该脚本非常方便，可以快速为给定的数据库表创建影子表。但是，它并非设计用于修改影子表以反映对相应基表的更改。在这种情况下，它只能删除影子表（丢失其中所有记录）并重新创建它。将@DropAuditTable设置为1以强制删除并重新创建影子表。

相关文章

我想提一下两篇与此主题相关的文章。

Nigel Rivett 撰写了一篇文章，描述了一个类似于此处描述的审计日志机制，但他在其中尝试解决我描述的一些缺点。他为数据库中的所有表使用一个集中的审计日志表，而不是为每个原始表创建一个单独的影子表。当然，这避免了大量的影子表，但他不得不选择varchar来存储所有值，无论它们在原始表中的类型是什么。但他提出的最有趣的观点是他构建到触发器中的逻辑。对旧值和新值进行比较，并且仅将已更改的列存储在审计日志中。这可以节省存储空间，因为整个记录不会被复制。只有一个警告。修改表中数据所需的时间会增加，因为触发器（由于该修改而触发）完成所需的时间会更长，因为它包含比此处提供的审计日志生成器创建的简单触发器更多的逻辑。

Ben Allfree 在SQL Server 记录版本控制中批评了我在这里采取的审计日志方法，并提出了一个替代方案。不幸的是，他没有提及触发器，而是将审计日志逻辑移到访问其数据库的代码中，例如存储过程。因此，使用他提出的方法，在不更改访问该数据库的应用程序的情况下，无法将审计日志添加到现有数据库。我另一个不喜欢的地方是他采用的软删除方法，即标记旧记录版本为非活动状态而不是真正删除它们。通过将旧记录版本移到单独的影子表中，我保持了原始表的干净和记录数量的低，而不会影响select 语句的速度。