SQL Server中通用数据库角色权限的处理详解

这篇文章主要给大家介绍了关于SQL Server中通用数据库角色权限处理的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧

前言

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

最近和同事在做数据库权限清理的事情,主要是删除一些账号;取消一些账号的较大的权限等,例如,有一些有db_owner权限,我们取消账号的数据库角色db_owner,授予最低要求的相关权限。但是这种工作完全是一个体力活,而且是吃力不讨好,而且推进很慢。另外,为了管理方便和细化,我们又在常用的数据库角色外,新增了6个通用的数据库角色。

如下截图所示。

SQL Server中通用数据库角色权限的处理详解

另外,为了减少授权工作量和一些重复的体力活,我们创建了一个作业,每天定期执行一个存储过程db_common_role_grant_rigths,这个存储过程的逻辑如下:

1:遍历所有用户数据库(排除了系统数据库以及一些特殊数据库),发现该数据库不存在这些通用数据库角色,那么就创建相关数据库角色。

2:遍历所有用户数据库,为相关数据库角色授权,例如,如果发现某个新增的存储过程,没有授权给db_procedure_execute数据库角色。那么就执行授权操作。

当然目前还在测试、应用阶段,以后会根据具体相关需求,不断完善相关功能。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

–==================================================================================================================

— 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

总结

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

原文链接:http://www.cnblogs.com/kerrycode/p/9719901.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24597/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月22日 上午11:18
下一篇 2022年9月22日 上午11:18
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!