~楓花雪岳~: [SQL] Logon Trigger

星期五, 12月 14, 2012

[SQL] Logon Trigger

Logon Trigger 是透過 LOGON 事件觸發，可以追蹤登入活動或限制登入 SQL Server。

Logon Trigger 語法基本上跟 DDL Trigger 是一樣的，但 Logon Trigger 只有一個觸發事件，就是 LOGON 事件，同樣地擷取 Logon Trigger 的相關資訊，也必須使用 EVENTDATA()，但其 XML 內容有些許差異而已。

EVENTDATA() 函數

ClientHost：來源用戶端之主機名稱。如果用戶端和伺服器名稱相同，這個值會是'<local_machine>'。否則會是用戶端的 IP 位址。

限制登入 SQL Server

禁止 Client 端利用 SSMS 連入 SQL Server。
禁止特定帳戶（ServerName\LogonTest）、特定 IP （192.168.0.20）或特定時間連入（2000 之後） SQL Server。

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER 
FOR LOGON
AS
    BEGIN
      -- 禁止 SSMS 連入
      IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%'
          ROLLBACK
        
      -- 禁止特定帳號或特定 IP 連入
      DECLARE @data xml
      DECLARE @ClientHost varchar(50)
      DECLARE @LoginName varchar(50)
      DECLARE @PostTime datetime

      SET @data = EVENTDATA()
      SET @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
      SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
      SET @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

      -- 特定 IP
      IF @ClientHost = '192.168.0.20'
          ROLLBACK

      -- 特定帳號
      IF @LoginName = 'ServerName\LogonTest'
          ROLLBACK

      -- 特定時間
      IF DATEPART(hh,@PostTime) >= 20
          ROLLBACK

    END