用户管理: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

总结

前言

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

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

总结

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

我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
沟通购买:QQ咨询 淘宝咨询 微信咨询 淘宝店铺
版权申明及联系
本站部分文章参考或来源于网络,如有侵权请联系站长。本站提供相关远程技术服务,有需要可联系QQ
数据库远程运维 » 用户管理:SQL Server中通用数据库角色权限的处理