注册
登录
| 关键词： 39 LoginName NVARCHAR NULL 登录 Value 账号 ClientProgramNam INSTANCE data |
一、应用场景 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%   |
| 本文出处： https://www.toutiao.com/a6835837361201349127/ |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
