Debian Codegraph @

mysql-test/suite/auth_sec/t/atomic_revokes.test

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 --echo # ----------------------------------------------------------------------
8
9 --echo # Begin : Tests for REVOKE on global, db and proxy privileges
10
11 CREATE USER userX, userY, userA;
12 CREATE DATABASE db1;
13
14 GRANT SUPER ON *.* TO userX, userA;
15 GRANT SELECT ON db1.* TO userX, userA;
16 GRANT PROXY ON userY TO userX, userA;
17
18 --source include/save_binlog_position.inc
19
20 --echo # Initial set of grants for userX
21 SHOW GRANTS FOR userX;
22
23 --echo # Case 1 : Revoke from non-existing user
24
25 --error ER_NONEXISTING_GRANT
26 REVOKE SUPER ON *.* FROM useX, userZ;
27
28 --error ER_NONEXISTING_GRANT
29 REVOKE SELECT ON db1.* FROM userX, userZ;
30
31 --error ER_NONEXISTING_GRANT
32 REVOKE PROXY ON userY FROM userX, userZ;
33
34 --echo # userX's grants must not have changed
35 SHOW GRANTS FOR userX;
36
37 --let $invert= 1
38 --let $event= !Q(REVOKE SUPER ON.*FROM.*userX.*userZ.*)
39 --source ../include/auth_sec_assert_binlog_events.inc
40
41 --let $event= !Q(REVOKE SELCT ON.*db1.*FROM.*userX.*userZ.*)
42 --source ../include/auth_sec_assert_binlog_events.inc
43
44 --let $event= !Q(REVOKE PROXY ON .*userY.*FROM.*userX.*userZ.*)
45 --source ../include/auth_sec_assert_binlog_events.inc
46 --let $invert= 0
47
48 --echo # Case 2 : Revoke non-existing grants
49
50 --error ER_NONEXISTING_GRANT
51 REVOKE SELECT ON db1.* FROM userY;
52
53 --error ER_NONEXISTING_GRANT
54 REVOKE PROXY ON userX FROM userY;
55
56 --echo # userX's grants must not have changed
57 SHOW GRANTS FOR userX;
58
59 --let $invert= 1
60 --let $event= !Q(REVOKE SELECT ON .*db1.*FROM.*userY.*)
61 --source ../include/auth_sec_assert_binlog_events.inc
62
63 --let $event= !Q(REVOKE PROXY ON .*userX.*FROM.*userY.*)
64 --source ../include/auth_sec_assert_binlog_events.inc
65 --let $invert= 0
66
67 --echo # Case 3 : Valid revoke
68
69 REVOKE SUPER ON *.* FROM userX, userA;
70
71 --let $event= !Q(REVOKE SUPER ON *.* FROM.*userX.*userA.*)
72 --source ../include/auth_sec_assert_binlog_events.inc
73 --source include/save_binlog_position.inc
74
75 REVOKE SELECT ON db1.* FROM userX, userA;
76
77 --let $event= !Q(REVOKE SELECT ON .*db1.*FROM.*userX.*userA.*)
78 --source ../include/auth_sec_assert_binlog_events.inc
79 --source include/save_binlog_position.inc
80
81 REVOKE PROXY ON userY FROM userX, userA;
82
83 --let $event= !Q(REVOKE PROXY ON.*userY.*FROM.*userX.*userA.*)
84 --source ../include/auth_sec_assert_binlog_events.inc
85
86 --echo # userX's grants must have changed
87 SHOW GRANTS FOR userX;
88
89 DROP USER userX, userY, userA;
90 DROP DATABASE db1;
91
92 --echo # End : Tests for REVOKE on global, db and proxy privileges
93
94 --echo # ----------------------------------------------------------------------
95
96 --echo # Begin : Tests for REVOKE on table and column privileges
97
98 CREATE USER userX, userY, userA;
99 CREATE DATABASE db1;
100 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
101
102 GRANT SELECT ON db1.table1 TO userX, userA;
103 GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
104
105 --source include/save_binlog_position.inc
106
107 --echo # Initial set of grants for userX
108 SHOW GRANTS FOR userX;
109
110 --echo # Case 1 : Revoke from non-existing user
111
112 --error ER_NONEXISTING_TABLE_GRANT
113 REVOKE SELECT ON db1.table1 FROM userX, userZ;
114
115 --error ER_NONEXISTING_TABLE_GRANT
116 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userZ;
117
118 --echo # userX's grants must not have changed
119 SHOW GRANTS FOR userX;
120
121 --let $invert= 1
122 --let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userZ)
123 --source ../include/auth_sec_assert_binlog_events.inc
124
125 --let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.*FROM.*userX.*userZ.*)
126 --source ../include/auth_sec_assert_binlog_events.inc
127 --let $invert= 0
128
129 --echo # Case 2 : Revoke non-existing grants
130
131 --error ER_NONEXISTING_TABLE_GRANT
132 REVOKE SELECT ON db1.table1 FROM userX, userY;
133
134 --error ER_NONEXISTING_TABLE_GRANT
135 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userY;
136
137 --echo # userX's grants must not have changed
138 SHOW GRANTS FOR userX;
139
140 --let $invert= 1
141 --let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userY.*)
142 --source ../include/auth_sec_assert_binlog_events.inc
143
144 --let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.* FROM.*userX.*userY.*)
145 --source ../include/auth_sec_assert_binlog_events.inc
146 --let $invert= 0
147
148 --echo # Case 3 : Valid revoke
149
150 REVOKE SELECT ON db1.table1 FROM userX, userA;
151
152 --let $event= !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userA.*)
153 --source ../include/auth_sec_assert_binlog_events.inc
154 --source include/save_binlog_position.inc
155
156 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userA;
157
158 --let $event= !Q(REVOKE INSERT(.*c1.*c2.*) ON.*db1.*table1.*FROM.*userX.*userA.*)
159 --source ../include/auth_sec_assert_binlog_events.inc
160
161 --echo # userX's grants must have changed
162 SHOW GRANTS FOR userX;
163
164 DROP USER userX, userY, userA;
165 DROP TABLE db1.table1;
166 DROP DATABASE db1;
167 --source include/save_binlog_position.inc
168
169 --echo # End : Tests for REVOKE on table and column privileges
170
171 --echo # ----------------------------------------------------------------------
172
173 --echo # Begin : Tests for REVOKE on procedure privileges
174
175 CREATE USER userX, userY, userA;
176 CREATE DATABASE db1;
177 DELIMITER ||;
178 CREATE PROCEDURE db1.proc1()
179 BEGIN
180 SELECT 1234;
181 END ||
182 DELIMITER ;||
183
184 GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
185 --source include/save_binlog_position.inc
186
187 --echo # Initial set of grants for userX
188 SHOW GRANTS FOR userX;
189
190 --echo Case 1 : Revoke from non-existing user
191
192 --error ER_NONEXISTING_PROC_GRANT
193 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userZ;
194
195 --echo # userX's grants must not have changed
196 SHOW GRANTS FOR userX;
197
198 --let $invert= 1
199 --let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userZ.*)
200 --source ../include/auth_sec_assert_binlog_events.inc
201 --let $invert= 0
202
203 --echo Case 2 : Revoke non-existing grants
204
205 --error ER_NONEXISTING_PROC_GRANT
206 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userY;
207
208 --echo # userX's grants must not have changed
209 SHOW GRANTS FOR userX;
210
211 --let $invert= 1
212 --let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userY.*)
213 --source ../include/auth_sec_assert_binlog_events.inc
214 --let $invert= 0
215
216 --echo Case 3 : Valid revoke
217
218 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userA;
219
220 --echo # userX's grants must have changed
221 SHOW GRANTS FOR userX;
222
223 --let $event= !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userA.*)
224 --source ../include/auth_sec_assert_binlog_events.inc
225
226 DROP USER userX, userY, userA;
227 DROP PROCEDURE db1.proc1;
228 DROP DATABASE db1;
229 --source include/save_binlog_position.inc
230
231 --echo # End : Tests for REVOKE on procedures privileges
232
233 --echo # ----------------------------------------------------------------------
234
235 --echo # Begin : Tests for REVOKE on roles
236
237 CREATE USER userX, userY, userA;
238 CREATE ROLE roleA, roleB;
239 GRANT roleA, roleB TO userX, userA;
240 --source include/save_binlog_position.inc
241
242 --echo # Initial set of grants for userX
243 SHOW GRANTS FOR userX;
244
245 --echo # Case 1 : Revoke from non-existing user
246
247 --error ER_UNKNOWN_AUTHID
248 REVOKE roleA, roleB FROM userX, userZ;
249
250 --echo # userX's grants must not have changed
251 SHOW GRANTS FOR userX;
252
253 --let $invert= 1
254 --let $event= !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userZ.*)
255 --source ../include/auth_sec_assert_binlog_events.inc
256 --let $invert= 0
257
258 --echo # Case 2 : Valid revoke
259
260 REVOKE roleA, roleB FROM userX, userA;
261
262 --echo # userX's grants must have changed
263 SHOW GRANTS FOR userX;
264
265 --let $event= !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userA.*)
266 --source ../include/auth_sec_assert_binlog_events.inc
267
268 DROP USER userX, userY, userA;
269 DROP ROLE roleA, roleB;
270 --source include/save_binlog_position.inc
271
272 --echo # End : Tests for REVOKE on roles
273
274 --echo # ----------------------------------------------------------------------
275
276 --echo # Begin : Tests for REVOKE ALL
277
278 CREATE USER userX, userY, userA;
279 CREATE ROLE roleA, roleB;
280 CREATE DATABASE db1;
281 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
282 DELIMITER ||;
283 CREATE PROCEDURE db1.proc1()
284 BEGIN
285 SELECT 1234;
286 END ||
287 DELIMITER ;||
288
289 GRANT SUPER ON *.* TO userX, userA;
290 GRANT SELECT ON db1.* TO userX, userA;
291 GRANT PROXY ON userY TO userX, userA;
292 GRANT SELECT ON db1.table1 TO userX, userA;
293 GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
294 GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
295 GRANT roleA, roleB TO userX, userA;
296
297 --source include/save_binlog_position.inc
298
299 --echo # Initial set of grants for userX
300 SHOW GRANTS FOR userX;
301
302 --echo # Case 1 : Revoke from non-existing user
303
304 --error ER_REVOKE_GRANTS
305 REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userZ;
306
307 --echo # userX's grants must not have changed
308 SHOW GRANTS FOR userX;
309
310 --let $invert= 1
311 --let $event= !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userZ.*)
312 --source ../include/auth_sec_assert_binlog_events.inc
313 --let $invert= 0
314
315 --echo # Case 2 : Valid revoke
316
317 REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userA;
318
319 --echo # userX's grants must have changed
320 SHOW GRANTS FOR userX;
321
322 --let $event= !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userA.*)
323 --source ../include/auth_sec_assert_binlog_events.inc
324
325 DROP USER userX, userY, userA;
326 DROP ROLE roleA, roleB;
327 DROP TABLE db1.table1;
328 DROP PROCEDURE db1.proc1;
329 DROP DATABASE db1;
330 --source include/save_binlog_position.inc
331
332 --echo # End : Tests for REVOKE ALL
333
334 --echo # ----------------------------------------------------------------------
335 # Wait till we reached the initial number of concurrent sessions
336 --source include/wait_until_count_sessions.inc