主頁 > 知識庫 > SQL Server 2008中的代碼安全（二） DDL觸發(fā)器與登錄觸發(fā)器

SQL Server 2008中的代碼安全（二） DDL觸發(fā)器與登錄觸發(fā)器

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

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

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

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

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

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

示例一：創(chuàng)建一個DDL觸發(fā)器審核數(shù)據(jù)庫級事件

復制代碼代碼如下:

 
/*************** 
創(chuàng)建一個審核表,其中EventData是一個XML數(shù)據(jù)列 
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 

/*************** 
在目標數(shù)據(jù)庫上創(chuàng)建一個觸發(fā)器,以記錄該數(shù)據(jù)庫的索引變化動作， 
包括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 

/*************** 
創(chuàng)建一個索引，以測試觸發(fā)器 
3w@live.cn 
*******************/ 

CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON 
dbo.ChangeAttempt(DBUser) 
GO 

/*************** 
查看審核記錄 
3w@live.cn 
*******************/ 

SELECT EventData 
FROM dbo.ChangeAttempt 



--------/*************** 
--------刪除測試觸發(fā)器和記錄表 
--------3w@live.cn 
--------*******************/ 

----drop TRIGGER [db_trg_RestrictINDEXChanges] 
----ON DATABASE 
----go 
----drop table dbo.ChangeAttempt 
----go 

執(zhí)行結果：

邀月工作室

示例二：創(chuàng)建一個DDL觸發(fā)器審核服務器級事件

復制代碼代碼如下:

 
--------/*************** 
--------在目標數(shù)據(jù)庫服務器上創(chuàng)建一個觸發(fā)器,以防止添加登錄賬號， 
--------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 

--------/*************** 
--------試圖創(chuàng)建一個登錄賬號 
--------3w@live.cn 
--------*******************/ 
CREATE LOGIN johny WITH PASSWORD = '123456' 
GO 

--------/*************** 
--------刪除演示觸發(fā)器 
--------3w@live.cn 
--------*******************/ 

drop TRIGGER srv_trg_RestrictNewLogins 
ON ALL SERVER 
go 

效果：

邀月工作室

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

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

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

示例三：創(chuàng)建一個登錄觸發(fā)器審核登錄事件

復制代碼代碼如下:

 
--------/*************** 
--------創(chuàng)建登錄賬號 
--------3w@live.cn 
--------*******************/ 

CREATE LOGIN nightworker WITH PASSWORD = '123b3b4' 
GO 

--------/*************** 
--------演示數(shù)據(jù)庫和審核表 
--------3w@live.cn 
--------*******************/ 

CREATE DATABASE ExampleAuditDB 
GO 
USE ExampleAuditDB 
GO 

CREATE TABLE dbo.RestrictedLogonAttempt 
(LoginNM sysname NOT NULL, 
AttemptDT datetime NOT NULL) 
GO 

--------/*************** 
--------創(chuàng)建登錄觸發(fā)器,如果不是在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 

--------/*************** 
--------刪除演示數(shù)據(jù)庫及演示觸發(fā)器 
--------3w@live.cn 
--------*******************/ 
use master 
go 

drop TRIGGER trg_logon_attempt 
ON ALL SERVER 
go 

drop database ExampleAuditDB 
go 

結果：

邀月工作室

當然，你也可以使用應用程序或類似于log4net的日志機制記錄類似的登錄事件，但SQL server 2008已經(jīng)為我們做到了，你所做的僅僅是有勇氣來試一試。

小結：作為對數(shù)據(jù)DDL操作和登錄事件的審核和監(jiān)控，SQL Server提供了比較完善的事件處理機制。這也是SQL server安全機制的一部分。下文將涉及SQL server數(shù)據(jù)庫級的透明加密,敬請關注。
http://www.cnblogs.com/downmoon

您可能感興趣的文章:

sql server連接不上怎么辦 SQL Server2008R無法登錄的解決方案（1814\18456）
SQL Server 2008用''sa''登錄失敗，啟用''sa''登錄的解決辦法
sql server 2008 用戶 NT AUTHORITY\IUSR 登錄失敗的解決方法
SQL Server 2008+ Reporting Services （SSRS）使用USER登錄問題
SQL Server 2008登錄錯誤:無法連接到(local)解決方法
Sql server 2008 express遠程登錄實例設置圖文教程
SQL server 2008 更改登錄驗證方式的方法

標簽：蘭州湖南仙桃衡水崇左黃山銅川湘潭

巨人網(wǎng)絡通訊聲明：本文標題《SQL Server 2008中的代碼安全（二） DDL觸發(fā)器與登錄觸發(fā)器》，本文關鍵詞；如發(fā)現(xiàn)本文內容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權與本站無關。