一、应用场景
1.控制A账号上班时间不允许从某些段的IP进行登录数据库。
2.控制B账号登录数据库只能使用某些程序进行登录。
3.记录所有账号的登录来源。（根据需要可以不用）
注意：此方法，一定要在本地经过严格测试后再使用。
二、实现方式。
1.创建测试数据库。
CREATE DATABASE TestDB
2.创建Config用于控制账号的配置。
CREATE TABLE [dbo].[Config](
[LoginName] [sysname] NOT NULL,
[Type] [VARCHAR](50) NOT NULL,
[Value] [NVARCHAR](50) NOT NULL,
[IsEnabled] [BIT] NOT NULL,
PRIMARY KEY CLUSTERED
(
[LoginName] ASC,
[Type] ASC,
[Value] ASC,
[IsEnabled] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
3.配置说明 ：
1.）B账号只能使用下面的程序连接数据库。
Red Gate Software%
Microsoft SQL Server Management Studio%
dbForge SQL Complete%
2.）控制A账号从84,85段IP连接数据库的访问。
4.创建表ServerLoginLog用于记录登录信息。
CREATE TABLE [dbo].[ServerLoginLog](
[LogID] [BIGINT] IDENTITY(1,1) NOT NULL,
[SPID] [SMALLINT] NOT NULL,
[LoginName] [sysname] NOT NULL,
[ClientHost] [NVARCHAR](200) NOT NULL,
[ClientHostName] [NVARCHAR](200) NULL,
[ClientProgramName] [NVARCHAR](200) NOT NULL,
[LoginType] [sysname] NOT NULL,
[EventType] [sysname] NULL,
[XmlEvent] [XML] NOT NULL,
[ServerName] [NVARCHAR](200) NOT NULL,
[SID] [NVARCHAR](200) NOT NULL,
[PostTime] [DATETIME2](3) NOT NULL,
[CreateTime] [DATETIME2](3) NOT NULL,
PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
5.在实例上创建触发器记录账号的登录信息。
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_connection_limit]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @data XML;
DECLARE @spidOfHostName NVARCHAR(100)='';
DECLARE @spidOfProgramName NVARCHAR(100)='';
DECLARE @spid SMALLINT=0;
SET @data = EVENTDATA();
SET @spid= @data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT');
SELECT @spidOfHostName=dess.HOST_NAME
,@spidOfProgramName=dess.program_name
FROM sys.dm_exec_sessions dess(NOLOCK)
LEFT JOIN sys.dm_exec_requests der(NOLOCK) ON der.session_id=dess.session_id
WHERE dess.session_id=@spid
INSERT INTO [ServerLog].[dbo].[ServerLoginLog] ([SPID],[SID],[ClientHost],[ClientHostName],[LoginName]
,[LoginType],[EventType],[XmlEvent],[ServerName],[ClientProgramName],[PostTime],[CreateTime])
VALUES
(
@data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT'),
@data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(200)'),
@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(200)'),
ISNULL(@spidOfHostName,''),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
@data,
@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),
ISNULL(@spidOfProgramName,''),
@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
GETDATE()
);
END
GO
ENABLE TRIGGER [trg_connection_limit] ON ALL SERVER
GO
6.在ServerLoginLog中创建表触发器，当登录的账号不满足相关测试时进行回滚不允许登录。
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*日志登陆初发器*/
CREATE TRIGGER [dbo].[trg_ServerLoginLog_insert] ON [dbo].[ServerLoginLog]
AFTER INSERT
AS
BEGIN
DECLARE @LoginName NVARCHAR(200);
DECLARE @ClientProgramName NVARCHAR(200);
DECLARE @ClientHost NVARCHAR(200);
SELECT @LoginName = ied.LoginName ,
@ClientProgramName = ied.ClientProgramName ,
@ClientHost = ClientHost
FROM Inserted ied;
IF @LoginName = 'sa'
OR @LoginName = 'Admin'
BEGIN
IF NOT EXISTS ( SELECT *
FROM Config cf
WHERE cf.LoginName = @LoginName
AND cf.[Type] = 'allow_client_program_name'
AND cf.IsEnabled = 1
AND @ClientProgramName LIKE cf.Value )
BEGIN
PRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端是['
+ @ClientProgramName + ']';
ROLLBACK;
END;
END;
IF EXISTS ( SELECT *
FROM Config cf
WHERE cf.LoginName = @LoginName
AND cf.[Type] = 'not_allow_ip'
AND cf.IsEnabled = 1
AND cf.Value = @ClientHost )
BEGIN
PRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端IP是[' + @ClientHost
+ ']';
ROLLBACK;
END;
END;
GO
7.启用启用访问策略.
WITH t AS(
SELECT * FROM Config cf WHERE cf.LoginName='Ttest'
) UPDATE t SET IsEnabled=1; ---启用访问策略(0时禁用范围策略)
8.验证策略是否生效。
1.）策略启用后，账号登录的时候会出现如下的错误。
2.）验证使用某些程序登录。
连接参数中随便设定App=aa,进行登录，出现如下的错误。
3.）使用允许的程序进行连接，便可以登录。
设定APP=Microsoft SQL Server Management Studio%