使用 SQL Server 2005 的 XML 列进行审计跟踪

• 下载脚本文件 - 2.67 KB

引言

目前，我正在开发一个基于 ASP.NET & SQL Server 2005 的应用程序。此应用程序需要审计追踪机制来跟踪用户对数据库表的更改。我开发了一种基于 SQL Server 2005 XML 数据类型的技术。此技术只需要一个表，并且不需要任何触发器，因为触发器会增加开销。在本文中，我将讨论如何在我的应用程序中实现审计追踪。

使用带有 XML 列的单个表的审计追踪

我使用一个名为 tbl_audittrail 的表来存储用户对数据库表的更改。我创建了一个名为 tbl_student 的表作为示例。每次在 tbl_student 中插入或更新记录时，都会在 tbl_audittrail 表中插入相应的记录。两个表的结构如下：

tbl_student 表的主键字段，即 pk_intStudentIddfs 是 IDENTITY 列。表 tbl_audittrail 由以下字段组成：

• pk_intAuditTrailIddfs: 主键 & 标识列
• strTableNamedfs: 用户更改数据的表的名称
• strPrimaryKeyfielddfs: 已更改表的主键字段的名称。例如，这里是 pk_intStudentIddfs
• intPrimaryKeyValuedfs: 插入或更新的记录的主键字段的值
• strActionModedfs: 用户对表执行的操作，即 INSERT & UPDATE
• xmlOldValuedfs: 更新之前，已更改表的所有列（标识列除外）的值，格式为 XML。对于 INSERT 操作，此字段将为 null
• xmlNewValuedfs: INSERT 或 UPDATE 之后，已更改表的所有列（标识列除外）的值，格式为 XML
• strUserIddfs: 更改表（即 tbl_student）的用户的用户 ID
• dtDatedfs: 更改的日期和时间

我为 tbl_student 的所有非标识列添加了描述性文本，以便在用户日志报告中显示，以方便用户阅读。

• strStudentNamedfs: “学生姓名”
• strStudentRolldfs: “学生学号”
• strStudentClassdfs: “学生班级”

我为此示例创建了四个存储过程

• stp_InsertUpdateStudent: 此过程用于插入或更新 tbl_student 表。 此过程接受 5 个参数
  1. @intStudentId (int): 接受将要更新的 tbl_student 表的记录的标识列值。 对于 INSERT，它采用 0 作为值。
  2. @strStudentName (varchar): 接受作为学生姓名的值。
  3. @strStudentRoll (varchar): 接受作为学生学号的值。
  4. @strStudentClass (varchar): 接受作为学生班级的值。
  5. @strUserId (varchar): 接受更改表的用户 ID。
• stp_GetXMLValue: 此过程用于从表生成 XML 数据。

  此过程接受 4 个参数（3 个输入和 1 个输出）

  1. @strTableName (varchar): 用户将要更改数据的表的名称。
  2. @strPrimaryKeyField (varchar): 已更改表的主键字段的名称。 例如，这里是 pk_intStudentIddfs。
  3. @intPrimaryKeyValue (int): 插入或更新的记录的主键字段的值。
  4. @XMLValue (XML OUTPUT): 此变量存储生成的 XML。
• stp_InsertAuditTrail: 此过程用于在 tbl_audittrail 表中插入记录。 此过程接受 8 个参数
  1. @strTableName (varchar): 用户将要更改数据的表的名称。
  2. @strPrimaryKeyField (varchar): 已更改表的主键字段的名称。 例如，这里是 pk_intStudentIddfs。
  3. @intPrimaryKeyValue (int): 插入或更新的记录的主键字段的值。
  4. @strActionName (varchar): 接受 'INSERT' 或 'UPDATE'。
  5. @strUserId (varchar): 接受更改表的用户 ID。
  6. @dtDate (datetime): 接受更改的日期和时间。
  7. @xmlNewValue (xml): 接受 insert 或 update 之后，已更改表（tbl_student）的所有列（标识列除外）的 XML 格式的值。
  8. @xmlOldValue (xml nullable) (可选): 接受更新之前，已更改表（tbl_student）的所有列（标识列除外）的 XML 格式的值。 对于 INSERT，此值将为 null。
• stp_GetUserLog: 此过程生成用户日志报告。 此过程接受一个参数
  1. @intAuditTrailId (int): 接受 tbl_audittrail 表的标识列值。

我在 stp_InsertUpdateStudent 存储过程中调用了 stp_GetXMLValue & stp_InsertAuditTrail 过程。代码如下：

IF @intStudentId=0
BEGIN
	INSERT INTO dbo.tbl_student
			( strStudentNamedfs ,
			  strStudentRolldfs ,
			  strStudentClassdfs
			)
	VALUES  ( @strStudentName,
			  @strStudentRoll,
			  @strStudentClass
			)

	SELECT @IdentityValue=@@IDENTITY

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@IdentityValue,@XMLNewValue OUTPUT

	EXEC dbo.stp_InsertAuditTrail 'tbl_student','pk_intStudentIddfs',_
	@IdentityValue,'NEW',@strUserId,@Date,@XMLNewValue

END
ELSE
BEGIN

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,@XMLOldValue OUTPUT

	UPDATE dbo.tbl_student SET
	strStudentNamedfs=@strStudentName,
	strStudentRolldfs=@strStudentRoll,
	strStudentClassdfs=@strStudentClass
	WHERE pk_intStudentIddfs=@intStudentId

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,@XMLNewValue OUTPUT

	EXEC dbo.stp_InsertAuditTrail 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,'EDIT',_
			@strUserId,@Date,@XMLNewValue,@XMLOldValue
END

此技术假定每个表在序号位置 1 处都具有一个标识列。

要在其他地方使用此技术，只需运行脚本，而不包括 tbl_student table & stp_InsertUpdateStudent 存储过程。