Как поддерживать информацию аудита для DML в SQL?

Опубликовано: 9 Января, 2023

Предварительные требования: контрольный журнал

В современном мире данные играют жизненно важную роль для любой организации. Не только для организации, но и для клиентов. В любом приложении становится жизненно важным отслеживать изменения, происходящие в ваших данных, чтобы вести контрольный журнал.

Существует множество причин для реализации механизма аудита вашего приложения или данных. Давайте посмотрим некоторые из них.

  • Вашему приложению может потребоваться контрольный журнал, чтобы отслеживать, какие изменения были сделаны кем.
  • Журнал аудита может помочь вам вернуться к любому состоянию в любой момент времени в случае случайных изменений или ошибок приложения.
  • Аудиторский след может помочь вам выявить шаблоны, задействованные или выполняемые пользователями, которые помогут улучшить ваше приложение или будущие возможности разработки.

В этой статье мы собираемся понять и внедрить различные механизмы, доступные для реализации отслеживания аудита в данных вашего приложения на уровне Microsoft SQL Server. Здесь мы увидим, как мы можем реализовать механизм аудита для объектов DML, то есть таблиц.

Аудит DML:

Давайте рассмотрим различные решения, доступные для реализации отслеживания аудита ваших данных на сервере SQL. Упомянутые ниже решения реализованы в Microsoft SQL Server.

Способ 1: использование триггеров

В первом методе мы видим традиционный подход, при котором большинство организаций все еще используют или, возможно, старые устаревшие приложения, реализованные с помощью этого механизма. Чтобы отслеживать изменения данных в таблицах базы данных SQL, можно использовать триггеры для отслеживания предыдущих и новых изменений.

Давайте сначала создадим следующую таблицу в нашем SQL Server, используя SSMS.

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)

Выход:

Чтобы отслеживать изменения, внесенные в вашу таблицу, мы будем реализовывать следующий триггер, который поможет нам отслеживать предыдущие или новые значения всякий раз, когда операции вставки и удаления выполняются в таблице сотрудников.

Чтобы вести записи аудита или исторические записи, мы собираемся создать новую таблицу под названием «AuditLog», которая будет заполняться триггерами всякий раз, когда происходят какие-либо операции обновления или удаления.

CREATE TABLE [dbo].[AuditLog]
(
   [AuditLog_Id] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Action] [nvarchar](50) NOT NULL,
   [PreviousRecord]  NULL,
   [NewRecord]  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
UPDATE [dbo].[Employee] 
SET 
   [Employee_Designation] = "Engineering Manager"
  ,[Employee_Salary] = "50000"
WHERE [Employee_Id] = 3

Выполните приведенную выше команду UPDATE и проверьте таблицу AuditLog, чтобы узнать, какой аудит или история ведутся.

Выход:

Теперь мы видим, что триггер работает отлично. Он записывает операцию как UPDATE и вставляет значения предыдущей и новой записи в таблицу в виде XML. Давайте попробуем открыть эти XML-файлы, чтобы понять, что изменилось.

Все, наш триггер работает отлично и отслеживает все изменения, происходящие с вашими записями в таблице Employee. Теперь попробуйте команду Удалить и посмотрите, как она сохраняется в таблице AuditLog.

Метод 2: временные таблицы с системной версией

Согласно Microsoft, временные таблицы с системным управлением версиями предоставляются базой данных, которая предоставляет встроенные функции для предоставления информации о ваших данных, хранящихся в таблице, в любой момент времени, а не просто для предоставления текущих данных в текущий момент времени. Я бы сказал, что темпоральные таблицы с системной версией являются современной версией устаревшего решения реализации через триггеры, если мне нужно упростить его.

Посмотрим, как. В нашем решении №1 мы увидели, что каждая обновленная или удаленная запись будет отслеживать каждое изменение столбца и добавлять его в таблицу AuditLog в формате XML. Никакой специальной логики, определяющей, какой столбец изменился, не написано, а просто делается запись в таблицу с предыдущими и новыми записями с помощью специальных волшебных таблиц.

С временными таблицами с системным управлением версиями это происходит точно так же, но более структурировано и просто. 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.

Вы обнаружите, что имя таблицы теперь отображается как System versioned. Если вы расширитесь дальше, вы найдете дополнительную таблицу под названием «StudentAuditLog», которая будет вашей исторической таблицей, в которой будут храниться все изменения, внесенные в таблицу «Студент».

Давайте разберемся с некоторыми эмпирическими правилами применения темпоральных таблиц с системным управлением версиями:

  • При создании таблицы необходимо указать условие SYSTEM_VERSIONING = ON.
  • Для темпоральной таблицы с системным управлением версиями должен быть определен первичный ключ и ровно один PERIOD FOR SYSTEM_TIME, определенный с двумя столбцами datetime2, объявленными как GENERATED ALWAYS AS ROW START/END.
  • Указание имени таблицы журнала является необязательным, но рекомендуется указать его.
  • Эта функция доступна только в SQL Server 2016 (13.x) и более поздних версиях.

Работа временной таблицы:

Системное управление версиями для таблицы реализовано как комбинация двух таблиц — (1) текущей таблицы и (2) исторической таблицы. В каждой из этих таблиц вы найдете 2 столбца с типом данных datetime2, которые используются для определения периода времени, в течение которого запись была действительна в таблице.

  • Столбец ValidFrom : SQL записывает время начала для строки в этом столбце.
  • Столбец ValidTo : SQL записывает время окончания строки в этом столбце.

Текущая таблица содержит текущее значение для каждой строки. Таблица истории содержит каждое предыдущее значение (старая версия) для каждой строки, если таковая имеется, а также время начала и время окончания для периода, в течение которого оно было действительным.

Теперь давайте добавим несколько записей в таблицу 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

Теперь, если мы запросим операторы Select как для текущей таблицы, так и для исторической таблицы, т.е. для соответствующих таблиц Student и StudentAuditLog.

Выход:

Теперь мы можем легко определить, какие значения были изменены для ваших записей. Хорошая часть здесь по сравнению с триггерным решением заключается в том, что вы получите хорошо структурированный способ аудита. Это именно то, как ваша схема таблицы, легко писать запросы для извлечения исторических данных и их представления.

SQL