Как поддерживать информацию аудита для DML в SQL?
Предварительные требования: контрольный журнал
В современном мире данные играют жизненно важную роль для любой организации. Не только для организации, но и для клиентов. В любом приложении становится жизненно важным отслеживать изменения, происходящие в ваших данных, чтобы вести контрольный журнал.
Существует множество причин для реализации механизма аудита вашего приложения или данных. Давайте посмотрим некоторые из них.
- Вашему приложению может потребоваться контрольный журнал, чтобы отслеживать, какие изменения были сделаны кем.
- Журнал аудита может помочь вам вернуться к любому состоянию в любой момент времени в случае случайных изменений или ошибок приложения.
- Аудиторский след может помочь вам выявить шаблоны, задействованные или выполняемые пользователями, которые помогут улучшить ваше приложение или будущие возможности разработки.
В этой статье мы собираемся понять и внедрить различные механизмы, доступные для реализации отслеживания аудита в данных вашего приложения на уровне 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()
)
ENDUPDATE [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.
Выход:
Теперь мы можем легко определить, какие значения были изменены для ваших записей. Хорошая часть здесь по сравнению с триггерным решением заключается в том, что вы получите хорошо структурированный способ аудита. Это именно то, как ваша схема таблицы, легко писать запросы для извлечения исторических данных и их представления.