當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL Server 限制IP登陆（登陆触发器运用）

發(fā)布時間：2025/6/17 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL Server 限制IP登陆（登陆触发器运用）小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

一.本文所涉及的內容（Contents）

本文所涉及的內容（Contents）

背景（Contexts）

實現(xiàn)代碼（SQL Codes）

補充說明（Addon）

疑問（Questions）

參考文獻（References）

二.背景（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登陆（登陆触发器运用）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： HelloWorld !
下一篇： Word2003文档打不开怎么办？1分钟