前言
安全性是所有數據庫管理系統的一個重要特征。理解安全性問題是理解數據庫管理系統安全性機制的前提。
最近和同事在做數據庫權限清理的事情，主要是刪除一些賬號；取消一些賬號的較大的權限等，例如，有一些有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;ENDGO CREATE PROCEDURE Maint.db_common_role_grant_rigthsASBEGIN 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 #databasesSELECT database_id , nameFROM sys.databasesWHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'distribution', 'ReportServer', 'ReportServerTempDB', 'YourSQLDba' ) AND state = 0; --state_desc=ONLINE --開始循環每一個用戶數據庫（排除了上面相關數據庫）WHILE 1= 1BEGIN 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總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答
