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

Вступление

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

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

Давайте предположим, что у нас есть библиотечное приложение, содержащее следующие две таблицы:

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

Таблица book_audit_log создается следующим образом:

CREATE TABLE book_audit_log (
    book_id BIGINT NOT NULL, 
    old_row_data JSON,
    new_row_data JSON,
    dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    dml_timestamp TIMESTAMP NOT NULL,
    dml_created_by VARCHAR(255) NOT NULL,
    PRIMARY KEY (book_id, dml_type, dml_timestamp)
)

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

В столбце book_id хранится идентификатор строки book , которая была создана, обновлена или удалена.
old_row_data – это столбец JSON, который будет фиксировать состояние записи book перед выполнением инструкции INSERT, UPDATE или DELETE.
new_row_data – это столбец JSON, который будет фиксировать состояние записи book после выполнения инструкции INSERT, UPDATE или DELETE.
dml_type – это столбец перечисления, в котором хранится тип инструкции DML, который создал, обновил или удалил данную книгу запись.
В dml_timestamp хранится метка времени выполнения инструкции DML.
dml_created_by хранит пользователя приложения, который выдал инструкцию INSERT, UPDATE или DELETE DML.

Первичный ключ book_audit_log является составным из book_id , dmi_type и dmi_timestamp , поскольку строка book может иметь несколько связанных book_audit_log записей.

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

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

MySQL ПОСЛЕ триггера ВСТАВКИ

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

CREATE TRIGGER book_insert_audit_trigger
AFTER INSERT ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        null,
        JSON_OBJECT(
            "title", NEW.title,
            "author", NEW.author,
            "price_in_cents", NEW.price_in_cents,
            "publisher", NEW.publisher
        ),
        'INSERT',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

book_insert_audit_trigger_ выполняется после вставки записи в таблицу book .

Ключевое слово NEW ссылается на только что вставленные значения записей, поэтому мы можем использовать его для извлечения значений столбцов book .

Установлен только столбец new_row_data , так как в столбце old_row_data нет состояния старой записи, которое можно установить.

Функция JSON_OBJECT MySQL позволяет нам создать объект JSON, который принимает предоставленные пары ключ-значение.

В столбце dml_type задано значение INSERT , а в столбце dmi_timestamp задано значение CURRENT_TIMESTAMP .

В столбце dml_created_by задано значение переменной сеанса @logged_user MySQL, которое ранее было задано приложением для текущего зарегистрированного пользователя:

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

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices()
    .getDialect();

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "SET @logged_user = '%s'", 
            ReflectionUtils.invokeMethod(
                dialect,
                "escapeLiteral",
                LoggedUser.get()
            )
        )
    );
});

MySQL ПОСЛЕ запуска ОБНОВЛЕНИЯ

Чтобы зафиксировать операторы ОБНОВЛЕНИЯ в записях book , мы создадим следующий book_update_audit_trigger_ |:

CREATE TRIGGER book_update_audit_trigger
AFTER UPDATE ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        JSON_OBJECT(
            "title", OLD.title,
            "author", OLD.author,
            "price_in_cents", OLD.price_in_cents,
            "publisher", OLD.publisher
        ),
        JSON_OBJECT(
            "title", NEW.title,
            "author", NEW.author,
            "price_in_cents", NEW.price_in_cents,
            "publisher", NEW.publisher
        ),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

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

MySQL ПОСЛЕ триггера УДАЛЕНИЯ

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

CREATE TRIGGER book_delete_audit_trigger
AFTER DELETE ON book FOR EACH ROW 
BEGIN
    INSERT INTO book_audit_log (
        book_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        OLD.id,
        JSON_OBJECT(
            "title", OLD.title,
            "author", OLD.author,
            "price_in_cents", OLD.price_in_cents,
            "publisher", OLD.publisher
        ),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @logged_user
    );
END

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

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

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

INSERT INTO book (
    id,
    author, 
    price_in_cents, 
    publisher, 
    title
) 
VALUES (
    1,
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition'
)

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

| book_id | old_row_data | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |              | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |

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

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

Мы видим, что новая запись будет добавлена в book_audit_log триггером AFTER UPDATE в таблице book .:

| book_id | old_row_data                                                                                                                         | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |                                                                                                                                      | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-07-29 13:50:48 | Vlad Mihalcea  |

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

DELETE FROM book 
WHERE id = 1

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

| book_id | old_row_data                                                                                                                         | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |                                                                                                                                      | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-07-29 13:50:48 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                      | DELETE   | 2020-07-29 14:05:33 | Vlad Mihalcea  |

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

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

Вывод

Когда дело доходит до внедрения механизма ведения журнала аудита, существует множество вариантов на выбор. Если вы используете Hibernate, самое простое решение-использовать энверы Hibernate .

Если вы не используете режим гибернации или хотите фиксировать события CDC независимо от того, как генерируются операторы DML (например, обычный JDBC, с помощью консоли SQL), то решение для запуска базы данных очень легко реализовать. Использование столбцов JSON для хранения старого и нового состояния строки, которая создается, обновляется или удаляется, намного лучше, чем перечисление всех столбцов в таблице журнала аудита.

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