The idea behind database auditing is to know who accessed your database tables and when, along with what modifications were made to them. It's not only considered to be the standard minimum requirement for any enterprise level application, but is also a legal requirement for many domains such as banking and cybersecurity. Database Audit Trails are essential in investigating all sorts of application issues such as unauthorized access, problematic configuration changes, and many more.
In today's blog, we're going to add logging to the MySQL Sakila Sample Database to audit the rental table. It's a key table because the database represents the business processes of a DVD rental store.
Creating a Table to Store Audit Trail Data
Ideally it's best to have an audit table for each table being audited. Here's the DDL statement to create the audit trail table for the rental table:
create table rental_audit_log( id int NOT NULL AUTO_INCREMENT, rental_id int NOT NULL, old_values varchar(255), new_values varchar(255), done_by varchar(255) NOT NULL, done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );
Alternatively, in Navicat, you can use the Table Designer to specify all of the fields and attributes without having to write a DDL statement:
Creating the Audit Logging Triggers
We'll need to create 3 database triggers to insert records in the rental_audit_log table, one for each type of DML statement performed on the rental table (INSERT, UPDATE, and DELETE).
AFTER INSERT Trigger
INSERT statements on the rental table will be intercepted by the rental_insert_audit_trigger. We'll get it to fire AFTER Insert operations and provide all of the new data as a JSON_OBJECT. In Navicat, all of those details may be supplied on the Triggers tab of the Table Designer:
After adding a new row to the rental table, we can see a new record in the rental_audit_log as well:
AFTER UPDATE Trigger
UPDATE statements on the rental table will be captured by the following rental_update_audit_trigger:
Now, every time a rental record is updated, the rental_update_audit_trigger is executed, and a rental_audit_log row will be created to capture both the old and the new state of the modified record. In this case, we can see that user robg changed the rental_date from "2005-05-25 17:17:04" to "2005-05-31 19:47:04":
AFTER DELETE Trigger
To track DELETE statements on the rental table, we will create the following rental_delete_audit_trigger:
In this case, only the old_values column is set since there is no new record state. Hence the empty new_values column in the generated rental_audit_log row:
Here, we can see that user fsmith deleted record 1114 from the rental table on 2023-03-22 at 08:46:07.
Final Thoughts on Audit Trail Logging Using Triggers
In today's blog, we added logging to the MySQL Sakila Sample Database to audit the rental table. Our logging table included some of the most common audit fields. Some organizations include others, such as the DML operation type, while others only include changed fields. It's really whatever works best for the organization.
