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

栏目: 数据库 · SQL Server · 发布时间: 6年前

内容简介:最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有db_owner权限,我们取消账号的数据库角色db_owner,授予最低要求的相关权限。但是这种工作完全是一个体力活,而且是吃力不讨好,而且推进很慢。另外,为了管理方便和细化,我们又在常用的数据库角色外,新增了6个

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

最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有db_owner权限,我们取消账号的数据库角色db_owner,授予最低要求的相关权限。但是这种工作完全是一个体力活,而且是吃力不讨好,而且推进很慢。另外,为了管理方便和细化,我们又在常用的数据库角色外,新增了6个 通用 的数据库角色。如下截图所示。

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

另外,为了减少授权工作量和一些重复的体力活,我们创建了一个作业,每天定期执行一个存储过程 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
 
 
 
 
 

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

海量运维、运营规划之道

海量运维、运营规划之道

唐文 / 电子工业出版社 / 2014-1-1 / 59.00

《海量运维、运营规划之道》作者具有腾讯、百度等中国一线互联网公司多年从业经历,书中依托工作实践,以互联网海量产品质量、效率、成本为核心,从规划、速度、监控、告警、安全、管理、流程、预案、考核、设备、带宽等方面,结合大量案例与读者分享了作者对互联网海量运维、运营规划的体会。 《海量运维、运营规划之道》全面介绍大型互联网公司运维工作所涉及的各个方面,是每个互联网运维工程师、架构师、管理人员不可或......一起来看看 《海量运维、运营规划之道》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器