SQL Server中通用数据库角色权限的处理详解
前言
安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。
最近和同事在做数据库权限清理的事情，主要是删除一些账号；取消一些账号的较大的权限等，例如，有一些有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
1804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
