SQL Server中通用数据库角色权限的处理详解
作者:潇湘隐者 时间:2024-01-18 17:24:27
前言
安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。
最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有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
来源:http://www.cnblogs.com/kerrycode/p/9719901.html
标签:sqlserver,通用,角色权限
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python数据归一化及三种方法详解
2023-02-28 01:48:40
![](https://img.aspxhome.com/file/2023/7/78777_0s.gif)
对Python中列表和数组的赋值,浅拷贝和深拷贝的实例讲解
2023-06-04 19:17:50
php+mysql开发的最简单在线题库(在线做题系统)完整案例
2023-08-21 20:03:14
![](https://img.aspxhome.com/file/2023/3/553631_0s.png)
python 全角半角互换的实现示例
2021-02-03 20:30:52
Python BeautifulReport可视化报告代码实例
2023-11-12 14:53:13
![](https://img.aspxhome.com/file/2023/4/64314_0s.png)
好玩的vbs微信小程序之语言播报功能
2023-04-27 12:54:29
![](https://img.aspxhome.com/file/2023/4/107174_0s.gif)
jQuery获取radio选中项的值实例
2024-04-09 19:45:16
![](https://img.aspxhome.com/file/2023/3/137103_0s.jpg)
django将图片保存到mysql数据库并展示在前端页面的实现
2024-01-26 06:59:44
![](https://img.aspxhome.com/file/2023/1/115401_0s.png)
Python常用模块logging——日志输出功能(示例代码)
2022-01-21 15:24:01
SQL Server 数据库备份和还原认识和总结(二)
2012-10-07 10:52:52
python双向链表实现实例代码
2023-05-16 09:38:54
![](https://img.aspxhome.com/file/2023/8/99218_0s.jpg)
python 第三方库的安装及pip的使用详解
2023-12-27 07:06:53
![](https://img.aspxhome.com/file/2023/9/65019_0s.png)
关于利用:first-letter实现首字下沉的一些看法
2010-04-20 17:19:00
![](https://img.aspxhome.com/file/UploadPic/20104/20/first-letter_no_float-38s.png)
python跨文件使用全局变量的实现
2022-03-31 17:24:36
MSSQL中递归SQL查询语句实例说明-
2024-01-28 19:54:01
![](https://img.aspxhome.com/file/2023/4/88264_0s.gif)
Python 的Json 模块编码详解
2023-03-19 08:29:15
Python GUI学习之登录系统界面篇
2023-01-06 14:32:42
![](https://img.aspxhome.com/file/2023/2/72222_0s.png)
python使用ctypes模块调用windowsapi获取系统版本示例
2023-05-04 08:46:48
Python 数据分析教程探索性数据分析
2022-06-25 17:38:38
![](https://img.aspxhome.com/file/2023/3/97423_0s.png)
Navicat for MySQL 与 MySQL-Front比较
2009-02-12 17:33:00
![](https://img.aspxhome.com/file/UploadPic/20092/2009218192314499.jpg)