您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 內建工具建構審計系統：DDL Trigger 記錄結構與安全變更

編輯：關於SqlServer

從 DML Trigger 到 DDL Trigger：補上結構/安全變更的審計盲區

SQL Server 2000 僅有 DML Trigger (`INSERT/UPDATE/DELETE`)，無法追蹤 `CREATE TABLE`、`ALTER LOGIN`、`GRANT` 等結構/安全變更。2005 引入 **DDL Trigger**，配合 `XML` 資料型別儲存事件完整內容，**零成本建構企業級審計系統**，滿足 SOX (Sarbanes-Oxley) 合規需求。

架構設計

審計資料庫**：`DDLTriggerTest`，表 `EventTableData`：

CREATE DATABASE DDLTriggerTest;
GO
USE DDLTriggerTest;
GO
CREATE TABLE EventTableData (
    IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY,
    XMLEvent XML,                    -- 事件完整 XML (EVENTDATA() 回傳)
    DatabaseName VARCHAR(50),
    SystemUser VARCHAR(50),
    EntryDate DATETIME DEFAULT GETDATE()
);

Server 級 DDL Trigger**：攔截所有登入相關事件 (`DDL_LOGIN_EVENTS` = `CREATE/ALTER/DROP LOGIN` + `GRANT/DENY/REVOKE SERVER`)。

CREATE TRIGGER tr_Security
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
BEGIN
    INSERT INTO DDLTriggerTest..EventTableData (XMLEvent, DatabaseName, SystemUser)
    VALUES (EVENTDATA(), DB_NAME(), SYSTEM_USER);
END;
GO

EVENTDATA() XML 內容範例


  CREATE LOGIN
  2024-01-15T10:30:00.123
  52
  NewUser
  SQL Login
  master
  us_english
  APP-SERVER-01
  Microsoft SQL Server Management Studio
  
    
    CREATE LOGIN [NewUser] WITH PASSWORD=******

擴充：資料庫級 DDL Trigger (Schema 變更)

USE YourDB;
GO
CREATE TRIGGER tr_DDLAudit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS  -- CREATE/ALTER/DROP TABLE, VIEW, PROC, INDEX, TRIGGER, SYNONYM, etc.
AS
BEGIN
    INSERT INTO DDLTriggerTest..EventTableData (XMLEvent, DatabaseName, SystemUser)
    VALUES (EVENTDATA(), DB_NAME(), SYSTEM_USER);
END;
GO

查詢與報表

**XQuery 擷取關鍵欄位**：`SELECT XMLEvent.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') AS EventType, ... FROM EventTableData`
**SSRS/Power BI 連接審計庫** 產出每日/每週合規報表。
**保留策略**：分區表按月切分，自動清理超期分區 (SOX 要求 ≥ 7 年)。

優勢與限制

優勢	限制
零授權成本 (內建功能)	僅記錄 DDL，不記錄 SELECT/資料存取 (需 SQL Audit/Extended Events)
XML 完整保留事件上下文	高頻 DDL 環境可能產生大量寫入，需評估審計庫 IO
Server 級觸發器一次部署全例覆蓋	Trigger 本身可被 `DISABLE TRIGGER` / `DROP TRIGGER` (需高權限)，建議審計 Trigger 自身變更也納入審計
支援 `ON ALL SERVER` / `ON DATABASE` 彈性範圍	含資料庫備份/還原時 Trigger 定義會隨庫移動，需部署腳本確保一致性