在SQL Server中创建数据审计

Question

我最近接到一个任务，要在一个数据库表上创建一个审计，以便跟踪对任何列所做的任何更改。

假设我有以下表格:

[TableA]
------
ID
ColumnA
ColumnB
ColumnC

对于审计，我创建了如下表:

[TableA.Audit]
------
ID
TableAID
UserID
Date (default value = getdate())
ColumnA
ColumnB
ColumnC

我写了这样一个脚本:

DECLARE @currentColumnA int
       ,@currentColumnB int
       ,@currentColumnC int
SELECT TOP 1 @currentColumnA=ColumnA
            ,@currentColumnB=ColumnB
            ,@currentColumnC=ColumnC
FROM [TableA]
WHERE ID=@TableAID
UPDATE [TableA]
SET ColumnA=@ColumnA
    ,ColumnB=@ColumnB
    ,ColumnC=@ColumnC
WHERE ID=@TableAID
INSERT INTO [TableA.Audit] (TableAID, UserID, ColumnA, ColumnB, ColumnC)
VALUES (@TableAID, @UserID, NULLIF(@ColumnA, @currentColumnA), NULLIF(@ColumnB, @currentColumnB), NULLIF(@ColumnC, @currentColumnC))

这个问题是，如果我要添加一个ColumnD字段到TableA，我将不得不编辑我的TableA.Audit表以及上面的脚本。

因此，是否有更好的方法来做到这一点?

Answer 1

您最好为表编写AFTER INSERT、AFTER DELETE和AFTER UPDATE的触发器。这样，任何时候(应用程序、Management Studio等)插入、更新或删除表中的数据都将被记录下来。您必须为审计操作添加一个字段，并在触发器中插入操作的文字(例如:'I'或'INSERT')。我是这样构造审计表的:

audit_id: INT IDENTITY 
audit_date: DATETIME GETDATE() 
audit_action: VARCHAR(16) ... or you can use CHAR(1) 
audit_user: VARCHAR(128) SUSER_SNAME()
(the fields from the table being audited)

由于我们的应用程序使用活动目录，我可以默认audit_user为SUSER_SNAME()。

Answer 2

我们使用触发器(唯一的方法，并确保您编写它们来处理多个记录的插入/更新/删除)，我们的结构有点不同。首先，我们有一个表，其中存储有关操作的信息、执行操作的人员/应用程序、日期和受影响记录的数量。然后我们有一个存储详细信息的表。这个表有一个标识列，column_name，旧值，新值。(我们对审计表中的列使用nvarchar (max))这样，如果表中有新列，我们就不必担心更改审计表。对于要审计的每个表，我们都有一组审计表。

新版本的Sql server有更改跟踪，但是我们发现它没有足够的细节来审计我们需要的，它删除数据太快，除非你把它移动到另一个永久表

Answer 3

这个问题是，如果我要添加一个columnnd字段到我要编辑TableA。审计表以及上面的脚本。

因此，是否有更好的方法来做到这一点?

没有。正如HardCode提到的那样，你可以通过触发器使实现更好，但你仍然需要修改审计和相关脚本。

我目睹过一些尝试，试图使它"更好"，你不必更新触发器或审计表。这总是导致把小问题(嘿，添加了一个专栏，我必须做一些事情)换成更大的问题。通常是性能、正确性和可靠性问题。