sql server数据库权限(SQL Server中通用数据库角色权限的处理详解)
类别:数据库 浏览量:2376
时间:2021-10-19 06:06:11 sql server数据库权限
SQL Server中通用数据库角色权限的处理详解前言
安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。
最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有db_owner权限,我们取消账号的数据库角色db_owner,授予最低要求的相关权限。但是这种工作完全是一个体力活,而且是吃力不讨好,而且推进很慢。另外,为了管理方便和细化,我们又在常用的数据库角色外,新增了6个通用的数据库角色。
如下截图所示。
另外,为了减少授权工作量和一些重复的体力活,我们创建了一个作业,每天定期执行一个存储过程db_common_role_grant_rigths,这个存储过程的逻辑如下:
1:遍历所有用户数据库(排除了系统数据库以及一些特殊数据库),发现该数据库不存在这些通用数据库角色,那么就创建相关数据库角色。
2:遍历所有用户数据库,为相关数据库角色授权,例如,如果发现某个新增的存储过程,没有授权给db_procedure_execute数据库角色。那么就执行授权操作。
当然目前还在测试、应用阶段,以后会根据具体相关需求,不断完善相关功能。
--================================================================================================================== -- ScriptName : db_common_role_grant_rigths.sql -- Author : 潇湘隐者 -- CreateDate : 2018-09-13 -- Description : 创建数据库角色db_procedure_execute等,并授予相关权限给角色。 -- Note : /****************************************************************************************************************** Parameters : 参数说明 ******************************************************************************************************************** @RoleName : 角色名 ******************************************************************************************************************** Modified Date Modified User Version Modified Reason ******************************************************************************************************************** 2018-09-12 潇湘隐者 V01.00.00 新建该脚本。 2018-09-12 潇湘隐者 V01.00.01 注意@@ROWCOUNT的生效范围;解决循环逻辑问题。 2018-09-26 潇湘隐者 V01.00.02 修正类型为FT(CLR_TABLE_VALUED_FUNCTION)的函数问题。程序集 (CLR) 表值函数 *******************************************************************************************************************/ --=================================================================================================================== USE YourSQLDba; GO IF EXISTS (SELECT 1 FROM sys.procedures WHERE type='P' AND name='db_common_role_grant_rigths') BEGIN DROP PROCEDURE Maint.db_common_role_grant_rigths; END GO CREATE PROCEDURE Maint.db_common_role_grant_rigths AS BEGIN DECLARE @database_id INT; DECLARE @database_name sysname; DECLARE @cmdText NVARCHAR(MAX); DECLARE @prc_text NVARCHAR(MAX); DECLARE @RowIndex INT; IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL DROP TABLE dbo.#databases; CREATE TABLE #databases ( database_id INT, database_name sysname ) IF OBJECT_ID('TempDB.dbo.#sql_text') IS NOT NULL DROP TABLE dbo.#sql_text; CREATE TABLE #sql_text ( sql_id INT IDENTITY(1,1), sql_cmd NVARCHAR(MAX) ) INSERT INTO #databases SELECT database_id , name FROM sys.databases WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'distribution', 'ReportServer', 'ReportServerTempDB', 'YourSQLDba' ) AND state = 0; --state_desc=ONLINE --开始循环每一个用户数据库(排除了上面相关数据库) WHILE 1= 1 BEGIN SELECT TOP 1 @database_name= database_name FROM #databases ORDER BY database_id; IF @@ROWCOUNT =0 BREAK; --PRINT(@database_name); -- SP_EXECUTESQL 中切换数据库不能当参数传入。 --创建数据库角色db_procedure_execute SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10) SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_procedure_execute'') BEGIN CREATE ROLE [db_procedure_execute] AUTHORIZATION [dbo]; END ' + CHAR(10); --创建数据库角色db_function_execute SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_function_execute'') BEGIN CREATE ROLE [db_function_execute] AUTHORIZATION [dbo]; END' + CHAR(10); --创建数据库角色db_view_table_definition SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_table_definition'') BEGIN CREATE ROLE [db_view_table_definition] AUTHORIZATION [dbo]; END ' + CHAR(10); --创建数据库角色db_view_view_definition SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_view_definition'') BEGIN CREATE ROLE [db_view_view_definition] AUTHORIZATION [dbo]; END ' + CHAR(10); --创建数据库角色db_view_procedure_definition SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_procedure_definition'') BEGIN CREATE ROLE [db_view_procedure_definition] AUTHORIZATION [dbo]; END ' + CHAR(10); --创建数据库角色db_view_function_definition SELECT @cmdText += 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name =''db_view_function_definition'') BEGIN CREATE ROLE [db_view_function_definition] AUTHORIZATION [dbo]; END ' + CHAR(10); --PRINT @cmdText; -- EXECUTE SP_EXECUTESQL @cmdText; EXECUTE (@cmdText); --给角色db_procedure_execute授权 SET @cmdText ='USE ' + QUOTENAME(@database_name) + ';' SET @cmdText +='INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT EXECUTE ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_procedure_execute;'' FROM sys.procedures s WHERE NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_procedure_execute''))'; EXECUTE SP_EXECUTESQL @cmdText; --给角色db_function_execute(标量函数授权) SET @cmdText ='USE ' + QUOTENAME(@database_name) + ';' SET @cmdText += 'INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT EXEC ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_function_execute; '' FROM sys.all_objects s WHERE SCHEMA_NAME(schema_id) NOT IN (''sys'', ''INFORMATION_SCHEMA'') AND NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id =USER_ID(''db_function_execute'') ) AND ( s.[type] = ''FN'' OR s.[type] = ''AF'' OR s.[type] = ''FS'' --OR s.[type] = ''FT'' ) ;' EXECUTE SP_EXECUTESQL @cmdText; --给角色db_function_execute(表值函数授权) SET @cmdText ='USE ' + @database_name + ';' SET @cmdText += 'INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT SELECT ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_function_execute;'' FROM sys.all_objects s WHERE SCHEMA_NAME(schema_id) NOT IN (''sys'', ''INFORMATION_SCHEMA'') AND NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_function_execute'')) AND ( s.[type] = ''TF'' OR s.[type] = ''IF'' ) ; ' EXECUTE SP_EXECUTESQL @cmdText; --查看存储过程定义授权 SET @cmdText ='USE ' + @database_name + ';' SET @cmdText +=' INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_view_procedure_definition;'' FROM sys.procedures s WHERE NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_view_procedure_definition''))' EXECUTE(@cmdText); --查看函数定义的授权 SET @cmdText ='USE ' + @database_name + ';' SELECT @cmdText += 'INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_view_function_definition;'' FROM sys.objects s WHERE type_desc IN (''SQL_SCALAR_FUNCTION'', ''SQL_TABLE_VALUED_FUNCTION'', ''AGGREGATE_FUNCTION'' ) AND NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_view_function_definition''))'; EXECUTE SP_EXECUTESQL @cmdText; --查看表定义的授权 SET @cmdText ='USE ' + @database_name + ';' SET @cmdText +='INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_view_table_definition ;'' FROM sys.tables s WHERE NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_view_table_definition''))'; EXECUTE SP_EXECUTESQL @cmdText; --查看视图定义的授权 SET @cmdText ='USE ' + @database_name + ';' SET @cmdText +='INSERT INTO #sql_text(sql_cmd) SELECT ''GRANT VIEW DEFINITION ON '' + SCHEMA_NAME(schema_id) + ''.'' + QUOTENAME(name) + '' TO db_view_view_definition; '' FROM sys.views s WHERE NOT EXISTS ( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = USER_ID(''db_view_view_definition''))'; EXECUTE SP_EXECUTESQL @cmdText; WHILE 1= 1 BEGIN SELECT TOP 1 @RowIndex=sql_id, @cmdText = 'USE ' + @database_name + '; '+ sql_cmd FROM #sql_text ORDER BY sql_id; IF @@ROWCOUNT =0 BREAK; PRINT(@cmdText); EXECUTE(@cmdText); DELETE FROM #sql_text WHERE sql_id =@RowIndex END DELETE FROM #databases WHERE database_name=@database_name; END DROP TABLE #databases; DROP TABLE #sql_text; END
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对开心学习网的支持。
您可能感兴趣
- sqlserver创建带参数的存储过程(SQLServer存储过程实现单条件分页)
- 完整版sqlserver2019卸载教程(Sql Server 2012完全卸载方法 只需8步轻松卸载)
- sql server查询操作怎么做(sqlserver分页查询处理方法小结)
- navicat如何连接sql数据库(Navicat 连接SQLServer数据库图文步骤)
- sqlserver2008收缩数据文件(SQL2008 详解直接将XML存入到SQL中)
- sqlserver实例全库备份(sql server通过脚本进行数据库压缩全备份的方法推荐)
- sql server2008如何升级到2012(MS sqlserver 2008数据库转换成2000版本的方法)
- sqlserver替换脚本(SQL Server中对数据截取替换的方法详解)
- sqlserver常用配置调整(sql server性能调优 I/O开销的深入解析)
- sqlserver索引实例(SQL Server索引的原理深入解析)
- sql server怎么导出数据库(sqlserver复制数据库的方法步骤图文)
- sqlserver触发器循环条件(SQL Server 利用触发器对多表视图进行更新的实现方法)
- SQLServer中JSON文档型数据的查询问题解决(SQLServer中JSON文档型数据的查询问题解决)
- sqlserver怎么显示变量所占字节数(SQL Server中关于基数估计计算预估行数的一些方法探讨)
- sqlserver恢复delete数据(SQL Server数据库的三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢)
- sqlserver中有几种锁定模式(SQL Server 开窗函数 Over代替游标的使用详解)
- 刘韬涛丁子贺小品《根治低头族》台词剧本(刘韬涛丁子贺小品根治低头族台词剧本)
- 看完《夺冠》,黄渤的演技我实在夸不起来,彭昱畅反令人惊喜(黄渤的演技我实在夸不起来)
- 黄渤泪目 我的痴呆父亲,我内心永远的痛(黄渤泪目我的痴呆父亲)
- 蒜苔和鱿鱼尾巴一起炒,味道特别棒,又脆又嫩,有滋又有味(蒜苔和鱿鱼尾巴一起炒)
- 鱿鱼炒蒜苔不是黑暗料理,这样做清香扑鼻,鲜美脆嫩,开胃又下饭(鱿鱼炒蒜苔不是黑暗料理)
- 蒜苔炒鱿鱼(蒜苔炒鱿鱼)
热门推荐
- php开发详细步骤(php源码的安装方法和实例)
- 程序员面试需要注意的地方
- sql版本号处理并发(SQL server高并发生成唯一订单号的方法实现)
- thinkphp分页效果怎样(thinkphp5+layui实现的分页样式示例)
- 如何设置bootstrap轮播图的速度(bootstrap实现轮播图效果)
- vue各种模块(一篇文章学会Vue中间件管道)
- vue过滤器filters怎么用(如何使用vue过滤器filter)
- docker desktop怎么安装共识容器(安装Docker Desktop报错WSL 2 installation is incomplete的问题解决报错)
- sqlserver函数条件判断(Sql Server 开窗函数Over的使用实例详解)
- css文字图片垂直居中对齐(CSS设置文字图片垂直居中的方法总结)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9