[問題處理]TRIGGER 使用時的權限問題
在工作生涯中有很多共識過的朋友，有很多都是程式設計師，有些時候當系統在某些狀況或客戶家出問題的時候，很常會聽到一句話「在我電腦測試沒有問題，可能是環境的問題才造成的異常」，或者是「我測試的時候沒有問題，應該是別人的問題吧」。而在最近處理的幾個狀況，也是聽到有這樣的說法，但歸咎起來可能都還是因為對一些技術底層的熟悉度不足，才造成的問題。之前在撰寫「Trigger 撰寫時要注意的小細節」的時候，是比較著重在開發上的處理，而在這一篇當中，我們會模擬一個環境，來介紹一下使用 TRIGGER 所需要注意的權限問題。
在這個範例中會有兩個資料庫，分別是 DEMO1 和 DEMO2，各有一些資料表，因此我們可以用以下的指令來建立測試資料庫。
USE [master]
GO
-- 建立測試資料庫
CREATE DATABASE [DEMO1]
GO
CREATE DATABASE [DEMO2]
GO
-- 建立測試資料表
USE [DEMO1]
GO
CREATE TABLE T1( F1 INT, F2 VARCHAR(10) )
GO
CREATE TABLE T2( F1 INT, F2 VARCHAR(10) )
GO
CREATE TABLE T3( F1 INT, F2 VARCHAR(10) )
GO
USE [DEMO2]
GO
CREATE TABLE T4( F1 INT, F2 VARCHAR(10) )
GO
CREATE TABLE T5( F1 INT, F2 VARCHAR(10) )
GO
接著用以下的指令來建立測試帳號 User1 , User2 和 User3
USE [master]
GO
-- 建立測試帳號
CREATE LOGIN [User1] WITH PASSWORD=N'123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE LOGIN [User2] WITH PASSWORD=N'123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE LOGIN [User3] WITH PASSWORD=N'123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- 設定權限 User1 是 sysadmin
ALTER SERVER ROLE [sysadmin] ADD MEMBER [User1]
GO
-- 設定權限 User2 是資料庫 DEMO1 的 db_owner
USE [DEMO1]
GO
CREATE USER [User2] FOR LOGIN [User2]
GO
ALTER ROLE [db_owner] ADD MEMBER [User2]
GO
-- 設定權限 User3 是資料庫 DEMO2 的 db_owner
USE [DEMO2]
GO
CREATE USER [User3] FOR LOGIN [User3]
GO
ALTER ROLE [db_owner] ADD MEMBER [User3]
GO
當根據上述處理之後，基本上使用都沒有問題，User1 & User2 會去建立和修改 DEMO1 資料庫內的資料；而 User1 & User3 會去修改 DEMO2 內的資料。但後來又衍生出一個需求，想要去記錄這些資料表何時有異動，但為了避免程式還要去做調整，因此就想到利用 DML Trigger，將異動紀錄存放到一個特定的資料庫內，於是就做了以下的處理。
use [master]
GO
CREATE DATABASE [AuditDB]
GO
use [AuditDB]
GO
CREATE TABLE ChangeTracking(
UserName varchar(10),
TableName varchar(10),
Action varchar(10),
Rows int,
EventTime DateTime DEFAULT GETDATE()
)
GO
並且在資料表上放入 TRIIGER 來進行處理
use [DEMO1]
GO
-- INSERT TRIGGER
CREATE TRIGGER TRIG_T1_INSERT ON dbo.T1
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @User VARCHAR(10)
DECLARE @Rows INT
SELECT @User=login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
SELECT @Rows=COUNT(*) FROM inserted
INSERT INTO AuditDB.dbo.ChangeTracking([UserName],[TableName],[Action],[Rows])
VALUES( @User, 'T1' , 'INSERT', @Rows )
GO
-- UPDATE TRIGGER
CREATE TRIGGER TRIG_T1_UPDATE ON dbo.T1
AFTER UPDATE
AS
SET NOCOUNT ON
DECLARE @User VARCHAR(10)
DECLARE @Rows INT
SELECT @User=login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
SELECT @Rows=COUNT(*) FROM deleted
INSERT INTO AuditDB.dbo.ChangeTracking([UserName],[TableName],[Action],[Rows])
VALUES( @User, 'T1' , 'UPDATE', @Rows )
GO
-- DELETE TRIGGER
CREATE TRIGGER TRIG_T1_DELETE ON dbo.T1
AFTER DELETE
AS
SET NOCOUNT ON
DECLARE @User VARCHAR(10)
DECLARE @Rows INT
SELECT @User=login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
SELECT @Rows=COUNT(*) FROM deleted
INSERT INTO AuditDB.dbo.ChangeTracking([UserName],[TableName],[Action],[Rows])
VALUES( @User, 'T1' , 'DELETE', @Rows )
GO
也做了一點簡單的測試
INSERT INTO DEMO1.dbo.T1 VALUES ( 1, 'A' ) , ( 2, 'B' )
GO
UPDATE DEMO1.dbo.T1 SET F2 = F2 + 'X' WHERE F1 = 1
GO
DELETE DEMO1.dbo.T1 WHERE F1 = 2
GO
SELECT * FROM AuditDB.dbo.ChangeTracking
GO
看起來都很正常
當看起來似乎都沒有問題之後，也很快的把上述資料表 T1 上面的 TRIGGER 給改寫，複製到 DEMO1 資料庫下的 T2 & T3 以及 DEMO2 資料庫下的 T4 & T5，測試一下看起來也很正常，就把他給放到正式區去執行了。
當 DEMO1 資料庫在一般狀況下都沒有問題，但把使用者給換成非 sysadmin 伺服器角色的使用者 User2 之後，當資料表沒有 TRIGGER 的時候都很正常可以異動資料，可是放上 TRIGGER 之後，卻出現以下的錯誤訊息
訊息 916，層級 14，狀態 1，程序 TRIG_T1_UPDATE，行 17
伺服器主體 "User2" 在目前的安全性內容下無法存取資料庫 "AuditDB"。
而另外一個資料庫 DEMO2 也會有類似的狀況
訊息 916，層級 14，狀態 1，程序 TRIG_T4_UPDATE，行 17
伺服器主體 "User3" 在目前的安全性內容下無法存取資料庫 "AuditDB"。
很多時候，為了系統開發的便利，很多時候都會採用 sa 當作連線帳號，但這樣的狀況很容易造成被駭的狀況，因此很多時候都會建議使用特定的帳號給特定的應用程式，並且賦予合適的權限，也就是使用者雖然對特定的資料庫有權限，但對整個 Instance 的其他資料庫並不會特別去指定權限。當初步看到這樣的問題，或許會有人想說，那就在 AuditDB 資料庫去加入 User2 和 User3 ，並給予權限允許異動資料，看起來似乎問題就解決了。但這個會有個限制，因為我們只能針對既有的帳號去指定權限，要是這些資料庫後續更換新的使用者呢 ? 或者是建立更多不同權限的帳號時候，是否會讓我們非常麻煩呢 ?
因此在這個時候我們想到了使用 public 的群組，透過 GRANT 的 DCL 來賦予權限給 public 群組
use [AuditDB]
GO
GRANT INSERT,UPDATE,DELETE TO [public]
GO
當在討論的時候，有人提出另外一個問題，那是否我就乾脆直接利用 「GRANT ALL TO [public]」，就不用這樣慢慢賦與權限呢 ? 這是一個蠻好玩的問題，因此我們先用 REVOKE 將原本賦予的權限回收，改成 GRANT ALL 的方式來進行設定
use [AuditDB]
GO
REVOKE INSERT,UPDATE,DELETE TO [public]
GO
GRANT ALL TO [public]
GO
此時我們會看到一段訊息「ALL 權限已被取代，只保留相容性。此權限不代表實體上定義的 ALL 權限。」，這一段訊息看起來有點特別，看來 GRANT ALL 並不是所有的權限都有，只是為了和舊版本的相容性才保留的，那既然這樣，那 ALL 到底指的是哪些權限呢 ?
從查看 AuditDB 「屬性」→「權限」中可以看起來，當使用 GRANT ALL 的時候其實只有賦予部份建立特定類型物件的權限，因此還不能去更改資料內容。因此可能要看一下 TRIGGER 內會使用到的處理，再來針對 GRANT 的時候去賦予合適的權限。
基本上如果只是要記錄異動紀錄和狀況，使用 TRIGGER 不見得會是個好的解決方案，畢竟他會使得交易更複雜，甚至因為 TRIGGER 影響到反應時間，因此在 SQL Server 2008 之後，倒是可以有另外的選擇，也就是使用資料庫層級稽核 ( Database Level Audit )，一來對系統的影響較小，二來可以有更便利和更詳實的紀錄。但他比較大的缺點是在於，資料庫層級稽核則限於 Enterprise、Developer 和 Evaluation Edition，因此一般我們較常使用的 Standard 或 Express 版本就沒有辦法使用了。
使用資料庫稽核是蠻簡單的，可以在 SSMS 使用 GUI 去做設定，或者是使用類似以下的指令來建立
-- 建立
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150404-090955]
FOR SERVER AUDIT [Audit-20150404-090748]
ADD (DELETE ON DATABASE::[DEMO1] BY [public]),
ADD (INSERT ON DATABASE::[DEMO1] BY [public]),
ADD (UPDATE ON DATABASE::[DEMO1] BY [public])
GO
-- 啟用
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150404-090955]
WITH ( STATE = ON )
資訊也是很足夠的
希望透過這樣的範例，讓大家在使用 Trigger 的時候避免遇到類似的問題囉。