SQL Server中通用數(shù)據(jù)庫角色權限的處理詳解

更新時間：2018年10月10日 09:49:14 作者：瀟湘隱者

這篇文章主要給大家介紹了關于SQL Server中通用數(shù)據(jù)庫角色權限處理的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

前言

安全性是所有數(shù)據(jù)庫管理系統(tǒng)的一個重要特征。理解安全性問題是理解數(shù)據(jù)庫管理系統(tǒng)安全性機制的前提。

最近和同事在做數(shù)據(jù)庫權限清理的事情，主要是刪除一些賬號；取消一些賬號的較大的權限等，例如，有一些有db_owner權限，我們取消賬號的數(shù)據(jù)庫角色db_owner，授予最低要求的相關權限。但是這種工作完全是一個體力活，而且是吃力不討好，而且推進很慢。另外，為了管理方便和細化，我們又在常用的數(shù)據(jù)庫角色外，新增了6個通用的數(shù)據(jù)庫角色。

如下截圖所示。

另外，為了減少授權工作量和一些重復的體力活，我們創(chuàng)建了一個作業(yè)，每天定期執(zhí)行一個存儲過程db_common_role_grant_rigths，這個存儲過程的邏輯如下：

1：遍歷所有用戶數(shù)據(jù)庫（排除了系統(tǒng)數(shù)據(jù)庫以及一些特殊數(shù)據(jù)庫），發(fā)現(xiàn)該數(shù)據(jù)庫不存在這些通用數(shù)據(jù)庫角色，那么就創(chuàng)建相關數(shù)據(jù)庫角色。

2：遍歷所有用戶數(shù)據(jù)庫，為相關數(shù)據(jù)庫角色授權，例如，如果發(fā)現(xiàn)某個新增的存儲過程，沒有授權給db_procedure_execute數(shù)據(jù)庫角色。那么就執(zhí)行授權操作。

當然目前還在測試、應用階段，以后會根據(jù)具體相關需求，不斷完善相關功能。

--==================================================================================================================
--  ScriptName   :   db_common_role_grant_rigths.sql
--  Author    :   瀟湘隱者 
--  CreateDate   :   2018-09-13
--  Description   :   創(chuàng)建數(shù)據(jù)庫角色db_procedure_execute等，并授予相關權限給角色。
--  Note     :   
/******************************************************************************************************************
  Parameters    :         參數(shù)說明
********************************************************************************************************************
    @RoleName   :   角色名
********************************************************************************************************************
 Modified Date Modified User  Version     Modified Reason
********************************************************************************************************************
 2018-09-12  瀟湘隱者   V01.00.00  新建該腳本。
 2018-09-12  瀟湘隱者   V01.00.01  注意@@ROWCOUNT的生效范圍;解決循環(huán)邏輯問題。
 2018-09-26  瀟湘隱者   V01.00.02  修正類型為FT(CLR_TABLE_VALUED_FUNCTION)的函數(shù)問題。程序集 (CLR) 表值函數(shù)
*******************************************************************************************************************/
--===================================================================================================================
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 
 
 
--開始循環(huán)每一個用戶數(shù)據(jù)庫（排除了上面相關數(shù)據(jù)庫）
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 中切換數(shù)據(jù)庫不能當參數(shù)傳入。
 
 --創(chuàng)建數(shù)據(jù)庫角色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);
 
 
 
 --創(chuàng)建數(shù)據(jù)庫角色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);
 
 
 --創(chuàng)建數(shù)據(jù)庫角色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);
 
 --創(chuàng)建數(shù)據(jù)庫角色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);
 
 --創(chuàng)建數(shù)據(jù)庫角色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);
 
  --創(chuàng)建數(shù)據(jù)庫角色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(標量函數(shù)授權)
 
  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(表值函數(shù)授權)
  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);
 
  --查看函數(shù)定義的授權
  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數(shù)據(jù)庫連接查詢和子查詢實戰(zhàn)案例
子查詢(嵌套查詢)子查詢也稱嵌套查詢,是指一個SELECT查詢語句可以嵌入另一個SELECT查詢語句之中,下面這篇文章主要給大家介紹了關于SQL?Server數(shù)據(jù)庫連接查詢和子查詢的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下
2023-04-04
存儲過程解密(破解函數(shù),過程,觸發(fā)器,視圖.僅限于SQLSERVER2000)
解密指定存儲過程 exec sp_decrypt '存儲過程名'
2009-05-05
SQL實現(xiàn)模糊查詢的四種方法總結
本文主要介紹了SQL實現(xiàn)模糊查詢的四種方法總結，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-07-07
恢復.mdf 數(shù)據(jù)庫步驟
完成后一般就可以訪問數(shù)據(jù)庫中的數(shù)據(jù)了,這時,數(shù)據(jù)庫本身一般還要問題,解決辦法是,利用數(shù)據(jù)庫的腳本創(chuàng)建一個新的數(shù)據(jù)庫,并將數(shù)據(jù)導進去就行了.
2009-07-07
SQL Server查詢條件IN中能否使用變量的示例詳解
這篇文章主要介紹了SQL Server查詢條件IN中能否使用變量,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2021-01-01
SQL Server 服務由于登錄失敗而無法啟動
當域的密碼被修改過后，相應服務使用的登陸驗證信息不會自動更新需要手動來更新，才能解決此問題。如果大家嫌比較麻煩，還是像上面的解決方案那樣直接將登陸身份修改為“本地系統(tǒng)帳戶”比較簡單。
2010-05-05
SQL Server中使用Linkserver連接Oracle的方法
SQL Server提供了Linkserver來連接不同數(shù)據(jù)庫上的同構或異構數(shù)據(jù)源。下面以圖示介紹一下連接Oracle的方式
2012-07-07
mybatis調用sqlserver存儲過程返回結果集的方法
這篇文章主要介紹了mybatis調用sqlserver存儲過程返回結果集的方法,本文分兩種方法給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2021-05-05
SQL Server誤區(qū)30日談第19天 Truncate表的操作不會被記錄到日志
這個誤區(qū)也同樣流傳已久，我想是時候通過一些Demo進行揭穿了
2013-01-01
SQL Server 更改DB的Collation
經(jīng)常發(fā)現(xiàn)同事爲了更改DB的Collation而刪掉DB，重新創(chuàng)建過。其實，只要一條SQL就可以解，唔使哽麻煩??！
2009-05-05