Ide di balik audit database adalah untuk mengetahui siapa yang mengakses tabel database Anda dan kapan, bersama dengan modifikasi apa yang dilakukan pada mereka. Ini tidak hanya dianggap sebagai persyaratan minimum standar untuk aplikasi tingkat perusahaan apa pun, tetapi juga merupakan persyaratan hukum untuk banyak domain seperti perbankan dan keamanan siber. Jejak Audit Database sangat penting dalam menyelidiki segala macam masalah aplikasi seperti akses yang tidak sah, perubahan konfigurasi yang bermasalah, dan banyak lagi.
Di blog hari ini, kita akan menambahkan logging ke MySQLSakila Sample Database untuk mengaudit tabel rental. Ini adalah tabel key karena database mewakili proses bisnis toko penyewaan DVD.
Membuat Tabel untuk Menyimpan Audit Trail Data
Idealnya, yang terbaik adalah memiliki tabel audit untuk setiap tabel yang diaudit. Berikut pernyataan DDL untuk membuat tabel jejak audit untuk tabel rental :
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) );
Atau, di Navicat, Anda dapat menggunakan Desainer Tabel untuk menentukan semua field dan atribut tanpa harus menulis pernyataan DDL:
Membuat Audit Logging Triggers
Kita harus membuat 3 Trigger database untuk menyisipkan catatan di tabel rental_audit_log, satu untuk setiap jenis pernyataan DML yang dilakukan pada tabel rental (INSERT, UPDATE, dan DELETE).
AFTER INSERT Trigger
Pernyataan INSERT di tabel rental akan dicegat oleh rental_insert_audit_trigger. Kami akan menjalankannya AFTER operasi Insert dan menyediakan semua data baru sebagai JSON_OBJECT. Di Navicat, semua detail tersebut dapat diberikan pada Trigger Tab Desainer Tabel:
Setelah menambahkan baris baru ke tabel rental , kita dapat melihat catatan baru di rental_audit_log juga:
AFTER UPDATE Trigger
Pernyataan UPDATE pada tabel rental akan ditangkap oleh rental_update_audit_trigger berikut:
Sekarang, setiap kali catatan rental diperbarui, rental_update_audit_trigger dijalankan, dan baris rental_audit_log akan dibuat untuk menangkap keadaan lama dan baru dari catatan yang dimodifikasi. Dalam hal ini, kita dapat melihat bahwa robg pengguna mengubah rental_date dari "2005-05-25 17:17:04" menjadi "2005-05-31 19:47:04":
AFTER DELETE Trigger
Untuk melacak pernyataan DELETE pada tabel rental, kami akan membuat rental_delete_audit_trigger berikut:
Dalam hal ini, hanya kolom old_values yang diatur karena tidak ada status rekaman baru. Oleh karena itu kolom new_values kosong di baris rental_audit_log yang dihasilkan:
Di sini, kita dapat melihat bahwa pengguna fsmith menghapus catatan 1114 dari tabel rental pada 2023-03-22 pukul 08:46:07.
Pikiran Akhir tentang Audit Trail Logging Using Triggers
Di blog hari ini, kami menambahkan logging ke MySQL Sakila Sample Database untuk mengaudit tabel rental . Tabel logging kami mencakup beberapa field audit yang paling umum. Beberapa organisasi menyertakan yang lain, seperti jenis operasi DML, sementara yang lain hanya menyertakan field yang diubah. Ini benar-benar apa pun yang terbaik untuk organisasi.
