控制資料庫使用者的登入

很久沒有寫blog了，自從上次的旅遊紀錄。

最近做了比較多技術層面的東西，想想一方面也該是要回饋以外，另一方面也是怕自己忘記了(哈哈)。

開始來記錄一些技術的東西吧(旅遊的話就…XD)~

這篇是來說明控制MSSQL資料庫使用者的登入。 

因為公司的資料庫有個需求，防火牆雖然已設定了內網連到資料庫的Policies，但是對於帳號本身的控管仍然較為鬆散，也就是說雖有開通防火牆連線，但是帳號的連線無法正常管控，所以要做的事情就是要讓帳號的登入受到管控。

網路上大部分的解法都是使用Login Trigger來做處理，因此我也做了一個Login Trigger到資料庫上，以下是我的作法。

目標：能夠控管資料庫上的帳號(必要)，根據來源IP(必要)、使用的程式(非必要)、作業時間(AP帳號(全時暢通)或是管理用帳號(上班日可使用)，必要)來控管。

1. 資料庫設定

   1 2 3 4 5 6

   USE master GO GRANT SELECT ON LoginMain TO public GO GRANT SELECT ON LoginDate TO public GO

1
2
3
4
5
6
7
8
9
10
11
12

USE master
GO
ALTER DATABASE master SET ANSI_PADDING ON
GO
ALTER DATABASE master SET CONCAT_NULL_YIELDS_NULL ON
GO
ALTER DATABASE master SET ANSI_WARNINGS ON
GO
ALTER DATABASE master SET ANSI_NULL_DEFAULT ON
GO
ALTER DATABASE master SET ANSI_NULLS ON
GO

1. 加入Login Trigger

• 執行該Script新增。

• 這個Trigger透過Logon Event得到的XML來做判斷。

  1 2 3 4

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

• 第一個IF免除掉不被分析的帳號(如系統帳號、Domain帳號等)。

  1	IF (@username NOT LIKE '%\%')

• 判斷式中對於帳號、程式名、IP等有限制。

  1 2 3 4 5 6 7

  BEGIN SELECT @lm_idx = idx FROM LoginMain WHERE username=@username AND ((CHARINDEX(@srcip,srcip) <> 0) OR srcip = '') AND APP_NAME() like '%'+apname+'%' IF @lm_idx is null BEGIN RAISERROR ('無法在資料庫中找到您的登入帳號、來源IP、或是使用之程式不正確。',16,1) ROLLBACK END

• 接下來判斷時間是否正常。

  1 2 3 4 5 6 7

  BEGIN SELECT @ld_idx = idx FROM LoginDate WHERE lm_idx = @lm_idx AND wkdnum = @wkdnum AND dfrom <= lgtime="" and="" dto="">= @lgtime IF @ld_idx is null BEGIN RAISERROR ('您的帳號無法在此時段登入。',16,1) ROLLBACK END

• 如果登入的條件未達到，就會有如同以下的訊息。

• 如果是帳戶名稱不對，IP不對，或是程式名稱無法配對，在SQL的記錄檔會有錯誤訊息。

• 如果是時間不對，在SQL的記錄檔會有錯誤訊息。