淺析SQL Server 2008中的代碼安全之二：DDL觸發器與登錄觸發器

作者：邀月 2011-03-03 09:30:24

筆者之前介紹了代碼安全之一：存儲過程加密與安全上下文，這次為我們介紹了涉及DDL觸發器和登錄觸發器的應用實例。

本文主要涉及DDL觸發器和登錄觸發器的應用實例。

MicrosoftSQL Server 提供兩種主要機制來強制使用業務規則和數據完整性：約束和觸發器。觸發器為特殊類型的存儲過程，可在執行語言事件時自動生效。SQL Server 包括三種常規類型的觸發器：DML 觸發器、DDL 觸發器和登錄觸發器。

1、當數據庫中發生數據操作語言 (DML) 事件時將調用 DML 觸發器。DML 事件包括在指定表或視圖中修改數據的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發器可以查詢其他表，還可以包含復雜的 Transact-SQL 語句。將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待。如果檢測到錯誤（例如，磁盤空間不足），則整個事務即自動回滾。

關于DML觸發器應用最為廣泛。這里不再贅述。MSDN官方說明:http://msdn.microsoft.com/zh-cn/library/ms189799.aspx

2、當服務器或數據庫中發生數據定義語言 (DDL) 事件時將調用 DDL 觸發器。DDL 觸發器是一種特殊的觸發器，它在響應數據定義語言 (DDL) 語句時觸發。它們可以用于在數據庫中執行管理任務，例如，審核以及規范數據庫操作。

下面我們用舉例說明DDL觸發器（http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx）的應用：

示例一：創建一個DDL觸發器審核數據庫級事件

/***************  
創建一個審核表,其中EventData是一個XML數據列3w@live.cn*******************/  
USE master  
GO  
CREATE TABLE dbo.ChangeAttempt  
(EventData xml NOT NULL,  
AttemptDate datetime NOT NULL DEFAULT GETDATE(),  
DBUser char(50) NOT NULL)  
GO  
/***************  
在目標數據庫上創建一個觸發器,以記錄該數據庫的索引變化動作，包括Create|alter|Drop 
3w@live.cn*******************/  
CREATE TRIGGER db_trg_RestrictINDEXChanges  
ON DATABASE 
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX  
AS 
SET NOCOUNT ON 
INSERT dbo.ChangeAttempt  
(EventData, DBUser)  
VALUES (EVENTDATA(), USER)  
GO  
/***************  
創建一個索引，以測試觸發器3w@live.cn*******************/  
CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON 
dbo.ChangeAttempt(DBUser)  
GO  
/***************  
查看審核記錄  
3w@live.cn*******************/  
SELECT EventData  
FROM dbo.ChangeAttempt --------  
/***************-  
-------刪除測試觸發器和記錄表-------  
-3w@live.cn--------*******************/  
----drop TRIGGER [db_trg_RestrictINDEXChanges]  
----ON DATABASE  
----go  
----drop table dbo.ChangeAttempt  
----go

執行結果：

示例二：創建一個DDL觸發器審核服務器級事件

--------/  
在目標數據庫服務器上創建一個觸發器,以防止添加登錄賬號，  
--------3w@live.cn--------*******************/  
USE master  
GO  
-- Disallow new Logins on the SQL instance  
CREATE TRIGGER srv_trg_RestrictNewLogins  
ON ALL SERVER  
FOR CREATE_LOGIN  
AS 
PRINT 'No login creations without DBA involvement.' 
ROLLBACK 
GO  
--------/***************  
--------試圖創建一個登錄賬號  
--------3w@live.cn--------*******************/  
CREATE LOGIN johny WITH PASSWORD = '123456' 
GO  
--------/***************  
--------刪除演示觸發器  
--------3w@live.cn--------*******************/  
drop TRIGGER srv_trg_RestrictNewLogins  
ON ALL SERVER  
go

效果：

注意：要特別謹慎使用DDL觸發器。如果設置不當，將會在數據庫級甚至服務器級引發不可預知的后果。

3、登錄觸發器（http://msdn.microsoft.com/zh-cn/library/bb326598.aspx）將為響應 LOGON 事件而激發存儲過程。與 SQL Server 實例建立用戶會話時將引發此事件。

如果你有這樣的需求：在某個特定的時間只允許某個賬號登錄服務器（如單位和家里使用不同的賬號遠程登錄服務器），那么登錄觸發器是一個不錯的選擇。

示例三：創建一個登錄觸發器審核登錄事件

--------/***************  
--------創建登錄賬號  
--------3w@live.cn--------*******************/  
CREATE LOGIN nightworker WITH PASSWORD = '123b3b4' 
GO  
--------/***************  
--------演示數據庫和審核表  
--------3w@live.cn--------*******************/  
CREATE DATABASE ExampleAuditDB  
GO  
USE ExampleAuditDB  
GO  
CREATE TABLE dbo.RestrictedLogonAttempt  
(LoginNM sysname NOT NULL,  
AttemptDT datetime NOT NULL)  
GO  
--------/***************  
--------創建登錄觸發器,如果不是在7:00-17:00登錄，  
則記錄審核日志，并提示失敗  
--------3w@live.cn--------*******************/  
USE master  
GO  
Create TRIGGER trg_logon_attempt  
ON ALL SERVER  
WITH EXECUTE AS 'sa' 
FOR LOGON  
AS 
BEGIN 
IF ORIGINAL_LOGIN()='nightworker' AND 
DATEPART(hh,GETDATE()) BETWEEN 7 AND 17  
BEGIN 
ROLLBACK 
INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt  
(LoginNM, AttemptDT)  
VALUES (ORIGINAL_LOGIN(), GETDATE())  
END 
END 
GO  
--------/***************  
--------查看審核記錄  
--------3w@live.cn--------*******************/  
USE ExampleAuditDB  
GO  
select * from dbo.RestrictedLogonAttempt  
go  
--------/***************  
--------刪除演示數據庫及演示觸發器  
--------3w@live.cn--------*******************/  
use master  
go  
drop TRIGGER trg_logon_attempt  
ON ALL SERVER  
go  
drop database ExampleAuditDB  
go