您当前的位置:首页 > 电脑百科 > 数据库 > SQL Server

SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

时间:2020-06-09 09:23:31  来源:  作者:

SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

一、应用场景

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连接数据库的访问。

SQL Server账号控制(控制某些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.)策略启用后,账号登录的时候会出现如下的错误。

SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

 

2.)验证使用某些程序登录。

连接参数中随便设定App=aa,进行登录,出现如下的错误。

SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

 


SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

 

3.)使用允许的程序进行连接,便可以登录。

设定APP=Microsoft SQL Server Management Studio%

SQL Server账号控制(控制某些IP登录时间和某些账号的登录方式)

 



Tags:SQL Server   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
介绍一个SQL Server 2016后新增的功能:查询存储。查询存储的工作原理类似于飞行数据记录器或者黑匣子,不断地收集与查询和计划相关的编译和运行时信息,包括已执行查询的历史记...【详细内容】
2021-08-17  Tags: SQL Server  点击:(65)  评论:(0)  加入收藏
众所周知SQL SERVER是微软的数据库拳头产品,有着图形化友好界面、操作门槛低、部署难度小,一键式安装的特点,受到全球开发者及企业的青睐。从历代版本说起,经典2000版本是划时代...【详细内容】
2021-07-19  Tags: SQL Server  点击:(90)  评论:(0)  加入收藏
要想看懂存储过程的意思,就必须要多看存储过程和实例,多百度查询前人的积累的说明,只有在不断地实践才能形成自己的知识,首先,先看到一些基础的语法,慢慢就看懂存储过程说表达的意...【详细内容】
2021-05-14  Tags: SQL Server  点击:(177)  评论:(0)  加入收藏
在SQL server应用开发过程中,可能会遇到ntext、text 和 image 三种数据类型,这三种数据类型是做什么的,有什么区别呢?下面表格从存储类型、存储数据的最大长度和存储大小三个维...【详细内容】
2021-04-26  Tags: SQL Server  点击:(232)  评论:(0)  加入收藏
在数据库使用过程中,为了数据的安全,除了通过服务器防火墙设置访问权限外,还可以通过在数据库中通过创建触发器的方式来控制用户或特定IP的登录权限。USE master;创建数据库登...【详细内容】
2021-04-23  Tags: SQL Server  点击:(219)  评论:(0)  加入收藏
文章来源:https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw作者:廖学强 1. sqlserver查看实例级别的信息,使用SERVERPROPERTY函数select SERVERPROPERTY ('propertyname...【详细内容】
2021-01-12  Tags: SQL Server  点击:(188)  评论:(0)  加入收藏
因工作需要,长期用到Microsoft SQL Server,很多人还不知道怎么安装和配置这个Microsoft SQL Server,今天我就从头到尾来安装一遍,用截图软件截下图来打上文字或者画上箭头来标示...【详细内容】
2020-09-15  Tags: SQL Server  点击:(109)  评论:(0)  加入收藏
Legal informations for Sage, depending on each country. Address. 修改数据库环境的排序规则登录数据库后,查看当前安装数据库默认排序规则的两种方式方式一、使用SQL S...【详细内容】
2020-08-10  Tags: SQL Server  点击:(213)  评论:(0)  加入收藏
因为发展需求,公司使用了OSI 公司的 PI 系统对于PI系统没有深入的了解,只是根据供应商的资料学习了一些基本内容,因为这个pi系统是从底层DCS 收集数据,然后再在第三方客户端程序...【详细内容】
2020-08-04  Tags: SQL Server  点击:(109)  评论:(0)  加入收藏
本文描述了SQL Server中存储过程和用户定义函数之间的区别。存储过程(Stored Procedure)存储过程只不过是您事先保存好的SQL代码而已,您可以反复使用该代码。 如果您一遍又一...【详细内容】
2020-06-26  Tags: SQL Server  点击:(159)  评论:(0)  加入收藏
▌简易百科推荐
概述我们知道SQL Server是微软公司推出的重要的数据库产品,通常情况下只支持部署在windows平台上。不过令人感到兴奋的是,从SQL Server 2017开始支持 linux系统。此 SQL Serve...【详细内容】
2021-12-17  雪竹聊运维    Tags:SQLSERVER   点击:(13)  评论:(0)  加入收藏
介绍一个SQL Server 2016后新增的功能:查询存储。查询存储的工作原理类似于飞行数据记录器或者黑匣子,不断地收集与查询和计划相关的编译和运行时信息,包括已执行查询的历史记...【详细内容】
2021-08-17  哈囉克里    Tags:SQL Server   点击:(65)  评论:(0)  加入收藏
众所周知SQL SERVER是微软的数据库拳头产品,有着图形化友好界面、操作门槛低、部署难度小,一键式安装的特点,受到全球开发者及企业的青睐。从历代版本说起,经典2000版本是划时代...【详细内容】
2021-07-19  风影互联    Tags:SQL SERVER   点击:(90)  评论:(0)  加入收藏
要想看懂存储过程的意思,就必须要多看存储过程和实例,多百度查询前人的积累的说明,只有在不断地实践才能形成自己的知识,首先,先看到一些基础的语法,慢慢就看懂存储过程说表达的意...【详细内容】
2021-05-14  ITLIFEMAN    Tags:sql server   点击:(177)  评论:(0)  加入收藏
在SQL server应用开发过程中,可能会遇到ntext、text 和 image 三种数据类型,这三种数据类型是做什么的,有什么区别呢?下面表格从存储类型、存储数据的最大长度和存储大小三个维...【详细内容】
2021-04-26  数据超酷  今日头条  Tags:SQL Server   点击:(232)  评论:(0)  加入收藏
在数据库使用过程中,为了数据的安全,除了通过服务器防火墙设置访问权限外,还可以通过在数据库中通过创建触发器的方式来控制用户或特定IP的登录权限。USE master;创建数据库登...【详细内容】
2021-04-23  数据超酷  今日头条  Tags:SQL Server   点击:(219)  评论:(0)  加入收藏
今天给大家梳理Apply关键字用法,希望对大家能有所帮助!1、概念介绍APPLy关键字是SQLServer版本中开始提供的一个系统关键字。APPLY的功能同联接很类似,APPLY运算分左右两个部...【详细内容】
2021-04-16    数据库技术分享社区  Tags:Apply关键字   点击:(217)  评论:(0)  加入收藏
文章来源:https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw作者:廖学强 1. sqlserver查看实例级别的信息,使用SERVERPROPERTY函数select SERVERPROPERTY ('propertyname...【详细内容】
2021-01-12      Tags:SQL Server   点击:(188)  评论:(0)  加入收藏
作为程序员来说,与数据库打交道是十分频繁的分页查询是一个开发者必须掌握的基本知识点,目前整理了下面三种SQLServer分页查询语句的写法,仅供参考。一、Top Not IN 方式(查询...【详细内容】
2020-12-29      Tags:分页查询   点击:(174)  评论:(0)  加入收藏
因工作需要,长期用到Microsoft SQL Server,很多人还不知道怎么安装和配置这个Microsoft SQL Server,今天我就从头到尾来安装一遍,用截图软件截下图来打上文字或者画上箭头来标示...【详细内容】
2020-09-15      Tags:SQL Server2000   点击:(109)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条