Debian Codegraph @

mysql-test/suite/auth_sec/include/partial_revokes_dml.inc

raw
1 ###############################################################################
2 # #
3 # This script executes partial_revokes scenarios in following modes : #
4 # 1. Normal : Script executes on single server #
5 # 2. Replication : Script executes in replication setup #
6 # on a master and a slave #
7 # #
8 # Arguments required: #
9 # 1. $TEST_MODE - [NORMAL | RPL] #
10 # #
11 ###############################################################################
12
13 # If required parameters are not set then throw error and exit.
14 --let PARAM_PERL_QUIT=0
15 if ($TEST_MODE == '')
16 {
17 --let VARIABLE_NAME = TEST_MODE
18 --let PARAM_PERL_QUIT=1
19 }
20
21 --perl
22 $variable_name = "$ENV{VARIABLE_NAME}";
23 die "Error: partial_revokes_dml.inc: input variable $variable_name is missing!"
24 if ($ENV{PARAM_PERL_QUIT} == 1);
25 EOF
26
27 --echo # -----------------------------------------------------------------------
28 --echo # Start : DML Tests for Partial Revokes
29 --echo # -----------------------------------------------------------------------
30 --echo # -----------------------------------------------------------------------
31 --echo # Setup
32 --echo # Create database and objects
33 CREATE DATABASE partial_revokes_db;
34 CREATE DATABASE partial_revokes_db_2;
35 --echo # Create users
36 CREATE USER foo@localhost IDENTIFIED BY 'abcd';
37 CREATE USER bar@localhost IDENTIFIED BY 'efgh';
38
39 # Format : Case: <type of grant> + Partial revoke info + Operation
40
41 --echo # -----------------------------------------------------------------------
42 --echo # Start: Test cases for Base tables.
43 CREATE TABLE partial_revokes_db.t1(c1 int, c2 int);
44 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4);
45 CREATE TABLE partial_revokes_db_2.t1(a1 int, a2 int);
46 INSERT INTO partial_revokes_db_2.t1 values (1,2), (5,6), (9,10), (11,12);
47 CREATE TABLE partial_revokes_db.t2 (a date, b date, c date not null, d date);
48 --echo # -----------------------------------------------------------------------
49
50 --echo # -----------------------------------------------------------------------
51 --echo # Case: *.* + Partial Revoke on INSERT + INSERT
52
53 # setup
54 --connection default
55 GRANT INSERT, SELECT ON *.* TO foo@localhost;
56 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
57
58 --connect(foo_conn, localhost, foo, abcd,,,,)
59 # Insert should block
60 --error ER_TABLEACCESS_DENIED_ERROR
61 INSERT INTO partial_revokes_db.t1 VALUES (5,6);
62 # Select should not
63 SELECT COUNT(*) FROM partial_revokes_db.t1;
64
65 # cleanup
66 --connection default
67 --disconnect foo_conn
68 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
69
70 --echo # -----------------------------------------------------------------------
71 --echo # Case: *.* + Partial Revoke on SELECT + INSERT ... SELECT
72
73 # setup
74 --connection default
75 GRANT INSERT, SELECT ON *.* TO foo@localhost;
76 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
77
78 --connect(foo_conn, localhost, foo, abcd,,,,)
79 # INSERT ... SELECT should not work
80 --error ER_TABLEACCESS_DENIED_ERROR
81 INSERT INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1;
82 # Regular INSERT should succeed
83 INSERT INTO partial_revokes_db_2.t1 VALUES (1,2), (3,4);
84 # Even on the other table
85 INSERT INTO partial_revokes_db.t1 VALUES (5,6), (7,8);
86
87 # cleanup
88 --connection default
89 --disconnect foo_conn
90 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
91
92 --echo # -----------------------------------------------------------------------
93 --echo # Case: *.* + Partial Revoke on UPDATE + UPDATE
94
95 # setup
96 --connection default
97 GRANT UPDATE, SELECT ON *.* TO foo@localhost;
98 REVOKE UPDATE ON partial_revokes_db.* FROM foo@localhost;
99
100 --connect(foo_conn, localhost, foo, abcd,,,,)
101 # Simple UPDATE should work
102 --error ER_TABLEACCESS_DENIED_ERROR
103 UPDATE partial_revokes_db.t1 SET c1 = c1 + 1;
104 # UPDATE with SELECT should too
105 --error ER_TABLEACCESS_DENIED_ERROR
106 UPDATE partial_revokes_db.t1 SET c2 = c2 + 1 WHERE c1 = 2;
107
108 # cleanup
109 --connection default
110 --disconnect foo_conn
111 REVOKE UPDATE, SELECT ON *.* FROM foo@localhost;
112
113 --echo # -----------------------------------------------------------------------
114 --echo # Case: *.* + Partial Revoke on SELECT + UPDATE ... SELECT
115
116 # setup
117 --connection default
118 GRANT UPDATE, SELECT ON *.* TO foo@localhost;
119 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
120
121 --connect(foo_conn, localhost, foo, abcd,,,,)
122 # Simple UPDATE should work
123 UPDATE partial_revokes_db.t1 SET c1 = 1;
124 # UPDATE with SELECT should not
125 --error ER_COLUMNACCESS_DENIED_ERROR
126 UPDATE partial_revokes_db.t1 SET c1 = c1 + 1;
127 # UPDATE with SELECT should not
128 --error ER_COLUMNACCESS_DENIED_ERROR
129 UPDATE partial_revokes_db.t1 SET c2 = c2 + 1 WHERE c1 = 2;
130
131 # cleanup
132 --connection default
133 --disconnect foo_conn
134 REVOKE UPDATE, SELECT ON *.* FROM foo@localhost;
135
136 --echo # -----------------------------------------------------------------------
137 --echo # Case: *.* + Partial Revoke on DELETE + DELETE
138
139 # setup
140 --connection default
141 GRANT DELETE, SELECT ON *.* TO foo@localhost;
142 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
143
144 --connect(foo_conn, localhost, foo, abcd,,,,)
145 # DELETE with SELECT should not work
146 --error ER_TABLEACCESS_DENIED_ERROR
147 DELETE FROM partial_revokes_db.t1 WHERE c1 = 1;
148 # Simple DELETE should too
149 --error ER_TABLEACCESS_DENIED_ERROR
150 DELETE FROM partial_revokes_db.t1;
151
152 # cleanup
153 --connection default
154 --disconnect foo_conn
155 REVOKE DELETE, SELECT ON *.* FROM foo@localhost;
156
157 --echo # -----------------------------------------------------------------------
158 --echo # Case: *.* + Partial Revoke on SELECT + DELETE ... SELECT
159
160 # setup
161 --connection default
162 GRANT DELETE, SELECT ON *.* TO foo@localhost;
163 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
164
165 --connect(foo_conn, localhost, foo, abcd,,,,)
166 # DELETE with SELECT should not work
167 --error ER_COLUMNACCESS_DENIED_ERROR
168 DELETE FROM partial_revokes_db.t1 WHERE c1 = 1;
169 # Simple DELETE should
170 DELETE FROM partial_revokes_db.t1;
171
172 # cleanup
173 --connection default
174 --disconnect foo_conn
175 REVOKE DELETE, SELECT ON *.* FROM foo@localhost;
176
177 --echo # -----------------------------------------------------------------------
178 --echo # Case: *.* + Partial Revoke on DELETE + REPLACE
179
180 # setup
181 --connection default
182 GRANT SELECT, INSERT, DELETE ON *.* TO foo@localhost;
183 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
184
185 --connect(foo_conn, localhost, foo, abcd,,,,)
186 # REPLACE requires INSERT + DELETE
187 --error ER_TABLEACCESS_DENIED_ERROR
188 REPLACE INTO partial_revokes_db.t1 VALUES (1,3);
189 --error ER_TABLEACCESS_DENIED_ERROR
190 REPLACE INTO partial_revokes_db.t1 VALUES (5,6);
191 # Following should work
192 REPLACE INTO partial_revokes_db_2.t1 VALUES (1,3);
193 if($TEST_MODE != RPL)
194 {
195 # Avoid warning in server log in case of BINLOG_FORMAT= statement
196 REPLACE INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1 ORDER BY c1;
197 }
198
199 # cleanup
200 --connection default
201 --disconnect foo_conn
202 REVOKE SELECT, INSERT, DELETE ON *.* FROM foo@localhost;
203
204 --echo # -----------------------------------------------------------------------
205 --echo # Case: *.* + Partial Revoke on INSERT + REPLACE
206
207 # setup
208 --connection default
209 GRANT SELECT, INSERT, DELETE ON *.* TO foo@localhost;
210 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
211
212 --connect(foo_conn, localhost, foo, abcd,,,,)
213 # REPLACE requires INSERT + DELETE
214 --error ER_TABLEACCESS_DENIED_ERROR
215 REPLACE INTO partial_revokes_db.t1 VALUES (1,3);
216 --error ER_TABLEACCESS_DENIED_ERROR
217 REPLACE INTO partial_revokes_db.t1 VALUES (5,6);
218 # Following should work
219 REPLACE INTO partial_revokes_db_2.t1 VALUES (1,3);
220 if($TEST_MODE != RPL)
221 {
222 REPLACE INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1 ORDER BY c1;
223 }
224 # cleanup
225 --connection default
226 --disconnect foo_conn
227 REVOKE SELECT, INSERT, DELETE ON *.* FROM foo@localhost;
228
229 --echo # -----------------------------------------------------------------------
230 --echo # Case: *.* + Partial Revoke on INSERT + LOAD
231
232 # setup
233 --connection default
234 GRANT INSERT, SELECT, FILE ON *.* TO foo@localhost;
235 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
236
237 --connect(foo_conn, localhost, foo, abcd,,,,)
238 # Should fail
239 --error ER_TABLEACCESS_DENIED_ERROR
240 LOAD DATA INFILE '../../std_data/loaddata1.dat' IGNORE INTO TABLE partial_revokes_db.t2 FIELDS TERMINATED BY ',';
241 SELECT * FROM partial_revokes_db.t2;
242
243 # cleanup
244 --connection default
245 --disconnect foo_conn
246 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
247
248 --echo # -----------------------------------------------------------------------
249 --echo # Case: *.* + Partial Revoke on INSERT + CTE
250
251 # setup
252 --connection default
253 GRANT SELECT ON *.* TO foo@localhost;
254 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
255
256 --connect(foo_conn, localhost, foo, abcd,,,,)
257 --error ER_TABLEACCESS_DENIED_ERROR
258 WITH cte1 AS (SELECT c1, c2 FROM partial_revokes_db.t1),
259 cte2 AS (SELECT a1, a2 FROM partial_revokes_db_2.t1)
260 SELECT c2, a2 FROM cte1 JOIN cte2 WHERE cte1.c1 = cte2.a1;
261
262 # cleanup
263 --connection default
264 --disconnect foo_conn
265 REVOKE SELECT ON *.* FROM foo@localhost;
266
267 --echo # -----------------------------------------------------------------------
268 --echo # End: Test cases for Base tables.
269 # cleanup
270 --connection default
271 DROP TABLE partial_revokes_db.t1;
272 DROP TABLE partial_revokes_db.t2;
273 DROP TABLE partial_revokes_db_2.t1;
274 --echo # -----------------------------------------------------------------------
275
276 --echo # -----------------------------------------------------------------------
277 --echo # Start: Test cases for views.
278 CREATE TABLE partial_revokes_db.t1(c1 int, c2 int);
279 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
280 CREATE TABLE partial_revokes_db_2.t2(a1 int, a2 int);
281 INSERT INTO partial_revokes_db_2.t2 values (1,2), (5,6), (9,10), (11,12);
282 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db.v1t1d AS SELECT * FROM partial_revokes_db.t1;
283 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db.v1t1i AS SELECT * FROM partial_revokes_db.t1;
284 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db.v1t2d AS SELECT * FROM partial_revokes_db_2.t2;
285 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db.v1t2i AS SELECT * FROM partial_revokes_db_2.t2;
286
287 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db_2.v1t1d AS SELECT * FROM partial_revokes_db.t1;
288 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t1i AS SELECT * FROM partial_revokes_db.t1;
289 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db_2.v1t2d AS SELECT * FROM partial_revokes_db_2.t2;
290 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t2i AS SELECT * FROM partial_revokes_db_2.t2;
291 --echo # -----------------------------------------------------------------------
292
293 --echo # -----------------------------------------------------------------------
294 --echo # Case: *.* + Partial Revoke on SELECT + SELECT, SHOW VIEW
295
296 #setup
297 --connection default
298 GRANT SELECT, SHOW VIEW ON *.* TO foo@localhost;
299 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
300
301 --connect(foo_conn, localhost, foo, abcd,,,,)
302 # should fail : View is in partial_revokes_db
303 --error ER_TABLEACCESS_DENIED_ERROR
304 SELECT * FROM partial_revokes_db.v1t1d;
305 --error ER_TABLEACCESS_DENIED_ERROR
306 SELECT * FROM partial_revokes_db.v1t1i;
307 --error ER_TABLEACCESS_DENIED_ERROR
308 SELECT * FROM partial_revokes_db.v1t2d;
309 --error ER_TABLEACCESS_DENIED_ERROR
310 SELECT * FROM partial_revokes_db.v1t2i;
311
312 # should work : Base table is in partial_revokes_db but this is a DEFINER view
313 SELECT * FROM partial_revokes_db_2.v1t1d;
314 # should fail : Base table is in partial_revokes_db and this is an INVOKER view
315 --error ER_VIEW_INVALID
316 SELECT * FROM partial_revokes_db_2.v1t1i;
317
318 # should work : view and base table are in partial_revokes_db_2
319 SELECT * FROM partial_revokes_db_2.v1t2d;
320 SELECT * FROM partial_revokes_db_2.v1t2i;
321
322 # Show create view
323 --error ER_TABLEACCESS_DENIED_ERROR
324 SHOW CREATE VIEW partial_revokes_db.v1t1d;
325 --error ER_TABLEACCESS_DENIED_ERROR
326 SHOW CREATE VIEW partial_revokes_db.v1t1i;
327 --error ER_TABLEACCESS_DENIED_ERROR
328 SHOW CREATE VIEW partial_revokes_db.v1t2d;
329 --error ER_TABLEACCESS_DENIED_ERROR
330 SHOW CREATE VIEW partial_revokes_db.v1t2i;
331
332 SHOW CREATE VIEW partial_revokes_db_2.v1t1d;
333 SHOW CREATE VIEW partial_revokes_db_2.v1t1i;
334 SHOW CREATE VIEW partial_revokes_db_2.v1t2d;
335 SHOW CREATE VIEW partial_revokes_db_2.v1t2i;
336
337 # cleanup
338 --connection default
339 --disconnect foo_conn
340 REVOKE SELECT ON *.* FROM foo@localhost;
341
342 --echo # -----------------------------------------------------------------------
343 --echo # Case: *.* + Partial Revoke on INSERT + INSERT
344
345 #setup
346 --connection default
347 GRANT INSERT ON *.* TO foo@localhost;
348 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
349
350 --connect(foo_conn, localhost, foo, abcd,,,,)
351 # should fail : View is in partial_revokes_db
352 --error ER_TABLEACCESS_DENIED_ERROR
353 INSERT INTO partial_revokes_db.v1t1d VALUES (1,2);
354 --error ER_TABLEACCESS_DENIED_ERROR
355 INSERT INTO partial_revokes_db.v1t1i VALUES (1,2);
356 --error ER_TABLEACCESS_DENIED_ERROR
357 INSERT INTO partial_revokes_db.v1t2i VALUES (1,2);
358 --error ER_TABLEACCESS_DENIED_ERROR
359 INSERT INTO partial_revokes_db.v1t2d VALUES (1,2);
360
361 # should work : Base table is in partial_revokes_db but this is a DEFINER view
362 INSERT INTO partial_revokes_db_2.v1t1d VALUES (1,2);
363 # should fail : Base table is in partial_revokes_db and this is an INVOKER view
364 --error ER_VIEW_INVALID
365 INSERT INTO partial_revokes_db_2.v1t1i VALUES (1,2);
366
367 # should work : view and base table are in partial_revokes_db_2
368 INSERT INTO partial_revokes_db_2.v1t2d VALUES(1,2);
369 INSERT INTO partial_revokes_db_2.v1t2i VALUES(1,2);
370
371 # cleanup
372 --connection default
373 --disconnect foo_conn
374 REVOKE INSERT ON *.* FROM foo@localhost;
375 DELETE FROM partial_revokes_db.t1;
376 DELETE FROM partial_revokes_db_2.t2;
377
378 --echo # -----------------------------------------------------------------------
379 --echo # Case: *.* + Partial Revoke on INSERT + INSERT ... SELECT
380
381 #setup
382 --connection default
383 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
384 GRANT INSERT, SELECT ON *.* TO foo@localhost;
385 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
386
387 --connect(foo_conn, localhost, foo, abcd,,,,)
388
389 # Following should fail because we are trying to select from a view in partial_revokes_db
390 --error ER_TABLEACCESS_DENIED_ERROR
391 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t1d;
392 --error ER_TABLEACCESS_DENIED_ERROR
393 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t1i;
394 --error ER_TABLEACCESS_DENIED_ERROR
395 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t2d;
396 --error ER_TABLEACCESS_DENIED_ERROR
397 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t2i;
398
399 # Following should fail because view is INVOKER view and base table is in partial_revokes_db
400 --error ER_TABLEACCESS_DENIED_ERROR
401 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db.v1t1i;
402
403 # All of the following should succeed
404 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t1d;
405 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t2d;
406 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t2i;
407
408 # cleanup
409 --connection default
410 --disconnect foo_conn
411 DELETE FROM partial_revokes_db.t1;
412 DELETE FROM partial_revokes_db_2.t2;
413 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
414
415 --echo # -----------------------------------------------------------------------
416 --echo # Case: *.* + Partial Revoke on DELETE + DELETE
417
418 #setup
419 --connection default
420 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
421 INSERT INTO partial_revokes_db_2.t2 values (1,2), (3,4), (5,6), (7,8);
422 GRANT DELETE ON *.* TO foo@localhost;
423 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
424
425 --connect(foo_conn, localhost, foo, abcd,,,,)
426 # should fail : View is in partial_revokes_db
427 --error ER_TABLEACCESS_DENIED_ERROR
428 DELETE FROM partial_revokes_db.v1t1d;
429 --error ER_TABLEACCESS_DENIED_ERROR
430 DELETE FROM partial_revokes_db.v1t1i;
431 --error ER_TABLEACCESS_DENIED_ERROR
432 DELETE FROM partial_revokes_db.v1t2d;
433 --error ER_TABLEACCESS_DENIED_ERROR
434 DELETE FROM partial_revokes_db.v1t2i;
435
436 # should work : Base table is in partial_revokes_db but this is a DEFINER view
437 DELETE FROM partial_revokes_db_2.v1t1d;
438 # should fail : Base table is in partial_revokes_db and this is an INVOKER view
439 --error ER_VIEW_INVALID
440 DELETE FROM partial_revokes_db_2.v1t1i;
441
442 # should work : view and base table are in partial_revokes_db_2
443 DELETE FROM partial_revokes_db_2.v1t2d;
444 DELETE FROM partial_revokes_db_2.v1t2i;
445
446 # cleanup
447 --connection default
448 --disconnect foo_conn
449 DELETE FROM partial_revokes_db.t1;
450 DELETE FROM partial_revokes_db_2.t2;
451 REVOKE DELETE ON *.* FROM foo@localhost;
452
453 --echo # -----------------------------------------------------------------------
454 --echo # Case: *.* + Partial Revoke on SELECT + DELETE ... SELECT
455
456 #setup
457 --connection default
458 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
459 INSERT INTO partial_revokes_db_2.t2 values (1,2), (3,4), (5,6), (7,8);
460 GRANT DELETE, SELECT ON *.* TO foo@localhost;
461 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
462
463 --connect(foo_conn, localhost, foo, abcd,,,,)
464 # should fail : View is in partial_revokes_db
465 --error ER_COLUMNACCESS_DENIED_ERROR
466 DELETE FROM partial_revokes_db.v1t1d WHERE c1 = 1;
467 --error ER_COLUMNACCESS_DENIED_ERROR
468 DELETE FROM partial_revokes_db.v1t1i WHERE c1 = 1;
469 --error ER_COLUMNACCESS_DENIED_ERROR
470 DELETE FROM partial_revokes_db.v1t2d WHERE a1 = 1;
471 --error ER_COLUMNACCESS_DENIED_ERROR
472 DELETE FROM partial_revokes_db.v1t2i WHERE a1 = 1;
473
474 # should work : Base table is in partial_revokes_db but this is a DEFINER view
475 DELETE FROM partial_revokes_db_2.v1t1d;
476 # should fail : Base table is in partial_revokes_db and this is an INVOKER view
477 --error ER_VIEW_INVALID
478 DELETE FROM partial_revokes_db_2.v1t1i WHERE c1 = 1;
479
480 # should work : view and base table are in partial_revokes_db_2
481 DELETE FROM partial_revokes_db_2.v1t2d WHERE a1 = 1;
482 DELETE FROM partial_revokes_db_2.v1t2i WHERE a1 = 1;
483
484 # cleanup
485 --connection default
486 --disconnect foo_conn
487 DELETE FROM partial_revokes_db.t1;
488 DELETE FROM partial_revokes_db_2.t2;
489 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
490
491 --echo # -----------------------------------------------------------------------
492 --echo # End: Test cases for views.
493 # cleanup
494 --connection default
495 DROP TABLE IF EXISTS partial_revokes_db.t1;
496 DROP TABLE IF EXISTS partial_revokes_db_2.t1;
497 DROP VIEW IF EXISTS partial_revokes_db.v1t1d;
498 DROP VIEW IF EXISTS partial_revokes_db.v1t1i;
499 DROP VIEW IF EXISTS partial_revokes_db.v1t2d;
500 DROP VIEW IF EXISTS partial_revokes_db.v1t2i;
501 DROP VIEW IF EXISTS partial_revokes_db_2.v1t1d;
502 DROP VIEW IF EXISTS partial_revokes_db_2.v1t1i;
503 DROP VIEW IF EXISTS partial_revokes_db_2.v1t2d;
504 DROP VIEW IF EXISTS partial_revokes_db_2.v1t2i;
505 --echo # -----------------------------------------------------------------------
506
507 --echo # -----------------------------------------------------------------------
508 --echo # Case: *.* + Partial Revoke + SHOW DATABASE
509 CREATE USER baz@localhost IDENTIFIED BY 'abcd';
510 CREATE ROLE r3;
511 GRANT INSERT,SELECT ON *.* TO baz@localhost;
512 GRANT INSERT,SELECT ON *.* TO r3;
513 REVOKE INSERT,SELECT ON partial_revokes_db.* FROM baz@localhost;
514 REVOKE INSERT,SELECT ON partial_revokes_db_2.* FROM r3;
515 GRANT r3 TO baz@localhost;
516 --connect(baz_conn, localhost, baz, abcd,,,,)
517 # partial_revokes_db should not be visible.
518 SHOW GRANTS;
519 SHOW DATABASES;
520 SET ROLE r3;
521 # All dbs should be visible now.
522 SHOW DATABASES;
523 --connection default
524 --disconnect baz_conn
525 # partial_revokes_db should be visible now since it has at least one
526 # global privilege without any restriction on that.
527 GRANT INSERT ON `partial_revokes_db`.* TO baz@localhost;
528 --connect(baz_conn, localhost, baz, abcd,,,,)
529 SHOW GRANTS;
530 SHOW DATABASES;
531 SET ROLE r3;
532 # partial_revokes_db_2 should not visible now.
533 SHOW DATABASES;
534 --connection default
535 --disconnect baz_conn
536 GRANT SELECT ON `partial_revokes_db`.* TO baz@localhost;
537 --connect(baz_conn, localhost, baz, abcd,,,,)
538 SHOW GRANTS;
539 # partial_revokes_db should be visible of course
540 SHOW DATABASES;
541 SET ROLE r3;
542 # All dbs should be visible now.
543 SHOW DATABASES;
544 --echo # -----------------------------------------------------------------------
545
546 #cleanup
547 --connection default
548 --disconnect baz_conn
549 DROP USER baz@localhost;
550 DROP ROLE r3;
551 --echo # End: Test cases for Show database.
552 --echo # -----------------------------------------------------------------------
553
554 --echo # -----------------------------------------------------------------------
555 --echo # Start: Test cases for stored routines.
556 --echo # -----------------------------------------------------------------------
557 CREATE TABLE partial_revokes_db.tp1(c1 int, c2 int);
558 INSERT INTO partial_revokes_db.tp1 VALUES (1,2), (3,4);
559 CREATE TABLE partial_revokes_db_2.tp2(c1 int, c2 int);
560 INSERT INTO partial_revokes_db_2.tp2 VALUES (5,6), (7,8);
561
562 CREATE PROCEDURE partial_revokes_db.p1tp1d_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
563 CREATE PROCEDURE partial_revokes_db.p1tp1i_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
564 ALTER PROCEDURE partial_revokes_db.p1tp1i_count SQL SECURITY INVOKER;
565 CREATE PROCEDURE partial_revokes_db.p1tp2d_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
566 CREATE PROCEDURE partial_revokes_db.p1tp2i_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
567 ALTER PROCEDURE partial_revokes_db.p1tp2i_count SQL SECURITY INVOKER;
568
569 CREATE PROCEDURE partial_revokes_db_2.p2tp1d_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
570 CREATE PROCEDURE partial_revokes_db_2.p2tp1i_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
571 ALTER PROCEDURE partial_revokes_db_2.p2tp1i_count SQL SECURITY INVOKER;
572 CREATE PROCEDURE partial_revokes_db_2.p2tp2d_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
573 CREATE PROCEDURE partial_revokes_db_2.p2tp2i_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
574 ALTER PROCEDURE partial_revokes_db_2.p2tp2i_count SQL SECURITY INVOKER;
575 --echo # -----------------------------------------------------------------------
576
577 --echo # -----------------------------------------------------------------------
578 --echo # Case: *.* + Partial Revoke on EXECUTE
579
580 #setup
581 --connection default
582 GRANT EXECUTE, SELECT ON *.* TO foo@localhost;
583 REVOKE EXECUTE, SELECT ON partial_revokes_db.* FROM foo@localhost;
584 SHOW GRANTS FOR foo@localhost;
585
586 --connect(foo_conn, localhost, foo, abcd,,,,)
587 --error ER_PROCACCESS_DENIED_ERROR
588 CALL partial_revokes_db.p1tp1d_count();
589 --error ER_PROCACCESS_DENIED_ERROR
590 CALL partial_revokes_db.p1tp1i_count();
591 --error ER_PROCACCESS_DENIED_ERROR
592 CALL partial_revokes_db.p1tp2d_count();
593 --error ER_PROCACCESS_DENIED_ERROR
594 CALL partial_revokes_db.p1tp2i_count();
595
596 CALL partial_revokes_db_2.p2tp1d_count();
597 --error ER_TABLEACCESS_DENIED_ERROR
598 CALL partial_revokes_db_2.p2tp1i_count();
599 CALL partial_revokes_db_2.p2tp2d_count();
600 CALL partial_revokes_db_2.p2tp2i_count();
601
602 --connection default
603 --disconnect foo_conn
604 REVOKE EXECUTE, SELECT ON *.* FROM foo@localhost;
605 --echo # -----------------------------------------------------------------------
606 #cleanup
607 --connection default
608 DROP PROCEDURE partial_revokes_db.p1tp1d_count;
609 DROP PROCEDURE partial_revokes_db.p1tp1i_count;
610 DROP PROCEDURE partial_revokes_db.p1tp2d_count;
611 DROP PROCEDURE partial_revokes_db.p1tp2i_count;
612 DROP PROCEDURE partial_revokes_db_2.p2tp1d_count;
613 DROP PROCEDURE partial_revokes_db_2.p2tp1i_count;
614 DROP PROCEDURE partial_revokes_db_2.p2tp2d_count;
615 DROP PROCEDURE partial_revokes_db_2.p2tp2i_count;
616 DROP TABLE partial_revokes_db.tp1;
617 DROP TABLE partial_revokes_db_2.tp2;
618 --echo # End: Test cases for stored routines.
619 --echo # -----------------------------------------------------------------------
620
621 --echo # -----------------------------------------------------------------------
622 --echo # Start: Test case for table and column grants
623 --echo # -----------------------------------------------------------------------
624 --connection default
625 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
626 SHOW GRANTS FOR foo@localhost;
627
628 CREATE TABLE partial_revokes_db.t1(c1 int);
629 INSERT INTO partial_revokes_db.t1 VALUES (1), (2), (3);
630 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t1i AS SELECT * FROM partial_revokes_db.t1;
631 CREATE PROCEDURE partial_revokes_db_2.p1t1i_c1() SELECT COUNT(*) FROM partial_revokes_db.t1;
632 ALTER PROCEDURE partial_revokes_db_2.p1t1i_c1 SQL SECURITY INVOKER;
633
634 CREATE TABLE partial_revokes_db.t2(c1 int, c2 int);
635 INSERT INTO partial_revokes_db.t2 VALUES (1,2), (3,4), (5,6);
636 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v2t2i AS SELECT c1 FROM partial_revokes_db.t2;
637 CREATE PROCEDURE partial_revokes_db_2.p2t2i_c1() SELECT c1 FROM partial_revokes_db.t2;
638 ALTER PROCEDURE partial_revokes_db_2.p2t2i_c1 SQL SECURITY INVOKER;
639
640 GRANT SELECT, EXECUTE ON *.* TO foo@localhost;
641 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
642 GRANT SELECT ON partial_revokes_db.t1 TO foo@localhost;
643 GRANT SELECT(c1) ON partial_revokes_db.t2 TO foo@localhost;
644 SHOW GRANTS FOR foo@localhost;
645
646 --connect(foo_conn, localhost, foo, abcd,,,,)
647
648 SELECT * FROM partial_revokes_db.t1;
649 SELECT * FROM partial_revokes_db_2.v1t1i;
650 CALL partial_revokes_db_2.p1t1i_c1();
651
652 SELECT c1 FROM partial_revokes_db.t2;
653 SELECT * FROM partial_revokes_db_2.v2t2i;
654 CALL partial_revokes_db_2.p2t2i_c1();
655
656 --echo # -----------------------------------------------------------------------
657 # Cleanup
658 --connection default
659 --disconnect foo_conn
660 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
661 DROP TABLE partial_revokes_db.t1;
662 DROP VIEW partial_revokes_db_2.v1t1i;
663 DROP PROCEDURE partial_revokes_db_2.p1t1i_c1;
664 DROP TABLE partial_revokes_db.t2;
665 DROP VIEW partial_revokes_db_2.v2t2i;
666 DROP PROCEDURE partial_revokes_db_2.p2t2i_c1;
667
668 --echo # End: Test cases for table and column grants
669 --echo # -----------------------------------------------------------------------
670
671 --echo # -----------------------------------------------------------------------
672 --echo # Cleanup
673 --connection default
674 --echo # Drop database
675 DROP DATABASE partial_revokes_db;
676 DROP DATABASE partial_revokes_db_2;
677 --echo # Drop users
678 DROP USER foo@localhost, bar@localhost;
679 --echo # -----------------------------------------------------------------------
680 --echo # -----------------------------------------------------------------------
681 --echo # End : DML Tests for Partial Revokes
682 --echo # -----------------------------------------------------------------------