65.9K
CodeProject 正在变化。 阅读更多。
Home

DML 在 SQL 中维护审计信息的不同方法

Avinash A. Dhumal
starIconstarIconstarIconstarIconstarIcon

5.00/5 (2投票s)

2022 年 8 月 8 日

CPOL

5分钟阅读

viewsIcon

4537

为 DML 对象实现审计机制。

引言

在当今世界，数据对任何组织都至关重要。不仅仅对组织，也对客户。在任何应用程序中，跟踪数据更改以保留审计跟踪都变得至关重要。

有许多原因需要为您的应用程序或数据实现审计机制。让我们看看其中的一些。

  1. 您的应用程序可能需要审计跟踪来维护谁对数据进行了什么更改。
  2. 万一发生意外更改或应用程序错误，审计跟踪可以帮助您在任何给定时间点恢复到任何状态。
  3. 审计跟踪可以帮助您找出用户所涉及或执行的模式，这将有助于增强您的应用程序或未来的开发机会。

在今天的话题中，我们将了解并实现 Microsoft SQL Server 级别应用程序数据中实现审计跟踪的不同机制。

我们将讨论如何为 DML 对象实现审计机制。

DML 审计（表审计）

让我们深入探讨 SQL Server 中实现数据审计跟踪的各种解决方案。以下解决方案已在 Microsoft SQL Server 2016 数据库中实现。

解决方案 #1（触发器）

在解决方案 1 中，我们将讨论大多数组织仍在使用的传统方法，或者可能是使用此机制实现的旧遗留应用程序。

为了跟踪 SQL 数据库表中的数据更改，可以使用触发器来跟踪先前和新的更改。

首先，我们使用 SSMS 在 SQL Server 中创建以下表。

CREATE TABLE [dbo].[Employee]
(
    [Employee_Id] [int] NOT NULL,
    [Employee_Name] [nvarchar](100) NOT NULL,
    [Employee_Designation] [nvarchar](50) NOT NULL,
    [Employee_Salary] [float] NOT NULL
)

现在，让我们向 Employee 表中添加一些记录。

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (1, 'David Schofield', 'Technical Manager', 4000000)

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (2, 'John Smith', 'Director', 10000000)

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (3, 'Anna Boston', 'Engineer', 200000)

您的表将如下所示

为了跟踪对表的更改，我们将实现以下触发器，它将在 Employee 表上执行 Insert、Delete 操作时帮助我们跟踪先前值或新值。

为了保留审计记录或历史记录，我们将创建一个名为“AuditLog”的新表，每当发生任何 update 或 delete 操作时，该表将由触发器填充。

CREATE TABLE [dbo].[AuditLog]
(
	[AuditLog_Id] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[Action] [nvarchar](50) NOT NULL,
	[PreviousRecord] [xml] NULL,
	[NewRecord] [xml] NULL,
	[ModifiedOn] [datetime] NOT NULL
)

现在，让我们在 Employee 表上编写一个触发器。

ALTER TRIGGER [dbo].[trg_AuditTable] 
   ON  [dbo].[Employee] 
   AFTER DELETE, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @PreviousRecord AS XML
	DECLARE @NewRecord AS XML
	
	DECLARE @Action VARCHAR(10)
	IF EXISTS(SELECT * FROM deleted)
		SELECT @Action = 'DELETE'
	IF EXISTS(SELECT * FROM inserted)
	  IF EXISTS(SELECT * FROM deleted)
		SELECT @Action = 'UPDATE'

	SET @PreviousRecord = (SELECT * FROM Deleted FOR XML PATH('Employee'), _
	TYPE, ROOT('Record')) -- READ THE PREVIOUS / CURRENT STATE OF THE RECORD
	SET @NewRecord = (SELECT * FROM Inserted FOR XML PATH('Employee'), _
	TYPE, ROOT('Record')) -- -- READ THE NEW STATE OF THE RECORD

	INSERT INTO [dbo].[AuditLog]
    (
		 [Action]
		,[PreviousRecord]
		,[NewRecord]
		,[ModifiedOn]
	)
     VALUES
    (
		 @Action
		,@PreviousRecord
		,@NewRecord
		,GETDATE()
	)
END

仅此而已！您的 Employee 表已启用审计机制。让我们尝试在 Employee 表上执行一些 UPDATE 和 DELETE 操作，看看它如何记录到 AuditLog 表中。

UPDATE [dbo].[Employee] 
SET 
     [Employee_Designation] = 'Engineering Manager'
    ,[Employee_Salary] = '50000'
WHERE [Employee_Id] = 3

执行上述 UPDATE 命令，然后检查 AuditLog 表以查找正在维护的审计或历史记录。

现在，我们可以看到触发器工作正常。它将操作记录为 UPDATE，并将之前的记录和新记录值作为 XML 插入表中。让我们尝试打开这些 XML 来了解发生了什么变化。

就是这样，我们的触发器工作正常，并跟踪我们 Employee 表中记录的所有更改。

解决方案 #2（系统版本时间表）

微软的说法是——时间表（也称为系统版本时间表）是一项数据库功能，它提供了内置支持，可以随时提供表中存储的数据信息，而不仅仅是当前时刻正确的数据。

我认为系统版本时间表是实现触发器方法的现代版本。

让我们来看看。在解决方案 1 中，我们看到每条更新或删除的记录都会跟踪每一列的更改，并以 XML 格式将其添加到 AuditLog 表中。没有编写特定的逻辑来识别哪个列发生了更改，而是借助特殊的魔术表在表中创建一个包含之前和之后记录的条目。

使用系统版本时间表，其工作方式完全相同，但结构更完善，方式更简单。Microsoft SQL Server 会自动负责维护历史记录。让我们通过实际操作来进一步了解。

这次，我们将创建一个名为 STUDENT 的新表。

CREATE TABLE [dbo].[Student]
(
	[Student_Id] [int] NOT NULL PRIMARY KEY IDENTITY (1, 1),
	[Student_Name] [nvarchar](100) NOT NULL,
	[Student_Address] [nvarchar](50) NOT NULL,
	[Student_City] [nvarchar](50) NOT NULL,
	[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StudentAuditLog))

在数据库中创建表后，在 SSMS 的对象资源管理器中检查 Tables。

您会发现表名现在显示为系统版本。如果进一步展开，您会发现一个名为“StudentAuditLog”的附加表，它将成为您的历史记录表，将保存对 Student 表进行的所有更改。

让我们了解一下应用系统版本时间表的一些基本规则

  1. 创建表时，必须指定子句 SYSTEM_VERSIONING = ON。
  2. 系统版本时间表必须定义主键，并且必须恰好定义一个 PERIOD FOR SYSTEM_TIME，包含两个 datetime2 列，声明为 GENERATED ALWAYS AS ROW START / END。
  3. 指定 History 表名是可选的，但建议提供。
  4. 此功能仅从 SQL Server 2016 (13.x) 及更高版本可用。

时间表如何工作？

表的系统版本是通过一对表实现的：当前表和历史记录表。在这两个表中，另外两个 datetime2 列用于定义每行的有效期间。

  • 周期开始列：系统在此列中记录行的开始时间，通常表示为 ValidFrom 列。
  • 周期结束列：系统在此列中记录行的结束时间，通常表示为 ValidTo 列。

当前表包含每行的当前值。历史记录表包含每行的每个先前值（旧版本），如果有的话，以及该行有效期间的开始时间和结束时间。

现在，让我们向 Student 表中添加一些记录。

INSERT [dbo].[Student] ([Student_Name], [Student_Address], _
[Student_City]) VALUES ('John Smith', '45 Street Avenue', 'New York')
INSERT [dbo].[Student] ([Student_Name], [Student_Address], _
[Student_City]) VALUES ('Anna Boston', '511 Avenue', 'New York')

现在让我们尝试更新一些记录，看看如何使用系统版本时间表进行审计。执行以下查询来更新 Student 表中的值。

UPDATE [dbo].[Student]
SET
    [Student_Address] = 'Madison Road'
   ,[Student_City] = 'Washington DC'
WHERE [Student_Id] = 1

现在，如果我们分别查询当前表和历史记录表（即 Student 和 StudentAuditLog 表）的 Select 语句，我们将获得如下结果

太棒了，对吧！现在我们可以轻松识别记录的值发生了什么变化。与触发器解决方案相比，这里的好处是——您将获得一种更结构化的审计方式。这与您的表模式完全一致，易于编写查询来提取历史数据并进行表示。

历史

  • 2022 年 8 月 8 日：初次发布
© . All rights reserved.