前言
安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。
最近和同事在做数据库权限清理的事情，主要是删除一些账号；取消一些账号的较大的权限等，例如，有一些有db_owner权限，我们取消账号的数据库角色db_owner，授予最低要求的相关权限。但是这种工作完全是一个体力活，而且是吃力不讨好，而且推进很慢。另外，为了管理方便和细化，我们又在常用的数据库角色外，新增了6个通用的数据库角色。
如下截图所示。
另外，为了减少授权工作量和一些重复的体力活，我们创建了一个作业，每天定期执行一个存储过程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;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 |
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：http://www.cnblogs.com/kerrycode/p/9719901.html
