当前位置:数据库 > > 正文

sql server数据库权限(SQL Server中通用数据库角色权限的处理详解)

时间:2021-10-19 06:06:11类别:数据库

sql server数据库权限

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

前言

安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。

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

如下截图所示。

sql server数据库权限(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
    
  • 总结

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

    上一篇下一篇

    猜您喜欢

    热门推荐