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

2024-08-31 01:00:51

字體：大中小

來源：轉載

供稿：網友

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

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

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

關于DML觸發器應用最為廣泛。這里不再贅述。MSDN官方說明:

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

下面我們用舉例說明DDL觸發器（%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

執行結果：

SQL Server 2008中的代碼安全（二） DDL觸發器與登錄觸發器 ROLLBACK
GO

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

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

drop TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
go

效果：

SQL Server 2008中的代碼安全（二） DDL觸發器與登錄觸發器 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