建站
咨詢
售后
建站咨詢
新聞中心
本文主要 涉及DDL觸發(fā)器和登錄觸發(fā)器的應(yīng)用實(shí)例。
高碑店網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營(yíng)維護(hù)。成都創(chuàng)新互聯(lián)自2013年創(chuàng)立以來到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。
MicrosoftSQL Server 提供兩種主要機(jī)制來強(qiáng)制使用業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。觸發(fā)器為特殊類型的存儲(chǔ)過程，可在執(zhí)行語言事件時(shí)自動(dòng)生效。SQL Server 包括三種常規(guī)類型的觸發(fā)器：DML 觸發(fā)器、DDL 觸發(fā)器和登錄觸發(fā)器。
1、當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。
關(guān)于DML觸發(fā)器應(yīng)用最為廣泛。這里不再贅述。MSDN官方說明:http://msdn.microsoft.com/zh-cn/library/ms189799.aspx
2、當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言 (DDL) 事件時(shí)將調(diào)用 DDL 觸發(fā)器。DDL 觸發(fā)器是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言 (DDL) 語句時(shí)觸發(fā)。它們可以用于在數(shù)據(jù)庫中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫操作。
下面我們用舉例說明DDL觸發(fā)器（http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx）的應(yīng)用：
示例一：創(chuàng)建一個(gè)DDL觸發(fā)器審核數(shù)據(jù)庫級(jí)事件
- /***************
- 創(chuàng)建一個(gè)審核表,其中EventData是一個(gè)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
- /***************
- 在目標(biāo)數(shù)據(jù)庫上創(chuàng)建一個(gè)觸發(fā)器,以記錄該數(shù)據(jù)庫的索引變化動(dòng)作，包括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)建一個(gè)索引，以測(cè)試觸發(fā)器3w@live.cn*******************/
- CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON
- dbo.ChangeAttempt(DBUser)
- GO
- /***************
- 查看審核記錄
- 3w@live.cn*******************/
- SELECT EventData
- FROM dbo.ChangeAttempt --------
- /***************-
- -------刪除測(cè)試觸發(fā)器和記錄表-------
- -3w@live.cn--------*******************/
- ----drop TRIGGER [db_trg_RestrictINDEXChanges]
- ----ON DATABASE
- ----go
- ----drop table dbo.ChangeAttempt
- ----go
執(zhí)行結(jié)果：
示例二：創(chuàng)建一個(gè)DDL觸發(fā)器審核服務(wù)器級(jí)事件
- --------/
- 在目標(biāo)數(shù)據(jù)庫服務(wù)器上創(chuàng)建一個(gè)觸發(fā)器,以防止添加登錄賬號(hào)，
- --------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)建一個(gè)登錄賬號(hào)
- --------3w@live.cn--------*******************/
- CREATE LOGIN johny WITH PASSWORD = '123456'
- GO
- --------/***************
- --------刪除演示觸發(fā)器
- --------3w@live.cn--------*******************/
- drop TRIGGER srv_trg_RestrictNewLogins
- ON ALL SERVER
- go
效果：
注意：要特別謹(jǐn)慎使用DDL觸發(fā)器。如果設(shè)置不當(dāng)，將會(huì)在數(shù)據(jù)庫級(jí)甚至服務(wù)器級(jí)引發(fā)不可預(yù)知的后果。
3、登錄觸發(fā)器（http://msdn.microsoft.com/zh-cn/library/bb326598.aspx）將為響應(yīng) LOGON 事件而激發(fā)存儲(chǔ)過程。與 SQL Server 實(shí)例建立用戶會(huì)話時(shí)將引發(fā)此事件。
如果你有這樣的需求：在某個(gè)特定的時(shí)間只允許某個(gè)賬號(hào)登錄服務(wù)器（如單位和家里使用不同的賬號(hào)遠(yuǎn)程登錄服務(wù)器），那么登錄觸發(fā)器是一個(gè)不錯(cuò)的選擇。
示例三：創(chuàng)建一個(gè)登錄觸發(fā)器審核登錄事件
- --------/***************
- --------創(chuàng)建登錄賬號(hào)
- --------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
結(jié)果：
當(dāng)然，你也可以使用應(yīng)用程序或類似于log4net的日志機(jī)制記錄類似的登錄事件，但SQL server 2008已經(jīng)為我們做到了，你所做的僅僅是有勇氣來試一試。
小結(jié)：作為對(duì)數(shù)據(jù)DDL操作和登錄事件的審核和監(jiān)控，SQL Server提供了比較完善的事件處理機(jī)制。這也是SQL server安全機(jī)制的一部分。
原文出處：http://www.cnblogs.com/downmoon/archive/2011/03/01/1966670.html
分享題目：淺析SQLServer2008中的代碼安全之二：DDL觸發(fā)器與登錄觸發(fā)器
分享地址：http://www.dlmjj.cn/article/djssocj.html
