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 文章
频道条目
分享
文章归档