在现代数据库应用中，审计跟踪是维护数据完整性和安全性的关键部分。无论是为了满足合规性要求，还是为了监控和分析用户行为，审计跟踪都扮演着至关重要的角色。本文将探讨在MicrosoftSQL Server中实现数据审计跟踪的不同机制，包括使用触发器和系统版本化表的方法。
审计跟踪对于应用程序和数据来说都是至关重要的。它可以帮助跟踪数据的变化，以保持审计线索。以下是实施审计机制的一些原因：
在本文中，将了解和实现在Microsoft SQL Server级别上对应用程序数据实施审计跟踪的不同机制。将讨论如何为DML对象实现审计机制。
让深入了解在SQL Server中实现数据审计跟踪的不同解决方案。下面提到的解决方案是在Microsoft SQL Server 2016数据库中实现的。
在解决方案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 INTO [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], [Employee_Salary])
VALUES (1, 'David Schofield', 'Technical Manager', 4000000)
INSERT INTO [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], [Employee_Salary])
VALUES (2, 'John Smith', 'Director', 10000000)
INSERT INTO [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'))
SET @NewRecord = (
SELECT * FROM Inserted FOR XML PATH('Employee'), TYPE, ROOT('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表以找出维护的审计或历史记录。
微软表示 - 系统版本化表（也称为系统版本化表）是数据库功能，它提供了内置支持，用于提供表中存储的数据在任何时间点的信息，而不仅仅是当前时刻正确的数据。
会说系统版本化表是实现触发器的现代版本。让看看如何实现。
这次，将创建一个名为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))
现在让向Student表中添加一些记录。
INSERT INTO [dbo].[Student] ([Student_Name], [Student_Address], [Student_City])
VALUES ('John Smith', '45 Street Avenue', 'New York')
INSERT INTO [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