审计与触发器-优快云博客

本文链接：https://blog.youkuaiyun.com/A37644548/article/details/147356259

在现代数据库系统中，审计(Audit)和触发器(Trigger)是两个强大的功能，它们如同数据库的隐形守护者，默默地记录着数据的变化并在特定条件下自动执行预定义的操作。本文将深入探讨这两个概念，展

一、数据库审计

1.1 什么是数据库审计？

数据库审计是指对数据库活动进行系统性的记录和审查，以便监控数据库的使用情况，检测可疑活动，并满足合规性要求。

1.2 审计的主要类型

语句审计：记录特定的SQL语句执行
权限审计：记录特定权限的使用情况
对象审计：记录对特定数据库对象的访问
精细审计：基于内容的审计，如只记录访问特定数据的操作

1.3 实现审计的SQL示例

示它们如何协同工作来增强数据库的安全性、完整性和可追溯性。

-- Oracle中启用审计
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE BY ACCESS;

-- MySQL中的审计示例(需要企业版或使用审计插件)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';

-- SQL Server中的审计
CREATE SERVER AUDIT MyAudit
TO FILE (FILEPATH = 'C:\Audits\')
WITH (QUEUE_DELAY = 1000);

CREATE DATABASE AUDIT SPECIFICATION MyDbAudit
FOR SERVER AUDIT MyAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);

二、数据库触发器：自动化的业务规则执行者

2.1 触发器的基本概念

触发器是一种特殊的存储过程，它会在特定数据库事件(如INSERT、UPDATE、DELETE)发生时自动执行。

2.2 触发器的类型

DML触发器：响应数据操作语言事件
- AFTER触发器(在操作后执行)
- INSTEAD OF触发器(替代原操作执行)
- BEFORE触发器(在操作前执行，某些数据库支持)
DDL触发器：响应数据定义语言事件
登录触发器：响应登录事件

2.3 创建触发器的SQL示例

-- 创建一个简单的AFTER INSERT触发器
CREATE TRIGGER trg_after_insert
ON Employees
AFTER INSERT
AS
BEGIN
    DECLARE @EmployeeID INT
    SELECT @EmployeeID = EmployeeID FROM inserted
    
    INSERT INTO AuditLog(TableName, Action, RecordID, ChangeDate)
    VALUES ('Employees', 'INSERT', @EmployeeID, GETDATE())
END;

三、审计与触发器的协同应用

3.1 使用触发器实现自定义审计

当数据库自带的审计功能不能满足需求时，可以使用触发器创建自定义审计解决方案：

CREATE TRIGGER trg_audit_employee_changes
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 记录插入操作
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO EmployeeAudit(
            EmployeeID, 
            Action, 
            ChangedBy, 
            ChangeDate,
            OldData,
            NewData
        )
        SELECT 
            i.EmployeeID,
            'INSERT',
            SYSTEM_USER,
            GETDATE(),
            NULL,
            (SELECT * FROM inserted FOR JSON PATH)
        FROM inserted i;
    END
    
    -- 记录更新操作
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO EmployeeAudit(
            EmployeeID, 
            Action, 
            ChangedBy, 
            ChangeDate,
            OldData,
            NewData
        )
        SELECT 
            i.EmployeeID,
            'UPDATE',
            SYSTEM_USER,
            GETDATE(),
            (SELECT * FROM deleted FOR JSON PATH),
            (SELECT * FROM inserted FOR JSON PATH)
        FROM inserted i;
    END
    
    -- 记录删除操作
    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO EmployeeAudit(
            EmployeeID, 
            Action, 
            ChangedBy, 
            ChangeDate,
            OldData,
            NewData
        )
        SELECT 
            d.EmployeeID,
            'DELETE',
            SYSTEM_USER,
            GETDATE(),
            (SELECT * FROM deleted FOR JSON PATH),
            NULL
        FROM deleted d;
    END
END;