通过 .NET (CLR) 触发器详细 SQL Server 数据审计
4.91/5 (14投票s)
创建用于 SQL Server 的 .NET 触发器，该触发器将记录（精确到字段级别）对表进行的所有更改。
引言
我一直在寻找一个可靠（且精细）的审计解决方案，用于跟踪 SQL Server 数据库中数据的更改，但一直找不到（除了商业解决方案）。我想要的很简单：每当表中的一条记录被插入、删除或更新时，我希望某种形式的审计能够显示采取了什么操作，由谁执行，何时执行，以及具体的更改是什么（即，显示任何受影响字段的更改前和更改后的值）。
微软似乎意识到这是一个迫切的需求，因为他们似乎在 SQL Server 2008 中包含了类似的功能，但据我所知，这只记录了针对表的实际执行语句，而没有跟踪更改前后的值。后者对于任何良好的日志或审计都至关重要。
在查找过程中，我看到了一些尝试编写 SQL 触发器来显示此信息的例子，但我可以理解 SQL 触发器可能非常高效，但我不喜欢这样一个事实：当它们变得冗长而复杂时，会难以维护，有时甚至难以阅读。我看到 SQL 触发器可以使用 `COLUMNS_UPDATED` 等项，但这些项的使用并不十分直接。本质上，我正在寻找一种能够正确执行日志记录、易于维护并且——非常重要——优雅的解决方案。这时 CLR 触发器就派上用场了。
我发现您可以创建 .NET 触发器并将其附加到服务器，它们可以访问与使用常规 SQL 触发器时相同的对象和表。我还找到了一篇不错的入门文章（http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/60ed909b-51ac-425c-8e00-4d7978f39f70/），该文章涉及为审计编写 CLR 触发器，我的文章大致基于它。
必备组件
在使用此功能之前，有几点需要注意：
- 必须在服务器上启用 CLR 对象的执行。通过在 Enterprise Manager 中执行以下语句来启用它：
exec sp_configure 'clr enabled', 1
reconfigure
exec sp_configure
表结构
我正在使用的示例表名为 Customers，它非常简单。以下是它的 `CREATE` 语句：
CREATE TABLE [dbo].[Customers]
(
[ID] [INT] IDENTITY(1, 1) NOT NULL,
[Name] [NVARCHAR](50) NOT NULL,
[Address1] [NVARCHAR](100) NULL,
[Address2] [NVARCHAR](100) NULL,
[City] [NVARCHAR](100) NULL,
[Zip] [NVARCHAR](100) NULL,
[Country] [NVARCHAR](100) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
为了进行审计，数据已规范化并存储在成对的头表和明细表中。头表包含表名、受影响记录的主键值、一个批次标识符（帮助查看哪些记录属于同一 `INSERT`/`UPDATE`/`DELETE` 语句）、用户名和日期，以及一个备注字段，用于跟踪异常情况（有关更多详细信息，请参阅下文）。明细表然后保存受影响字段的更改前和更改后的值。
审计头表结构
CREATE TABLE [dbo].[AuditHeader]
(
[AuditHeaderID] [INT] IDENTITY(1, 1) NOT NULL,
[Action] [NVARCHAR](5) NOT NULL,
[BatchID] [NVARCHAR](MAX) NOT NULL,
[TableName] [NVARCHAR](MAX) NOT NULL,
[PrimaryKey] [NVARCHAR](MAX) NOT NULL,
[Note] [NVARCHAR](MAX) NULL,
[UpdateDate] [DATETIME] NOT NULL,
[UpdateUserName] [NVARCHAR](MAX) NOT NULL,
CONSTRAINT [PK_AuditHeader] PRIMARY KEY CLUSTERED ( [AuditHeaderID] ASC )
)
审计明细表结构
CREATE TABLE [dbo].[AuditDetail]
(
[AuditHeaderID] [INT] NOT NULL,
[AuditDetailID] [INT] IDENTITY(1, 1) NOT NULL,
[FieldName] [NVARCHAR](100) NOT NULL,
[OldValue] [NVARCHAR](MAX) NULL,
[NewValue] [NVARCHAR](MAX) NULL,
CONSTRAINT [PK_AuditDetail] PRIMARY KEY CLUSTERED ( [AuditDetailID] ASC )
)
ALTER TABLE [dbo].[AuditDetail] WITH CHECK ADD CONSTRAINT
[FK_AuditDetail_AuditHeader] FOREIGN KEY([AuditHeaderID]) REFERENCES
[dbo].[AuditHeader] ([AuditHeaderID])
关于效率的重要说明
对于其中的 SQL 纯粹主义者，我确定您在看到文章中的“CLR”和“触发器”这两个词时就开始翻白眼了。我不会责怪您。编写纯 SQL 触发器可能是您可以创建的最快的触发器类型，但它确实付出了巨大的代价——您能编写的代码类型受到严重限制，可用的调试功能非常少（与调试 .NET 应用程序相比），面向对象的优势缺失严重，等等。所以我选择了 CLR 路线，但我对审计以这种方式维护时所花费的时间进行了一些度量。结果并不令人意外。
我的测试是在一台运行 Windows 7、双核 CPU 的普通台式机上进行的。关闭审计时，向 Customers 表插入 20,000 条记录平均需要 10 秒。一旦打开 CLR 触发器，这个时间就膨胀到 10 分钟。但通过移除触发器中最耗时的语句（请参阅下方的选项 #3），时间缩短到更合理的 1 分 40 秒。公平地说，我没有编写类似的 SQL 触发器，所以我不知道它需要多长时间。SQL 触发器仍然需要获取有关表名、主键值、来自审计头表的自动增量值等的相同信息。我的直觉是，它可能介于这两个数字之间。
为了降低执行时间，有几种选择：
- 多线程——一旦进入触发器，就会启动一个单独的线程来执行所有审计操作，从而使整个过程异步。理论上，所需时间会回到接近原始的 10 秒。不过，在研究这个问题时，我发现存在一些安全方面的复杂性需要考虑，这使得整个过程过于复杂而难以管理。请记住，我正在寻找一种优雅的解决方案；而不是一个需要 5 名开发人员和 2 名管理员来管理的解决方案。
- 消息队列——这似乎是迄今为止最好的选择。不再在现场处理所有审计逻辑，而是将 DELETED 和 INSERTED 表的内容，以及一些额外信息，序列化为 XML 并写入队列。我尝试了此方法，对于 20,000 条记录，时间降至约 30 秒。这绝对是一个不错的选择，但您需要构建一个始终监视队列的服务，从队列中提取序列化数据，并将其处理以插入到审计表中。这很可能是我将要采取的路线。
- 我发现有一个特别耗时的语句——负责检索表名。删除此语句可以节省大量时间（约七分钟）。我还没有找到合适的替代方案，SQL Server 似乎也没有将此信息提供给触发器。您可能会认为 `SqlTriggerContext.EventData` 应该包含此信息，但根据 MSDN，它只在运行数据定义语言触发器（DDL，即 `CREATE`、`ALTER` 等）时包含数据，而不是数据操作语言触发器（DML，即 `INSERT`、`UPDATE` 等）。他们为什么会遗漏这一点，我实在不解。也许这还在开发中？一种绕过此语句的方法是创建多个公共触发器（每个表一个），并在方法本身中硬编码表名（请参阅“深入代码”下的示例）。
底线
CLR 触发器无疑功能强大且潜力巨大。然而，当记录更改数量很高时，执行时间会受到影响，如果您希望触发器真正动态。因此，在数据经常被大量操作的系统中实施此功能之前，我会三思（或者实施它，但使用下面描述的特定于表的触发器）。但是，如果您正在处理一个最多只处理数十条（甚至数百条）记录更改的系统，那么额外的延迟几乎察觉不到，因此审计的优势肯定会得到回报。
设置
CLR 触发器的一大优点是，您可以从 Visual Studio 中管理编译、部署和测试。
编译很简单——只需像往常一样打开解决方案并编译。要部署，您需要先设置数据库连接设置，这可以通过转到项目属性并在“数据库”选项卡中设置连接字符串来完成。
测试也很简单——在项目“测试脚本”文件夹下，您会找到 `Test.Sql`。添加要测试的 SQL 语句，在代码（或 SQL 语句）上设置断点，然后按 F5。项目将被编译，部署到服务器，然后执行测试 SQL 语句。
不过，有一个重要的事情要注意，即主 `AuditTrigger` 方法上的 `SqlTrigger` 属性在调试代码时需要三个参数：`Name`（将在数据库中创建的触发器名称）、`Event`（触发器将触发的事件）和 `Target`（触发器将附加到的表）。最后一个参数对于从 Visual Studio 测试触发器至关重要，但如果您的触发器是通用的并附加到多个表，则应将其省略。
如果触发器是通用的，那么在完成测试后，只需删除 `Target` 参数，然后通过单击“生成”\“生成审计”来重新编译项目。不要尝试部署它，否则会因为缺少目标而导致错误。然后，您需要像下面这样从 Enterprise Manager 中将触发器附加到需要的表（请注意，您应该一次运行一个，而不是一次性全部运行，因为 `CREATE TRIGGER` 需要是批处理中运行的第一个语句）。
IF EXISTS (SELECT *
FROM sys.triggers
WHERE object_id = Object_id(N'[dbo].[AuditTrigger]'))
DROP TRIGGER [dbo].[AuditTrigger]
IF EXISTS (SELECT *
FROM sys.assemblies asms
WHERE asms.name = N'Audit'
AND is_user_defined = 1)
DROP ASSEMBLY [Audit]
CREATE ASSEMBLY audit FROM '[your solution's path]\Audit\bin\Debug\Audit.dll' WITH permission_set
= safe
CREATE TRIGGER [dbo].[AuditTrigger]
ON [dbo].[Customers]
AFTER INSERT, DELETE, UPDATE
AS
external name [Audit].[Triggers].[AuditTrigger]
EXEC sys.Sp_addextendedproperty
@name=N'SqlAssemblyFile',
@value=N'Trigger.cs',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Customers',
@level2type=N'TRIGGER',
@level2name=N'AuditTrigger'
EXEC sys.Sp_addextendedproperty
@name=N'SqlAssemblyFileLine',
@value=N'21',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Customers',
@level2type=N'TRIGGER',
@level2name=N'AuditTrigger'
审计的样子
审计显示了对表执行的所有操作的历史记录，无论是删除、插入还是更新。数据存储在两个表中：头表和明细表。
AuditHeader 结构
- AuditHeaderID - 表的主键（只是一个自动增量字段）。作为 AuditDetail 的外键。
- Action - “I”、“D”或“U”。表示所执行的操作是 `INSERT`、`DELETE` 还是 `UPDATE`。
- BatchID - 列中捕获的值本身没有意义，但用于链接作为同一 `INSERT`/`DELETE`/`UPDATE` 语句一部分而生成的其他审计记录。例如，如果执行具有 `WHERE` 子句且影响 100 条客户记录的 `UPDATE`，则所有 100 条 AuditHeader 记录将具有相同的 BatchID 值。一个更有用的值是事务 ID，但该 ID 有一些安全问题需要处理（参见下面的“未来改进”部分）。
- TableName - 执行数据库操作的表。
- PrimaryKey - 受影响记录的主键字段名和值的逗号分隔列表。
- Note - 如果在为特定案例生成审计记录时出现问题，此字段将包含有关该问题的消息。目前存在两种会生成解释性注释的情况（对于这两种情况，请参阅下文“记录 INSERT 或 UPDATE”部分以获取更多详细信息）。
- 如果 `UPDATE` 语句导致主键列发生更改，则触发器无法生成包含更改的 AuditDetail 记录。
- 如果受影响的表没有定义主键，则触发器无法生成包含更改的 AuditDetail 记录。
- UpdateDate - 更改发生的日期。
- UpdateUserName - 执行更改的用户名。
示例屏幕截图
AuditDetail
AuditDetail 将包含有关记录及其经过的具体更改的详细信息。请注意，仅记录值已更改的列。
- AuditHeaderID - AuditHeader 的主键。作为当前表的外键。
- AuditDetailID - 表的主键（只是一个自动增量字段）。
- FieldName - 受影响字段的名称。
- OldValue - 更改前的字段值。
- NewValue - 更改后的字段值。
示例屏幕截图（请注意，AuditHeaderID 13 有更多条目未在图像中捕获）
深入代码
触发器使用 `DataTable` 来存储和操作已修改的数据和审计数据，并使用 `DataAdapter` 快速填充它们或将它们刷新回数据库。它还使用 `SqlCommandBuilder` 根据 `DataAdapter` 中的 `SELECT` 语句自动生成审计表的 `INSERT` 语句。
由于 AuditHeader 上的主键是自动生成的字段，而 AuditDetail 需要此值作为外键，因此我们需要检索数据库生成的值，以便将其存储在 AuditDetail 中。为此，会捕获 `AuditHeaderAdapter` 上的 `RowUpdated` 事件，检索生成的主键，然后将其存储在 AuditHeader `DataTable` 中。这使其可用于 AuditDetail 的 `DataTable` 更新。
SqlDataAdapter AuditHeaderAdapter = new SqlDataAdapter("SELECT * FROM AuditHeader WHERE 1=0", conn);
...
AuditHeaderAdapter.RowUpdated += delegate(object sender, SqlRowUpdatedEventArgs args)
{
// Include a variable and a command to retrieve the identity value from the Access database.
int newRecordNumber = 0;
SqlCommand idCmd = new SqlCommand("SELECT @@IDENTITY", conn);
if (args.StatementType == StatementType.Insert)
{
// Retrieve the identity value and store it in the AuditHeaderID column.
newRecordNumber = int.Parse(idCmd.ExecuteScalar().ToString());
args.Row["AuditHeaderID"] = newRecordNumber;
}
};
如前所述，获取受影响表名的操作是在触发器执行过程中最耗时的操作之一，如下面的查询所示：
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id)" +
" FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
affectedTableName = cmd.ExecuteScalar().ToString();
我还没有找到其他方法来做到这一点，除了为每个表创建一个公共触发器方法，让它们中的每一个都调用同一个私有方法并将表名作为参数传递。
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger",
Event = "FOR INSERT, UPDATE, DELETE", Target = "Customers")]
public static void AuditTrigger()
{
UpdateAuditTables("Customers");
}
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger",
Event = "FOR INSERT, UPDATE, DELETE", Target = "Customers")]
public static void AuditTrigger()
{
UpdateAuditTables("Clients");
}
private static void UpdateAuditTables(string tableName)
{
...
}
获取用户名是一个相当直接的事情：
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
affectingUserName = curUserCommand.ExecuteScalar().ToString();
通过以下查询检索构成受影响表主键的字段列表。它基本上将字段名存储在 `DataTable` 中，并在稍后提供给审计代码，以便记录该记录的键，并且我们可以找到旧/新值。
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + affectedTableName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable primaryKeyTable = new DataTable();
PKTableAdapter.Fill(primaryKeyTable);
记录 DELETE
这些语句相当简单，因为它们的所有数据都包含在 DELETED 表中。我们只需循环遍历所有行（因为一条语句可能删除多行），然后为每一行创建一个 AuditHeader 记录和 AuditDetail 记录（其中列值不为 null）。
记录 INSERT 或 UPDATE
这两个操作类似，因为它们都将新数据集存储在 INSERTED 表中。此外，`UPDATE` 命令会将旧数据存储在 DELETED 表中。
对于 `INSERT` 情况，工作很简单，其逻辑与删除记录完全相同，只是数据存在于 INSERTED 表中。
但是，对于 `UPDATE` 情况，事情就变得棘手了。INSERTED 中的每个记录在 DELETED 表中都有一个包含旧值的对应项，并且需要将它们匹配起来，以便审计显示每条记录的旧值和新值。最简单的方法是为主键添加到两个 `DataTable` 中，然后从 INSERTED 表添加一个指向 DELETED 表的 `ChildRelationship`。然后，当您循环遍历 INSERTED 行时，只需获取子行即可完成。唯一的问题是，再次是执行成本。遵循此方法会在上述测试中增加几分钟，因为创建主键对象、表关系等的后台工作所涉及的开销。用于处理这些关系的代码在项目中可用，供您好奇，但已被注释掉。
DataColumn[] insertedPrimaryKeyColumns = new DataColumn[primaryKeyTable.Rows.Count];
DataColumn[] deletedPrimaryKeyColumns = new DataColumn[primaryKeyTable.Rows.Count];
int colCtr = 0;
foreach (DataRow primaryKeyRow in primaryKeyTable.Rows)
{
insertedPrimaryKeyColumns[colCtr] = inserted.Columns[primaryKeyRow[0].ToString()];
deletedPrimaryKeyColumns[colCtr] = deleted.Columns[primaryKeyRow[0].ToString()];
}
inserted.PrimaryKey = insertedPrimaryKeyColumns;
deleted.PrimaryKey = deletedPrimaryKeyColumns;
inserted.ChildRelations.Add("FK_HeaderDetailRelation",
insertedPrimaryKeyColumns, deletedPrimaryKeyColumns);
DataRow[] childDeletedRows = insertedRow.GetChildRows("FK_HeaderDetailRelation");
if (childDeletedRows.Length == 0)
{
auditHeaderRow["Note"] = "Unable to log field level changes " +
"because the primary key of the record may have changed.";
}
else if (childDeletedRows.Length > 1)
{
auditHeaderRow["Note"] = "Unable to log field level changes " +
"because a distinct match between INSERTED and DELETED could not be made.";
}
else
{
deletedRow = childDeletedRows[0];
}
相反，匹配项是通过一种稍微复杂（但更快）的方法找到的。我们创建一个索引列表，该列表跟踪 DELETED 表中的所有记录位置。然后，我们循环遍历主键表中的字段，并将索引列表中的每个已删除行与每个已插入行进行比较，从索引列表中删除任何不匹配的已删除行。
List<int> matchingDeletedRows = new List<int>();
for (int ctr = 0; ctr < deleted.Rows.Count; ctr++)
{
matchingDeletedRows.Add(ctr);
}
foreach (DataRow primaryKeyRow in primaryKeyTable.Rows)
{
for (int ctr = 0; ctr < matchingDeletedRows.Count; ctr++)
{
int deleteRowIndex = matchingDeletedRows[ctr];
string deletedPrimaryKeyValue =
deleted.Rows[deleteRowIndex][primaryKeyRow[0].ToString()].ToString();
string insertedPrimaryKeyValue =
insertedRow[primaryKeyRow[0].ToString()].ToString();
if (deletedPrimaryKeyValue != insertedPrimaryKeyValue)
{
// current primary key field value does not match,
// so the index for this deleted record can be removed.
matchingDeletedRows.RemoveAt(ctr);
ctr--;
}
}
}
当所有主键字段都已比较完毕后，我们应该会遇到以下三种情况之一：
- 如果索引列表为空，则未找到匹配的 DELETED 记录。这可能是因为 `UPDATE` 语句更改了主键字段之一，因此我们找不到匹配项。向 `AuditHeader.Note` 添加一条消息，并跳过记录字段更改。
- 如果索引包含多个值，则不知何故找到了多个匹配项，因此我们再次无法记录字段更改。我不确定这种情况可能在什么场景下出现，但您永远不知道。我们在此处向 `AuditHeader.Note` 添加一条消息，然后继续。
- `matchingDeletedRows` 中留下了一个条目，这意味着我们找到了匹配项。使用此索引从 DELETED 表中获取正确的记录，并记录它与当前 INSERTED 记录之间的差异。
其他一些注意事项...
如果表没有主键信息，则无法进行 INSERTED 和 DELETED 之间的比较，因此会在 AuditHeader 记录上留下相应的注释，并跳过 AuditDetail 部分。
另外，如果 INSERTED 和 DELETED 都包含一条记录，我们就不费力去匹配记录，而是假定它们是相关的，并像这样进行记录。我想不出它们不相关的场景，但如果有人能想到，请告诉我。
未来改进
目前，我有两项改进希望进行研究：
- 寻找一种更快速、更有效的方法来获取受影响的表名。
- 将事务 ID 存储在头级别，以便可以一起审查所有相关更改。这有一些安全设置需要审查，因为默认情况下，非管理员用户似乎无法访问包含事务信息的系统表。
结论
正如开头提到的，这种方法有一些非常大的优点，但也存在一些缺点。如果您想在小型/中型数据库中实现审计，通常一次不会影响大量记录，那么此方法就可以很好地工作。但是，如果您确实如此，那么您可以实施它，但我强烈建议将获取受影响表名的代码替换为单个的、针对特定表的触发器，这些触发器已经在其中硬编码了表名。这不优雅，但直到微软修复 `SqlTriggerContext.EventData` 对 DML 语句为 null 的问题，我暂时看不到其他解决方法。
如果有人对改进此代码有任何建议，请随时告知我。如果您能想到其他获取受影响表名的方法，我将非常感兴趣。
一些好的链接和参考资料
- 让我开始这个话题的文章，作者是 m_shane_tx - http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/60ed909b-51ac-425c-8e00-4d7978f39f70。
- Jon Galloway 完全用 SQL 实现的一个类似的想法 - http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx。
- MSDN 关于部署 CLR 触发器的文章 - http://msdn.microsoft.com/en-us/library/ms345099.aspx。
- MSDN 关于 `SqlTriggerContext.EventData` 的文章 - http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(MICROSOFT.SQLSERVER.SERVER.SQLTRIGGERCONTEXT.EVENTDATA);k(TargetFrameworkMoniker-%22.NETFRAMEWORK%2cVERSION%3dV3.5%22);k(DevLang-CSHARP)&rd=true。