前言
安全性是所有數(shù)據(jù)庫(kù)管理系統(tǒng)的一個(gè)重要特征。理解安全性問題是理解數(shù)據(jù)庫(kù)管理系統(tǒng)安全性機(jī)制的前提。
最近和同事在做數(shù)據(jù)庫(kù)權(quán)限清理的事情，主要是刪除一些賬號(hào)；取消一些賬號(hào)的較大的權(quán)限等，例如，有一些有db_owner權(quán)限，我們?nèi)∠~號(hào)的數(shù)據(jù)庫(kù)角色db_owner，授予最低要求的相關(guān)權(quán)限。但是這種工作完全是一個(gè)體力活，而且是吃力不討好，而且推進(jìn)很慢。另外，為了管理方便和細(xì)化，我們又在常用的數(shù)據(jù)庫(kù)角色外，新增了6個(gè)通用的數(shù)據(jù)庫(kù)角色。
如下截圖所示。
另外，為了減少授權(quán)工作量和一些重復(fù)的體力活，我們創(chuàng)建了一個(gè)作業(yè)，每天定期執(zhí)行一個(gè)存儲(chǔ)過程db_common_role_grant_rigths，這個(gè)存儲(chǔ)過程的邏輯如下：
1：遍歷所有用戶數(shù)據(jù)庫(kù)（排除了系統(tǒng)數(shù)據(jù)庫(kù)以及一些特殊數(shù)據(jù)庫(kù)），發(fā)現(xiàn)該數(shù)據(jù)庫(kù)不存在這些通用數(shù)據(jù)庫(kù)角色，那么就創(chuàng)建相關(guān)數(shù)據(jù)庫(kù)角色。
2：遍歷所有用戶數(shù)據(jù)庫(kù)，為相關(guān)數(shù)據(jù)庫(kù)角色授權(quán)，例如，如果發(fā)現(xiàn)某個(gè)新增的存儲(chǔ)過程，沒有授權(quán)給db_procedure_execute數(shù)據(jù)庫(kù)角色。那么就執(zhí)行授權(quán)操作。
當(dāng)然目前還在測(cè)試、應(yīng)用階段，以后會(huì)根據(jù)具體相關(guān)需求，不斷完善相關(guān)功能。
--==================================================================================================================
-- ScriptName : db_common_role_grant_rigths.sql
-- Author : 瀟湘隱者
-- CreateDate : 2018-09-13
-- Description : 創(chuàng)建數(shù)據(jù)庫(kù)角色db_procedure_execute等，并授予相關(guān)權(quán)限給角色。
-- 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)每一個(gè)用戶數(shù)據(jù)庫(kù)（排除了上面相關(guān)數(shù)據(jù)庫(kù)）
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ù)庫(kù)不能當(dāng)參數(shù)傳入。
--創(chuàng)建數(shù)據(jù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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ù)庫(kù)角色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授權(quán)
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(標(biāo)量函數(shù)授權(quán))
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ù)授權(quán))
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;
--查看存儲(chǔ)過程定義授權(quán)
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ù)定義的授權(quán)
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;
--查看表定義的授權(quán)
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;
--查看視圖定義的授權(quán)
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
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- sqlserver 2000中每個(gè)服務(wù)器角色的解釋
- SQL SERVER 利用存儲(chǔ)過程查看角色和用戶信息的寫法
- asp.net+sqlserver實(shí)現(xiàn)的簡(jiǎn)單高效的權(quán)限設(shè)計(jì)示例
- SQLServer 2005 控制用戶權(quán)限訪問表圖文教程
- SQL Server SA權(quán)限總結(jié)經(jīng)典技術(shù)
- sql server 2005用戶權(quán)限設(shè)置深入分析
