Debian Codegraph @

mysql-test/suite/auth_sec/include/partial_revokes_add_remove.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_add_remove.inc: input variable $variable_name is missing!"
24 if ($ENV{PARAM_PERL_QUIT} == 1);
25 EOF
26 CREATE DATABASE pr_db;
27 CREATE DATABASE normal_db;
28 CREATE TABLE pr_db.t1(c1 int, c2 int);
29 INSERT INTO pr_db.t1 values (1,2), (3,4);
30 CREATE TABLE normal_db.t1(c1 int, c2 int);
31 INSERT INTO normal_db.t1 values (1,2), (3,4);
32 --let $conn_name = foo_master
33 --let $reconn_name = foo_master_reconn
34 --let $bar_conn = bar_master
35 --echo -------------------------------------------------------------------------
36 --echo Tests related to users
37 --echo -------------------------------------------------------------------------
38 --echo #
39 --echo # 1. Grantor does not have restriction list but has global priv
40 --echo #
41 CREATE USER foo, bar;
42 GRANT INSERT,SELECT ON *.* TO foo WITH GRANT OPTION;
43
44 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
45 --echo [connection $conn_name]
46 --echo # Must fail since no priv granted to bar yet.
47 --error ER_NONEXISTING_GRANT
48 REVOKE INSERT ON mysql.* FROM bar;
49 --echo # Create global insert priv with partial revoke for bar.
50 GRANT INSERT ON *.* TO bar;
51 REVOKE INSERT ON mysql.* FROM bar;
52 SHOW GRANTS FOR bar;
53 --source ./partial_revokes_rpl_helper.inc
54 --echo # Clear partial revoke from bar but global INSERT priv remains as it is
55 GRANT INSERT ON *.* TO bar;
56 SHOW GRANTS FOR bar;
57 --source ./partial_revokes_rpl_helper.inc
58 --echo # Create the partial revoke on mysql db again.
59 REVOKE INSERT ON mysql.* FROM bar;
60 SHOW GRANTS FOR bar;
61 --source ./partial_revokes_rpl_helper.inc
62 --echo # Remove partial revoke only
63 GRANT INSERT ON mysql.* TO bar;
64 SHOW GRANTS FOR bar;
65 --source ./partial_revokes_rpl_helper.inc
66 --echo # Grant DB level privilege only
67 GRANT INSERT ON mysql.* TO bar;
68 SHOW GRANTS FOR bar;
69 --source ./partial_revokes_rpl_helper.inc
70 --connection default
71 disconnect $conn_name;
72 DROP USER foo, bar;
73
74 --echo #
75 --echo # 2. Grantor has restriction list and grantee has global priv
76 --echo #
77 CREATE USER foo, bar;
78 GRANT INSERT, SELECT, UPDATE ON *.* TO foo WITH GRANT OPTION;
79 --echo # Restrict INSERT on mysql DB.
80 REVOKE INSERT ON mysql.* FROM foo;
81 SHOW GRANTS FOR foo;
82 --echo # Restrict UPDATE on mysql DB as well.
83 REVOKE UPDATE ON mysql.* FROM foo;
84 SHOW GRANTS FOR foo;
85 --echo # Must fail, DELETE privilege is not granted
86 --error ER_NONEXISTING_GRANT
87 REVOKE DELETE ON mysql.* FROM foo;
88 GRANT INSERT ON *.* TO bar;
89 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
90 --echo [connection $conn_name]
91 --error ER_DBACCESS_DENIED_ERROR
92 GRANT INSERT ON mysql.* TO bar;
93 --error ER_DBACCESS_DENIED_ERROR
94 REVOKE INSERT ON mysql.* FROM bar;
95 --error ER_DB_ACCESS_DENIED
96 REVOKE INSERT ON *.* FROM bar;
97 --echo # Grantee will not get the restriction list from grantor because
98 --echo # former already has higher priv than grantor
99 GRANT INSERT ON *.* TO bar;
100 SHOW GRANTS FOR bar;
101 --source ./partial_revokes_rpl_helper.inc
102 --connection default
103 disconnect $conn_name;
104 DROP USER foo, bar;
105
106 --echo #
107 --echo # 3. Grantor has restriction list and grantee has DB level privileges
108 --echo #
109 CREATE USER foo, bar;
110 GRANT INSERT, UPDATE, SELECT ON *.* TO foo WITH GRANT OPTION;
111 --echo # Restrict INSERT and UPDATE on mysql DB from foo.
112 REVOKE INSERT,UPDATE ON mysql.* FROM foo;
113 SHOW GRANTS FOR foo;
114 --echo # Grant DB level INSERT priv on mysql DB to bar.
115 GRANT INSERT ON mysql.* TO bar;
116 SHOW GRANTS FOR bar;
117 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
118 --echo [connection $conn_name]
119 --error ER_DBACCESS_DENIED_ERROR
120 REVOKE INSERT ON mysql.* FROM bar;
121 --error ER_DB_ACCESS_DENIED
122 REVOKE INSERT ON *.* FROM bar;
123 --error ER_DBACCESS_DENIED_ERROR
124 GRANT INSERT ON mysql.* TO bar;
125 --echo # There should not be any restriction list:
126 --echo # INSERT on mysql DB because bar already has access to this DB
127 --echo # UPDATE on mysql DB because global UPDATE priv is not being granted
128 GRANT INSERT ON *.* TO bar;
129 SHOW GRANTS FOR bar;
130 --connection default
131 --echo # Should be able to grant/revoke other DB level privileges without
132 --echo # affecting existing partial revokes.
133 REVOKE ALL ON *.* FROM bar;
134 --connection $conn_name
135 GRANT INSERT ON *.* TO bar;
136 GRANT INSERT ON `pr_db`.* TO bar;
137 SHOW GRANTS FOR bar;
138 --echo # Revoke the privilge. Partial revokes should remain intact
139 REVOKE INSERT ON `pr_db`.* FROM bar;
140 SHOW GRANTS FOR bar;
141 --source ./partial_revokes_rpl_helper.inc
142 --connection default
143 disconnect $conn_name;
144 DROP USER foo, bar;
145
146 --echo #
147 --echo # 4. Grantor and grantee both have restriction list
148 --echo #
149 --echo # 4.1 Restrictions are aggregated
150 CREATE USER foo, bar;
151 GRANT INSERT, SELECT, UPDATE ON *.* TO foo WITH GRANT OPTION;
152 --echo # Restrict INSERT, UPDATE on mysql DB from foo.
153 REVOKE INSERT, UPDATE ON mysql.* FROM foo;
154 SELECT user, user_attributes FROM mysql.user WHERE user LIKE 'foo';
155 GRANT DELETE, DROP, UPDATE, SELECT ON *.* TO bar;
156 --echo # Restrict UPDATE and DROP on mysql DB.
157 REVOKE UPDATE, DROP ON mysql.* FROM bar;
158 SHOW GRANTS FOR bar;
159 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
160 --echo [connection $conn_name]
161 --echo # Restrictions from Grantor and Grantee must be aggregated
162 GRANT INSERT ON *.* TO bar;
163 SHOW GRANTS FOR bar;
164 --source ./partial_revokes_rpl_helper.inc
165 --connection default
166 --disconnect $conn_name
167 REVOKE ALL ON *.* FROM foo, bar;
168 --echo # 4.2 Restrictions are cleared
169 GRANT SELECT, CREATE, ALTER ON *.* TO foo WITH GRANT OPTION;
170 REVOKE SELECT ON mysql.* FROM foo;
171 GRANT SELECT, CREATE ON *.* TO bar;
172 REVOKE CREATE ON mysql.* FROM bar;
173 SHOW GRANTS FOR foo;
174 SHOW GRANTS FOR bar;
175 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
176 --echo [connection $conn_name]
177 --echo # Aggregated restrictions turns out to be empty
178 GRANT SELECT, CREATE,ALTER ON *.* TO bar;
179 --source ./partial_revokes_rpl_helper.inc
180 --connection default
181 SHOW GRANTS FOR bar;
182 disconnect $conn_name;
183 DROP USER foo, bar;
184 --echo #
185 --echo # 5. Grantor has restriction list and grantee does not have any priv
186 --echo #
187 CREATE USER foo, bar;
188 GRANT INSERT, SELECT ON *.* TO foo WITH GRANT OPTION;
189 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
190 --echo [connection $conn_name]
191 --echo # Grantor should be able to add revoke on mysql DB on itself
192 REVOKE INSERT ON mysql.* FROM foo;
193 SHOW GRANTS FOR foo;
194 --connect($reconn_name, localhost, foo,,,$MASTER_MYPORT)
195 --echo [connection $reconn_name]
196 --echo # Restrictions must remain intact if grantor reconnects and try to
197 --echo # grant global privilege to self. In other words, it must be no-op.
198 GRANT INSERT ON *.* TO foo;
199 SHOW GRANTS FOR foo;
200 --echo # Grantee should get the restriction_list of grantor
201 GRANT INSERT, SELECT ON *.* TO bar;
202 SHOW GRANTS FOR bar;
203 --echo # Grantor should be able to add additional restrictions list
204 REVOKE INSERT,SELECT ON `pr_db`.* FROM bar;
205 SHOW GRANTS FOR bar;
206 --echo # Additional restrictions of Grantee should be removed.
207 GRANT INSERT, SELECT ON *.* TO bar;
208 SHOW GRANTS FOR bar;
209 --echo # Should remove, the global privileges and restrictions both
210 REVOKE SELECT, INSERT ON *.* FROM bar;
211 SHOW GRANTS FOR bar;
212 --echo # Grantee should get the restriction_list of grantor again
213 GRANT INSERT, SELECT ON *.* TO bar;
214 SHOW GRANTS FOR bar;
215 --echo # Remove only global privilege from grantee
216 REVOKE SELECT ON *.* FROM bar;
217 SHOW GRANTS FOR bar;
218 --source ./partial_revokes_rpl_helper.inc
219 --connection default
220 disconnect $conn_name;
221 disconnect $reconn_name;
222 DROP USER foo, bar;
223
224 --echo #
225 --echo # 6. Revoke global privilege as well restriction list from grantee
226 --echo # when grantor does not have restriction list
227 --echo #
228 CREATE USER foo, bar;
229 GRANT SELECT, INSERT ON *.* TO foo WITH GRANT OPTION;
230 --echo # User already have DB level priv
231 GRANT INSERT ON mysql.* TO bar;
232 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
233 --echo [connection $conn_name]
234 --echo # Grant global INSERT privilege to bar
235 GRANT INSERT ON *.* TO bar;
236 --echo # Revoke INSERT on mysql DB from bar
237 REVOKE INSERT ON mysql.* FROM bar;
238 SHOW GRANTS FOR bar;
239 --source ./partial_revokes_rpl_helper.inc
240 --echo # Add partial revoke INSERT on mysql DB for bar.
241 REVOKE INSERT ON mysql.* FROM bar;
242 SHOW GRANTS FOR bar;
243 --source ./partial_revokes_rpl_helper.inc
244 --echo # Revoke Global privilege as well as partial revoke.
245 REVOKE INSERT ON *.* FROM bar;
246 SHOW GRANTS FOR bar;
247 --source ./partial_revokes_rpl_helper.inc
248 disconnect $conn_name;
249 --connection default
250 DROP USER foo,bar;
251
252 --echo #
253 --echo # 7. Granting a global privilege should not affect the restrictions
254 --echo # of different privileges on other databases.
255 --echo #
256 CREATE USER foo;
257 CREATE DATABASE secret;
258 CREATE DATABASE meeting;
259 GRANT INSERT, SELECT, UPDATE ON *.* TO foo;
260 REVOKE SELECT,INSERT ON secret.* FROM foo;
261 REVOKE SELECT, UPDATE ON meeting.* FROM foo;
262 SHOW GRANTS FOR foo;
263 --echo # Only SELECT restrictions should have removed from
264 --echo # all databases in the restriction_list
265 GRANT SELECT ON *.* TO foo;
266 SHOW GRANTS FOR foo;
267 DROP USER foo;
268 DROP DATABASE secret;
269 DROP DATABASE meeting;
270
271 --echo #
272 --echo # 8. Granting a DB level privilege should not affect the restrictions
273 --echo # of different privileges on other databases.
274 --echo #
275 CREATE USER foo;
276 CREATE DATABASE secret;
277 CREATE DATABASE meeting;
278 GRANT INSERT,DELETE, SELECT, UPDATE ON *.* TO foo;
279 GRANT SELECT,INSERT ON meeting.* TO foo;
280 GRANT DELETE,UPDATE ON secret.* TO foo;
281 REVOKE SELECT,INSERT ON secret.* FROM foo;
282 REVOKE DELETE, UPDATE ON meeting.* FROM foo;
283 --echo # Grant sub-set from the set of already granted privileges
284 GRANT SELECT ON meeting.* TO foo WITH GRANT OPTION;
285 --echo # The restrictions should not have modified.
286 SHOW GRANTS FOR foo;
287 --echo # Grant all privileges from set of already granted privileges
288 GRANT SELECT,INSERT ON meeting.* TO foo WITH GRANT OPTION;
289 --echo # The restrictions should not have modified.
290 SHOW GRANTS FOR foo;
291 --echo # Grant sub-set from the set of already granted privileges
292 GRANT UPDATE ON secret.* TO foo;
293 --echo # The restrictions should not have modified.
294 SHOW GRANTS FOR foo;
295 --echo # Grant all privileges from set of already granted privileges
296 GRANT DELETE,UPDATE ON secret.* TO foo;
297 --echo # The restrictions should not have modified.
298 SHOW GRANTS FOR foo;
299 --echo # Remove the partial revokes
300 GRANT SELECT,INSERT ON secret.* TO foo;
301 GRANT DELETE,UPDATE ON meeting.* TO foo;
302 SHOW GRANTS FOR foo;
303 DROP USER foo;
304 DROP DATABASE secret;
305 DROP DATABASE meeting;
306
307 --echo #
308 --echo # 9. Revoke all privileges from user(s)
309 --echo #
310 CREATE USER foo, bar, baz;
311 CREATE DATABASE secret_db;
312 --echo #
313 --echo # 9.1. Grantor has restriction list but grantee does not.
314 GRANT ALL ON *.* TO foo,baz;
315 GRANT ALL ON mysql.* TO foo,baz;
316 GRANT ALL ON mysql.* TO bar;
317 --echo # All privileges must be cleared from all three users
318 REVOKE ALL ON *.* FROM foo;
319 REVOKE ALL ON mysql.* FROM baz;
320 REVOKE ALL ON mysql.* FROM bar;
321 SHOW GRANTS FOR foo;
322 SHOW GRANTS FOR bar;
323 SHOW GRANTS FOR baz;
324 DROP USER baz;
325 --echo # Create partial_revokes for foo
326 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
327 REVOKE SELECT ON mysql.* FROM foo;
328 --echo # GRANT ALL should remove the partial revokes created above
329 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
330 SHOW GRANTS FOR foo;
331 --echo # create partial revokes again
332 REVOKE SELECT ON mysql.* FROM foo;
333 SHOW GRANTS FOR foo;
334 GRANT ALL ON mysql.* TO bar;
335 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
336 --echo [connection $conn_name]
337 --error ER_DB_ACCESS_DENIED
338 REVOKE ALL ON *.* FROM bar;
339 --connection default
340 REVOKE ALL ON mysql.* FROM bar;
341 GRANT ALL ON *.* TO bar;
342 REVOKE SELECT ON mysql.* FROM bar;
343 --echo # grantor and grantee both have exact same privileges and partial revokes
344 SHOW GRANTS FOR foo;
345 SHOW GRANTS FOR bar;
346 --connection $conn_name
347 --echo # grantor should be able to remove the privileges & partial revokes both
348 REVOKE ALL ON *.* FROM bar;
349 --connection default
350 SHOW GRANTS FOR bar;
351 --source ./partial_revokes_rpl_helper.inc
352 --connection default
353 --disconnect $conn_name
354 REVOKE ALL ON *.* FROM foo,bar;
355 --echo #
356 --echo # 9.2. REVOKE ALL followed by GLOBAL grants should create restrictions
357 --connection default
358 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
359 --echo # Following should create restrictions for mysql db
360 REVOKE ALL ON mysql.* FROM foo;
361 SHOW GRANTS FOR foo;
362 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
363 --echo # restrictions must pass to bar as well
364 GRANT ALL ON *.* TO bar;
365 --connection default
366 SHOW GRANTS FOR bar;
367 --source ./partial_revokes_rpl_helper.inc
368 --connection default
369 --disconnect $conn_name
370 REVOKE ALL ON *.* FROM foo,bar;
371
372 --echo #
373 --echo # 9.3. Grantee has restriction list but grantor does not.
374 GRANT CREATE USER ON *.* TO foo WITH GRANT OPTION;
375 GRANT INSERT,SELECT ON *.* TO foo, bar;
376 REVOKE SELECT,INSERT ON mysql.* FROM bar;
377 REVOKE INSERT ON secret_db.* FROM bar;
378 SHOW GRANTS FOR bar;
379 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
380 --echo [connection $conn_name]
381 REVOKE ALL ON *.* FROM bar;
382 SHOW GRANTS FOR bar;
383 --source ./partial_revokes_rpl_helper.inc
384 --connection default
385 --disconnect $conn_name
386 --echo #
387 --echo # 9.4. Grantor and grantee both have restriction_list
388 GRANT INSERT,SELECT ON *.* TO bar;
389 REVOKE SELECT ON mysql.* FROM bar;
390 REVOKE SELECT,INSERT ON secret_db.* FROM bar;
391 SHOW GRANTS FOR bar;
392 GRANT UPDATE ON *.* TO foo;
393 REVOKE UPDATE,SELECT ON secret_db.* FROM foo;
394 SHOW GRANTS FOR foo;
395 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
396 --echo [connection $conn_name]
397 --echo # Should fail because foo cannot remove UPDATE priv on bar
398 --error ER_DB_ACCESS_DENIED
399 REVOKE ALL ON *.* FROM bar;
400 --connection default
401 --disconnect $conn_name
402 GRANT UPDATE ON `secret_db`.* TO foo;
403 SHOW GRANTS FOR foo;
404 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
405 --echo [connection $conn_name]
406 --echo # Should work because restriction on foo is different from bar
407 REVOKE ALL ON *.* FROM bar;
408 SHOW GRANTS FOR bar;
409 --source ./partial_revokes_rpl_helper.inc
410 --connection default
411 --disconnect $conn_name
412 DROP USER foo, bar;
413 DROP DATABASE secret_db;
414
415 --echo #
416 --echo # 10. Revoke a global privilege from grantee which is not present in the
417 --echo # grantor's restriction_list
418 --echo #
419 CREATE USER foo, bar;
420 GRANT SELECT,INSERT ON *.* TO foo WITH GRANT OPTION;
421 REVOKE INSERT ON mysql.* FROM foo;
422 GRANT SELECT ON *.* TO bar;
423 SHOW GRANTS FOR foo;
424 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
425 --echo [connection $conn_name]
426 --echo # Should work because restriction on foo is different from bar
427 REVOKE SELECT ON *.* FROM bar;
428 SHOW GRANTS FOR bar;
429 --source ./partial_revokes_rpl_helper.inc
430 --connection default
431 --disconnect $conn_name
432 DROP USER foo, bar;
433
434 --echo #
435 --echo # 11. Partial revokes with anonymous user
436 --echo #
437 INSERT INTO pr_db.t1 values (1,2), (3,4);
438 INSERT INTO normal_db.t1 values (1,2), (3,4);
439 --echo #
440 --echo # 11.1 Grantor is anonymous user
441 --echo #
442 --let $USER_1 = ''@'%'
443 --let $USER_2 = ''@''
444 # We can't create ''@'%' unless we delete row from mysql.db
445 # Disable warning when running on binlog_format=stmt, to keep
446 # keep the result files same for all binlog format.
447 --disable_warnings
448 CREATE TEMPORARY TABLE mysql.db_copy AS SELECT * FROM mysql.db;
449 --enable_warnings
450 DELETE FROM mysql.db WHERE host='%';
451 FLUSH PRIVILEGES;
452 eval CREATE USER $USER_1, $USER_2;
453 # Restore mysql.db to its original state
454 eval GRANT SELECT ON *.* TO $USER_1 WITH GRANT OPTION;
455 eval REVOKE SELECT ON pr_db.* FROM $USER_1;
456 --let $conn_name = anonymous_con_1
457 --connect($conn_name,localhost,anon,,,$MASTER_MYPORT)
458 --echo [connection $conn_name]
459 SHOW GRANTS;
460 SELECT COUNT(*) FROM normal_db.t1;
461 --error ER_TABLEACCESS_DENIED_ERROR
462 SELECT COUNT(*) FROM pr_db.t1;
463 eval GRANT SELECT ON *.* TO $USER_2;
464 eval SHOW GRANTS FOR $USER_2;
465 --connection default
466 --disconnect $conn_name
467 eval DROP USER $USER_1,$USER_2;
468 --echo #
469 --echo # 11.2 Grantee is anonymous user
470 --echo #
471 --let $USER_1 = foo@localhost
472 --let $USER_2 = ''@'%'
473 eval CREATE USER $USER_1,$USER_2;
474 eval GRANT SELECT ON *.* TO $USER_1,$USER_2 WITH GRANT OPTION;
475 --let $conn_name = foo_master
476 --connect($conn_name,localhost,foo,,,$MASTER_MYPORT)
477 --echo [connection $conn_name]
478 --echo # Create partial revoke for anonymous user from normal user
479 eval REVOKE SELECT ON pr_db.* FROM $USER_2;
480 eval SHOW GRANTS FOR $USER_2;
481 --disconnect $conn_name
482 --echo # Anynmous user cannot access the restricted database
483 --let $conn_name = anonymous_con_1
484 --connect($conn_name,localhost,anon,,,$MASTER_MYPORT)
485 --echo [connection $conn_name]
486 SELECT COUNT(*) FROM normal_db.t1;
487 --error ER_TABLEACCESS_DENIED_ERROR
488 SELECT COUNT(*) FROM pr_db.t1;
489 --connection default
490 --disconnect $conn_name
491 eval DROP USER $USER_1,$USER_2;
492 # Restore mysql.db to its original state
493 DELETE FROM mysql.db;
494 INSERT INTO mysql.db SELECT * FROM mysql.db_copy;
495 FLUSH PRIVILEGES;
496 DROP TABLE mysql.db_copy;
497 --echo #
498 --echo # 12. Hybrid scenarios
499 --echo #
500 CREATE USER foo, bar;
501 CREATE DATABASE robo;
502 GRANT UPDATE, INSERT ON *.* TO bar;
503 REVOKE UPDATE, INSERT ON robo.* FROM bar;
504 SHOW GRANTS FOR bar;
505 --echo # 12.1. Remove the applicable partial revokes(update and insert) as
506 --echo # well as grant applicable DB level privileges(select and delete)
507 GRANT INSERT, UPDATE, DELETE, SELECT ON robo.* TO bar;
508 SHOW GRANTS FOR bar;
509 --echo # 12.2. Add the applicable partial revokes(update and insert) as well
510 --echo # as revoke applicable DB level privileges(select and delete)
511 REVOKE INSERT, UPDATE, DELETE, SELECT ON robo.* FROM bar;
512 SHOW GRANTS FOR bar;
513 --echo # 12.3 Grant global and DB level privilege then revoke the DB level
514 # privilege as well as create a partial revoke
515 GRANT SELECT, DROP ON *.* TO foo;
516 GRANT DROP ON robo.* TO foo;
517 SHOW GRANTS FOR foo;
518 REVOKE SELECT, DROP ON robo.* FROM foo;
519 SHOW GRANTS FOR foo;
520 # Cleanup
521 DROP USER foo,bar;
522 DROP DATABASE robo;
523 --echo #
524 --echo # 13. Misc scenarios
525 --echo #
526 --echo # 13.1 Revoking DB level privilege multiple times should not
527 --echo # create/affect the restrictions
528 CREATE USER foo;
529 GRANT ALL ON mysql.* TO foo;
530 REVOKE SELECT ON mysql.* FROM foo;
531 REVOKE SELECT ON mysql.* FROM foo;
532 SHOW GRANTS FOR foo;
533 --echo # 13.2 Granting DB level privilege multiple times should not
534 --echo # create/affect the restrictions
535 REVOKE ALL ON mysql.* FROM foo;
536 GRANT SELECT ON mysql.* TO foo;
537 GRANT SELECT ON mysql.* TO foo;
538 SHOW GRANTS FOR foo;
539 DROP USER foo;
540
541 --echo ------------Tests related to Users end here -----------------------------
542
543 --echo -------------------------------------------------------------------------
544 --echo Tests related to Roles
545 --echo -------------------------------------------------------------------------
546
547 --echo #
548 --echo # 1. Activate/Deactivate roles with partial revokes
549 --echo #
550 CREATE USER foo, bar;
551 CREATE ROLE insert_role, select_role;
552 GRANT INSERT ON *.* TO insert_role WITH GRANT OPTION;
553 REVOKE INSERT ON pr_db.* FROM insert_role;
554 GRANT SELECT ON *.* TO select_role WITH GRANT OPTION;
555 REVOKE SELECT ON pr_db.* FROM select_role;
556 GRANT ROLE_ADMIN ON *.* TO foo;
557 GRANT select_role TO foo;
558 GRANT insert_role TO foo;
559 SHOW GRANTS FOR foo;
560 --let $conn_name = foo_master
561 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
562 --echo [connection $conn_name]
563 --echo # Must fail because foo does not have the SELECT privilege
564 --error ER_TABLEACCESS_DENIED_ERROR
565 SELECT COUNT(*) FROM mysql.user WHERE user = 'foo';
566 --echo # 1.1. Activate the select_role, now SELECT should work except on pr_db
567 SET ROLE select_role;
568 SHOW GRANTS;
569 SELECT COUNT(*) FROM normal_db.t1;
570 --error ER_TABLEACCESS_DENIED_ERROR
571 SELECT COUNT(*) FROM pr_db.t1;
572 --echo # Grant roles to another user 'bar'
573 GRANT select_role TO bar;
574 SHOW GRANTS FOR bar;
575 --source ./partial_revokes_rpl_helper.inc
576 --echo # 1.2. Activate the insert_role and deactivate the select_role
577 SET ROLE ALL EXCEPT select_role;
578 SHOW GRANTS;
579 --echo # select must fail but insert should work
580 --error ER_TABLEACCESS_DENIED_ERROR
581 SELECT COUNT(*) FROM normal_db.t1;
582 INSERT INTO normal_db.t1 values (5,6);
583 --echo # Grant roles to another user 'bar'
584 GRANT insert_role TO bar;
585 --connection default
586 SHOW GRANTS FOR bar;
587 --connection $conn_name
588 --source ./partial_revokes_rpl_helper.inc
589 --echo # 1.3. Deactivate the select_role activated before
590 SET ROLE NONE;
591 SHOW GRANTS;
592 --error ER_TABLEACCESS_DENIED_ERROR
593 SELECT COUNT(*) FROM normal_db.t1;
594 --error ER_TABLEACCESS_DENIED_ERROR
595 INSERT INTO normal_db.t1 values (5,6);
596 --echo # 1.4. Activate all roles
597 SET ROLE ALL;
598 SHOW GRANTS;
599 INSERT INTO normal_db.t1 values (7,8);
600 SELECT COUNT(*) FROM normal_db.t1;
601 --error ER_TABLEACCESS_DENIED_ERROR
602 SELECT COUNT(*) FROM pr_db.t1;
603 --error ER_TABLEACCESS_DENIED_ERROR
604 INSERT INTO pr_db.t1 values (7,8);
605 GRANT insert_role, select_role TO bar;
606 --connection default
607 SHOW GRANTS FOR bar;
608 --connection $conn_name
609 --source ./partial_revokes_rpl_helper.inc
610 --echo # Deactivate the select_role activated before
611 SET ROLE NONE;
612 --connection default
613 --echo #
614 --echo # 2. Default roles with partial revokes
615 --echo #
616 CREATE USER baz;
617 GRANT select_role to baz;
618 SHOW GRANTS FOR foo;
619 SHOW GRANTS FOR bar;
620 SHOW GRANTS FOR baz;
621 ALTER USER foo DEFAULT ROLE ALL;
622 SET DEFAULT ROLE select_role TO baz;
623 --connection $conn_name
624 --echo [connection $conn_name]
625 SET ROLE DEFAULT;
626 SHOW GRANTS;
627 INSERT INTO normal_db.t1 values (9,10);
628 SELECT COUNT(*) FROM normal_db.t1;
629 --error ER_TABLEACCESS_DENIED_ERROR
630 SELECT COUNT(*) FROM pr_db.t1;
631 --error ER_TABLEACCESS_DENIED_ERROR
632 INSERT INTO pr_db.t1 values (9,10);
633 GRANT insert_role, select_role TO bar;
634 --connection default
635 SHOW GRANTS FOR bar;
636 --source ./partial_revokes_rpl_helper.inc
637 --let $conn_name = baz_master
638 --connect($conn_name, localhost, baz,,,$MASTER_MYPORT)
639 --echo [connection $conn_name]
640 SET ROLE DEFAULT;
641 SHOW GRANTS;
642 SELECT COUNT(*) FROM normal_db.t1;
643 --error ER_TABLEACCESS_DENIED_ERROR
644 SELECT COUNT(*) FROM pr_db.t1;
645 --connection default
646 --disconnect $conn_name
647 DROP USER baz;
648 --echo # Reset the default roles granted to foo earlier
649 --let $conn_name = foo_master
650 --connection $conn_name
651 --echo [connection $conn_name]
652 SET ROLE NONE;
653 --connection default
654 SET DEFAULT ROLE NONE TO foo;
655 --echo #
656 --echo # 3. Mandatory roles with partial revokes
657 --echo #
658 CREATE ROLE delete_role;
659 GRANT DELETE ON *.* TO delete_role;
660 REVOKE DELETE ON pr_db.* FROM delete_role;
661 SET GLOBAL mandatory_roles='delete_role';
662 --connection $conn_name
663 --echo [connection $conn_name]
664 SET ROLE delete_role;
665 SHOW GRANTS;
666 DELETE FROM normal_db.t1;
667 --error ER_TABLEACCESS_DENIED_ERROR
668 DELETE FROM pr_db.t1;
669 --connection default
670 SET GLOBAL mandatory_roles='';
671 DROP ROLE delete_role;
672 --disconnect $conn_name
673 DROP USER foo, bar;
674 DROP ROLE insert_role, select_role;
675
676 --echo #
677 --echo # 4. Aggregation should not reduce privileges
678 --echo #
679 --echo [connection default]
680 CREATE USER bar;
681 CREATE ROLE r1;
682 GRANT SELECT ON *.* TO r1, bar;
683 REVOKE SELECT ON mysql.* FROM r1;
684 GRANT INSERT ON *.* TO r1;
685 GRANT r1 TO bar;
686 SHOW GRANTS FOR bar;
687 SHOW GRANTS FOR bar USING 'r1';
688
689 --connect($bar_conn, localhost, bar,,,$MASTER_MYPORT)
690 --echo [connection $bar_conn]
691 SET ROLE ALL;
692 SELECT user FROM mysql.user WHERE user LIKE 'bar';
693 SHOW GRANTS;
694 --connection default
695 --echo [connection default]
696 REVOKE INSERT ON *.* FROM r1;
697 GRANT SELECT ON *.* TO r1, bar;
698 REVOKE SELECT ON mysql.* FROM bar;
699 SHOW GRANTS FOR bar;
700 SHOW GRANTS FOR bar USING 'r1';
701
702 --connection $bar_conn
703 --echo [connection $bar_conn]
704 SET ROLE ALL;
705 SELECT user FROM mysql.user WHERE user LIKE 'bar';
706
707 --connection default
708 --echo [connection default]
709 --disconnect $bar_conn
710 DROP ROLE r1;
711 DROP USER bar;
712
713 --echo #
714 --echo # 5. Restriction list of roles should apply to grantee
715 --echo #
716 --echo [connection default]
717 CREATE USER foo, bar;
718 CREATE ROLE r1;
719 GRANT SELECT ON *.* TO r1 WITH GRANT OPTION;
720 REVOKE SELECT ON mysql.* FROM r1;
721 GRANT r1 TO foo;
722
723 --connect($conn_name, localhost, foo,,,$MASTER_MYPORT)
724 --echo [connection $conn_name]
725 SET ROLE ALL;
726 SHOW GRANTS;
727 GRANT SELECT ON *.* TO bar;
728
729 --connection default
730 --echo [connection default]
731 SHOW GRANTS FOR bar;
732 --source ./partial_revokes_rpl_helper.inc
733 --connection default
734 --disconnect $conn_name
735 DROP ROLE r1;
736 DROP USER foo, bar;
737
738 --echo #
739 --echo # 6. Roles have restrictions but user has global privilege
740 --echo #
741 CREATE USER bar;
742 CREATE ROLE r1;
743 GRANT SELECT ON *.* TO r1, bar;
744 REVOKE SELECT ON mysql.* FROM r1;
745 GRANT r1 TO bar;
746 --let $conn_name = bar_master
747 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
748 --echo [connection $conn_name]
749 SET ROLE ALL;
750 --echo # Restrictions of roles should not apply since user has higher privilege
751 SHOW GRANTS;
752 --echo # User's actual privileges ( and restrictions) must be visible
753 SHOW GRANTS FOR bar;
754 --source ./partial_revokes_rpl_helper.inc
755 --connection default
756 --disconnect $conn_name
757 DROP USER bar;
758 DROP ROLE r1;
759
760 --echo #
761 --echo # 7. Roles have global privilege but user has restrictions
762 --echo #
763 CREATE USER bar;
764 CREATE ROLE r1;
765 GRANT SELECT ON *.* TO r1, bar;
766 REVOKE SELECT ON mysql.* FROM bar;
767 GRANT r1 TO bar;
768 --let $conn_name = bar_master
769 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
770 --echo [connection $conn_name]
771 SET ROLE ALL;
772 --echo # User's restrictions should be removed through grantor(role).
773 SHOW GRANTS;
774 --echo # User's actual privileges ( and restrictions) must be visible
775 SHOW GRANTS FOR bar;
776 --source ./partial_revokes_rpl_helper.inc
777 --connection default
778 --disconnect $conn_name
779 DROP USER bar;
780 DROP ROLE r1;
781
782 --echo #
783 --echo # 8. Roles negating each other's restrictions
784 --echo #
785 --echo # 8.1 Both roles have partial revokes
786 CREATE ROLE r1, r2;
787 CREATE USER bar;
788 GRANT SELECT,INSERT ON *.* TO r1, r2;
789 REVOKE SELECT ON mysql.* FROM r1;
790 REVOKE INSERT ON mysql.* FROM r2;
791 --echo # Grant privileges and roles to user
792 GRANT DROP ON *.* TO bar;
793 GRANT UPDATE ON mysql.* TO bar;
794 GRANT r1, r2 TO bar;
795 --echo # Roles privileges and restrictions should apply to user
796 SHOW GRANTS FOR bar USING r1;
797 SHOW GRANTS FOR bar USING r2;
798 --echo # Roles global privileges should negate each others restrictions
799 SHOW GRANTS FOR bar USING r1, r2;
800 --let $conn_name = bar_master
801 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
802 --echo [connection $conn_name]
803 SET ROLE r1, r2;
804 SHOW GRANTS;
805 --source ./partial_revokes_rpl_helper.inc
806 --connection default
807 --disconnect $conn_name
808 DROP USER bar;
809 DROP ROLE r1, r2;
810 --echo # 8.2 One role has partial revoke and another has DB level grant
811 CREATE USER bar;
812 CREATE ROLE r1, r2;
813 GRANT r1, r2 TO bar;
814 GRANT SELECT ON *.* TO r1;
815 REVOKE SELECT ON mysql.* FROM r1;
816 GRANT SELECT ON mysql.* TO r2;
817 SHOW GRANTS FOR bar USING r1;
818 SHOW GRANTS FOR bar USING r2;
819 --echo # DB level privilege and partial revoke negate each other
820 SHOW GRANTS FOR bar USING r1, r2;
821 --let $conn_name = bar_master
822 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
823 --echo [connection $conn_name]
824 SET ROLE r1, r2;
825 SHOW GRANTS;
826 SET ROLE r1;
827 SHOW GRANTS;
828 SET ROLE r2;
829 SHOW GRANTS;
830 SET ROLE r2, r1;
831 SHOW GRANTS;
832 --source ./partial_revokes_rpl_helper.inc
833 --connection default
834 --disconnect $conn_name
835 DROP USER bar;
836 DROP ROLE r1, r2;
837 --echo # 8.3 One role has partial revoke & grants and another has DB grants
838 CREATE USER bar;
839 CREATE ROLE r1, r2;
840 GRANT r1, r2 TO bar;
841 GRANT SELECT, DROP ON *.* TO r1;
842 REVOKE SELECT ON mysql.* FROM r1;
843 GRANT UPDATE ON mysql.* TO r1;
844 GRANT SELECT, INSERT ON mysql.* TO r2;
845 SHOW GRANTS FOR bar USING r1;
846 SHOW GRANTS FOR bar USING r2;
847 --echo # DB level privilege and partial revoke negate each other
848 SHOW GRANTS FOR bar USING r1, r2;
849 --let $conn_name = bar_master
850 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
851 --echo [connection $conn_name]
852 SET ROLE r1, r2;
853 SHOW GRANTS;
854 --source ./partial_revokes_rpl_helper.inc
855 --connection default
856 --disconnect $conn_name
857 DROP USER bar;
858 DROP ROLE r1,r2;
859
860 --echo #
861 --echo # 9. SET ROLE removes the restrictions from the user. Now, Global
862 --echo # grant/revoke should not carry restrictions to grantee
863 --echo #
864 CREATE USER foo, bar;
865 CREATE ROLE r1;
866
867 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
868 REVOKE INSERT ON mysql.* FROM bar;
869 GRANT INSERT ON *.* TO r1;
870 GRANT r1 TO bar;
871 --let $conn_name = bar_master
872 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
873 --echo [connection $conn_name]
874 --echo # User should not have restriction for now
875 SET ROLE ALL;
876 SHOW GRANTS;
877 --echo # Grantee should not carry the actual restrictions of grantor
878 GRANT INSERT ON *.* TO foo;
879 SHOW GRANTS FOR foo;
880 --echo # Grantor should be able to remove the global privileges from grantee now
881 REVOKE INSERT ON *.* FROM foo;
882 SHOW GRANTS FOR foo;
883 --echo # Grantor should be able to grant global privilege to self
884 GRANT INSERT ON *.* TO bar;
885 SHOW GRANTS;
886 --connection default
887 --echo [connection default]
888 --echo # Since, user granted himself global privilege therefore it's
889 --echo # restrictions has gone permanently now.
890 SHOW GRANTS FOR bar;
891 --source ./partial_revokes_rpl_helper.inc
892 --connection default
893 --disconnect $conn_name
894 DROP USER foo, bar;
895 DROP ROLE r1;
896
897 --echo #
898 --echo # 10. SET ROLE removes the restrictions from the user. Now, DB level
899 --echo # grant/revoke should work as intended.
900 --echo #
901 CREATE USER foo, bar;
902 CREATE ROLE r1;
903
904 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
905 REVOKE INSERT ON mysql.* FROM bar;
906 GRANT INSERT ON *.* TO r1;
907 GRANT r1 TO bar;
908 --let $conn_name = bar_master
909 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
910 --echo [connection $conn_name]
911 --echo # User should not have restriction for now
912 SET ROLE ALL;
913 SHOW GRANTS;
914 --echo # Grantor should be able to grant DB level privilege to grantee
915 GRANT INSERT ON mysql.* TO foo;
916 SHOW GRANTS FOR foo;
917 --echo # Grantor should be able to revoke DB Level priviulege from grantee
918 REVOKE INSERT ON mysql.* FROM foo;
919 SHOW GRANTS FOR foo;
920 --echo # Should be able to grant DB level privilege to self
921 GRANT INSERT ON mysql.* TO bar;
922 SHOW GRANTS;
923 --connection default
924 --echo [connection default]
925 --echo # Since, user granted himself DB level privilege therefore it's
926 --echo # restrictions has gone permanently now.
927 SHOW GRANTS FOR bar;
928 --source ./partial_revokes_rpl_helper.inc
929 --connection default
930 --disconnect $conn_name
931 DROP USER foo, bar;
932 DROP ROLE r1;
933 --echo #
934 --echo # 11. SET ROLE removes the restrictions from the user. Now, DB level
935 --echo # grant/revoke ALL should work as intended.
936 --echo #
937 CREATE USER foo, bar;
938 CREATE ROLE r1;
939 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
940 GRANT ALL ON mysql.* TO bar;
941 REVOKE INSERT ON mysql.* FROM bar;
942 REVOKE INSERT ON mysql.* FROM bar;
943 GRANT INSERT ON *.* TO r1;
944 GRANT r1 TO bar;
945 --let $conn_name = bar_master
946 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
947 --echo [connection $conn_name]
948 --echo # User should not have restriction for now
949 SET ROLE ALL;
950 SHOW GRANTS;
951 --echo # Grantor should be able to grant ALL DB level privilege to grantee
952 GRANT ALL ON mysql.* TO foo;
953 SHOW GRANTS FOR foo;
954 --echo # Grantor should be able to revoke ALL DB Level privilege from grantee
955 REVOKE ALL ON mysql.* FROM foo;
956 SHOW GRANTS FOR foo;
957 --echo # Should be able to grant ALL DB level privileges to self
958 GRANT ALL ON mysql.* TO bar;
959 SHOW GRANTS;
960 --connection default
961 --echo [connection default]
962 --echo # Since, user granted himself all DB level privileges therefore it's
963 --echo # restrictions has gone permanently now.
964 SHOW GRANTS FOR bar;
965 --source ./partial_revokes_rpl_helper.inc
966 --connection default
967 --disconnect $conn_name
968 DROP USER foo, bar;
969 DROP ROLE r1;
970 --echo #
971 --echo # 12. SET ROLE removes the restrictions from the user. Now, global
972 --echo # grant/revoke ALL should work as intended.
973 --echo #
974 CREATE USER foo, bar;
975 CREATE ROLE r1;
976 GRANT ALL ON *.* TO bar WITH GRANT OPTION;
977 REVOKE INSERT ON mysql.* FROM bar;
978 GRANT INSERT ON *.* TO r1;
979 GRANT r1 TO bar;
980 --let $conn_name = bar_master
981 --connect($conn_name, localhost, bar,,,$MASTER_MYPORT)
982 --echo [connection $conn_name]
983 --echo # User should not have restriction for now
984 SET ROLE ALL;
985 SHOW GRANTS;
986 --echo # Grantor should be able to grant ALL global level privilege to grantee
987 GRANT ALL ON *.* TO foo;
988 SHOW GRANTS FOR foo;
989 --echo # Grantor should be able to revoke ALL global level privilege from
990 --echo # grantee
991 REVOKE ALL ON *.* FROM foo;
992 SHOW GRANTS FOR foo;
993 --echo # Should be able to grant ALL DB level privileges to self
994 GRANT ALL ON *.* TO bar;
995 SHOW GRANTS;
996 --connection default
997 --echo [connection default]
998 --echo # Since, user granted himself all DB level privileges therefore it's
999 --echo # restrictions has gone permanently now.
1000 SHOW GRANTS FOR bar;
1001 --source ./partial_revokes_rpl_helper.inc
1002 --connection default
1003 --disconnect $conn_name
1004 DROP USER foo, bar;
1005 DROP ROLE r1;
1006 --echo ---------------------Tests related to roles end here---------------------
1007
1008 --echo -------------------------------------------------------------------------
1009 --echo Invalid restrictions and privilege combinations
1010 --echo -------------------------------------------------------------------------
1011 CREATE USER foo, bar;
1012 --echo #
1013 --echo # 1. Add INSERT partial revoke on mysql DB for user bar
1014 --echo #
1015 GRANT SELECT, INSERT ON *.* TO bar;
1016 REVOKE SELECT, INSERT ON mysql.* FROM bar;
1017 SHOW GRANTS FOR bar;
1018 INSERT INTO mysql.db(user,host, db, Insert_priv) VALUES('bar', '%', 'mysql', 'Y');
1019 FLUSH PRIVILEGES;
1020 SHOW GRANTS FOR bar;
1021 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1022 GRANT INSERT ON *.* TO bar;
1023 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1024 REVOKE INSERT ON *.* FROM bar;
1025 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1026 GRANT INSERT ON mysql.* TO bar;
1027 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1028 REVOKE INSERT ON mysql.* FROM bar;
1029 --echo # We should not even able to grant/revoke fresh privileges.
1030 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1031 GRANT UPDATE ON mysql.* TO bar;
1032 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1033 REVOKE UPDATE ON mysql.* FROM bar;
1034 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1035 GRANT UPDATE ON *.* TO bar;
1036 --error ER_PARTIAL_REVOKE_AND_DB_GRANT_BOTH_EXISTS
1037 REVOKE UPDATE ON *.* FROM bar;
1038 --connect(conn_bar, localhost, bar,,,$MASTER_MYPORT)
1039 # Should work
1040 SELECT CURRENT_USER();
1041 SELECT COUNT(*) FROM pr_db.t1;
1042 --connection default
1043 disconnect conn_bar;
1044 DROP USER foo,bar;
1045
1046 --echo #
1047 --echo # 2. Add privilege other than DB_ACLs in user attributes column.
1048 --echo #
1049 CREATE USER u1;
1050 CREATE DATABASE db1;
1051 GRANT SELECT, INSERT ON *.* TO u1;
1052 REVOKE SELECT, INSERT ON db1.* FROM u1;
1053 UPDATE mysql.user SET user_attributes='{"Restrictions": [{"Database": "db1", "Privileges": ["SELECT", "INSERT", "SUPER", "DELETE"]}]}' WHERE user = 'u1';
1054 # We let our server restart attempts write to the file $server_log.
1055 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
1056 let SEARCH_FILE= $server_log;
1057
1058 --echo # Search for : ignored restrictions for privilege(s) 'SUPER' for database 'db1' as these are not valid database privileges
1059 let SEARCH_PATTERN= ignored restrictions for privilege(s) 'SUPER' for database 'db1' as these are not valid database privileges;
1060 --source include/search_pattern.inc
1061 --echo # Search completed.
1062
1063 --echo # Search for : ignored restrictions for privilege(s) 'DELETE' for database 'db1' as corresponding global privilege(s) are not granted
1064 let SEARCH_PATTERN= ignored restrictions for privilege(s) 'DELETE' for database 'db1' as corresponding global privilege(s) are not granted;
1065 --source include/search_pattern.inc
1066 --echo # Search completed.
1067
1068 DROP DATABASE db1;
1069 DROP USER u1;
1070 --echo -Tests related to invalid restrictions & privilege combinations end here-
1071 --echo
1072 --echo -------------------------------------------------------------------------
1073 --echo Partial revokes do not treat wildcard grants specially.
1074 --echo -------------------------------------------------------------------------
1075 CREATE USER foo;
1076 CREATE DATABASE `db_1`;
1077 CREATE DATABASE `db%1`;
1078 GRANT SELECT ON *.* TO foo;
1079
1080 --echo # Create restriction - We dont use '\' to escape '_' or '%'
1081 REVOKE SELECT ON `db_1`.* FROM foo;
1082 REVOKE SELECT ON `db%1`.* FROM foo;
1083
1084 --echo # Should show 2 restrictions
1085 SHOW GRANTS FOR foo;
1086
1087 --echo # Should not remove restrictions on db_1 or db%1,
1088 --echo # instead both grants should be added on db(s) as it is.
1089 GRANT SELECT ON `db\_1`.* TO foo;
1090 GRANT SELECT ON `db\%1`.* TO foo;
1091 SHOW GRANTS FOR foo;
1092
1093 --echo # Should remove two grants added above
1094 REVOKE SELECT ON `db\_1`.* FROM foo;
1095 REVOKE SELECT ON `db\%1`.* FROM foo;
1096
1097 --echo # Should show 2 restrictions
1098 SHOW GRANTS FOR foo;
1099
1100 # Cleanup
1101 REVOKE SELECT ON *.* FROM foo;
1102 DROP USER foo;
1103 DROP DATABASE `db_1`;
1104 DROP DATABASE `db%1`;
1105 --echo --------------- Test related to wildcard grants end here. ---------------
1106 --echo
1107 --echo -------------------------------------------------------------------------
1108 --echo Tests related to Bug 37570206 - REVOKE EXECUTE from a user throws ERROR
1109 --echo 3879 (HY000): Access denied *
1110 --echo -------------------------------------------------------------------------
1111
1112 CREATE USER bug37570206_user;
1113 CREATE USER bug37570206_admin;
1114 CREATE DATABASE bug37570206_db;
1115
1116 # Case 1: Both grantor and grantee has same set of restrictions
1117
1118 --connection default
1119 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1120 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_admin;
1121
1122 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1123 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1124 SHOW GRANTS FOR bug37570206_user;
1125
1126 --echo # Must succeed
1127 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1128 SHOW GRANTS FOR bug37570206_user;
1129
1130 # Case 2: Grantee has more restrictions than grantor
1131
1132 --connection default
1133 --disconnect conn_bug37570206_admin
1134 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1135
1136 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1137 REVOKE SELECT ON bug37570206_db.* FROM bug37570206_admin;
1138
1139 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1140 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1141
1142 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1143 --echo # Must succeed
1144 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1145
1146 --connection default
1147 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1148 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1149 REVOKE SELECT, EXECUTE ON mysql.* FROM bug37570206_user;
1150
1151 --connection conn_bug37570206_admin
1152 --echo # Must succeed
1153 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1154
1155 --connection default
1156 --disconnect conn_bug37570206_admin
1157 GRANT SELECT ON bug37570206_db.* TO bug37570206_admin;
1158 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1159 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1160
1161 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1162 --echo # Must succeed
1163 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1164
1165 # Case 3: Grantee has different privileges and/or restrictions than those being revoked
1166
1167 --connection default
1168 --disconnect conn_bug37570206_admin
1169 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1170
1171 GRANT SELECT, INSERT, UPDATE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1172 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1173
1174 GRANT SELECT, EXECUTE, DELETE ON *.* TO bug37570206_user;
1175 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1176
1177 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1178 --echo # Must succeed
1179 REVOKE SELECT ON *.* FROM bug37570206_user;
1180
1181 --connection default
1182 --disconnect conn_bug37570206_admin
1183 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1184
1185 GRANT ALL ON *.* TO bug37570206_admin WITH GRANT OPTION;
1186 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_admin;
1187
1188 GRANT SELECT, INSERT ON *.* TO bug37570206_user;
1189 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_user;
1190
1191 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1192 --echo # Must succeed
1193 REVOKE ALL ON *.* FROM bug37570206_user;
1194
1195 # Case 4: Grantee has less restrictions than grantor
1196
1197 --connection default
1198 --disconnect conn_bug37570206_admin
1199 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1200
1201 GRANT SELECT, INSERT, UPDATE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1202 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1203
1204 GRANT SELECT, EXECUTE, DELETE ON *.* TO bug37570206_user;
1205
1206 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1207 --echo # Must fail
1208 --error ER_DB_ACCESS_DENIED
1209 REVOKE SELECT ON *.* FROM bug37570206_user;
1210
1211 --connection default
1212 --disconnect conn_bug37570206_admin
1213 REVOKE DELETE ON bug37570206_db.* FROM bug37570206_user;
1214
1215 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1216 --echo # Must fail
1217 --error ER_DB_ACCESS_DENIED
1218 REVOKE SELECT ON *.* FROM bug37570206_user;
1219
1220 --connection default
1221 --disconnect conn_bug37570206_admin
1222 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1223
1224 GRANT ALL ON *.* TO bug37570206_admin WITH GRANT OPTION;
1225 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1226
1227 GRANT SELECT, UPDATE, DELETE, INSERT ON *.* TO bug37570206_user;
1228
1229 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1230 --echo # Must fail
1231 --error ER_DB_ACCESS_DENIED
1232 REVOKE ALL ON *.* FROM bug37570206_user;
1233
1234 --connection default
1235 --disconnect conn_bug37570206_admin
1236 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_user;
1237
1238 --connect(conn_bug37570206_admin, localhost, bug37570206_admin,,,$MASTER_MYPORT)
1239 --echo # Must fail
1240 --error ER_DB_ACCESS_DENIED
1241 REVOKE ALL ON *.* FROM bug37570206_user;
1242
1243 # Cleanup
1244 --connection default
1245 --disconnect conn_bug37570206_admin
1246 DROP USER bug37570206_admin, bug37570206_user;
1247 DROP DATABASE bug37570206_db;
1248
1249 --echo -------------------------------------------------------------------------
1250 --echo Tests related to Bug 37570206 - REVOKE EXECUTE from a user throws ERROR
1251 --echo 3879 (HY000): Access denied * end here
1252 --echo -------------------------------------------------------------------------
1253 #
1254 # Clean up
1255 #
1256 DROP DATABASE pr_db;
1257 DROP DATABASE normal_db;
1258 call mtr.add_suppression(".*Nothing to revoke. Global privilege.*");