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

总结

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