太湖手机网站建设,静态网站开发课程网,免费开发平台网站,wordpress分享后查看SQL Server中通用数据库角色权限的处理详解 前言 安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。 最近和同事在做数据库权限清理的事情#xff0c;主要是删除一些账号#xff1b;取消一些账号的较大的权限等#xff0c;例…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 typeP AND namedb_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_descONLINE --开始循环每一个用户数据库排除了上面相关数据库 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 RowIndexsql_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_namedatabase_name; END DROP TABLE #databases; DROP TABLE #sql_text; END 转载于:https://www.cnblogs.com/scwbky/p/10026938.html
