在 SQL Server 中创建数据审计 [英] Create Data Audit in SQL Server

问题描述

我最近接到了对数据库表创建审计的任务，以便可以跟踪对任何列所做的任何更改.

I've recently been given the task of creating an Audit on a database table so that any changes made to any columns can be tracked.

假设我有下表:

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

为了审计，我创建了一个表格，例如:

For Auditing I've created a table such as:

[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))

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

The problem with this, is that if I was to add a ColumnD field to TableA I'm going to have to edit my TableA.Audit table as well as the above script.

因此有没有更好的方法来做到这一点?

Therefore is there a better way of doing this?

推荐答案

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

You are better off writing triggers for the table for AFTER INSERT, AFTER DELETE, and AFTER UPDATE. This way, any time ANYTHING (application, Management Studio, etc.) that inserts, updates, or deletes data in the table will get logged. You'll have to add a field for the audit action, and in your trigger insert the literal for the action (e.g. 'I' or 'INSERT'). I structure my audit tables in this way:

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)

由于我们的应用程序使用 Active Directory，我可以将 audit_user 默认为 SUSER_SNAME().

Since our apps use Active Directory, I can default audit_user to SUSER_SNAME().

这篇关于在 SQL Server 中创建数据审计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！