欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server中通用数据库角色权限处理

程序员文章站 2022-07-11 20:40:21
SQL Server中通用数据库角色权限处理 最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有db_owner权限,我们取消账号的数据库角色db_owner,授予最低要求的相关权限。但是这种工作完全是一个体力活,而且是吃力不讨好,而且推进很慢。另外,... ......

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