在 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().
相关文章