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

数据库 2024/11/23 佚名

3 2 1

前言

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

最近和同事在做数据库权限清理的事情，主要是删除一些账号；取消一些账号的较大的权限等，例如，有一些有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

总结

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

sql,server使用教程,sqlserver权限设置,sql语句

标签：

sql,server使用教程,sqlserver权限设置,sql语句

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

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

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

暂无“SQL Server中通用数据库角色权限的处理详解”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

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

sql,server使用教程,sqlserver权限设置,sql语句

SQL Server中Table字典数据的查询SQL示例代码

SQL Server数据库的三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式

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

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

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

sql,server使用教程,sqlserver权限设置,sql语句

SQL Server中Table字典数据的查询SQL示例代码

SQL Server数据库的三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式

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

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存