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
|
--echo #
--echo # WL#14690: Support IF EXISTS and IGNORE UNKNOWN USER optional clauses
--echo # in REVOKE statement
--echo #
CREATE DATABASE wl14690;
CREATE USER u1,u2,u3,u4;
CREATE ROLE r1,r2;
CREATE TABLE wl14690.t(i int, j int);
CREATE FUNCTION wl14690.fun() RETURNS INT DETERMINISTIC CONTAINS SQL RETURN @var1;
SET @@global.partial_revokes = OFF;
--echo # FR1.1.1: REVOKE ... IGNORE UNKNOWN USER where target user/role does not exists
RESET MASTER;
--error ER_REVOKE_GRANTS
REVOKE ALL ON *.* FROM unknown_user;
REVOKE ALL ON *.* FROM unknown_user IGNORE UNKNOWN USER;
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
--error ER_NONEXISTING_GRANT
REVOKE SELECT ON *.* FROM unknown_user;
REVOKE SELECT ON *.* FROM unknown_user IGNORE UNKNOWN USER;
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
--error ER_REVOKE_GRANTS
REVOKE ALL PRIVILEGES, GRANT OPTION FROM unknown_user;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM unknown_user IGNORE UNKNOWN USER;
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
--error ER_NONEXISTING_GRANT
REVOKE PROXY ON u1 FROM unknown_user;
REVOKE PROXY ON u1 FROM unknown_user IGNORE UNKNOWN USER;
--error ER_UNKNOWN_AUTHID
REVOKE r1 FROM unknown_user;
REVOKE r1 FROM unknown_user IGNORE UNKNOWN USER;
SELECT COUNT(*) FROM mysql.user where user = 'unknown_user';
--source include/show_binlog_events.inc
--echo # FR1.1.2: REVOKE ... IGNORE UNKNOWN USER where privilege does not exist
RESET MASTER;
--error ER_NONEXISTING_GRANT
REVOKE ALL ON wl14690.* FROM u1;
--error ER_NONEXISTING_GRANT
REVOKE ALL ON wl14690.* FROM u1 IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
GRANT SELECT, INSERT (i) ON wl14690.t TO u1, u2;
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN ON *.* TO u2;
GRANT CREATE ROUTINE ON wl14690.* TO u1;
GRANT CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO u2;
GRANT EXECUTE ON FUNCTION wl14690.fun TO u1,u3;
--echo # FR1.2.1: REVOKE ... IGNORE UNKNOWN USER where target user/role exists
--echo # and privilege being revoked is granted
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
RESET MASTER;
REVOKE INSERT (i) ON wl14690.t FROM u1, u2 IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo # check that INSERT (i) is revoked.
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--echo # FR1.2.2: REVOKE ... IGNORE UNKNOWN USER with missing target user/role
GRANT INSERT (i) ON wl14690.t TO u1, u2;
GRANT r1 TO u2;
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
RESET MASTER;
--error ER_NONEXISTING_TABLE_GRANT
REVOKE SELECT, INSERT (i) ON wl14690.t FROM u1, u2, unknown_user;
--error ER_NONEXISTING_GRANT
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM unknown_user, u2;
--error ER_UNKNOWN_AUTHID
REVOKE r1 FROM unknown_user, u2;
--echo # check that r1 grant exists for u2
SHOW GRANTS FOR u2;
REVOKE r1 FROM unknown_user, u2 IGNORE UNKNOWN USER;
REVOKE SELECT, INSERT (i) ON wl14690.t FROM u1, u2, unknown_user IGNORE UNKNOWN USER;
REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM unknown_user, u2 IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo # check that SELECT, INSERT (i) are revoked.
SHOW GRANTS FOR u1;
--echo # check that r1 grant is revoked for u2
SHOW GRANTS FOR u2;
--echo # FR1.2.3: REVOKE ... IGNORE UNKNOWN USER with missing target user/role
--echo # and privilege being revoked is not granted
GRANT INSERT (i) ON wl14690.t TO u1, u2;
RESET MASTER;
--error ER_NONEXISTING_TABLE_GRANT
REVOKE INSERT (i) ON wl14690.t FROM u1, u2, u3, unknown_user IGNORE UNKNOWN USER;
--echo # check that INSERT (i) is not revoked.
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--error ER_NONEXISTING_GRANT
REVOKE EVENT, TRIGGER ON wl14690.* FROM u2, u3, unknown_user IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo # check that existing privileges are not revoked.
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--echo # FR1.3.1: REVOKE IF EXISTS where target user/role exists and
--echo # privilege being revoked is not granted
RESET MASTER;
--error ER_NONEXISTING_GRANT
REVOKE PROXY ON u3 FROM u1;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS PROXY ON u3 FROM unknown_user, u1;
--echo # report warning
REVOKE IF EXISTS PROXY ON u3 FROM u1;
REVOKE IF EXISTS PROXY ON u3 FROM r1;
REVOKE ALL ON *.* FROM u1;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON *.* to u1;
--error ER_NONEXISTING_GRANT
REVOKE SELECT ON wl14690.* FROM u1;
--echo # report warning
REVOKE IF EXISTS SELECT ON wl14690.* FROM u1;
REVOKE IF EXISTS SELECT ON wl14690.* FROM r1;
REVOKE IF EXISTS INSERT (i) ON wl14690.t FROM u4;
REVOKE IF EXISTS INSERT (j) ON wl14690.t FROM u4;
REVOKE IF EXISTS ENCRYPTION_KEY_ADMIN, APPLICATION_PASSWORD_ADMIN ON *.* FROM u4;
--error ER_UNKNOWN_AUTHID
REVOKE role1 FROM u1;
REVOKE IF EXISTS role1 FROM u1;
GRANT r1 TO u1;
--error ER_UNKNOWN_AUTHID
REVOKE r1, role1 FROM u1;
--echo # check that r1 grant is not revoked to u1
SHOW GRANTS FOR u1;
REVOKE IF EXISTS role1, r1 FROM u1;
--source include/show_binlog_events.inc
--echo # check that r1 grant is revoked from u1
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--echo # FR1.3.2: REVOKE IF EXISTS with missing target user/role and
--echo # privilege being revoked is granted
RESET MASTER;
--error ER_NONEXISTING_GRANT
REVOKE SELECT ON *.* FROM unknown_user;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS SELECT ON *.* FROM unknown_user;
--source include/show_binlog_events.inc
--echo # FR1.3.3: REVOKE IF EXISTS where target user/role exists and
--echo # privilege being revoked is granted
RESET MASTER;
REVOKE IF EXISTS SELECT, INSERT, UPDATE ON *.* FROM u1;
--source include/show_binlog_events.inc
--echo # FR1.4.1: REVOKE IF EXISTS where multiple target users/roles exists and
--echo # privilege being revoked is granted
REVOKE ALL ON *.* FROM u1;
REVOKE ALL ON *.* FROM u2;
REVOKE ALL ON *.* FROM u3;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on wl14690.* to u1;
GRANT SELECT ON wl14690.* TO u2;
GRANT UPDATE, SELECT ON wl14690.* TO u3;
RESET MASTER;
REVOKE IF EXISTS SELECT ON wl14690.* FROM u1, u2, u3;
--error ER_UNKNOWN_AUTHID
REVOKE role1 FROM u1, u2;
REVOKE IF EXISTS role1 FROM u1, u2;
--source include/show_binlog_events.inc
--echo # SELECT should be revoked for all 3 users
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
SHOW GRANTS FOR u3;
--echo # FR1.4.2: REVOKE IF EXISTS where multiple target users/roles exists and
--echo # privilege being revoked is not granted
REVOKE ALL ON *.* FROM u1;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on *.* to u1;
GRANT SELECT ON *.* TO u2;
GRANT SELECT ON wl14690.* TO u3;
SHOW GRANTS FOR u3;
RESET MASTER;
--error ER_NONEXISTING_GRANT
REVOKE DELETE, SELECT ON wl14690.* FROM u1, u2, u3;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM unknown_user, u1, u2;
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM u1, u2, u3;
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM r1;
--echo # for global level privleges IF EXISTS is implicit, so no warning
REVOKE IF EXISTS SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN
ON *.* FROM u1,u2,u3,u4,r1;
--source include/show_binlog_events.inc
--echo # SELECT should be revoked for u3
SHOW GRANTS FOR u3;
--echo # FR1.4.3: REVOKE IF EXISTS where multiple target users/roles missing and
--echo # privilege being revoked is granted
REVOKE ALL ON *.* FROM u1;
REVOKE ALL ON *.* FROM u2;
REVOKE ALL ON *.* FROM u3;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on *.* to u1;
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO u1;
GRANT UPDATE, SELECT ON *.* TO u2;
GRANT SELECT ON wl14690.* TO u3;
GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO u3;
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
SHOW GRANTS FOR u3;
RESET MASTER;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS UPDATE, SELECT ON *.* FROM u1, u2, unknown_user;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM unknown_user, u3, u1;
--source include/show_binlog_events.inc
--echo # grants should be same
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
SHOW GRANTS FOR u3;
--echo # FR1.5: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with single target user
--echo # and privilege which is not granted
RESET MASTER;
--echo # missing privileges and target user report warning
REVOKE IF EXISTS UPDATE, SELECT ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
--echo # missing privileges and target user
REVOKE IF EXISTS PROXY ON u1 FROM unknown_user IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo # FR1.6.1: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
--echo # user (present) and privilege being revoked is granted
GRANT SELECT, INSERT (i) ON wl14690.t TO u1, u2;
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
RESET MASTER;
REVOKE IF EXISTS INSERT (i) ON wl14690.t FROM u1, u2 IGNORE UNKNOWN USER;
REVOKE IF EXISTS TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM u1, u3
IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo # INSERT should be revoked
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--echo # FR1.6.2: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
--echo # user (present) and privilege being revoked is not granted
RESET MASTER;
REVOKE IF EXISTS DELETE, SELECT ON wl14690.* FROM u1, u2, u3
IGNORE UNKNOWN USER;
REVOKE IF EXISTS SYSTEM_VARIABLES_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN
ON *.* FROM u1, u2 IGNORE UNKNOWN USER;
SHOW GRANTS FOR u1;
SHOW GRANTS FOR u2;
--source include/show_binlog_events.inc
--echo # FR1.6.3: REVOKE IF EXISTS ... IGNORE UNKNOWN USER with multiple target
--echo # user (not present) and privilege being revoked is granted
RESET MASTER;
REVOKE IF EXISTS SELECT ON wl14690.t FROM u1, unknown_user IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo #
--echo # Bug #33899136: WL#14690 Revoking illegal privilege with IF EXISTS
--echo # doesn't issue warning
--echo #
RESET MASTER;
--error ER_ILLEGAL_PRIVILEGE_LEVEL
REVOKE DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM u1;
--echo # should report warning
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM u1;
--error ER_NONEXISTING_GRANT
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user;
--error ER_ILLEGAL_PRIVILEGE_LEVEL
REVOKE DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
--echo # should report warnings
REVOKE IF EXISTS DYNAMIC_PRIV1, DYNAMIC_PRIV2 ON wl14690.* FROM unknown_user IGNORE UNKNOWN USER;
--source include/show_binlog_events.inc
--echo #
--echo # Bug #33899156: WL#14690 Revoking mandatory role with IF EXISTS doesn't
--echo # issue warning
--echo #
SET GLOBAL mandatory_roles=r1;
RESET MASTER;
--error ER_MANDATORY_ROLE
REVOKE r1 FROM u1;
--error ER_MANDATORY_ROLE
REVOKE r1 FROM r2;
--error ER_MANDATORY_ROLE
REVOKE r1 FROM u1,u2,u3,r2;
REVOKE IF EXISTS r1 FROM u1;
REVOKE IF EXISTS r1 FROM r2;
REVOKE IF EXISTS r1 FROM u1,u2,u3,r2;
--source include/show_binlog_events.inc
SET GLOBAL mandatory_roles=default;
SET GLOBAL partial_revokes=default;
#cleanup
DROP USER u1,u2,u3,u4;
DROP ROLE r1,r2;
DROP DATABASE wl14690;
|
