MySQL audit logging using triggers

Introduction

In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types.

Database tables

Let’s assume we have a library application that has the following two tables:

The book table stores all the books that are found in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that happened to a given book record via an INSERT, UPDATE, or DELETE DML statement.

The book_audit_log table is created like this:

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)
)

The book_audit_log columns store the following info:

• The book_id column stores the identifier of the book row that has been either created, updated, or deleted.
• The old_row_data is a JSON column that will capture the state of the book record prior to executing an INSERT, UPDATE, or DELETE statement.
• The new_row_data is a JSON column that will capture the state of the book record after executing an INSERT, UPDATE, or DELETE statement.
• The dml_type is an enumeration column that stores the DML statement type that created, updated, or deleted a given book record.
• The dml_timestamp stores the DML statement execution timestamp.
• The dml_created_by stores the application user who issued the INSERT, UPDATE, or DELETE DML statement.

The Primary Key of the book_audit_log is a composite of the book_id , dml_type , and dml_timestamp since a book row can have multiple associated book_audit_log records.

MySQL audit logging triggers

To capture the INSERT, UPDATE, and DELETE DML statements, we need to create 3 database triggers that are going to insert records in the book_audit_log table.

MySQL AFTER INSERT trigger

To intercept the INSERT statements on the book table, we will create the 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

The book_insert_audit_trigger is executed after a record is inserted in the book table.

The NEW keyword references the record values that were just inserted, so we can use it to extract the book column values.

Only the new_row_data column is set since there is no old record state to set in the old_row_data column.

The JSON_OBJECT MySQL function allows us to create a JSON object that takes the provided key-value pairs.

The dml_type column is set to the value of INSERT , and the dml_timestamp value is set to the CURRENT_TIMESTAMP .

The dml_created_by column is set to the value of the @logged_user MySQL session variable, which was previously set by the application with the currently logged user:

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 AFTER UPDATE trigger

To capture the UPDATE statements on the book records, we will create the following 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

Every time a book record is updated, the book_update_audit_trigger is executed, and a book_audit_log row will be created to capture both the old and the new state of the modifying book record.

MySQL AFTER DELETE trigger

To intercept the DELETE statements on the book table rows, we will create the following 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

As you can see, only the old_row_data column is set since there is no new record state.

Testing time

When executing an INSERT statement on the book table:

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

We can see that a record is inserted in the book_audit_log that captures the INSERT statement that was just executed on the book table:

| 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  |

When updating the book table row:

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

We can see that a new record is going to be added to the book_audit_log by the AFTER UPDATE trigger on the book table:

| 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  |

When deleting the book table row:

DELETE FROM book 
WHERE id = 1

A new record is added to the book_audit_log by the AFTER DELETE trigger on the book table:

| 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  |

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

When it comes to implementing an audit logging mechanism, there are many options to choose from. If you are using Hibernate, the simplest solution is to useHibernate Envers.

If you are not using Hibernate or if you want to capture the CDC events no matter how the DML statements are generated (e.g., plain JDBC, via the SQL console), then a database trigger solution is very easy to implement. Using JSON columns to store the old and new state of the row that gets created, updated, or deleted is much better than listing all the columns in the audit log table.

Another option is to useDebezium and extract the CDC events from the Binary Log. This solution can work asynchronously, therefore having no impact on the OLTP transactions that trigger the CDC events.