Navicat 部落格

使用觸發器記錄稽核線索 2023 年 4 月 28 日,由 Robert Gravelle 撰寫

資料庫稽核的概念是想知道何時何人存取了你的資料庫資料表,以及對它們進行了哪些修改。它不僅被認為是任何企業級應用程式的標準最低要求,而且還是銀行和網路安全等許多領域的法律要求。資料庫稽核線索對於調查各種應用程式問題而言是不可或缺的,例如未經授權的存取、有問題的配置變更等等。

在今天的文章中,我們將在 MySQL 的Sakila 範例資料庫 加入記錄以稽核 rental 資料表。這是一個重要的資料表,因為該資料庫代表了 DVD 出租店的業務流程。

建立一個儲存稽核線索資料的資料表

理想情況下,每個被稽核的資料表最好有自己的稽核資料表。以下的 DDL 陳述式是為 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) );

或者,在 Navicat 中,你可以使用「資料表設計器」指定所有欄位和屬性,而無需編寫 DDL 陳述式:

rental_audit_trail_table_design (53K)

建立稽核記錄觸發器

我們需要建立 3 個資料庫觸發器來在 rental_audit_log 資料表中插入記錄,每個觸發器對應在 rental 資料表上執行的各類 DML 陳述式(INSERT、UPDATE 和 DELETE)。

AFTER INSERT 觸發器

rental_insert_audit_trigger 將攔截在 rental 資料表上執行的 INSERT 陳述式。它會在 INSERT 作業後觸發,並將所有新資料作為 JSON_OBJECT 儲存。在 Navicat 中,所有詳細資料都在「資料表設計器」的「觸發器」索引標籤中:

AFTER_INSERT_Trigger (62K)

在 rental 資料表中加入新列後,我們也可以在 rental_audit_log 中看到一筆新記錄:

rental_audit_log_entry (50K)

AFTER UPDATE 觸發器

在 rental 資料表上執行的 UPDATE 陳述式將由以下的 rental_update_audit_trigger 擷取:

AFTER_UPDATE_Trigger (84K)

現在,每次更新出租記錄時,都會執行 rental_update_audit_trigger,並建立一個 rental_audit_log 行來擷取修改記錄的舊狀態和新狀態。在本例中,我們可以看到使用者 robg 將 rental_date 從「2005-05-25 17:17:04」變更為「2005-05-31 19:47:04」:

rental_audit_log_update_entry (52K)

AFTER DELETE 觸發器

為了追蹤在 rental 資料表上執行的 DELETE 陳述式,我們將建立以下的 rental_delete_audit_trigger:

AFTER_DELETE_Trigger (69K)

在本例中,由於沒有新的記錄狀態,所以只設定了 old_values 欄。因此,產生的 rental_audit_log列中的 new_values 欄是空值:

rental_audit_log_delete_entry (46K)

在這裡,我們可以看到使用者 fsmith在 2023-03-22 08:46:07 刪除了 rental 資料表中的記錄 1114。

關於使用觸發器記錄稽核線索的結語

在今天的文章中,我們在 MySQL 的 Sakila 範例資料庫加入了記錄以稽核 rental 資料表。我們的記錄資料表包含了幾個最常見的稽核欄位。某部分組織會有其他欄位,例如 DML 作業類型,而其他組織則可能只有已變更的欄位。這要按照最適合該組織的方式選擇稽核欄位。

Navicat 文章
頻道記錄
分享
部落格封存檔