SQL Server基礎之登陸觸發器

  雖然同表級(DML)觸發器和庫級(DDL)觸發器共頂著一個帽子，但登陸觸發器與二者有本質區別。無論表級還是庫級，都是用來進行數據管理的，而登陸觸發器是純粹的安全工具。

  登陸觸發器只響應LOGON事件，在登陸資料庫成功後、用戶會話未實際建立前觸發。登陸資料庫失敗，如賬號密碼錯誤，不會激發登陸觸發器。登陸觸發器內部的錯誤消息及PRINT等SQL語句輸出信息，統一寫到SQL Server 錯誤日誌。登陸觸發器可以同時存在多個，但存在多個時,SQL Server不保證所有觸發器的執行順序，只能使用sp_settriggerorder 確定哪一個登陸觸發器第一個或最後一個執行。登陸觸發器內部執行rollback語句或者發生級別大於20的錯誤，將會阻止用戶繼續登陸。

  前面說到登陸觸發器是純粹的安全工具，作為安全工具，登陸觸發器使用場景都是與安全有關的。下麵列舉了登陸觸發器使用的4種場景：
1、記錄登陸用戶的登陸時間、登陸IP等。
2、限制某些用戶，如Test，在工作時間內不能登陸資料庫。
3、限制某些用戶，如sa，只能在指定IP或者IP段登陸。
4、限制只有來自指定IP或IP段的登陸可以成功登陸。

登陸觸發器語法：

--創建或修改登陸觸發器
CREATE [ OR ALTER ] TRIGGER trigger_name   --觸發器名字
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  --在實際項目中，多使用管理員賬號建立登陸觸發器，因而這項可以省略
{ FOR| AFTER } LOGON    
AS 
  sql_statement   --用戶自己編寫內容

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

-- WITH ENCRYPTION 表示加密該觸發器的SQL，使用該項後，將無法通過sp_helptext或資料庫管理器查看觸發器SQL。
-- WITH EXECUTE AS Clause ，示例：WITH EXECUTE AS 'sa'，指定觸發器使用哪個用戶的許可權，省略該項，將使用登陸用戶的許可權。

--刪除登陸觸發器
DROP TRIGGER  trigger_name   ON ALL SERVER  

--查詢登陸觸發器
select * from sys.server_triggers

 備註：在登陸觸發器內部使用函數EVENTDATA()可以獲取觸發器上下文事件信息，使用函數ORIGINAL_LOGIN()可以獲取當前登陸的用戶名。

--登陸觸發器示例
--限制用戶Test在工作時間（上午7時，晚上18時之間）不能登陸資料庫
create trigger tr_XianZhiDengLu
on all server
for logon
as 
    declare @XianZaiShiJian datetime
    set @XianZaiShiJian=GETDATE ()

    if ORIGINAL_LOGIN()='test' and (DATEPART(hh,getdate()) >7 and DATEPART(hh,getdate())<18)
        begin
          rollback 
        end

參考文獻：

登陸觸發器

寫作時間：2018-10-05

=====================================================================================
本文只代表本人的見解，可能存在錯誤，僅用於技術交流。如果你喜歡該文，可以掃下麵的二維碼打賞我（打賞敬請備註“博客園打賞”五字）。