很久沒有寫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的記錄檔會有錯誤訊息。