積沙成塔

【情境描述】

在上一篇筆者談到利用 SQL Server Audit 達到記錄資料庫管理動作（建立、修改、刪除），這種做法就筆者所知是屬於事後追蹤之用，若您想要在事前就防堵資料庫被未經許可的變更，或許可以考慮使用 DDL Trigger 來實現這個需求，該怎麼進行？請見下一節。

【實作步驟】

Trigger 是用來定義特定事件發生時所要做的動作，當您對 SQL Server 進行 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 等動作時，就會觸發 DDL Trigger，而本文所要探討的資料庫管理動作，舉凡 CREATE DATABASE、ALTER DATABASE 及 DROP DATABASE 等動作都會觸發相對應的 DDL 事件，導致 DDL Trigger 被觸發。有關 DDL Trigger 的語法如下：

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

接著筆者利用下列的 T-SQL 指令碼來建立 DDL Trigger，用來避免未經許可的建立資料庫動作：

CREATE TRIGGER tr_RollbackDBCreate
ON ALL SERVER
FOR CREATE_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許建立資料庫', 16, 1) 
	ROLLBACK
GO

在上述程式碼當中第 2 列的 ALL SERVER 是用來定義這個 DDL Trigger 的作用範圍為伺服器層級，第 3 列的 CREATE_DATABASE 用來定義 DDL Trigger 只在使用者利用 CREATE DATABASE 指令碼建立資料庫時才會觸發，相關的 DDL 事件請參閱參考資料中的 DDL 事件，第 5 - 7 列則是 Trigger 的主體，使用 EVENTDATA 函數搭配 Xquery 來取得使用者所執行的指令碼，接著利用 RAISERROR 產生錯誤訊息，最後 Rollback 資料庫建立的動作。您可以在建立上述 DDL Trigger 後利用下列 T-SQL 指令碼來嘗試建立資料庫：

CREATE DATABASE NW
GO

若 DDL Trigger 正確建立的話，您將會看到如下圖的錯誤訊息：

以下 T-SQL 指令碼用來建立 DDL Trigger 以示範避免資料庫被未經授權的修改：

CREATE TRIGGER tr_RollbackDBAlter
ON ALL SERVER
FOR ALTER_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許變更資料庫', 16, 1) 
	ROLLBACK
GO

上述指令碼和先前的 tr_RollbackDBCreate 大同小異，只差在第 3 列的 DDL 事件，利用 FOR ALTER_DATABASE 來定義 DDL Trigger 於使用者執行 ALTER DATABASE 或 sp_fulltext_database 動作時觸發，接著使用下列 T-SQL 指令碼用來嘗試變更 testdb 資料庫的相容性層級為 SQL Server 2005（90）。

USE [master]
GO
ALTER DATABASE testdb SET COMPATIBILITY_LEVEL = 90
GO

若 DDL Trigger 正確建立的話，您將會看到如下圖的錯誤訊息：

最後則是示範利用下列的 T-SQL 指令碼建立 DDL Trigger 以防止資料庫被有意或無意刪除：

CREATE TRIGGER tr_RollbackDBDrop
ON ALL SERVER
FOR DROP_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許刪除資料庫', 16, 1) 
	ROLLBACK
GO

上述指令碼和先前的 tr_RollbackDBCreate 大同小異，只差在第 3 列的 DDL 事件，利用 FOR DROP_DATABASE 來定義 DDL Trigger 於使用者執行 DROP DATABASE 動作時觸發，接著使用下列 T-SQL 指令碼用來嘗試刪除 testdb 資料庫。

DROP DATABASE testdb
GO

若 DDL Trigger 正確建立的話，您將會看到如下圖的錯誤訊息：

當您建立的 Trigger 屬於伺服器層級時，可以在 sys.server_triggers 目錄檢視中查詢的到，例如下列的 T-SQL 指令碼可以用來查詢本文所使用的 DDL Trigger：

SELECT * 
FROM sys.server_triggers
WHERE name like 'tr_Rollback%'

 

本節所使用的完整指令碼如下：

--#region CREATE_DATABASE 事件適用
IF EXISTS (SELECT * FROM sys.server_triggers
				  WHERE name = 'tr_RollbackDBCreate')
	DROP TRIGGER tr_RollbackDBCreate
	ON ALL SERVER
GO

CREATE TRIGGER tr_RollbackDBCreate
ON ALL SERVER
FOR CREATE_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許建立資料庫', 16, 1) 
	ROLLBACK
GO


CREATE DATABASE NW
GO


--#endregion
--#region ALTER_DATABASE 事件適用
IF EXISTS (SELECT * FROM sys.server_triggers
				  WHERE name = 'tr_RollbackDBAlter')
	DROP TRIGGER tr_RollbackDBAlter
	ON ALL SERVER
GO

CREATE TRIGGER tr_RollbackDBAlter
ON ALL SERVER
FOR ALTER_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許變更資料庫', 16, 1) 
	ROLLBACK
GO


USE [master]
GO
ALTER DATABASE testdb SET COMPATIBILITY_LEVEL = 90
GO
--#endregion
--#region DROP_DATABASE 事件適用
IF EXISTS (SELECT * FROM sys.server_triggers
				  WHERE name = 'tr_RollbackDBDrop')
	DROP TRIGGER tr_RollbackDBDrop
	ON ALL SERVER
GO

CREATE TRIGGER tr_RollbackDBDrop
ON ALL SERVER
FOR DROP_DATABASE
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
	RAISERROR (N'不允許刪除資料庫', 16, 1) 
	ROLLBACK
GO

DROP DATABASE testdb
GO
--#endregion

【結論】

上一節所提供的做法雖然可以順利避免未經授權的建立、修改、刪除資料庫，但如此一來會使得所有的登入（Login）包含 sa，因此筆者在 tr_RollbackDBCreate、tr_RollbackDBAlter 和 tr_RollbackDBDrop 中執行 Rollback 前利用 IS_SRVROLEMEMBER 安全性函數判斷登入識別是否為特定伺服器角色的成員，下列指令碼示範假設登入屬於 sysadmin 伺服器角色才允許建立資料庫，否則拒絕建立資料庫的動作：

CREATE TRIGGER tr_RollbackDBCreate
ON ALL SERVER
FOR CREATE_DATABASE
AS
	IF IS_SRVROLEMEMBER('sysadmin') = 0
		BEGIN
			SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
			RAISERROR (N'不允許建立資料庫', 16, 1) 
			ROLLBACK 
		END

GO

接著利用下列 T-SQL 指令碼建立登入並指定為 dbcreator 伺服器角色：

--#region 建立一個Login為User1，並將之設定為db_creator伺服器角色
USE [master]
GO
CREATE LOGIN [User1] WITH PASSWORD=N'User1', DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [User1]
GO
--#endregion

以 User 1 登入 SQL Server 後執行 CREATE DATABASE，您會發現原本應該可以建立資料庫的角色因為上述的 DDL Trigger 而無法建立了，如下圖所示：

反之，利用 sa 登入來建立資料庫則可順利完成，如下圖所示：

由此可知，利用本文所介紹的一些小技巧就可以讓您在管理資料庫上更加嚴謹以及有效率。

【參考資料】

- 利用 SQL Server Audit 追蹤資料庫管理動作

- CREATE TRIGGER (Transact-SQL)

- DDL 觸發程序

- DDL 事件群組

- DDL 事件

- 使用 EVENTDATA 函數

- Xquery 語言參考 (SQL Server)

- 伺服器層級角色