SQL Server 限制IP登陆(登陆触发器运用)
一.本文所涉及的內容(Contents)
二.背景(Contexts)
在MySQL的mysql.User表保存了登陸用戶的權限信息,Host和User字段則是關于登陸IP的限制。但是在SQL Server沒有這樣一個表,那SQL Server有什么辦法可以實現(xiàn)類似的安全控制的功能呢?
SQL Server 包括三種常規(guī)類型的觸發(fā)器:DML觸發(fā)器、DDL觸發(fā)器和登錄觸發(fā)器。DML觸發(fā)器是比較常使用的,它通常在表或視圖中修改數(shù)據(jù)(INSERT、UPDATE和DELETE 等)為了保證業(yè)務數(shù)據(jù)的完整性和一致性,可以對事務進行回滾等操作;如果你對DDL觸發(fā)器感興趣,可以參考:SQL Server DDL觸發(fā)器運用,里面涉及到DDL觸發(fā)器的知識;登陸觸發(fā)器將在本文運用到關于IP登陸限制的解決方案中。
登陸觸發(fā)器能為我們解決什么問題呢?本文將為你講述5種運用登陸觸發(fā)器的場景:
1) 限制某登錄名(比如sa)只能在本機或者指定的IP中登陸;
2) 限制服務器角色(比如sysadmin)只能在本機或者指定的IP中登陸;
3) 限制某登錄名(比如sa)只能某時間段內登陸;
4) 限制登錄名與IP的對應關系,支持多對多關系;
5) 限制某登錄名可以在某IP段登錄(比如192.168.1.*),如下圖;
三.實現(xiàn)代碼(SQL Codes)
(一) 我機器的IP是:192.168.1.48,首先我在數(shù)據(jù)庫創(chuàng)建一個test帳號,設置密碼為123,接著創(chuàng)建登陸觸發(fā)器:tr_connection_limit,它會在用戶登陸的時候觸發(fā),通過EVENTDATA()函數(shù)返回的客戶端的IP,使用ORIGINAL_LOGIN()函數(shù)返回的登陸名,對IP和登錄名進行判斷。
當?shù)卿浢莟est的時候,如果登陸的IP地址本地<local machine>或者是192.168.1.50,192.168.1.120就允許登陸,在這之外其它情況的登陸將回滾。登陸失敗的如Figure1所示。
--Script1:創(chuàng)建test登錄賬號 CREATE LOGIN test WITH PASSWORD = '123' GO-- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <限制test用戶只能在本機和指定的IP中登陸> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_connection_limit] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN--限制test這個帳號的連接 IF ORIGINAL_LOGIN()= 'test' --允許test在本機和下面的IP登錄 AND (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')) NOT IN('<local machine>','192.168.1.50','192.168.1.120')ROLLBACK; END;(Figure1:test用戶登陸錯誤信息)
我在一臺IP為192.168.1.115的機器上使用test登陸名登陸我的SQL Server數(shù)據(jù)庫,因為這個IP不在允許的IP列表中,所以出現(xiàn)了Figure1的錯誤信息。我再使用一臺IP為192.168.1.120的機器登陸我的SQL Server數(shù)據(jù)庫,成功登陸了,使用Script2返回登陸的信息;如Figure2,請看session_id為58的記錄:登陸名為test,登陸的IP為192.168.1.120。
--Script2:返回登錄信息 SELECT a.[session_id],a.[login_time],a.[host_name], a.[original_login_name],b.[client_net_address] FROM MASTER.sys.dm_exec_sessions a INNER JOIN MASTER.sys.dm_exec_connections b ON a.session_id=b.session_id(Figure2:用戶登陸信息)
關于Script1腳本中EXECUTE AS的用法可以參考:EXECUTE AS (Transact-SQL),ORIGINAL_LOGIN()函數(shù)可以參考:ORIGINAL_LOGIN (Transact-SQL),EVENTDATA()函數(shù)用法可以參考:EVENTDATA (Transact-SQL)
?
(二) 有些時候,你數(shù)據(jù)庫可能有許多個登陸帳號,而你更希望的是限制IP,而登陸名跟IP并沒有直接的關聯(lián),那這應該怎么實現(xiàn)呢?
首先創(chuàng)建一個Logon_DB數(shù)據(jù)庫,再創(chuàng)建一個ValidIP表,在表中插入<local machine>和192.168.1.195,表示允許本地和IP為192.168.1.195進行登陸,登陸的帳號屬于服務器角色:sysadmin。
--Script3: --創(chuàng)建測試數(shù)據(jù)庫 USE MASTER GO CREATE DATABASE Logon_DB--創(chuàng)建IP過濾表 USE Logon_DB GO CREATE TABLE dbo.ValidIP ( IP NVARCHAR(15), CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED(IP) ); GO--插入過濾IP USE Logon_DB GO INSERT INTO dbo.ValidIP(IP) VALUES('<local machine>'); INSERT INTO dbo.ValidIP(IP) VALUES('192.168.1.195');--創(chuàng)建登錄觸發(fā)器 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <限制本機和指定的IP登陸> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_logon_CheckIP] ON ALL SERVER FOR LOGON AS BEGIN IF IS_SRVROLEMEMBER('sysadmin') = 1 BEGIN DECLARE @IP NVARCHAR(15); SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); IF NOT EXISTS(SELECT IP FROM [Logon_DB].[dbo].[ValidIP] WHERE IP = @IP) ROLLBACK;END; END;這次我們在IP為:192.168.1.120的機器上進行測試,這個IP之前是允許使用test帳號登陸的(tr_connection_limit),這次使用sa這個帳號登陸,返回了Figure3的錯誤信息,這是因為它違反了登陸觸發(fā)器tr_logon_CheckIP的規(guī)則。
(Figure3:sa用戶登陸錯誤信息)
在IP為192.168.1.195的機器上使用sa這個帳號成功登陸,再次執(zhí)行Script2腳本,返回的列表如Figure4所示。
(Figure4:用戶登陸信息)
?
(三) 還有一種場景,我們需要限制某些用戶只能在指定的時間內登陸數(shù)據(jù)庫,比如業(yè)務上某些運用只能在晚上跑的,通過這個登陸觸發(fā)器,可以防止運用修改執(zhí)行時間在白天中運行。
首先我們創(chuàng)建一個名為nightworker的登陸名,再創(chuàng)建一個LogonBlockedLog的登陸攔截日志表,接著創(chuàng)建登陸觸發(fā)器:tr_logon_CheckTime,在早上7:00之后至晚上18:00之前(BETWEEN 7 AND 17)是不允許nightworker帳號登陸數(shù)據(jù)庫的。
--Script4: --創(chuàng)建nightworker登錄賬號 CREATE LOGIN nightworker WITH PASSWORD = '123' GO--創(chuàng)建登錄攔截日志表 USE Logon_DB GO CREATE TABLE dbo.LogonBlockedLog ( [Id] INT IDENTITY(1,1),[session_id] SMALLINT,[login_time] DATETIME,[host_name] NVARCHAR(128),[original_login_name] NVARCHAR(128),[client_net_address] VARCHAR(48),CONSTRAINT PK_LogonLog PRIMARY KEY CLUSTERED(Id) ); --創(chuàng)建登錄觸發(fā)器 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <限制登陸時間> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_logon_CheckTime] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGINIF ORIGINAL_LOGIN()='nightworker' ANDDATEPART(hh,GETDATE()) BETWEEN 7 AND 17BEGINROLLBACK;INSERT INTO [Logon_DB].[dbo].[LogonBlockedLog]([session_id],[login_time],[host_name],[original_login_name],[client_net_address])SELECT a.[session_id],a.[login_time],a.[host_name],a.[original_login_name],b.[client_net_address]FROM MASTER.sys.dm_exec_sessions a INNER JOIN MASTER.sys.dm_exec_connections b ON a.session_id=b.session_idWHERE a.session_id = @@SPIDEND; END;現(xiàn)在時間是17:20左右,我使用nightworker登陸數(shù)據(jù)庫,這違反了登陸觸發(fā)器:tr_logon_CheckTime,所以提示Figure5的錯誤信息,并且在LogonBlockedLog攔截日志表中出現(xiàn)了一條記錄,這個表可以幫助我們更好的了解登陸賬號的登陸信息。
(Figure5:nightworker用戶登陸錯誤信息)
(Figure6:攔截日志表)
?
(四) 我們進一步模仿MySQL的mysql.User表的用法,用表保存用戶與IP的對應關系,這樣就可以對所有登陸用戶進行控制了。
--Script5: --登陸名與有效IP對應表 USE Logon_DB GO CREATE TABLE [dbo].[ValidLogOn]([Id] INT IDENTITY(1,1) NOT NULL,[LoginName] [sysname] NOT NULL,[ValidIP] [nvarchar](15) NOT NULL,CONSTRAINT [PK_ValidLogOn] PRIMARY KEY CLUSTERED ([Id])) --創(chuàng)建唯一約束索引 CREATE UNIQUE NONCLUSTERED INDEX [IX_ValidLogOn_LV] ON [dbo].[ValidLogOn] ([LoginName] ASC,[ValidIP] ASC )--插入測試數(shù)據(jù) INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'BARXXX\Administrator', N'<local machine>') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'<local machine>') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.48') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'<local machine>') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'127.0.0.1') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'192.168.1.48') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'<local machine>') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.120') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.48') INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.50') --創(chuàng)建登錄觸發(fā)器 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <限制登陸名和IP> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_logon_CheckLogOn] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGINDECLARE @LoginName sysnameDECLARE @IP NVARCHAR(15)SET @LoginName = ORIGINAL_LOGIN();SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); --判斷登錄名和IPIF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) ROLLBACK; END;(Figure7:登陸名與有效IP對應表)
用戶登陸名與IP對應關系表[ValidLogOn],有幾點需要注意的,BARXXX\Administrator這個是Windows 身份驗證中操作系統(tǒng)的帳號,你需要根據(jù)你的實際情況進行修改;IP當中你則需要注意<local machine>和127.0.0.1這些特殊的地址,我個人還是建議在這個表中加入這些信息的。
?
(五) 對上面的再延伸一點,如果想類似Host like 192.168.1.* 這樣進行范圍的過濾,那這又應該怎么實現(xiàn)呢?
可以使用CLR擴展函數(shù)對IP進行判斷,后面會講到這種方式。這里使用SQL就能解決的方法,僅供參考。開放登錄名nightworker在內網(wǎng)所有IP:192.168.1.* 訪問本機的權限。
--Script6: --插入測試數(shù)據(jù) INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.*')--創(chuàng)建登錄觸發(fā)器 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <登陸名和IP過濾,支持IP范圍規(guī)范> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGINDECLARE @LoginName sysnameDECLARE @IP NVARCHAR(15)DECLARE @ValidIP NVARCHAR(15)DECLARE @len INTDECLARE @data XMLDECLARE @blocked BIT;SET @len = 0SET @blocked = 0SET @LoginName = ORIGINAL_LOGIN();SET @data = EVENTDATA();SET @IP = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)');--判斷登錄名和IPIF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) BEGIN--是否存在IP范圍匹配SET @ValidIP = (SELECT TOP 1 [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] LIKE '%[*]');--如果存在就替換Client的IPIF (CHARINDEX('*',@ValidIP) > 0 AND @IP <> '<local machine>' AND @IP <> '127.0.0.1')BEGINDECLARE @SubValidIP NVARCHAR(15)SET @SubValidIP = SUBSTRING(@ValidIP,0,CHARINDEX('*',@ValidIP))SET @len = LEN(@SubValidIP) + 1IF(SUBSTRING(@IP,0,@len) != @SubValidIP)BEGINROLLBACK;SET @blocked = 1ENDENDELSEBEGINROLLBACK;SET @blocked = 1ENDEND--日志記錄INSERT INTO [Logon_DB].[dbo].[LogonLog]([session_id],[login_time],[host_name],[original_login_name],[client_net_address],[XmlEvent],[Blocked])SELECT @data.value('(/EVENT_INSTANCE/SPID)[1]', 'smallint'),GETDATE(),@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'),@data,@blocked END;(Figure7_1:添加192.168.1.*后)
(Figure8:nightworker用戶登錄通過)
(Figure9:sa用戶登錄攔截)
從Figure8和Figure9的對比可以知道,在同一臺機器192.168.1.208使用nightworker和sa有不同的效果,nightworker用戶登錄成功,sa用戶登錄被攔截了。
?
(六) 這里使用CLR擴展函數(shù)對上面類似Host like 192.168.1.* 的實現(xiàn),關于CLR的一些基礎可以參考:SQL Server擴展函數(shù)的基本概念
新建程序集(引用一個寫好的SQLCLR.dll文件)之后執(zhí)行下面的SQL腳本創(chuàng)建標量值函數(shù),創(chuàng)建成功后效果如下圖所示:
--Script7: --CLR實現(xiàn)IP范圍判斷 CREATE FUNCTION [dbo].[RegexIsMatch](@input [nvarchar](max), @pattern [nvarchar](4000)) RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLCLR].[UserDefinedFunctions].[RegexIsMatch] GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'SQLRegex.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegexIsMatch' GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'20' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegexIsMatch' GO(Figure10:注冊成功后)
--Script8: --創(chuàng)建登錄觸發(fā)器 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2013.05.21> -- Description: <登陸名和IP過濾,支持IP范圍規(guī)范> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP_ByCRL] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGINDECLARE @LoginName sysnameDECLARE @IP NVARCHAR(15)DECLARE @ValidIP NVARCHAR(15)DECLARE @len INTDECLARE @data XMLDECLARE @blocked BIT;SET @len = 0SET @blocked = 0SET @LoginName = ORIGINAL_LOGIN();SET @data = EVENTDATA();SET @IP = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)');--判斷登錄名和IPIF NOT EXISTS(SELECT [LoginName] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) BEGINSET @ValidIP = (SELECT TOP 1 [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] LIKE '%[*]');--是否存在IP范圍匹配IF (CHARINDEX('*',@ValidIP) > 0 AND @IP <> '<local machine>' AND @IP <> '127.0.0.1' AND dbo.RegexIsMatch(@IP,@ValidIP) = 'True')SET @blocked = 0ELSEBEGIN ROLLBACK;SET @blocked = 1ENDEND--日志記錄INSERT INTO [Logon_DB].[dbo].[LogonLog]([session_id],[login_time],[host_name],[original_login_name],[client_net_address],[XmlEvent],[Blocked])SELECT @data.value('(/EVENT_INSTANCE/SPID)[1]', 'smallint'),GETDATE(),@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'),@data,@blocked END;四.補充說明(Addon)
(一) 我有一臺服務器A在本地無法使用SSMS登陸(2005),原因是因為我在A上重新安裝DotnetFramework的時候失敗了,但是不影響程序鏈接A的數(shù)據(jù)庫,在服務器B也可以使用SSMS鏈接到服務器A,我維護數(shù)據(jù)庫有時候需要在單用戶下進行表分區(qū)的維護(鎖比較多),如果在正常情況下,我只需要在A停止TCP/IP就可以阻止其它用戶登陸,那這種情況有什么辦法解決呢?對的,讓指定的服務器B的IP能訪問服務器A的數(shù)據(jù)庫,寫個登陸觸發(fā)器,重啟SQL Server服務,維護完之后刪除登陸觸發(fā)器,具體的SQL代碼可以參考Script1的登陸觸發(fā)器:tr_connection_limit。
(Figure11:A服務器SSMS打開錯誤)
(二) 如果在登陸觸發(fā)器中需要讀取表[Logon_DB].[dbo].[ValidLogOn],如果在ON ALL SERVER后面沒有加入WITH EXECUTE AS 'sa',當你使用test或者nightworker登陸就會一直報錯,因為test和nightworker是沒有權限讀取[Logon_DB].[dbo].[ValidLogOn]表。而tr_logon_CheckIP之所以不用WITH EXECUTE AS 'sa'是因為這本身就是對服務器角色sysadmin的邏輯處理。
(三) 測試本地登陸的情況的時候需要測試[.]、[local]、[localhost]、[127.0.0.1]、[ipconfig]里面顯示的內網(wǎng)IP地址這五種情況。([.]、[local]、[localhost]在EVENTDATA()的ClientHost標簽都是顯示<local machine>)
五.疑問(Questions)
(一) 像Figure1、Figure3和Figure5等并沒有清晰顯示登陸錯誤信息。比如:錯誤是什么原因造成的?是哪個登陸觸發(fā)器攔截的?攔截規(guī)則是什么?為了方便用戶清晰了解規(guī)則,我們需要自定義這些錯誤內容。
(二) 如果在tr_logon_CheckIP觸發(fā)器的ROLLBACK之前加入Print語句會出錯,錯誤信息就如Figure3所示,原來可以登陸的,加了這個Print就不行了?為什么?
(三) 創(chuàng)建登陸觸發(fā)器要在服務器角色:sysadmin(比如sa)的權限下執(zhí)行Create腳本,不然會報錯,即使使用了WITH EXECUTE AS 'sa'選項也同樣報錯,具體的官方文檔說明還沒找到。
(四) 為什么在服務器名稱使用localhost登陸的時候會有3條記錄插入到[LogonLog]表的呢?
(Figure12:一次登陸3條記錄)
六.參考文獻(References)
Blocking Users by IP
SQL Server 2008中的代碼安全(二):DDL觸發(fā)器與登錄觸發(fā)器
CREATE TRIGGER (Transact-SQL)
登錄觸發(fā)器
Sql server限制IP訪問方法
sys.dm_exec_sessions (Transact-SQL)
EXECUTE AS (Transact-SQL)
ORIGINAL_LOGIN (Transact-SQL)
EVENTDATA (Transact-SQL)
淺談SQL Server 數(shù)據(jù)庫之觸發(fā)器
事件通知
轉載于:https://www.cnblogs.com/gaizai/archive/2013/05/23/3095145.html
總結
以上是生活随笔為你收集整理的SQL Server 限制IP登陆(登陆触发器运用)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: HelloWorld !
- 下一篇: Word2003文档打不开怎么办?1分钟