(六) 这里使用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
(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;
三、补充说明
(一) 我有一台服务器A在本地无法使用SSMS登陆(2005),原因是因为我在A上重新安装DotnetFramework的时候失败了,但是不影响程序链接A的数据库,在服务器B也可以使用SSMS链接到服务器A,我维护数据库有时候需要在单用户下进行表分区的维护(锁比较多),如果在正常情况下,我只需要在A停止TCP/IP就可以阻止其它用户登陆,那这种情况有什么办法解决呢?对的,让指定的服务器B的IP能访问服务器A的数据库,写个登陆触发器,重启SQL Server服务,维护完之后删除登陆触发器,具体的SQL代码可以参考Script1的登陆触发器:tr_connection_limit。
(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>)
四、疑问
(一) 像Figure1、Figure3和Figure5等并没有清晰显示登陆错误信息。比如:错误是什么原因造成的?是哪个登陆触发器拦截的?拦截规则是什么?为了方便用户清晰了解规则,我们需要自定义这些错误内容。
(二) 如果在tr_logon_CheckIP触发器的ROLLBACK之前加入Print语句会出错,错误信息就如Figure3所示,原来可以登陆的,加了这个Print就不行了?为什么?
(三) 创建登陆触发器要在服务器角色:sysadmin(比如sa)的权限下执行Create脚本,不然会报错,即使使用了WITH EXECUTE AS 'sa'选项也同样报错,具体的官方文档说明还没找到。
(四) 为什么在服务器名称使用localhost登陆的时候会有3条记录插入到[LogonLog]表的呢?
(Figure12:一次登陆3条记录)