File: atomic_global_db_proxy_grants.result

package info (click to toggle)
mysql-8.0 8.0.43-3
  • links: PTS, VCS
  • area: main
  • in suites: sid
  • size: 1,273,924 kB
  • sloc: cpp: 4,684,605; ansic: 412,450; pascal: 108,398; java: 83,641; perl: 30,221; cs: 27,067; sql: 26,594; sh: 24,181; python: 21,816; yacc: 17,169; php: 11,522; xml: 7,388; javascript: 7,076; makefile: 2,194; lex: 1,075; awk: 670; asm: 520; objc: 183; ruby: 97; lisp: 86
file content (363 lines) | stat: -rw-r--r-- 13,510 bytes parent folder | download
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
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
include/save_binlog_position.inc
# ----------------------------------------------------------------------
# Begin : Tests for GRANT : Global grants
CREATE USER userX, userY, userZ;
include/save_binlog_position.inc
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Case 1 : Trying to create user with empty password
GRANT SUPER ON *.* TO userX, userY, userA;
ERROR 42000: You are not allowed to create a user with GRANT
# Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.user table too
SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
user	Super_priv
userX	N
userY	N
userZ	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SUPER ON.*userX.*userY.*userA.*)
include/assert_binlog_events.inc
# Case 3 : GRANT to a user with empty plugin
# Create userW and get rid of the plugin value for userW
CREATE USER userW IDENTIFIED BY 'abcd';
UPDATE mysql.user SET plugin="" WHERE user like 'userW';
call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
FLUSH PRIVILEGES;
include/save_binlog_position.inc
# Attempt to GRANT privileges to userW
GRANT SUPER ON *.* TO userX, userW, userY;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.user table too
SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
user	Super_priv
userW	N
userX	N
userY	N
userZ	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SUPER ON.*userX.*userW.*userY.*)
include/assert_binlog_events.inc
# Restore userW to its former state
UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
FLUSH PRIVILEGES;
include/save_binlog_position.inc
# Case 4 : Use unprivileged account to create new user using GRANT
CREATE USER userSUPER;
GRANT SUPER ON *.* TO userSUPER WITH GRANT OPTION;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated
include/save_binlog_position.inc
GRANT SUPER ON *.* TO userX, userY, userA , userB;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.user table too
SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
user	Super_priv
userSUPER	Y
userW	N
userX	N
userY	N
userZ	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SUPER ON.*userX.*userY.*userA.*userB)
include/assert_binlog_events.inc
# Case 5 : Valid grant
GRANT SUPER ON *.* TO userX, userY, userZ;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated
Grants for userX, userY, userZ should show SUPER
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT SUPER ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT SUPER ON *.* TO `userZ`@`%`
# Check mysql.user table too
SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
user	Super_priv
userSUPER	Y
userW	N
userX	Y
userY	Y
userZ	Y
# This event sequence pattern MUST be present in binlog: !Q(GRANT SUPER ON.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
DROP USER userW, userX, userY, userZ, userSUPER;
include/save_binlog_position.inc
# End : Tests for GRANT : Global grants
# ----------------------------------------------------------------------
# Begin : Tests for GRANT : DB grants
CREATE USER userX, userY, userZ;
# Grant some DB privileges to some of the users so that
# we cover both path : One creates new entry in mysql.db and
# the other which updates existing entries.
GRANT INSERT ON mysql.* TO userX;
include/save_binlog_position.inc
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Case 1 : Trying to create user with empty password
GRANT SELECT ON mysql.* TO userX, userY, userA;
ERROR 42000: You are not allowed to create a user with GRANT
# Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.db table too
SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
user	db	Insert_priv	Select_priv
userX	mysql	Y	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SELECT ON.*mysql.*userX.*userY.*userA)
include/assert_binlog_events.inc
# Case 3 : GRANT to a user with empty plugin
# Create userW and get rid of the plugin value for userW
CREATE USER userW IDENTIFIED BY 'abcd';
UPDATE mysql.user SET plugin="" WHERE user like 'userW';
call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
FLUSH PRIVILEGES;
include/save_binlog_position.inc
# Attempt to GRANT privileges to userW
GRANT SELECT ON mysql.* TO userX, userW, userY;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should show SUPER
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.db table too
SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
user	db	Insert_priv	Select_priv
userX	mysql	Y	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SELECT ON.*mysql.*userX.*userW.*userY.*)
include/assert_binlog_events.inc
# Restore userW to its former state
UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
FLUSH PRIVILEGES;
# Case 4 : Use unprivileged account to create new user using GRANT
CREATE USER userSELECT;
GRANT SELECT ON mysql.* TO userSELECT WITH GRANT OPTION;
include/save_binlog_position.inc
GRANT SELECT ON mysql.* TO userX, userY, userA , userB;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should show SUPER
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Check mysql.db table too
SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
user	db	Insert_priv	Select_priv
userSELECT	mysql	N	Y
userX	mysql	Y	N
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SELECT ON.*mysql.*userX.*userW.*userA.*userB.*)
include/assert_binlog_events.inc
# Case 5 : Valid grant
GRANT SELECT ON mysql.* TO userX, userY, userZ;
Grants for userX, userY, userZ should show SELECT on mysql.*
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT SELECT, INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
GRANT SELECT ON `mysql`.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
GRANT SELECT ON `mysql`.* TO `userZ`@`%`
# Check mysql.db table too
SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
user	db	Insert_priv	Select_priv
userSELECT	mysql	N	Y
userX	mysql	Y	Y
userY	mysql	N	Y
userZ	mysql	N	Y
# This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT ON.*mysql.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 6 : Wrong set of privileges for DB grant
GRANT SUPER ON mysql.* TO userX, userY, userZ;
ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT SELECT, INSERT ON `mysql`.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
GRANT SELECT ON `mysql`.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
GRANT SELECT ON `mysql`.* TO `userZ`@`%`
# Check mysql.db table too
SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
user	db	Insert_priv	Select_priv
userSELECT	mysql	N	Y
userX	mysql	Y	Y
userY	mysql	N	Y
userZ	mysql	N	Y
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT SUPER ON.*mysql.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
DROP USER userW, userX, userY, userZ, userSELECT;
include/save_binlog_position.inc
# End : Tests for GRANT : DB grants
# ----------------------------------------------------------------------
# Begin : Tests for GRANT : Proxy grants
CREATE USER userX, userY, userZ;
CREATE USER proxied;
include/save_binlog_position.inc
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# Case 1 : Trying to create user with empty password
GRANT PROXY ON proxied TO userX, userY, userA;
ERROR 42000: You are not allowed to create a user with GRANT
# Grants for userX, userY, userZ should not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
# Case 3 : GRANT to a user with empty plugin
# Create userW and get rid of the plugin value for userW
CREATE USER userW IDENTIFIED BY 'abcd';
UPDATE mysql.user SET plugin="" WHERE user like 'userW';
call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
FLUSH PRIVILEGES;
include/save_binlog_position.inc
# Attempt to GRANT privileges to userW
GRANT PROXY ON proxied TO userX, userW, userY;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should show SUPER
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userW.*userY.*)
include/assert_binlog_events.inc
# Restore userW to its former state
UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
FLUSH PRIVILEGES;
include/save_binlog_position.inc
# Case 4 : Use unprivileged account to create new user using GRANT
CREATE USER userPROXY;
GRANT PROXY ON proxied TO userPROXY WITH GRANT OPTION;
include/save_binlog_position.inc
GRANT PROXY ON proxied TO userX, userY, userA, userB;
ERROR 42000: You are not allowed to create a user with GRANT
Grants for userX, userY, userZ should show SUPER
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userY.*userA.*userB.*)
include/assert_binlog_events.inc
# Case 5 : Valid grant
GRANT PROXY ON proxied TO userX, userY, userZ;
Grants for userX, userY, userZ should show SELECT on mysql.*
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT PROXY ON `proxied`@`%` TO `userX`@`%`
SHOW GRANTS FOR userY;
Grants for userY@%
GRANT USAGE ON *.* TO `userY`@`%`
GRANT PROXY ON `proxied`@`%` TO `userY`@`%`
SHOW GRANTS FOR userZ;
Grants for userZ@%
GRANT USAGE ON *.* TO `userZ`@`%`
GRANT PROXY ON `proxied`@`%` TO `userZ`@`%`
# This event sequence pattern MUST be present in binlog: !Q(GRANT PROXY ON.*proxied.*TO.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
DROP USER proxied, userW, userX, userY, userZ, userPROXY;
include/save_binlog_position.inc
# End : Tests for GRANT : Proxy grants
# ----------------------------------------------------------------------