Debian Codegraph @

mysql-test/suite/funcs_1/r/is_user_privileges.result

raw
1 SHOW TABLES FROM information_schema LIKE 'USER_PRIVILEGES';
2 Tables_in_information_schema (USER_PRIVILEGES)
3 USER_PRIVILEGES
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.USER_PRIVILEGES;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.USER_PRIVILEGES;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.USER_PRIVILEGES;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.USER_PRIVILEGES;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout
28 #########################################################################
29 DESCRIBE information_schema.USER_PRIVILEGES;
30 Field Type Null Key Default Extra
31 GRANTEE varchar(292) NO
32 TABLE_CATALOG varchar(512) NO
33 PRIVILEGE_TYPE varchar(64) NO
34 IS_GRANTABLE varchar(3) NO
35 SHOW CREATE TABLE information_schema.USER_PRIVILEGES;
36 Table Create Table
37 USER_PRIVILEGES CREATE TEMPORARY TABLE `USER_PRIVILEGES` (
38 `GRANTEE` varchar(292) NOT NULL DEFAULT '',
39 `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
40 `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
41 `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
42 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
43 SHOW COLUMNS FROM information_schema.USER_PRIVILEGES;
44 Field Type Null Key Default Extra
45 GRANTEE varchar(292) NO
46 TABLE_CATALOG varchar(512) NO
47 PRIVILEGE_TYPE varchar(64) NO
48 IS_GRANTABLE varchar(3) NO
49 SELECT grantee, table_catalog, privilege_type
50 FROM information_schema.user_privileges
51 WHERE table_catalog IS NULL OR table_catalog <> 'def';
52 grantee table_catalog privilege_type
53 ##########################################################################
54 # Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES
55 # accessible information
56 ##########################################################################
57 DROP DATABASE IF EXISTS db_datadict;
58 CREATE DATABASE db_datadict;
59 DROP USER 'testuser1'@'localhost';
60 CREATE USER 'testuser1'@'localhost';
61 DROP USER 'testuser2'@'localhost';
62 CREATE USER 'testuser2'@'localhost';
63 DROP USER 'testuser3'@'localhost';
64 CREATE USER 'testuser3'@'localhost';
65 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
66 GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
67 GRANT INSERT ON *.* TO 'testuser2'@'localhost';
68 GRANT UPDATE ON *.* TO 'testuser2'@'localhost';
69 SELECT * FROM information_schema.user_privileges
70 WHERE grantee LIKE '''testuser%'''
71 ORDER BY grantee, table_catalog, privilege_type;
72 GRANTEE 'testuser1'@'localhost'
73 TABLE_CATALOG def
74 PRIVILEGE_TYPE USAGE
75 IS_GRANTABLE NO
76 GRANTEE 'testuser2'@'localhost'
77 TABLE_CATALOG def
78 PRIVILEGE_TYPE INSERT
79 IS_GRANTABLE NO
80 GRANTEE 'testuser2'@'localhost'
81 TABLE_CATALOG def
82 PRIVILEGE_TYPE UPDATE
83 IS_GRANTABLE NO
84 GRANTEE 'testuser3'@'localhost'
85 TABLE_CATALOG def
86 PRIVILEGE_TYPE USAGE
87 IS_GRANTABLE NO
88 SELECT * FROM mysql.user
89 WHERE user LIKE 'testuser%' ORDER BY host, user;
90 Host localhost
91 User testuser1
92 Select_priv N
93 Insert_priv N
94 Update_priv N
95 Delete_priv N
96 Create_priv N
97 Drop_priv N
98 Reload_priv N
99 Shutdown_priv N
100 Process_priv N
101 File_priv N
102 Grant_priv N
103 References_priv N
104 Index_priv N
105 Alter_priv N
106 Show_db_priv N
107 Super_priv N
108 Create_tmp_table_priv N
109 Lock_tables_priv N
110 Execute_priv N
111 Repl_slave_priv N
112 Repl_client_priv N
113 Create_view_priv N
114 Show_view_priv N
115 Create_routine_priv N
116 Alter_routine_priv N
117 Create_user_priv N
118 Event_priv N
119 Trigger_priv N
120 Create_tablespace_priv N
121 ssl_type
122 ssl_cipher
123 x509_issuer
124 x509_subject
125 max_questions 0
126 max_updates 0
127 max_connections 0
128 max_user_connections 0
129 plugin caching_sha2_password
130 authentication_string
131 password_expired N
132 password_last_changed DTVALUE
133 password_lifetime NULL
134 account_locked N
135 Create_role_priv N
136 Drop_role_priv N
137 Password_reuse_history NULL
138 Password_reuse_time NULL
139 Password_require_current NULL
140 User_attributes NULL
141 Host localhost
142 User testuser2
143 Select_priv N
144 Insert_priv Y
145 Update_priv Y
146 Delete_priv N
147 Create_priv N
148 Drop_priv N
149 Reload_priv N
150 Shutdown_priv N
151 Process_priv N
152 File_priv N
153 Grant_priv N
154 References_priv N
155 Index_priv N
156 Alter_priv N
157 Show_db_priv N
158 Super_priv N
159 Create_tmp_table_priv N
160 Lock_tables_priv N
161 Execute_priv N
162 Repl_slave_priv N
163 Repl_client_priv N
164 Create_view_priv N
165 Show_view_priv N
166 Create_routine_priv N
167 Alter_routine_priv N
168 Create_user_priv N
169 Event_priv N
170 Trigger_priv N
171 Create_tablespace_priv N
172 ssl_type
173 ssl_cipher
174 x509_issuer
175 x509_subject
176 max_questions 0
177 max_updates 0
178 max_connections 0
179 max_user_connections 0
180 plugin caching_sha2_password
181 authentication_string
182 password_expired N
183 password_last_changed DTVALUE
184 password_lifetime NULL
185 account_locked N
186 Create_role_priv N
187 Drop_role_priv N
188 Password_reuse_history NULL
189 Password_reuse_time NULL
190 Password_require_current NULL
191 User_attributes NULL
192 Host localhost
193 User testuser3
194 Select_priv N
195 Insert_priv N
196 Update_priv N
197 Delete_priv N
198 Create_priv N
199 Drop_priv N
200 Reload_priv N
201 Shutdown_priv N
202 Process_priv N
203 File_priv N
204 Grant_priv N
205 References_priv N
206 Index_priv N
207 Alter_priv N
208 Show_db_priv N
209 Super_priv N
210 Create_tmp_table_priv N
211 Lock_tables_priv N
212 Execute_priv N
213 Repl_slave_priv N
214 Repl_client_priv N
215 Create_view_priv N
216 Show_view_priv N
217 Create_routine_priv N
218 Alter_routine_priv N
219 Create_user_priv N
220 Event_priv N
221 Trigger_priv N
222 Create_tablespace_priv N
223 ssl_type
224 ssl_cipher
225 x509_issuer
226 x509_subject
227 max_questions 0
228 max_updates 0
229 max_connections 0
230 max_user_connections 0
231 plugin caching_sha2_password
232 authentication_string
233 password_expired N
234 password_last_changed DTVALUE
235 password_lifetime NULL
236 account_locked N
237 Create_role_priv N
238 Drop_role_priv N
239 Password_reuse_history NULL
240 Password_reuse_time NULL
241 Password_require_current NULL
242 User_attributes NULL
243 #
244 # Add GRANT OPTION db_datadict.* to testuser1;
245 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
246 SELECT * FROM information_schema.user_privileges
247 WHERE grantee LIKE '''testuser%'''
248 ORDER BY grantee, table_catalog, privilege_type;
249 GRANTEE 'testuser1'@'localhost'
250 TABLE_CATALOG def
251 PRIVILEGE_TYPE USAGE
252 IS_GRANTABLE NO
253 GRANTEE 'testuser2'@'localhost'
254 TABLE_CATALOG def
255 PRIVILEGE_TYPE INSERT
256 IS_GRANTABLE NO
257 GRANTEE 'testuser2'@'localhost'
258 TABLE_CATALOG def
259 PRIVILEGE_TYPE UPDATE
260 IS_GRANTABLE NO
261 GRANTEE 'testuser3'@'localhost'
262 TABLE_CATALOG def
263 PRIVILEGE_TYPE USAGE
264 IS_GRANTABLE NO
265 SELECT * FROM mysql.user
266 WHERE user LIKE 'testuser%' ORDER BY host, user;
267 Host localhost
268 User testuser1
269 Select_priv N
270 Insert_priv N
271 Update_priv N
272 Delete_priv N
273 Create_priv N
274 Drop_priv N
275 Reload_priv N
276 Shutdown_priv N
277 Process_priv N
278 File_priv N
279 Grant_priv N
280 References_priv N
281 Index_priv N
282 Alter_priv N
283 Show_db_priv N
284 Super_priv N
285 Create_tmp_table_priv N
286 Lock_tables_priv N
287 Execute_priv N
288 Repl_slave_priv N
289 Repl_client_priv N
290 Create_view_priv N
291 Show_view_priv N
292 Create_routine_priv N
293 Alter_routine_priv N
294 Create_user_priv N
295 Event_priv N
296 Trigger_priv N
297 Create_tablespace_priv N
298 ssl_type
299 ssl_cipher
300 x509_issuer
301 x509_subject
302 max_questions 0
303 max_updates 0
304 max_connections 0
305 max_user_connections 0
306 plugin caching_sha2_password
307 authentication_string
308 password_expired N
309 password_last_changed DTVALUE
310 password_lifetime NULL
311 account_locked N
312 Create_role_priv N
313 Drop_role_priv N
314 Password_reuse_history NULL
315 Password_reuse_time NULL
316 Password_require_current NULL
317 User_attributes NULL
318 Host localhost
319 User testuser2
320 Select_priv N
321 Insert_priv Y
322 Update_priv Y
323 Delete_priv N
324 Create_priv N
325 Drop_priv N
326 Reload_priv N
327 Shutdown_priv N
328 Process_priv N
329 File_priv N
330 Grant_priv N
331 References_priv N
332 Index_priv N
333 Alter_priv N
334 Show_db_priv N
335 Super_priv N
336 Create_tmp_table_priv N
337 Lock_tables_priv N
338 Execute_priv N
339 Repl_slave_priv N
340 Repl_client_priv N
341 Create_view_priv N
342 Show_view_priv N
343 Create_routine_priv N
344 Alter_routine_priv N
345 Create_user_priv N
346 Event_priv N
347 Trigger_priv N
348 Create_tablespace_priv N
349 ssl_type
350 ssl_cipher
351 x509_issuer
352 x509_subject
353 max_questions 0
354 max_updates 0
355 max_connections 0
356 max_user_connections 0
357 plugin caching_sha2_password
358 authentication_string
359 password_expired N
360 password_last_changed DTVALUE
361 password_lifetime NULL
362 account_locked N
363 Create_role_priv N
364 Drop_role_priv N
365 Password_reuse_history NULL
366 Password_reuse_time NULL
367 Password_require_current NULL
368 User_attributes NULL
369 Host localhost
370 User testuser3
371 Select_priv N
372 Insert_priv N
373 Update_priv N
374 Delete_priv N
375 Create_priv N
376 Drop_priv N
377 Reload_priv N
378 Shutdown_priv N
379 Process_priv N
380 File_priv N
381 Grant_priv N
382 References_priv N
383 Index_priv N
384 Alter_priv N
385 Show_db_priv N
386 Super_priv N
387 Create_tmp_table_priv N
388 Lock_tables_priv N
389 Execute_priv N
390 Repl_slave_priv N
391 Repl_client_priv N
392 Create_view_priv N
393 Show_view_priv N
394 Create_routine_priv N
395 Alter_routine_priv N
396 Create_user_priv N
397 Event_priv N
398 Trigger_priv N
399 Create_tablespace_priv N
400 ssl_type
401 ssl_cipher
402 x509_issuer
403 x509_subject
404 max_questions 0
405 max_updates 0
406 max_connections 0
407 max_user_connections 0
408 plugin caching_sha2_password
409 authentication_string
410 password_expired N
411 password_last_changed DTVALUE
412 password_lifetime NULL
413 account_locked N
414 Create_role_priv N
415 Drop_role_priv N
416 Password_reuse_history NULL
417 Password_reuse_time NULL
418 Password_require_current NULL
419 User_attributes NULL
420 # Establish connection testuser1 (user=testuser1)
421 SELECT * FROM information_schema.user_privileges
422 WHERE grantee LIKE '''testuser%'''
423 ORDER BY grantee, table_catalog, privilege_type;
424 GRANTEE 'testuser1'@'localhost'
425 TABLE_CATALOG def
426 PRIVILEGE_TYPE USAGE
427 IS_GRANTABLE NO
428 SELECT * FROM mysql.user
429 WHERE user LIKE 'testuser%' ORDER BY host, user;
430 Host localhost
431 User testuser1
432 Select_priv N
433 Insert_priv N
434 Update_priv N
435 Delete_priv N
436 Create_priv N
437 Drop_priv N
438 Reload_priv N
439 Shutdown_priv N
440 Process_priv N
441 File_priv N
442 Grant_priv N
443 References_priv N
444 Index_priv N
445 Alter_priv N
446 Show_db_priv N
447 Super_priv N
448 Create_tmp_table_priv N
449 Lock_tables_priv N
450 Execute_priv N
451 Repl_slave_priv N
452 Repl_client_priv N
453 Create_view_priv N
454 Show_view_priv N
455 Create_routine_priv N
456 Alter_routine_priv N
457 Create_user_priv N
458 Event_priv N
459 Trigger_priv N
460 Create_tablespace_priv N
461 ssl_type
462 ssl_cipher
463 x509_issuer
464 x509_subject
465 max_questions 0
466 max_updates 0
467 max_connections 0
468 max_user_connections 0
469 plugin caching_sha2_password
470 authentication_string
471 password_expired N
472 password_last_changed DTVALUE
473 password_lifetime NULL
474 account_locked N
475 Create_role_priv N
476 Drop_role_priv N
477 Password_reuse_history NULL
478 Password_reuse_time NULL
479 Password_require_current NULL
480 User_attributes NULL
481 Host localhost
482 User testuser2
483 Select_priv N
484 Insert_priv Y
485 Update_priv Y
486 Delete_priv N
487 Create_priv N
488 Drop_priv N
489 Reload_priv N
490 Shutdown_priv N
491 Process_priv N
492 File_priv N
493 Grant_priv N
494 References_priv N
495 Index_priv N
496 Alter_priv N
497 Show_db_priv N
498 Super_priv N
499 Create_tmp_table_priv N
500 Lock_tables_priv N
501 Execute_priv N
502 Repl_slave_priv N
503 Repl_client_priv N
504 Create_view_priv N
505 Show_view_priv N
506 Create_routine_priv N
507 Alter_routine_priv N
508 Create_user_priv N
509 Event_priv N
510 Trigger_priv N
511 Create_tablespace_priv N
512 ssl_type
513 ssl_cipher
514 x509_issuer
515 x509_subject
516 max_questions 0
517 max_updates 0
518 max_connections 0
519 max_user_connections 0
520 plugin caching_sha2_password
521 authentication_string
522 password_expired N
523 password_last_changed DTVALUE
524 password_lifetime NULL
525 account_locked N
526 Create_role_priv N
527 Drop_role_priv N
528 Password_reuse_history NULL
529 Password_reuse_time NULL
530 Password_require_current NULL
531 User_attributes NULL
532 Host localhost
533 User testuser3
534 Select_priv N
535 Insert_priv N
536 Update_priv N
537 Delete_priv N
538 Create_priv N
539 Drop_priv N
540 Reload_priv N
541 Shutdown_priv N
542 Process_priv N
543 File_priv N
544 Grant_priv N
545 References_priv N
546 Index_priv N
547 Alter_priv N
548 Show_db_priv N
549 Super_priv N
550 Create_tmp_table_priv N
551 Lock_tables_priv N
552 Execute_priv N
553 Repl_slave_priv N
554 Repl_client_priv N
555 Create_view_priv N
556 Show_view_priv N
557 Create_routine_priv N
558 Alter_routine_priv N
559 Create_user_priv N
560 Event_priv N
561 Trigger_priv N
562 Create_tablespace_priv N
563 ssl_type
564 ssl_cipher
565 x509_issuer
566 x509_subject
567 max_questions 0
568 max_updates 0
569 max_connections 0
570 max_user_connections 0
571 plugin caching_sha2_password
572 authentication_string
573 password_expired N
574 password_last_changed DTVALUE
575 password_lifetime NULL
576 account_locked N
577 Create_role_priv N
578 Drop_role_priv N
579 Password_reuse_history NULL
580 Password_reuse_time NULL
581 Password_require_current NULL
582 User_attributes NULL
583 SHOW GRANTS;
584 Grants for testuser1@localhost
585 GRANT USAGE ON *.* TO `testuser1`@`localhost`
586 GRANT SELECT, UPDATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
587 GRANT SELECT ON `mysql`.`user` TO `testuser1`@`localhost`
588
589 # Now add SELECT on *.* to testuser1;
590 # Switch to connection default
591 GRANT SELECT ON *.* TO 'testuser1'@'localhost';
592 #
593 # Here <SELECT NO> is shown correctly for testuser1;
594 SELECT * FROM information_schema.user_privileges
595 WHERE grantee LIKE '''testuser%'''
596 ORDER BY grantee, table_catalog, privilege_type;
597 GRANTEE 'testuser1'@'localhost'
598 TABLE_CATALOG def
599 PRIVILEGE_TYPE SELECT
600 IS_GRANTABLE NO
601 GRANTEE 'testuser2'@'localhost'
602 TABLE_CATALOG def
603 PRIVILEGE_TYPE INSERT
604 IS_GRANTABLE NO
605 GRANTEE 'testuser2'@'localhost'
606 TABLE_CATALOG def
607 PRIVILEGE_TYPE UPDATE
608 IS_GRANTABLE NO
609 GRANTEE 'testuser3'@'localhost'
610 TABLE_CATALOG def
611 PRIVILEGE_TYPE USAGE
612 IS_GRANTABLE NO
613 SELECT * FROM mysql.user
614 WHERE user LIKE 'testuser%' ORDER BY host, user;
615 Host localhost
616 User testuser1
617 Select_priv Y
618 Insert_priv N
619 Update_priv N
620 Delete_priv N
621 Create_priv N
622 Drop_priv N
623 Reload_priv N
624 Shutdown_priv N
625 Process_priv N
626 File_priv N
627 Grant_priv N
628 References_priv N
629 Index_priv N
630 Alter_priv N
631 Show_db_priv N
632 Super_priv N
633 Create_tmp_table_priv N
634 Lock_tables_priv N
635 Execute_priv N
636 Repl_slave_priv N
637 Repl_client_priv N
638 Create_view_priv N
639 Show_view_priv N
640 Create_routine_priv N
641 Alter_routine_priv N
642 Create_user_priv N
643 Event_priv N
644 Trigger_priv N
645 Create_tablespace_priv N
646 ssl_type
647 ssl_cipher
648 x509_issuer
649 x509_subject
650 max_questions 0
651 max_updates 0
652 max_connections 0
653 max_user_connections 0
654 plugin caching_sha2_password
655 authentication_string
656 password_expired N
657 password_last_changed DTVALUE
658 password_lifetime NULL
659 account_locked N
660 Create_role_priv N
661 Drop_role_priv N
662 Password_reuse_history NULL
663 Password_reuse_time NULL
664 Password_require_current NULL
665 User_attributes NULL
666 Host localhost
667 User testuser2
668 Select_priv N
669 Insert_priv Y
670 Update_priv Y
671 Delete_priv N
672 Create_priv N
673 Drop_priv N
674 Reload_priv N
675 Shutdown_priv N
676 Process_priv N
677 File_priv N
678 Grant_priv N
679 References_priv N
680 Index_priv N
681 Alter_priv N
682 Show_db_priv N
683 Super_priv N
684 Create_tmp_table_priv N
685 Lock_tables_priv N
686 Execute_priv N
687 Repl_slave_priv N
688 Repl_client_priv N
689 Create_view_priv N
690 Show_view_priv N
691 Create_routine_priv N
692 Alter_routine_priv N
693 Create_user_priv N
694 Event_priv N
695 Trigger_priv N
696 Create_tablespace_priv N
697 ssl_type
698 ssl_cipher
699 x509_issuer
700 x509_subject
701 max_questions 0
702 max_updates 0
703 max_connections 0
704 max_user_connections 0
705 plugin caching_sha2_password
706 authentication_string
707 password_expired N
708 password_last_changed DTVALUE
709 password_lifetime NULL
710 account_locked N
711 Create_role_priv N
712 Drop_role_priv N
713 Password_reuse_history NULL
714 Password_reuse_time NULL
715 Password_require_current NULL
716 User_attributes NULL
717 Host localhost
718 User testuser3
719 Select_priv N
720 Insert_priv N
721 Update_priv N
722 Delete_priv N
723 Create_priv N
724 Drop_priv N
725 Reload_priv N
726 Shutdown_priv N
727 Process_priv N
728 File_priv N
729 Grant_priv N
730 References_priv N
731 Index_priv N
732 Alter_priv N
733 Show_db_priv N
734 Super_priv N
735 Create_tmp_table_priv N
736 Lock_tables_priv N
737 Execute_priv N
738 Repl_slave_priv N
739 Repl_client_priv N
740 Create_view_priv N
741 Show_view_priv N
742 Create_routine_priv N
743 Alter_routine_priv N
744 Create_user_priv N
745 Event_priv N
746 Trigger_priv N
747 Create_tablespace_priv N
748 ssl_type
749 ssl_cipher
750 x509_issuer
751 x509_subject
752 max_questions 0
753 max_updates 0
754 max_connections 0
755 max_user_connections 0
756 plugin caching_sha2_password
757 authentication_string
758 password_expired N
759 password_last_changed DTVALUE
760 password_lifetime NULL
761 account_locked N
762 Create_role_priv N
763 Drop_role_priv N
764 Password_reuse_history NULL
765 Password_reuse_time NULL
766 Password_require_current NULL
767 User_attributes NULL
768 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
769 #
770 # Here <SELECT YES> is shown correctly for testuser1;
771 SELECT * FROM information_schema.user_privileges
772 WHERE grantee LIKE '''testuser%'''
773 ORDER BY grantee, table_catalog, privilege_type;
774 GRANTEE 'testuser1'@'localhost'
775 TABLE_CATALOG def
776 PRIVILEGE_TYPE SELECT
777 IS_GRANTABLE YES
778 GRANTEE 'testuser2'@'localhost'
779 TABLE_CATALOG def
780 PRIVILEGE_TYPE INSERT
781 IS_GRANTABLE NO
782 GRANTEE 'testuser2'@'localhost'
783 TABLE_CATALOG def
784 PRIVILEGE_TYPE UPDATE
785 IS_GRANTABLE NO
786 GRANTEE 'testuser3'@'localhost'
787 TABLE_CATALOG def
788 PRIVILEGE_TYPE USAGE
789 IS_GRANTABLE NO
790 SELECT * FROM mysql.user
791 WHERE user LIKE 'testuser%' ORDER BY host, user;
792 Host localhost
793 User testuser1
794 Select_priv Y
795 Insert_priv N
796 Update_priv N
797 Delete_priv N
798 Create_priv N
799 Drop_priv N
800 Reload_priv N
801 Shutdown_priv N
802 Process_priv N
803 File_priv N
804 Grant_priv Y
805 References_priv N
806 Index_priv N
807 Alter_priv N
808 Show_db_priv N
809 Super_priv N
810 Create_tmp_table_priv N
811 Lock_tables_priv N
812 Execute_priv N
813 Repl_slave_priv N
814 Repl_client_priv N
815 Create_view_priv N
816 Show_view_priv N
817 Create_routine_priv N
818 Alter_routine_priv N
819 Create_user_priv N
820 Event_priv N
821 Trigger_priv N
822 Create_tablespace_priv N
823 ssl_type
824 ssl_cipher
825 x509_issuer
826 x509_subject
827 max_questions 0
828 max_updates 0
829 max_connections 0
830 max_user_connections 0
831 plugin caching_sha2_password
832 authentication_string
833 password_expired N
834 password_last_changed DTVALUE
835 password_lifetime NULL
836 account_locked N
837 Create_role_priv N
838 Drop_role_priv N
839 Password_reuse_history NULL
840 Password_reuse_time NULL
841 Password_require_current NULL
842 User_attributes NULL
843 Host localhost
844 User testuser2
845 Select_priv N
846 Insert_priv Y
847 Update_priv Y
848 Delete_priv N
849 Create_priv N
850 Drop_priv N
851 Reload_priv N
852 Shutdown_priv N
853 Process_priv N
854 File_priv N
855 Grant_priv N
856 References_priv N
857 Index_priv N
858 Alter_priv N
859 Show_db_priv N
860 Super_priv N
861 Create_tmp_table_priv N
862 Lock_tables_priv N
863 Execute_priv N
864 Repl_slave_priv N
865 Repl_client_priv N
866 Create_view_priv N
867 Show_view_priv N
868 Create_routine_priv N
869 Alter_routine_priv N
870 Create_user_priv N
871 Event_priv N
872 Trigger_priv N
873 Create_tablespace_priv N
874 ssl_type
875 ssl_cipher
876 x509_issuer
877 x509_subject
878 max_questions 0
879 max_updates 0
880 max_connections 0
881 max_user_connections 0
882 plugin caching_sha2_password
883 authentication_string
884 password_expired N
885 password_last_changed DTVALUE
886 password_lifetime NULL
887 account_locked N
888 Create_role_priv N
889 Drop_role_priv N
890 Password_reuse_history NULL
891 Password_reuse_time NULL
892 Password_require_current NULL
893 User_attributes NULL
894 Host localhost
895 User testuser3
896 Select_priv N
897 Insert_priv N
898 Update_priv N
899 Delete_priv N
900 Create_priv N
901 Drop_priv N
902 Reload_priv N
903 Shutdown_priv N
904 Process_priv N
905 File_priv N
906 Grant_priv N
907 References_priv N
908 Index_priv N
909 Alter_priv N
910 Show_db_priv N
911 Super_priv N
912 Create_tmp_table_priv N
913 Lock_tables_priv N
914 Execute_priv N
915 Repl_slave_priv N
916 Repl_client_priv N
917 Create_view_priv N
918 Show_view_priv N
919 Create_routine_priv N
920 Alter_routine_priv N
921 Create_user_priv N
922 Event_priv N
923 Trigger_priv N
924 Create_tablespace_priv N
925 ssl_type
926 ssl_cipher
927 x509_issuer
928 x509_subject
929 max_questions 0
930 max_updates 0
931 max_connections 0
932 max_user_connections 0
933 plugin caching_sha2_password
934 authentication_string
935 password_expired N
936 password_last_changed DTVALUE
937 password_lifetime NULL
938 account_locked N
939 Create_role_priv N
940 Drop_role_priv N
941 Password_reuse_history NULL
942 Password_reuse_time NULL
943 Password_require_current NULL
944 User_attributes NULL
945 # Switch to connection testuser1
946 SELECT * FROM information_schema.user_privileges
947 WHERE grantee LIKE '''testuser%'''
948 ORDER BY grantee, table_catalog, privilege_type;
949 GRANTEE 'testuser1'@'localhost'
950 TABLE_CATALOG def
951 PRIVILEGE_TYPE SELECT
952 IS_GRANTABLE YES
953 SELECT * FROM mysql.user
954 WHERE user LIKE 'testuser%' ORDER BY host, user;
955 Host localhost
956 User testuser1
957 Select_priv Y
958 Insert_priv N
959 Update_priv N
960 Delete_priv N
961 Create_priv N
962 Drop_priv N
963 Reload_priv N
964 Shutdown_priv N
965 Process_priv N
966 File_priv N
967 Grant_priv Y
968 References_priv N
969 Index_priv N
970 Alter_priv N
971 Show_db_priv N
972 Super_priv N
973 Create_tmp_table_priv N
974 Lock_tables_priv N
975 Execute_priv N
976 Repl_slave_priv N
977 Repl_client_priv N
978 Create_view_priv N
979 Show_view_priv N
980 Create_routine_priv N
981 Alter_routine_priv N
982 Create_user_priv N
983 Event_priv N
984 Trigger_priv N
985 Create_tablespace_priv N
986 ssl_type
987 ssl_cipher
988 x509_issuer
989 x509_subject
990 max_questions 0
991 max_updates 0
992 max_connections 0
993 max_user_connections 0
994 plugin caching_sha2_password
995 authentication_string
996 password_expired N
997 password_last_changed DTVALUE
998 password_lifetime NULL
999 account_locked N
1000 Create_role_priv N
1001 Drop_role_priv N
1002 Password_reuse_history NULL
1003 Password_reuse_time NULL
1004 Password_require_current NULL
1005 User_attributes NULL
1006 Host localhost
1007 User testuser2
1008 Select_priv N
1009 Insert_priv Y
1010 Update_priv Y
1011 Delete_priv N
1012 Create_priv N
1013 Drop_priv N
1014 Reload_priv N
1015 Shutdown_priv N
1016 Process_priv N
1017 File_priv N
1018 Grant_priv N
1019 References_priv N
1020 Index_priv N
1021 Alter_priv N
1022 Show_db_priv N
1023 Super_priv N
1024 Create_tmp_table_priv N
1025 Lock_tables_priv N
1026 Execute_priv N
1027 Repl_slave_priv N
1028 Repl_client_priv N
1029 Create_view_priv N
1030 Show_view_priv N
1031 Create_routine_priv N
1032 Alter_routine_priv N
1033 Create_user_priv N
1034 Event_priv N
1035 Trigger_priv N
1036 Create_tablespace_priv N
1037 ssl_type
1038 ssl_cipher
1039 x509_issuer
1040 x509_subject
1041 max_questions 0
1042 max_updates 0
1043 max_connections 0
1044 max_user_connections 0
1045 plugin caching_sha2_password
1046 authentication_string
1047 password_expired N
1048 password_last_changed DTVALUE
1049 password_lifetime NULL
1050 account_locked N
1051 Create_role_priv N
1052 Drop_role_priv N
1053 Password_reuse_history NULL
1054 Password_reuse_time NULL
1055 Password_require_current NULL
1056 User_attributes NULL
1057 Host localhost
1058 User testuser3
1059 Select_priv N
1060 Insert_priv N
1061 Update_priv N
1062 Delete_priv N
1063 Create_priv N
1064 Drop_priv N
1065 Reload_priv N
1066 Shutdown_priv N
1067 Process_priv N
1068 File_priv N
1069 Grant_priv N
1070 References_priv N
1071 Index_priv N
1072 Alter_priv N
1073 Show_db_priv N
1074 Super_priv N
1075 Create_tmp_table_priv N
1076 Lock_tables_priv N
1077 Execute_priv N
1078 Repl_slave_priv N
1079 Repl_client_priv N
1080 Create_view_priv N
1081 Show_view_priv N
1082 Create_routine_priv N
1083 Alter_routine_priv N
1084 Create_user_priv N
1085 Event_priv N
1086 Trigger_priv N
1087 Create_tablespace_priv N
1088 ssl_type
1089 ssl_cipher
1090 x509_issuer
1091 x509_subject
1092 max_questions 0
1093 max_updates 0
1094 max_connections 0
1095 max_user_connections 0
1096 plugin caching_sha2_password
1097 authentication_string
1098 password_expired N
1099 password_last_changed DTVALUE
1100 password_lifetime NULL
1101 account_locked N
1102 Create_role_priv N
1103 Drop_role_priv N
1104 Password_reuse_history NULL
1105 Password_reuse_time NULL
1106 Password_require_current NULL
1107 User_attributes NULL
1108 SHOW GRANTS;
1109 Grants for testuser1@localhost
1110 GRANT SELECT ON *.* TO `testuser1`@`localhost` WITH GRANT OPTION
1111 GRANT SELECT, UPDATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
1112 GRANT SELECT ON `mysql`.`user` TO `testuser1`@`localhost`
1113 # Establish connection testuser2 (user=testuser2)
1114 SELECT * FROM information_schema.user_privileges
1115 WHERE grantee LIKE '''testuser%'''
1116 ORDER BY grantee, table_catalog, privilege_type;
1117 GRANTEE 'testuser2'@'localhost'
1118 TABLE_CATALOG def
1119 PRIVILEGE_TYPE INSERT
1120 IS_GRANTABLE NO
1121 GRANTEE 'testuser2'@'localhost'
1122 TABLE_CATALOG def
1123 PRIVILEGE_TYPE UPDATE
1124 IS_GRANTABLE NO
1125 SELECT * FROM mysql.user
1126 WHERE user LIKE 'testuser%' ORDER BY host, user;
1127 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'user'
1128 SHOW GRANTS;
1129 Grants for testuser2@localhost
1130 GRANT INSERT, UPDATE ON *.* TO `testuser2`@`localhost`
1131 # Establish connection testuser3 (user=testuser3)
1132 SELECT * FROM information_schema.user_privileges
1133 WHERE grantee LIKE '''testuser%'''
1134 ORDER BY grantee, table_catalog, privilege_type;
1135 GRANTEE 'testuser3'@'localhost'
1136 TABLE_CATALOG def
1137 PRIVILEGE_TYPE USAGE
1138 IS_GRANTABLE NO
1139 SELECT * FROM mysql.user
1140 WHERE user LIKE 'testuser%' ORDER BY host, user;
1141 ERROR 42000: SELECT command denied to user 'testuser3'@'localhost' for table 'user'
1142 SHOW GRANTS;
1143 Grants for testuser3@localhost
1144 GRANT USAGE ON *.* TO `testuser3`@`localhost`
1145
1146 # Revoke privileges from testuser1;
1147 # Switch to connection default
1148 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
1149 SELECT * FROM information_schema.user_privileges
1150 WHERE grantee LIKE '''testuser%'''
1151 ORDER BY grantee, table_catalog, privilege_type;
1152 GRANTEE 'testuser1'@'localhost'
1153 TABLE_CATALOG def
1154 PRIVILEGE_TYPE USAGE
1155 IS_GRANTABLE NO
1156 GRANTEE 'testuser2'@'localhost'
1157 TABLE_CATALOG def
1158 PRIVILEGE_TYPE INSERT
1159 IS_GRANTABLE NO
1160 GRANTEE 'testuser2'@'localhost'
1161 TABLE_CATALOG def
1162 PRIVILEGE_TYPE UPDATE
1163 IS_GRANTABLE NO
1164 GRANTEE 'testuser3'@'localhost'
1165 TABLE_CATALOG def
1166 PRIVILEGE_TYPE USAGE
1167 IS_GRANTABLE NO
1168 SELECT * FROM mysql.user
1169 WHERE user LIKE 'testuser%' ORDER BY host, user;
1170 Host localhost
1171 User testuser1
1172 Select_priv N
1173 Insert_priv N
1174 Update_priv N
1175 Delete_priv N
1176 Create_priv N
1177 Drop_priv N
1178 Reload_priv N
1179 Shutdown_priv N
1180 Process_priv N
1181 File_priv N
1182 Grant_priv N
1183 References_priv N
1184 Index_priv N
1185 Alter_priv N
1186 Show_db_priv N
1187 Super_priv N
1188 Create_tmp_table_priv N
1189 Lock_tables_priv N
1190 Execute_priv N
1191 Repl_slave_priv N
1192 Repl_client_priv N
1193 Create_view_priv N
1194 Show_view_priv N
1195 Create_routine_priv N
1196 Alter_routine_priv N
1197 Create_user_priv N
1198 Event_priv N
1199 Trigger_priv N
1200 Create_tablespace_priv N
1201 ssl_type
1202 ssl_cipher
1203 x509_issuer
1204 x509_subject
1205 max_questions 0
1206 max_updates 0
1207 max_connections 0
1208 max_user_connections 0
1209 plugin caching_sha2_password
1210 authentication_string
1211 password_expired N
1212 password_last_changed DTVALUE
1213 password_lifetime NULL
1214 account_locked N
1215 Create_role_priv N
1216 Drop_role_priv N
1217 Password_reuse_history NULL
1218 Password_reuse_time NULL
1219 Password_require_current NULL
1220 User_attributes NULL
1221 Host localhost
1222 User testuser2
1223 Select_priv N
1224 Insert_priv Y
1225 Update_priv Y
1226 Delete_priv N
1227 Create_priv N
1228 Drop_priv N
1229 Reload_priv N
1230 Shutdown_priv N
1231 Process_priv N
1232 File_priv N
1233 Grant_priv N
1234 References_priv N
1235 Index_priv N
1236 Alter_priv N
1237 Show_db_priv N
1238 Super_priv N
1239 Create_tmp_table_priv N
1240 Lock_tables_priv N
1241 Execute_priv N
1242 Repl_slave_priv N
1243 Repl_client_priv N
1244 Create_view_priv N
1245 Show_view_priv N
1246 Create_routine_priv N
1247 Alter_routine_priv N
1248 Create_user_priv N
1249 Event_priv N
1250 Trigger_priv N
1251 Create_tablespace_priv N
1252 ssl_type
1253 ssl_cipher
1254 x509_issuer
1255 x509_subject
1256 max_questions 0
1257 max_updates 0
1258 max_connections 0
1259 max_user_connections 0
1260 plugin caching_sha2_password
1261 authentication_string
1262 password_expired N
1263 password_last_changed DTVALUE
1264 password_lifetime NULL
1265 account_locked N
1266 Create_role_priv N
1267 Drop_role_priv N
1268 Password_reuse_history NULL
1269 Password_reuse_time NULL
1270 Password_require_current NULL
1271 User_attributes NULL
1272 Host localhost
1273 User testuser3
1274 Select_priv N
1275 Insert_priv N
1276 Update_priv N
1277 Delete_priv N
1278 Create_priv N
1279 Drop_priv N
1280 Reload_priv N
1281 Shutdown_priv N
1282 Process_priv N
1283 File_priv N
1284 Grant_priv N
1285 References_priv N
1286 Index_priv N
1287 Alter_priv N
1288 Show_db_priv N
1289 Super_priv N
1290 Create_tmp_table_priv N
1291 Lock_tables_priv N
1292 Execute_priv N
1293 Repl_slave_priv N
1294 Repl_client_priv N
1295 Create_view_priv N
1296 Show_view_priv N
1297 Create_routine_priv N
1298 Alter_routine_priv N
1299 Create_user_priv N
1300 Event_priv N
1301 Trigger_priv N
1302 Create_tablespace_priv N
1303 ssl_type
1304 ssl_cipher
1305 x509_issuer
1306 x509_subject
1307 max_questions 0
1308 max_updates 0
1309 max_connections 0
1310 max_user_connections 0
1311 plugin caching_sha2_password
1312 authentication_string
1313 password_expired N
1314 password_last_changed DTVALUE
1315 password_lifetime NULL
1316 account_locked N
1317 Create_role_priv N
1318 Drop_role_priv N
1319 Password_reuse_history NULL
1320 Password_reuse_time NULL
1321 Password_require_current NULL
1322 User_attributes NULL
1323 # Switch to connection testuser1
1324 SELECT * FROM information_schema.user_privileges
1325 WHERE grantee LIKE '''testuser%'''
1326 ORDER BY grantee, table_catalog, privilege_type;
1327 GRANTEE 'testuser1'@'localhost'
1328 TABLE_CATALOG def
1329 PRIVILEGE_TYPE USAGE
1330 IS_GRANTABLE NO
1331 SELECT * FROM mysql.user
1332 WHERE user LIKE 'testuser%' ORDER BY host, user;
1333 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
1334 SHOW GRANTS;
1335 Grants for testuser1@localhost
1336 GRANT USAGE ON *.* TO `testuser1`@`localhost`
1337 CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
1338 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_55'
1339 SELECT * FROM information_schema.user_privileges
1340 WHERE grantee LIKE '''testuser%'''
1341 ORDER BY grantee, table_catalog, privilege_type;
1342 GRANTEE 'testuser1'@'localhost'
1343 TABLE_CATALOG def
1344 PRIVILEGE_TYPE USAGE
1345 IS_GRANTABLE NO
1346 SELECT * FROM mysql.user
1347 WHERE user LIKE 'testuser%' ORDER BY host, user;
1348 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
1349 SHOW GRANTS;
1350 Grants for testuser1@localhost
1351 GRANT USAGE ON *.* TO `testuser1`@`localhost`
1352 CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
1353 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_66'
1354
1355 # Add ALL on db_datadict.* (and select on mysql.user) to testuser1;
1356 # Switch to connection default
1357 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
1358 GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
1359 SELECT * FROM information_schema.user_privileges
1360 WHERE grantee LIKE '''testuser%'''
1361 ORDER BY grantee, table_catalog, privilege_type;
1362 GRANTEE 'testuser1'@'localhost'
1363 TABLE_CATALOG def
1364 PRIVILEGE_TYPE USAGE
1365 IS_GRANTABLE NO
1366 GRANTEE 'testuser2'@'localhost'
1367 TABLE_CATALOG def
1368 PRIVILEGE_TYPE INSERT
1369 IS_GRANTABLE NO
1370 GRANTEE 'testuser2'@'localhost'
1371 TABLE_CATALOG def
1372 PRIVILEGE_TYPE UPDATE
1373 IS_GRANTABLE NO
1374 GRANTEE 'testuser3'@'localhost'
1375 TABLE_CATALOG def
1376 PRIVILEGE_TYPE USAGE
1377 IS_GRANTABLE NO
1378 SELECT * FROM mysql.user
1379 WHERE user LIKE 'testuser%' ORDER BY host, user;
1380 Host localhost
1381 User testuser1
1382 Select_priv N
1383 Insert_priv N
1384 Update_priv N
1385 Delete_priv N
1386 Create_priv N
1387 Drop_priv N
1388 Reload_priv N
1389 Shutdown_priv N
1390 Process_priv N
1391 File_priv N
1392 Grant_priv N
1393 References_priv N
1394 Index_priv N
1395 Alter_priv N
1396 Show_db_priv N
1397 Super_priv N
1398 Create_tmp_table_priv N
1399 Lock_tables_priv N
1400 Execute_priv N
1401 Repl_slave_priv N
1402 Repl_client_priv N
1403 Create_view_priv N
1404 Show_view_priv N
1405 Create_routine_priv N
1406 Alter_routine_priv N
1407 Create_user_priv N
1408 Event_priv N
1409 Trigger_priv N
1410 Create_tablespace_priv N
1411 ssl_type
1412 ssl_cipher
1413 x509_issuer
1414 x509_subject
1415 max_questions 0
1416 max_updates 0
1417 max_connections 0
1418 max_user_connections 0
1419 plugin caching_sha2_password
1420 authentication_string
1421 password_expired N
1422 password_last_changed DTVALUE
1423 password_lifetime NULL
1424 account_locked N
1425 Create_role_priv N
1426 Drop_role_priv N
1427 Password_reuse_history NULL
1428 Password_reuse_time NULL
1429 Password_require_current NULL
1430 User_attributes NULL
1431 Host localhost
1432 User testuser2
1433 Select_priv N
1434 Insert_priv Y
1435 Update_priv Y
1436 Delete_priv N
1437 Create_priv N
1438 Drop_priv N
1439 Reload_priv N
1440 Shutdown_priv N
1441 Process_priv N
1442 File_priv N
1443 Grant_priv N
1444 References_priv N
1445 Index_priv N
1446 Alter_priv N
1447 Show_db_priv N
1448 Super_priv N
1449 Create_tmp_table_priv N
1450 Lock_tables_priv N
1451 Execute_priv N
1452 Repl_slave_priv N
1453 Repl_client_priv N
1454 Create_view_priv N
1455 Show_view_priv N
1456 Create_routine_priv N
1457 Alter_routine_priv N
1458 Create_user_priv N
1459 Event_priv N
1460 Trigger_priv N
1461 Create_tablespace_priv N
1462 ssl_type
1463 ssl_cipher
1464 x509_issuer
1465 x509_subject
1466 max_questions 0
1467 max_updates 0
1468 max_connections 0
1469 max_user_connections 0
1470 plugin caching_sha2_password
1471 authentication_string
1472 password_expired N
1473 password_last_changed DTVALUE
1474 password_lifetime NULL
1475 account_locked N
1476 Create_role_priv N
1477 Drop_role_priv N
1478 Password_reuse_history NULL
1479 Password_reuse_time NULL
1480 Password_require_current NULL
1481 User_attributes NULL
1482 Host localhost
1483 User testuser3
1484 Select_priv N
1485 Insert_priv N
1486 Update_priv N
1487 Delete_priv N
1488 Create_priv N
1489 Drop_priv N
1490 Reload_priv N
1491 Shutdown_priv N
1492 Process_priv N
1493 File_priv N
1494 Grant_priv N
1495 References_priv N
1496 Index_priv N
1497 Alter_priv N
1498 Show_db_priv N
1499 Super_priv N
1500 Create_tmp_table_priv N
1501 Lock_tables_priv N
1502 Execute_priv N
1503 Repl_slave_priv N
1504 Repl_client_priv N
1505 Create_view_priv N
1506 Show_view_priv N
1507 Create_routine_priv N
1508 Alter_routine_priv N
1509 Create_user_priv N
1510 Event_priv N
1511 Trigger_priv N
1512 Create_tablespace_priv N
1513 ssl_type
1514 ssl_cipher
1515 x509_issuer
1516 x509_subject
1517 max_questions 0
1518 max_updates 0
1519 max_connections 0
1520 max_user_connections 0
1521 plugin caching_sha2_password
1522 authentication_string
1523 password_expired N
1524 password_last_changed DTVALUE
1525 password_lifetime NULL
1526 account_locked N
1527 Create_role_priv N
1528 Drop_role_priv N
1529 Password_reuse_history NULL
1530 Password_reuse_time NULL
1531 Password_require_current NULL
1532 User_attributes NULL
1533 # Switch to connection testuser1
1534 SELECT * FROM information_schema.user_privileges
1535 WHERE grantee LIKE '''testuser%'''
1536 ORDER BY grantee, table_catalog, privilege_type;
1537 GRANTEE 'testuser1'@'localhost'
1538 TABLE_CATALOG def
1539 PRIVILEGE_TYPE USAGE
1540 IS_GRANTABLE NO
1541 SELECT * FROM mysql.user
1542 WHERE user LIKE 'testuser%' ORDER BY host, user;
1543 Host localhost
1544 User testuser1
1545 Select_priv N
1546 Insert_priv N
1547 Update_priv N
1548 Delete_priv N
1549 Create_priv N
1550 Drop_priv N
1551 Reload_priv N
1552 Shutdown_priv N
1553 Process_priv N
1554 File_priv N
1555 Grant_priv N
1556 References_priv N
1557 Index_priv N
1558 Alter_priv N
1559 Show_db_priv N
1560 Super_priv N
1561 Create_tmp_table_priv N
1562 Lock_tables_priv N
1563 Execute_priv N
1564 Repl_slave_priv N
1565 Repl_client_priv N
1566 Create_view_priv N
1567 Show_view_priv N
1568 Create_routine_priv N
1569 Alter_routine_priv N
1570 Create_user_priv N
1571 Event_priv N
1572 Trigger_priv N
1573 Create_tablespace_priv N
1574 ssl_type
1575 ssl_cipher
1576 x509_issuer
1577 x509_subject
1578 max_questions 0
1579 max_updates 0
1580 max_connections 0
1581 max_user_connections 0
1582 plugin caching_sha2_password
1583 authentication_string
1584 password_expired N
1585 password_last_changed DTVALUE
1586 password_lifetime NULL
1587 account_locked N
1588 Create_role_priv N
1589 Drop_role_priv N
1590 Password_reuse_history NULL
1591 Password_reuse_time NULL
1592 Password_require_current NULL
1593 User_attributes NULL
1594 Host localhost
1595 User testuser2
1596 Select_priv N
1597 Insert_priv Y
1598 Update_priv Y
1599 Delete_priv N
1600 Create_priv N
1601 Drop_priv N
1602 Reload_priv N
1603 Shutdown_priv N
1604 Process_priv N
1605 File_priv N
1606 Grant_priv N
1607 References_priv N
1608 Index_priv N
1609 Alter_priv N
1610 Show_db_priv N
1611 Super_priv N
1612 Create_tmp_table_priv N
1613 Lock_tables_priv N
1614 Execute_priv N
1615 Repl_slave_priv N
1616 Repl_client_priv N
1617 Create_view_priv N
1618 Show_view_priv N
1619 Create_routine_priv N
1620 Alter_routine_priv N
1621 Create_user_priv N
1622 Event_priv N
1623 Trigger_priv N
1624 Create_tablespace_priv N
1625 ssl_type
1626 ssl_cipher
1627 x509_issuer
1628 x509_subject
1629 max_questions 0
1630 max_updates 0
1631 max_connections 0
1632 max_user_connections 0
1633 plugin caching_sha2_password
1634 authentication_string
1635 password_expired N
1636 password_last_changed DTVALUE
1637 password_lifetime NULL
1638 account_locked N
1639 Create_role_priv N
1640 Drop_role_priv N
1641 Password_reuse_history NULL
1642 Password_reuse_time NULL
1643 Password_require_current NULL
1644 User_attributes NULL
1645 Host localhost
1646 User testuser3
1647 Select_priv N
1648 Insert_priv N
1649 Update_priv N
1650 Delete_priv N
1651 Create_priv N
1652 Drop_priv N
1653 Reload_priv N
1654 Shutdown_priv N
1655 Process_priv N
1656 File_priv N
1657 Grant_priv N
1658 References_priv N
1659 Index_priv N
1660 Alter_priv N
1661 Show_db_priv N
1662 Super_priv N
1663 Create_tmp_table_priv N
1664 Lock_tables_priv N
1665 Execute_priv N
1666 Repl_slave_priv N
1667 Repl_client_priv N
1668 Create_view_priv N
1669 Show_view_priv N
1670 Create_routine_priv N
1671 Alter_routine_priv N
1672 Create_user_priv N
1673 Event_priv N
1674 Trigger_priv N
1675 Create_tablespace_priv N
1676 ssl_type
1677 ssl_cipher
1678 x509_issuer
1679 x509_subject
1680 max_questions 0
1681 max_updates 0
1682 max_connections 0
1683 max_user_connections 0
1684 plugin caching_sha2_password
1685 authentication_string
1686 password_expired N
1687 password_last_changed DTVALUE
1688 password_lifetime NULL
1689 account_locked N
1690 Create_role_priv N
1691 Drop_role_priv N
1692 Password_reuse_history NULL
1693 Password_reuse_time NULL
1694 Password_require_current NULL
1695 User_attributes NULL
1696 SHOW GRANTS;
1697 Grants for testuser1@localhost
1698 GRANT USAGE ON *.* TO `testuser1`@`localhost`
1699 GRANT ALL PRIVILEGES ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
1700 GRANT SELECT ON `mysql`.`user` TO `testuser1`@`localhost`
1701 CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
1702 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_56'
1703 USE db_datadict;
1704 SELECT * FROM information_schema.user_privileges
1705 WHERE grantee LIKE '''testuser%'''
1706 ORDER BY grantee, table_catalog, privilege_type;
1707 GRANTEE 'testuser1'@'localhost'
1708 TABLE_CATALOG def
1709 PRIVILEGE_TYPE USAGE
1710 IS_GRANTABLE NO
1711 SELECT * FROM mysql.user
1712 WHERE user LIKE 'testuser%' ORDER BY host, user;
1713 Host localhost
1714 User testuser1
1715 Select_priv N
1716 Insert_priv N
1717 Update_priv N
1718 Delete_priv N
1719 Create_priv N
1720 Drop_priv N
1721 Reload_priv N
1722 Shutdown_priv N
1723 Process_priv N
1724 File_priv N
1725 Grant_priv N
1726 References_priv N
1727 Index_priv N
1728 Alter_priv N
1729 Show_db_priv N
1730 Super_priv N
1731 Create_tmp_table_priv N
1732 Lock_tables_priv N
1733 Execute_priv N
1734 Repl_slave_priv N
1735 Repl_client_priv N
1736 Create_view_priv N
1737 Show_view_priv N
1738 Create_routine_priv N
1739 Alter_routine_priv N
1740 Create_user_priv N
1741 Event_priv N
1742 Trigger_priv N
1743 Create_tablespace_priv N
1744 ssl_type
1745 ssl_cipher
1746 x509_issuer
1747 x509_subject
1748 max_questions 0
1749 max_updates 0
1750 max_connections 0
1751 max_user_connections 0
1752 plugin caching_sha2_password
1753 authentication_string
1754 password_expired N
1755 password_last_changed DTVALUE
1756 password_lifetime NULL
1757 account_locked N
1758 Create_role_priv N
1759 Drop_role_priv N
1760 Password_reuse_history NULL
1761 Password_reuse_time NULL
1762 Password_require_current NULL
1763 User_attributes NULL
1764 Host localhost
1765 User testuser2
1766 Select_priv N
1767 Insert_priv Y
1768 Update_priv Y
1769 Delete_priv N
1770 Create_priv N
1771 Drop_priv N
1772 Reload_priv N
1773 Shutdown_priv N
1774 Process_priv N
1775 File_priv N
1776 Grant_priv N
1777 References_priv N
1778 Index_priv N
1779 Alter_priv N
1780 Show_db_priv N
1781 Super_priv N
1782 Create_tmp_table_priv N
1783 Lock_tables_priv N
1784 Execute_priv N
1785 Repl_slave_priv N
1786 Repl_client_priv N
1787 Create_view_priv N
1788 Show_view_priv N
1789 Create_routine_priv N
1790 Alter_routine_priv N
1791 Create_user_priv N
1792 Event_priv N
1793 Trigger_priv N
1794 Create_tablespace_priv N
1795 ssl_type
1796 ssl_cipher
1797 x509_issuer
1798 x509_subject
1799 max_questions 0
1800 max_updates 0
1801 max_connections 0
1802 max_user_connections 0
1803 plugin caching_sha2_password
1804 authentication_string
1805 password_expired N
1806 password_last_changed DTVALUE
1807 password_lifetime NULL
1808 account_locked N
1809 Create_role_priv N
1810 Drop_role_priv N
1811 Password_reuse_history NULL
1812 Password_reuse_time NULL
1813 Password_require_current NULL
1814 User_attributes NULL
1815 Host localhost
1816 User testuser3
1817 Select_priv N
1818 Insert_priv N
1819 Update_priv N
1820 Delete_priv N
1821 Create_priv N
1822 Drop_priv N
1823 Reload_priv N
1824 Shutdown_priv N
1825 Process_priv N
1826 File_priv N
1827 Grant_priv N
1828 References_priv N
1829 Index_priv N
1830 Alter_priv N
1831 Show_db_priv N
1832 Super_priv N
1833 Create_tmp_table_priv N
1834 Lock_tables_priv N
1835 Execute_priv N
1836 Repl_slave_priv N
1837 Repl_client_priv N
1838 Create_view_priv N
1839 Show_view_priv N
1840 Create_routine_priv N
1841 Alter_routine_priv N
1842 Create_user_priv N
1843 Event_priv N
1844 Trigger_priv N
1845 Create_tablespace_priv N
1846 ssl_type
1847 ssl_cipher
1848 x509_issuer
1849 x509_subject
1850 max_questions 0
1851 max_updates 0
1852 max_connections 0
1853 max_user_connections 0
1854 plugin caching_sha2_password
1855 authentication_string
1856 password_expired N
1857 password_last_changed DTVALUE
1858 password_lifetime NULL
1859 account_locked N
1860 Create_role_priv N
1861 Drop_role_priv N
1862 Password_reuse_history NULL
1863 Password_reuse_time NULL
1864 Password_require_current NULL
1865 User_attributes NULL
1866 SHOW GRANTS;
1867 Grants for testuser1@localhost
1868 GRANT USAGE ON *.* TO `testuser1`@`localhost`
1869 GRANT ALL PRIVILEGES ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
1870 GRANT SELECT ON `mysql`.`user` TO `testuser1`@`localhost`
1871 CREATE TABLE tb_57 ( c1 TEXT )
1872 ENGINE = <other_engine_type>;
1873
1874 # Revoke privileges from testuser1;
1875 # Switch to connection default
1876 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
1877 SELECT * FROM information_schema.user_privileges
1878 WHERE grantee LIKE '''testuser%'''
1879 ORDER BY grantee, table_catalog, privilege_type;
1880 GRANTEE 'testuser1'@'localhost'
1881 TABLE_CATALOG def
1882 PRIVILEGE_TYPE USAGE
1883 IS_GRANTABLE NO
1884 GRANTEE 'testuser2'@'localhost'
1885 TABLE_CATALOG def
1886 PRIVILEGE_TYPE INSERT
1887 IS_GRANTABLE NO
1888 GRANTEE 'testuser2'@'localhost'
1889 TABLE_CATALOG def
1890 PRIVILEGE_TYPE UPDATE
1891 IS_GRANTABLE NO
1892 GRANTEE 'testuser3'@'localhost'
1893 TABLE_CATALOG def
1894 PRIVILEGE_TYPE USAGE
1895 IS_GRANTABLE NO
1896 SELECT * FROM mysql.user
1897 WHERE user LIKE 'testuser%' ORDER BY host, user;
1898 Host localhost
1899 User testuser1
1900 Select_priv N
1901 Insert_priv N
1902 Update_priv N
1903 Delete_priv N
1904 Create_priv N
1905 Drop_priv N
1906 Reload_priv N
1907 Shutdown_priv N
1908 Process_priv N
1909 File_priv N
1910 Grant_priv N
1911 References_priv N
1912 Index_priv N
1913 Alter_priv N
1914 Show_db_priv N
1915 Super_priv N
1916 Create_tmp_table_priv N
1917 Lock_tables_priv N
1918 Execute_priv N
1919 Repl_slave_priv N
1920 Repl_client_priv N
1921 Create_view_priv N
1922 Show_view_priv N
1923 Create_routine_priv N
1924 Alter_routine_priv N
1925 Create_user_priv N
1926 Event_priv N
1927 Trigger_priv N
1928 Create_tablespace_priv N
1929 ssl_type
1930 ssl_cipher
1931 x509_issuer
1932 x509_subject
1933 max_questions 0
1934 max_updates 0
1935 max_connections 0
1936 max_user_connections 0
1937 plugin caching_sha2_password
1938 authentication_string
1939 password_expired N
1940 password_last_changed DTVALUE
1941 password_lifetime NULL
1942 account_locked N
1943 Create_role_priv N
1944 Drop_role_priv N
1945 Password_reuse_history NULL
1946 Password_reuse_time NULL
1947 Password_require_current NULL
1948 User_attributes NULL
1949 Host localhost
1950 User testuser2
1951 Select_priv N
1952 Insert_priv Y
1953 Update_priv Y
1954 Delete_priv N
1955 Create_priv N
1956 Drop_priv N
1957 Reload_priv N
1958 Shutdown_priv N
1959 Process_priv N
1960 File_priv N
1961 Grant_priv N
1962 References_priv N
1963 Index_priv N
1964 Alter_priv N
1965 Show_db_priv N
1966 Super_priv N
1967 Create_tmp_table_priv N
1968 Lock_tables_priv N
1969 Execute_priv N
1970 Repl_slave_priv N
1971 Repl_client_priv N
1972 Create_view_priv N
1973 Show_view_priv N
1974 Create_routine_priv N
1975 Alter_routine_priv N
1976 Create_user_priv N
1977 Event_priv N
1978 Trigger_priv N
1979 Create_tablespace_priv N
1980 ssl_type
1981 ssl_cipher
1982 x509_issuer
1983 x509_subject
1984 max_questions 0
1985 max_updates 0
1986 max_connections 0
1987 max_user_connections 0
1988 plugin caching_sha2_password
1989 authentication_string
1990 password_expired N
1991 password_last_changed DTVALUE
1992 password_lifetime NULL
1993 account_locked N
1994 Create_role_priv N
1995 Drop_role_priv N
1996 Password_reuse_history NULL
1997 Password_reuse_time NULL
1998 Password_require_current NULL
1999 User_attributes NULL
2000 Host localhost
2001 User testuser3
2002 Select_priv N
2003 Insert_priv N
2004 Update_priv N
2005 Delete_priv N
2006 Create_priv N
2007 Drop_priv N
2008 Reload_priv N
2009 Shutdown_priv N
2010 Process_priv N
2011 File_priv N
2012 Grant_priv N
2013 References_priv N
2014 Index_priv N
2015 Alter_priv N
2016 Show_db_priv N
2017 Super_priv N
2018 Create_tmp_table_priv N
2019 Lock_tables_priv N
2020 Execute_priv N
2021 Repl_slave_priv N
2022 Repl_client_priv N
2023 Create_view_priv N
2024 Show_view_priv N
2025 Create_routine_priv N
2026 Alter_routine_priv N
2027 Create_user_priv N
2028 Event_priv N
2029 Trigger_priv N
2030 Create_tablespace_priv N
2031 ssl_type
2032 ssl_cipher
2033 x509_issuer
2034 x509_subject
2035 max_questions 0
2036 max_updates 0
2037 max_connections 0
2038 max_user_connections 0
2039 plugin caching_sha2_password
2040 authentication_string
2041 password_expired N
2042 password_last_changed DTVALUE
2043 password_lifetime NULL
2044 account_locked N
2045 Create_role_priv N
2046 Drop_role_priv N
2047 Password_reuse_history NULL
2048 Password_reuse_time NULL
2049 Password_require_current NULL
2050 User_attributes NULL
2051 # Switch to connection testuser1
2052 SELECT * FROM information_schema.user_privileges
2053 WHERE grantee LIKE '''testuser%'''
2054 ORDER BY grantee, table_catalog, privilege_type;
2055 GRANTEE 'testuser1'@'localhost'
2056 TABLE_CATALOG def
2057 PRIVILEGE_TYPE USAGE
2058 IS_GRANTABLE NO
2059 SELECT * FROM mysql.user
2060 WHERE user LIKE 'testuser%' ORDER BY host, user;
2061 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
2062 SHOW GRANTS;
2063 Grants for testuser1@localhost
2064 GRANT USAGE ON *.* TO `testuser1`@`localhost`
2065 CREATE TABLE db_datadict.tb_58 ( c1 TEXT )
2066 ENGINE = <other_engine_type>;
2067 USE db_datadict;
2068 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'db_datadict'
2069 CREATE TABLE db_datadict.tb_59 ( c1 TEXT )
2070 ENGINE = <other_engine_type>;
2071 # Switch to connection default and close connections testuser1,testuser2,testuser3
2072 DROP USER 'testuser1'@'localhost';
2073 DROP USER 'testuser2'@'localhost';
2074 DROP USER 'testuser3'@'localhost';
2075 DROP DATABASE IF EXISTS db_datadict;
2076 ########################################################################################
2077 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications
2078 ########################################################################################
2079 SELECT * FROM information_schema.user_privileges
2080 WHERE grantee = '''testuser1''@''localhost''';
2081 SHOW GRANTS FOR 'testuser1'@'localhost';
2082 ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
2083 DROP USER 'testuser1'@'localhost';
2084 CREATE USER 'testuser1'@'localhost';
2085 SELECT * FROM information_schema.user_privileges
2086 WHERE grantee = '''testuser1''@''localhost''';
2087 GRANTEE 'testuser1'@'localhost'
2088 TABLE_CATALOG def
2089 PRIVILEGE_TYPE USAGE
2090 IS_GRANTABLE NO
2091 SHOW GRANTS FOR 'testuser1'@'localhost';
2092 Grants for testuser1@localhost
2093 GRANT USAGE ON *.* TO `testuser1`@`localhost`
2094 GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost';
2095 SELECT * FROM information_schema.user_privileges
2096 WHERE grantee = '''testuser1''@''localhost''';
2097 GRANTEE 'testuser1'@'localhost'
2098 TABLE_CATALOG def
2099 PRIVILEGE_TYPE SELECT
2100 IS_GRANTABLE NO
2101 GRANTEE 'testuser1'@'localhost'
2102 TABLE_CATALOG def
2103 PRIVILEGE_TYPE FILE
2104 IS_GRANTABLE NO
2105 SHOW GRANTS FOR 'testuser1'@'localhost';
2106 Grants for testuser1@localhost
2107 GRANT SELECT, FILE ON *.* TO `testuser1`@`localhost`
2108 DROP USER 'testuser1'@'localhost';
2109 SELECT * FROM information_schema.user_privileges
2110 WHERE grantee = '''testuser1''@''localhost''';
2111 SHOW GRANTS FOR 'testuser1'@'localhost';
2112 ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
2113 ########################################################################
2114 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
2115 # DDL on INFORMATION_SCHEMA tables are not supported
2116 ########################################################################
2117 DROP USER 'testuser1'@'localhost';
2118 CREATE USER 'testuser1'@'localhost';
2119 INSERT INTO information_schema.user_privileges
2120 SELECT * FROM information_schema.user_privileges;
2121 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2122 UPDATE information_schema.user_privileges
2123 SET PRIVILEGE_TYPE = 'gaming';
2124 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2125 DELETE FROM information_schema.user_privileges
2126 WHERE grantee = '''testuser1''@''localhost''';
2127 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2128 TRUNCATE information_schema.user_privileges;
2129 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2130 CREATE INDEX i1 ON information_schema.user_privileges(grantee);
2131 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2132 ALTER TABLE information_schema.user_privileges ADD f1 INT;
2133 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2134 DROP TABLE information_schema.user_privileges;
2135 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2136 ALTER TABLE information_schema.user_privileges
2137 RENAME db_datadict.user_privileges;
2138 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2139 ALTER TABLE information_schema.user_privileges
2140 RENAME information_schema.xuser_privileges;
2141 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
2142 DROP USER 'testuser1'@'localhost';