SQL Server 限制IP登陆(登陆触发器运用) - 听风吹雨

时间:2024-03-12 11:24:23

SQL Server 限制IP登陆(登陆触发器运用)

2013-05-23 16:51  听风吹雨  阅读(28282)  评论(25编辑  收藏  举报

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 实现代码(SQL Codes)
  4. 补充说明(Addon)
  5. 疑问(Questions)
  6. 参考文献(References)

二.背景(Contexts)

  在MySQL的mysql.User表保存了登陆用户的权限信息,Host和User字段则是关于登陆IP的限制。但是在SQL Server没有这样一个表,那SQL Server有什么办法可以实现类似的安全控制的功能呢?

  SQL Server 包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。DML触发器是比较常使用的,它通常在表或视图中修改数据(INSERT、UPDATE和DELETE 等)为了保证业务数据的完整性和一致性,可以对事务进行回滚等操作;如果你对DDL触发器感兴趣,可以参考:SQL Server DDL触发器运用,里面涉及到DDL触发器的知识;登陆触发器将在本文运用到关于IP登陆限制的解决方案中。

  登陆触发器能为我们解决什么问题呢?本文将为你讲述5种运用登陆触发器的场景:

1) 限制某登录名(比如sa)只能在本机或者指定的IP中登陆;

2) 限制服务器角色(比如sysadmin)只能在本机或者指定的IP中登陆;

3) 限制某登录名(比如sa)只能某时间段内登陆;

4) 限制登录名与IP的对应关系,支持多对多关系;

5) 限制某登录名可以在某IP段登录(比如192.168.1.*),如下图;

image

三.实现代码(SQL Codes)

(一) 我机器的IP是:192.168.1.48,首先我在数据库创建一个test帐号,设置密码为123,接着创建登陆触发器:tr_connection_limit,它会在用户登陆的时候触发,通过EVENTDATA()函数返回的客户端的IP,使用ORIGINAL_LOGIN()函数返回的登陆名,对IP和登录名进行判断。

  当登录名是test的时候,如果登陆的IP地址本地<local machine>或者是192.168.1.50,192.168.1.120就允许登陆,在这之外其它情况的登陆将回滚。登陆失败的如Figure1所示。

--Script1:创建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;

clip_image002

(Figure1:test用户登陆错误信息)

  我在一台IP为192.168.1.115的机器上使用test登陆名登陆我的SQL Server数据库,因为这个IP不在允许的IP列表中,所以出现了Figure1的错误信息。我再使用一台IP为192.168.1.120的机器登陆我的SQL Server数据库,成功登陆了,使用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

clip_image004

(Figure2:用户登陆信息)

  关于Script1脚本中EXECUTE AS的用法可以参考:EXECUTE AS (Transact-SQL),ORIGINAL_LOGIN()函数可以参考:ORIGINAL_LOGIN (Transact-SQL),EVENTDATA()函数用法可以参考:EVENTDATA (Transact-SQL)

 

(二) 有些时候,你数据库可能有许多个登陆帐号,而你更希望的是限制IP,而登陆名跟IP并没有直接的关联,那这应该怎么实现呢?

  首先创建一个Logon_DB数据库,再创建一个ValidIP表,在表中插入<local machine>和192.168.1.195,表示允许本地和IP为192.168.1.195进行登陆,登陆的帐号属于服务器角色:sysadmin。

--Script3:
--创建测试数据库
USE MASTER
GO
CREATE DATABASE Logon_DB

--创建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\');

--创建登录触发器
-- =============================================
-- 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的错误信息,这是因为它违反了登陆触发器tr_logon_CheckIP的规则。

clip_image006

(Figure3:sa用户登陆错误信息)

  在IP为192.168.1.195的机器上使用sa这个帐号成功登陆,再次执行Script2脚本,返回的列表如Figure4所示。

clip_image008

(Figure4:用户登陆信息)

 

(三) 还有一种场景,我们需要限制某些用户只能在指定的时间内登陆数据库,比如业务上某些运用只能在晚上跑的,通过这个登陆触发器,可以防止运用修改执行时间在白天中运行。

  首先我们创建一个名为nightworker的登陆名,再创建一个LogonBlockedLog的登陆拦截日志表,接着创建登陆触发器:tr_logon_CheckTime,在早上7:00之后至晚上18:00之前(BETWEEN 7 AND 17)是不允许nightworker帐号登陆数据库的。

--Script4:
--创建nightworker登录账号
CREATE LOGIN nightworker WITH PASSWORD = \'123\'
GO

--创建登录拦截日志表
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) 
); 

--创建登录触发器
-- =============================================
-- 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
BEGIN
    IF ORIGINAL_LOGIN()=\'nightworker\' AND
    DATEPART(hh,GETDATE()) BETWEEN 7 AND 17
    BEGIN
        ROLLBACK;
        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_id
            WHERE a.session_id = @@SPID
    END;
END;

  现在时间是17:20左右,我使用nightworker登陆数据库,这违反了登陆触发器:tr_logon_CheckTime,所以提示Figure5的错误信息,并且在LogonBlockedLog拦截日志表中出现了一条记录,这个表可以帮助我们更好的了解登陆账号的登陆信息。

clip_image010

(Figure5:nightworker用户登陆错误信息)

clip_image011

(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])
 )
--创建唯一约束索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_ValidLogOn_LV] ON [dbo].[ValidLogOn] 
(
    [LoginName] ASC,
    [ValidIP] ASC
)
 --插入测试数据
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\')
--创建登录触发器
-- =============================================
-- 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
BEGIN
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    SET @LoginName = ORIGINAL_LOGIN();
    SET @IP = (SELECT EVENTDATA().value(\'(/EVENT_INSTANCE/ClientHost)[1]\', \'NVARCHAR(15)\')); 
    --判断登录名和IP
    IF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) 
        ROLLBACK;
END;

clip_image012

(Figure7:登陆名与有效IP对应表)

  用户登陆名与IP对应关系表[ValidLogOn],有几点需要注意的,BARXXX\Administrator这个是Windows 身份验证中操作系统的帐号,你需要根据你的实际情况进行修改;IP当中你则需要注意<local machine>和127.0.0.1这些特殊的地址,我个人还是建议在这个表中加入这些信息的。

 

(五) 对上面的再延伸一点,如果想类似Host like 192.168.1.* 这样进行范围的过滤,那这又应该怎么实现呢?

  可以使用CLR扩展函数对IP进行判断,后面会讲到这种方式。这里使用SQL就能解决的方法,仅供参考。开放登录名nightworker在内网所有IP:192.168.1.* 访问本机的权限。

--Script6:
--插入测试数据
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N\'nightworker\', N\'192.168.1.*\')

--创建登录触发器
-- =============================================
-- Author:        <听风吹雨>
-- Create date:    <2013.05.21>
-- Description:    <登陆名和IP过滤,支持IP范围规范>
-- Blog:        <http://www.cnblogs.com/gaizai/>
-- =============================================
CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP]
ON ALL SERVER WITH EXECUTE AS \'sa\'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    DECLARE @ValidIP NVARCHAR(15)
    DECLARE @len INT
    DECLARE @data XML
    DECLARE @blocked BIT;
    
    SET @len = 0
    SET @blocked = 0
    SET @LoginName = ORIGINAL_LOGIN();
    SET @data = EVENTDATA();
    SET @IP = @data.value(\'(/EVENT_INSTANCE/ClientHost)[1]\', \'NVARCHAR(15)\');
    
    --判断登录名和IP
    IF 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的IP
        IF (CHARINDEX(\'*\',@ValidIP) > 0 AND @IP <> \'<local machine>\' AND @IP <> \'127.0.0.1\')
        BEGIN
            DECLARE @SubValidIP NVARCHAR(15)
            SET @SubValidIP = SUBSTRING(@ValidIP,0,CHARINDEX(\'*\',@ValidIP))
            SET @len = LEN(@SubValidIP) + 1
            IF(SUBSTRING(@IP,0,@len) != @SubValidIP)
            BEGIN
                ROLLBACK;
                SET @blocked = 1
            END
        END
        ELSE
        BEGIN
            ROLLBACK;
            SET @blocked = 1
        END
    END

    --日志记录
    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;

image

(Figure7_1:添加192.168.1.*后)

clip_image014

(Figure8:nightworker用户登录通过)

clip_image016

(Figure9:sa用户登录拦截)

  从Figure8和Figure9的对比可以知道,在同一台机器192.168.1.208使用nightworker和sa有不同的效果,nightworker用户登录成功,sa用户登录被拦截了。

 

(六) 这里使用CLR扩展函数对上面类似Host like 192.168.1.* 的实现,关于CLR的一些基础可以参考:SQL Server扩展函数的基本概念

  新建程序集(引用一个写好的SQLCLR.dll文件)之后执行下面的SQL脚本创建标量值函数,创建成功后效果如下图所示:

--Script7:
--CLR实现IP范围判断
CREATE FUNCTION [dbo].[RegexIsMatch](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLCLR].[UserDefinedFunctions].[RegexIsMatch]
GO

EXEC sys.sp_addextendedproperty @name=N\'SqlAssemblyFile\', @value=N\'SQLRegex.cs\' , @level0type=N\'SCHEMA\',@level0name=N\'dbo\', @level1type=N\'FUNCTION\',@level1name=N\'RegexIsMatch\'
GO

EXEC sys.sp_addextendedproperty @name=N\'SqlAssemblyFileLine\', @value=N\'20\' , @level0type=N\'SCHEMA\',@level0name=N\'dbo\', @level1type=N\'FUNCTION\',@level1name=N\'RegexIsMatch\'
GO

clip_image017

(Figure10:注册成功后)

--Script8:
--创建登录触发器
-- =============================================
-- Author:        <听风吹雨>
-- Create date:    <2013.05.21>
-- Description:    <登陆名和IP过滤,支持IP范围规范>
-- Blog:        <http://www.cnblogs.com/gaizai/>
-- =============================================
CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP_ByCRL]
ON ALL SERVER WITH EXECUTE AS \'sa\'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    DECLARE @ValidIP NVARCHAR(15)
    DECLARE @len INT
    DECLARE @data XML
    DECLARE @blocked BIT;
    
    SET @len = 0
    SET @blocked = 0
    SET @LoginName = ORIGINAL_LOGIN();
    SET @data = EVENTDATA();
    SET @IP = @data.value(\'(/EVENT_INSTANCE/ClientHost)[1]\', \'NVARCHAR(15)\');
    
    --判断登录名和IP
    IF NOT EXISTS(SELECT [LoginName] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) 
    BEGIN
        SET @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 = 0
        ELSE
        BEGIN    
            ROLLBACK;
            SET @blocked = 1
        END
    END

    --日志记录
    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的数据库,在服务器B也可以使用SSMS链接到服务器A,我维护数据库有时候需要在单用户下进行表分区的维护(锁比较多),如果在正常情况下,我只需要在A停止TCP/IP就可以阻止其它用户登陆,那这种情况有什么办法解决呢?对的,让指定的服务器B的IP能访问服务器A的数据库,写个登陆触发器,重启SQL Server服务,维护完之后删除登陆触发器,具体的SQL代码可以参考Script1的登陆触发器:tr_connection_limit。

clip_image019

(Figure11:A服务器SSMS打开错误)

(二) 如果在登陆触发器中需要读取表[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]里面显示的内网IP地址这五种情况。([.]、[local]、[localhost]在EVENTDATA()的ClientHost标签都是显示<local machine>)

五.疑问(Questions)

(一) 像Figure1、Figure3和Figure5等并没有清晰显示登陆错误信息。比如:错误是什么原因造成的?是哪个登陆触发器拦截的?拦截规则是什么?为了方便用户清晰了解规则,我们需要自定义这些错误内容。

(二) 如果在tr_logon_CheckIP触发器的ROLLBACK之前加入Print语句会出错,错误信息就如Figure3所示,原来可以登陆的,加了这个Print就不行了?为什么?

(三) 创建登陆触发器要在服务器角色:sysadmin(比如sa)的权限下执行Create脚本,不然会报错,即使使用了WITH EXECUTE AS \'sa\'选项也同样报错,具体的官方文档说明还没找到。

(四) 为什么在服务器名称使用localhost登陆的时候会有3条记录插入到[LogonLog]表的呢?

clip_image021

(Figure12:一次登陆3条记录)

六.参考文献(References)

Blocking Users by IP

SQL Server 2008中的代码安全(二):DDL触发器与登录触发器

CREATE TRIGGER (Transact-SQL)

登录触发器

Sql server限制IP访问方法

sys.dm_exec_sessions (Transact-SQL)

EXECUTE AS (Transact-SQL)

ORIGINAL_LOGIN (Transact-SQL)

EVENTDATA (Transact-SQL)

浅谈SQL Server 数据库之触发器

事件通知