一、 背景
MySQL由 usename+host 构成用户,在SQL Server没有这样的机制,那SQL Server如何实现类似的安全控制的功能呢?
本文将介绍5种运用登录触发器实现安全控制的场景:
- 限制某登录名(比如sa)只能在本机或者指定的IP中登录;
- 限制服务器角色(比如sysadmin)只能在本机或者指定的IP中登录;
- 限制某登录名(比如sa)只能某时间段内登录;
- 限制登录名与IP的对应关系,支持多对多关系;
- 限制某登录名可以在某IP段登录(比如192.168.1.*)
二、 实现代码
1. 限制某登录名只能在本机或者指定的IP中登录
创建登录触发器:tr_connection_limit,它会在用户登录的时候触发,通过EVENTDATA()函数返回的客户端的IP,使用ORIGINAL_LOGIN()函数返回的登录名,对IP和登录名进行判断。
实现以下功能:当登录名是test的时候,若登录IP为本地<local machine>或192.168.1.50,192.168.1.120则允许登录,若是其它IP则登录失败。
--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;
关于EXECUTE AS的用法参考:EXECUTE AS (Transact-SQL),ORIGINAL_LOGIN()函数参考:ORIGINAL_LOGIN (Transact-SQL),EVENTDATA()函数用法参考:EVENTDATA (Transact-SQL)
非法ip登录报错如下
2. 限制服务器角色(比如sysadmin)只能在本机或者指定的IP中登录
可以做一个IP白名单表,避免后期重复修改触发器。首先创建一个Logon_DB数据库,再创建一个ValidIP表,在表中插入<local machine>和192.168.1.195,表示允许本地和IP为192.168.1.195进行登录,登录的帐号属于服务器角色:sysadmin。
--创建测试数据库
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;
尝试sa用户从192.168.1.120登录,报错信息如下
3. 限制某登录名(比如sa)只能某时间段内登录
比如业务上某些操作只能在晚上跑的,通过这个登录触发器,可以防止用户在白天操作。
首先我们创建一个名为nightworker的登录名,再创建一个LogonBlockedLog的登录拦截日志表,接着创建登录触发器:tr_logon_CheckTime,7:00-17:00不允许nightworker帐号登录数据库。
--创建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;
测试报错信息如下
拦截报错表记录
4. 限制登录名与IP的对应关系,支持多对多关系
进一步地,可以模仿mysql.User表用法,保存用户与IP对应关系,这样就可以对所有登录用户进行控制了。
--登录名与有效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;
有几点需要注意:BARXXX\Administrator是Windows 身份验证中操作系统的帐号,需要根据实际情况进行修改;IP中则需要注意<local machine>和127.0.0.1这些特殊的地址,个人还是建议在这个表中加入这些信息的。
5. 限制某登录名可以在某IP段登录
如果想进行类似Host like 192.168.1.* 这样的范围过滤,可以使用CLR扩展函数对IP进行判断,后面会讲到这种方式。这里使用SQL就能解决的方法,仅供参考。开放登录名nightworker在内网所有IP:192.168.1.* 访问本机的权限。
--插入测试数据
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;
添加192.168.1.*后
nightworker用户登录通过
sa用户登录拦截
6. 使用CLR扩展函数的实现
关于CLR的一些基础可以参考:SQL Server扩展函数的基本概念
新建程序集(引用一个写好的SQLCLR.dll文件)之后执行下面的SQL脚本创建标量值函数:
--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
--创建登录触发器
-- =============================================
-- 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;
三、 补充说明
如果在登录触发器中需要读取表[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>)
参考
SQL Server 2008中的代码安全(二):DDL触发器与登录触发器
sys.dm_exec_sessions (Transact-SQL)
转载:https://blog.csdn.net/Hehuyi_In/article/details/102485227