Ведение журнала аудита SQL Server с использованием триггеров

Вступление

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

Таблицы базы данных

Во-первых, давайте предположим, что нам нужно реализовать библиотечное приложение, имеющее таблицу Book , информация журнала аудита которой хранится в таблице BookAuditLog , как показано на следующей диаграмме классов:

В таблице Книга хранятся все книги, которые у нас есть в нашей библиотеке, а в таблице BookAuditLog хранятся CDC (Запись данных об изменении) события, которые были созданы для данной Книги записи при выполнении инструкции INSERT, UPDATE или DELETE DML.

Таблица Журнал аудита книги создается следующим образом:

CREATE TABLE BookAuditLog (
    BookId bigint NOT NULL, 
    OldRowData nvarchar(1000) CHECK(ISJSON(OldRowData) = 1),
    NewRowData nvarchar(1000) CHECK(ISJSON(NewRowData) = 1),
    DmlType varchar(10) NOT NULL CHECK (DmlType IN ('INSERT', 'UPDATE', 'DELETE')),
    DmlTimestamp datetime NOT NULL,
    DmlCreatedBy varchar(255) NOT NULL,
    TrxTimestamp datetime NOT NULL,
    PRIMARY KEY (BookId, DmlType, DmlTimestamp)
)

В столбцах Журнал аудита книги хранится следующая информация:

В столбце BookID хранится идентификатор Книги строка, для которой была создана эта запись CDC.
В Oldrawdata – это столбец JSON, в котором хранится состояние записи Book до выполнения инструкции INSERT, UPDATE или DELETE.
Новые данные строки – это столбец JSON, в котором хранится состояние записи Book после выполнения инструкции INSERT, UPDATE или DELETE.
DmlType – это столбец перечисления, в котором хранится тип инструкции DML, который создал, обновил или удалил данную Книгу запись.
Метка времени Dml хранит метку времени выполнения инструкции DML.
Dml, созданный , хранит пользователя приложения, который выдал инструкцию INSERT, UPDATE или DELETE DML.
Метка времени Trx хранит метку времени транзакции.

Журнал аудита книги содержит составной первичный ключ, составленный из Букид , Тип DML , и Метка времени Dmi столбцы, как Книга строка может иметь несколько связанных Журнал аудита книги записей.

Триггеры ведения журнала аудита SQL Server

Чтобы записать инструкции INSERT, UPDATE и DELETE DML, нам нужно создать три триггера базы данных, которые будут вставлять записи в таблицу BookAuditLog .

SQL Server ПОСЛЕ ВСТАВКИ триггера ведения журнала аудита

Чтобы перехватить инструкции INSERT в таблице Book , мы создадим триггер TR_Book_Insert_AuditLog :

CREATE TRIGGER TR_Book_Insert_AuditLog ON Book
FOR INSERT AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        null,
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'INSERT',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

В Триггер TR_Book_Insert_AuditLog выполняется каждый раз, когда запись вставляется в таблицу Книга .

Виртуальная таблица Inserted ссылается на только что вставленные значения записей, поэтому мы можем использовать ее для извлечения значений столбцов Book .

Установлен только столбец New rowData , так как это новая запись, которая была вставлена, поэтому у нее нет предыдущего состояния, которое нужно сохранить в столбце OldRowData .

Функция ДЛЯ ПУТИ JSON SQL Server позволяет нам создавать объект JSON из набора результатов запроса.

В столбце DmlType установлено значение ВСТАВКА , а также временная метка Dmi значение равно CURRENT_TIMESTAMP .

В столбце Метка времени Dmi задано значение переменной сеанса @loggedUser SQL Server, заданной приложением с текущим зарегистрированным пользователем:

Session session = entityManager.unwrap(Session.class);

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices().getDialect();
    
String loggedUser = ReflectionUtils.invokeMethod(
    dialect,
    "escapeLiteral",
    LoggedUser.get()
);

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "EXEC sys.sp_set_session_context @key = N'loggedUser', @value = N'%s'", 
            loggedUser
        )
    );
});

SQL Server Триггер ведения журнала аудита ПОСЛЕ ОБНОВЛЕНИЯ

Чтобы зафиксировать инструкции ОБНОВЛЕНИЯ в записях Book , мы создадим следующий TR_Book_Update_Audit Журнал триггер:

CREATE TRIGGER TR_Book_Update_AuditLog ON Book
FOR UPDATE AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

Каждый раз, когда Книга запись обновляется, выполняется триггер TR_Book_Update_Audit Log , и будет создана строка Журнала аудита книги для записи как старого, так и нового состояния изменяющегося Книга запись.

SQL Server ПОСЛЕ УДАЛЕНИЯ триггера ведения журнала аудита

Чтобы перехватить операторы УДАЛЕНИЯ в строках Книги таблицы, мы создадим следующий TR_Book_Delete_Audit Журнал триггер:

CREATE TRIGGER TR_Book_Delete_AuditLog ON Book
FOR DELETE AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Deleted),
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

Как вы можете видеть, задается только Старые данные строки столбца, поскольку состояние новой записи отсутствует.

Время тестирования

При выполнении инструкции INSERT в таблице Книга :

INSERT INTO Book (
    Author, 
    PriceInCents, 
    Publisher, 
    Title, 
    Id
)
VALUES (
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition', 
    1
)

Мы видим, что в журнал аудита Книги вставлена запись , которая фиксирует инструкцию INSERT, только что выполненную в таблице Книга :

| BookId | OldRowData | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |            | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |

При обновлении строки Книга таблица:

UPDATE Book 
SET PriceInCents = 4499 
WHERE Id = 1

Мы видим, что новая запись будет добавлена в журнал аудита Книги триггером ПОСЛЕ ОБНОВЛЕНИЯ в таблице Книга :

| BookId | OldRowData                                                                                                                         | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |                                                                                                                                    | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE  | 2020-11-08 08:43:22.803 | Vlad Mihalcea | 2020-11-08 06:43:22.807 |

При удалении строки Книга таблица:

DELETE FROM Book 
WHERE Id = 1

Новая запись добавляется в журнал аудита Книги триггером ПОСЛЕ УДАЛЕНИЯ в таблице Книга :

| BookId | OldRowData                                                                                                                         | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |                                                                                                                                    | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE  | 2020-11-08 08:43:22.803 | Vlad Mihalcea | 2020-11-08 06:43:22.807 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} |                                                                                                                                    | DELETE  | 2020-11-08 08:44:25.630 | Vlad Mihalcea | 2020-11-08 06:44:25.633 |

Если вы хотите преобразовать объект журнала аудита на основе JSON в начальную запись реляционной таблицы, вы можете использовать функцию SQL Server OPENJSON , как описано в этой статье .

Потрясающе, правда?

Вывод

Использование триггеров базы данных SQL Server и столбцов JSON для записи старого и нового состояния записей проверяемой таблицы является очень простым способом реализации механизма ведения журнала аудита.

Однако подход, основанный на триггерах базы данных, не является единственной стратегией. Если вы используете Hibernate, очень простое решение-использовать энверы Hibernate .

Другой вариант-использовать Debezium и извлекать события CDC из журнала транзакций SQL Server. Это решение также может работать асинхронно, что не увеличивает время отклика на транзакции нашего приложения.