数据安全之——SQL Server限制IP访问
2017-05-09 09:14阅读：
1.问题背景：
之前有客户朋友在做数据安全加固项目的时候和我聊到，SQL Server能不能像mysql一样设置类似的IP访问白名单、黑名单，以此来加强数据访问安全。我们知道mysql里有张mysql.user表记录了用户的权限信息，其中host、user字段就是用来限制用户IP访问权限的。
当然SQL Server和Oracle是没有这样的权限表的，换个思路，SQL
Server我们可以通过登录触发器来间接实现同样的功能，而oracle
可通过
sqlnet.ora文件中添加CP.VALIDNODE_CHECKING参数来实现
2. 登录触发器
SQL Server
包括三种常规类型的触发器：DML
TRIGGER、DDL
TRIGGER和LOGON TRIGGER。DML触发器是比较常使用的，它主要针对表或视图中的数据修改（INSERT、UPDATE和DELETE 等）在激发任何有效的事件时，将会激发这些触发器，而无论是否会影响任何表行；DDL
触发器用于响应各种数据定义语言 (DDL) 事件。 这些事件主要对应于
Transact-SQL CREATE、ALTER 和 DROP 语句，以及执行类似 DDL
操作的某些系统存储过程。 而登录触发器在遇到 LOGON 事件时触发，LOGON 事件是在建立用户会话时引发的，也是我们此文将要提及的触发器。
3. 限制登录条件
比如说
1） 限制某个login（比如test） 只能在本机或指定IP登录
2） 限制服务器角色（比如sysadmin）只能在本机或指定IP登录
3） 限制某个login（比如testtime）只能在某个时间段登录同时记录非法登录日志
4. 实现过程
1）限制某个login（比如test）
只能在本机或指定IP登录
当检测到登录账号为test时，则会限制该账号只能在本机，或IP地址为192.168.0.108或192.168.0.109上登录。此例涉及到EVENTDATA()函数，有兴趣可以到msdn上查看其使用方法。
代码：
--创建test登录账号
CREATE LOGIN
test
WITH
PASSWORD
=
'test'
GO
--创建登录触发器
ALTER TRIGGER
[restrict_login]
ON ALL
SERVER
WITH
EXECUTE
AS
'sa'
FOR LOGON
AS
BEGIN
IF
ORIGINAL_LOGIN()=
'test'
AND
(SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
'NVARCHAR(15)'))
NOT
IN('','192.168.0.108','192.168.0.109')
ROLLBACK;
END;
若test账号在除'','192.168.0.108','192.168.0.109'之外的服务器上登录时，则会抛出如下错误
但是在本机上使用test账号则能正常登录，使用以下代码查看登录情况：
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
2）限制服务器角色（比如sysadmin）只能在本机或指定IP登录
这里我们效仿mysql里的user表，设置了一个IP表来限制访问IP。
当检测到登录login为sysadmin服务器角色（这个角色可以设为diskadmin,dbcreator等9种服务器角色中的任一种），且登录ip地址不在dbo.connectip定义的有效ip地址内，则拒绝登录访问
代码：
--创建IP过滤表
USE db22
GO
CREATE TABLE
dbo.connectip
(
IP
NVARCHAR(20),
CONSTRAINT
PK_IP
PRIMARY
KEY
CLUSTERED(IP)
);
GO
--插入白名单IP
USE db22
GO
INSERT INTO
dbo.connectip(IP)
VALUES('');
INSERT INTO
dbo.connectip(IP)
VALUES('192.168.0.108');
--创建登录触发器
CREATE TRIGGER
[restrict_login_check]
ON ALL
SERVER
FOR LOGON
AS
BEGIN
