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     :   

--===================================================================================================================
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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对考高分网的支持。