在SQL Server中创建数据审计

问题描述：

我最近被授予了在数据库表上创建审计的任务，以便可以跟踪对任何列进行的任何更改。在SQL Server中创建数据审计

可以说我有如下表：

[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 [email protected] 

UPDATE [TableA] 
SET [email protected] 
    ,[email protected] 
    ,[email protected] 
WHERE [email protected] 

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表以及上面的脚本。

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

根据你的情况，[这个答案]（http://*.com/questions/6998774/update-function-in-transact-sql-a-boolean-condition/7151476#7151476）可能有一些帮助。（这是@HLGEM正在讨论的简化版本。） –

答

您最好为AFTER INSERT，AFTER DELETE和AFTER UPDATE的表写入触发器。这样，任何时候插入，更新或删除表中的数据的ANYTHING（应用程序，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)

由于我们的应用程序使用Active Directory，我可以默认的audit_user到SUSER_SNAME（）。

我使用ASP.NET Forms Authentication/Membership。有没有在触发器中获取用户ID？目前我通过应用程序的@UserID参数 – Curt

嘿，你有什么建议，我可以如何使用表单身份验证/成员资格做到这一点？ – Curt

我不确定会员资格 - 我从未使用过它 - 但对于非Active Directory解决方案，您必须将用户标识作为常规旧参数传递给存储过程。 – HardCode

答

的问题与此是，如果我是一个ColumnD字段添加到表A，我将不得不修改我TableA.Audit表还有上面的脚本。

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

不是。您可以通过HardCode提及的触发器来使实现更好，但您仍然需要修改审计和相关的修改。

我已经目睹了试图让这个“更好”，你不必更新触发器或审计表。这总是导致交易的小问题（嘿一列增加了，我必须做一些东西）的更大的。通常表现，正确性和可靠性问题。

答

我们使用触发器（唯一的办法，并确保您编写它们来处理多个记录插入/更新/删除），我们的结构有点不同。首先，我们有一个表格，用于存储关于该行为的信息，该人员/应用程序是否记录了受影响记录的日期数量。然后我们有一个表格来存储细节。该表具有标识列，column_name，旧值，新值。（我们对审计表中的列使用nvarchar（max））这样，如果表获取新列，我们不必担心更改审计表。对于我们审计的每个表，我们都有一组审计表。

更新版本的Sql服务器有更改跟踪，但我们没有发现它有足够的细节用于我们需要的审计，并且它太快地删除数据，除非将它移动到另一个永久表。

在SQL Server中创建数据审计

相关推荐