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(385) NO NULL
32 TABLE_CATALOG varchar(512) NO NULL
33 PRIVILEGE_TYPE varchar(64) NO NULL
34 IS_GRANTABLE varchar(3) NO NULL
35 SHOW CREATE TABLE information_schema.USER_PRIVILEGES;
36 Table Create Table
37 USER_PRIVILEGES CREATE TEMPORARY TABLE `USER_PRIVILEGES` (
38 `GRANTEE` varchar(385) NOT NULL,
39 `TABLE_CATALOG` varchar(512) NOT NULL,
40 `PRIVILEGE_TYPE` varchar(64) NOT NULL,
41 `IS_GRANTABLE` varchar(3) NOT NULL
42 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
43 SHOW COLUMNS FROM information_schema.USER_PRIVILEGES;
44 Field Type Null Key Default Extra
45 GRANTEE varchar(385) NO NULL
46 TABLE_CATALOG varchar(512) NO NULL
47 PRIVILEGE_TYPE varchar(64) NO NULL
48 IS_GRANTABLE varchar(3) NO NULL
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 CREATE DATABASE db_datadict;
58 DROP USER 'testuser1'@'localhost';
59 CREATE USER 'testuser1'@'localhost';
60 DROP USER 'testuser2'@'localhost';
61 CREATE USER 'testuser2'@'localhost';
62 DROP USER 'testuser3'@'localhost';
63 CREATE USER 'testuser3'@'localhost';
64 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
65 GRANT SELECT ON mysql.global_priv TO 'testuser1'@'localhost';
66 GRANT INSERT ON *.* TO 'testuser2'@'localhost';
67 GRANT UPDATE ON *.* TO 'testuser2'@'localhost';
68 SELECT * FROM information_schema.user_privileges
69 WHERE grantee LIKE '''testuser%'''
70 ORDER BY grantee, table_catalog, privilege_type;
71 GRANTEE 'testuser1'@'localhost'
72 TABLE_CATALOG def
73 PRIVILEGE_TYPE USAGE
74 IS_GRANTABLE NO
75 GRANTEE 'testuser2'@'localhost'
76 TABLE_CATALOG def
77 PRIVILEGE_TYPE INSERT
78 IS_GRANTABLE NO
79 GRANTEE 'testuser2'@'localhost'
80 TABLE_CATALOG def
81 PRIVILEGE_TYPE UPDATE
82 IS_GRANTABLE NO
83 GRANTEE 'testuser3'@'localhost'
84 TABLE_CATALOG def
85 PRIVILEGE_TYPE USAGE
86 IS_GRANTABLE NO
87 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
88 WHERE user LIKE 'testuser%' ORDER BY host, user;
89 host localhost
90 user testuser1
91 json_detailed(priv) {
92 "access": 0,
93 "version_id": VERSION_ID,
94 "plugin": "mysql_native_password",
95 "authentication_string": "",
96 "password_last_changed": #
97 }
98 host localhost
99 user testuser2
100 json_detailed(priv) {
101 "access": 6,
102 "version_id": VERSION_ID,
103 "plugin": "mysql_native_password",
104 "authentication_string": "",
105 "password_last_changed": #
106 }
107 host localhost
108 user testuser3
109 json_detailed(priv) {
110 "access": 0,
111 "version_id": VERSION_ID,
112 "plugin": "mysql_native_password",
113 "authentication_string": "",
114 "password_last_changed": #
115 }
116 #
117 # Add GRANT OPTION db_datadict.* to testuser1;
118 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
119 SELECT * FROM information_schema.user_privileges
120 WHERE grantee LIKE '''testuser%'''
121 ORDER BY grantee, table_catalog, privilege_type;
122 GRANTEE 'testuser1'@'localhost'
123 TABLE_CATALOG def
124 PRIVILEGE_TYPE USAGE
125 IS_GRANTABLE NO
126 GRANTEE 'testuser2'@'localhost'
127 TABLE_CATALOG def
128 PRIVILEGE_TYPE INSERT
129 IS_GRANTABLE NO
130 GRANTEE 'testuser2'@'localhost'
131 TABLE_CATALOG def
132 PRIVILEGE_TYPE UPDATE
133 IS_GRANTABLE NO
134 GRANTEE 'testuser3'@'localhost'
135 TABLE_CATALOG def
136 PRIVILEGE_TYPE USAGE
137 IS_GRANTABLE NO
138 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
139 WHERE user LIKE 'testuser%' ORDER BY host, user;
140 host localhost
141 user testuser1
142 json_detailed(priv) {
143 "access": 0,
144 "version_id": VERSION_ID,
145 "plugin": "mysql_native_password",
146 "authentication_string": "",
147 "password_last_changed": #
148 }
149 host localhost
150 user testuser2
151 json_detailed(priv) {
152 "access": 6,
153 "version_id": VERSION_ID,
154 "plugin": "mysql_native_password",
155 "authentication_string": "",
156 "password_last_changed": #
157 }
158 host localhost
159 user testuser3
160 json_detailed(priv) {
161 "access": 0,
162 "version_id": VERSION_ID,
163 "plugin": "mysql_native_password",
164 "authentication_string": "",
165 "password_last_changed": #
166 }
167 connect testuser1, localhost, testuser1, , db_datadict;
168 SELECT * FROM information_schema.user_privileges
169 WHERE grantee LIKE '''testuser%'''
170 ORDER BY grantee, table_catalog, privilege_type;
171 GRANTEE 'testuser1'@'localhost'
172 TABLE_CATALOG def
173 PRIVILEGE_TYPE USAGE
174 IS_GRANTABLE NO
175 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
176 WHERE user LIKE 'testuser%' ORDER BY host, user;
177 host localhost
178 user testuser1
179 json_detailed(priv) {
180 "access": 0,
181 "version_id": VERSION_ID,
182 "plugin": "mysql_native_password",
183 "authentication_string": "",
184 "password_last_changed": #
185 }
186 host localhost
187 user testuser2
188 json_detailed(priv) {
189 "access": 6,
190 "version_id": VERSION_ID,
191 "plugin": "mysql_native_password",
192 "authentication_string": "",
193 "password_last_changed": #
194 }
195 host localhost
196 user testuser3
197 json_detailed(priv) {
198 "access": 0,
199 "version_id": VERSION_ID,
200 "plugin": "mysql_native_password",
201 "authentication_string": "",
202 "password_last_changed": #
203 }
204 SHOW GRANTS;
205 Grants for testuser1@localhost
206 GRANT USAGE ON *.* TO `testuser1`@`localhost`
207 GRANT SELECT, UPDATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
208 GRANT SELECT ON `mysql`.`global_priv` TO `testuser1`@`localhost`
209
210 # Now add SELECT on *.* to testuser1;
211 connection default;
212 GRANT SELECT ON *.* TO 'testuser1'@'localhost';
213 #
214 # Here <SELECT NO> is shown correctly for testuser1;
215 SELECT * FROM information_schema.user_privileges
216 WHERE grantee LIKE '''testuser%'''
217 ORDER BY grantee, table_catalog, privilege_type;
218 GRANTEE 'testuser1'@'localhost'
219 TABLE_CATALOG def
220 PRIVILEGE_TYPE SELECT
221 IS_GRANTABLE NO
222 GRANTEE 'testuser2'@'localhost'
223 TABLE_CATALOG def
224 PRIVILEGE_TYPE INSERT
225 IS_GRANTABLE NO
226 GRANTEE 'testuser2'@'localhost'
227 TABLE_CATALOG def
228 PRIVILEGE_TYPE UPDATE
229 IS_GRANTABLE NO
230 GRANTEE 'testuser3'@'localhost'
231 TABLE_CATALOG def
232 PRIVILEGE_TYPE USAGE
233 IS_GRANTABLE NO
234 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
235 WHERE user LIKE 'testuser%' ORDER BY host, user;
236 host localhost
237 user testuser1
238 json_detailed(priv) {
239 "access": 1,
240 "version_id": VERSION_ID,
241 "plugin": "mysql_native_password",
242 "authentication_string": "",
243 "password_last_changed": #
244 }
245 host localhost
246 user testuser2
247 json_detailed(priv) {
248 "access": 6,
249 "version_id": VERSION_ID,
250 "plugin": "mysql_native_password",
251 "authentication_string": "",
252 "password_last_changed": #
253 }
254 host localhost
255 user testuser3
256 json_detailed(priv) {
257 "access": 0,
258 "version_id": VERSION_ID,
259 "plugin": "mysql_native_password",
260 "authentication_string": "",
261 "password_last_changed": #
262 }
263 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
264 #
265 # Here <SELECT YES> is shown correctly for testuser1;
266 SELECT * FROM information_schema.user_privileges
267 WHERE grantee LIKE '''testuser%'''
268 ORDER BY grantee, table_catalog, privilege_type;
269 GRANTEE 'testuser1'@'localhost'
270 TABLE_CATALOG def
271 PRIVILEGE_TYPE SELECT
272 IS_GRANTABLE YES
273 GRANTEE 'testuser2'@'localhost'
274 TABLE_CATALOG def
275 PRIVILEGE_TYPE INSERT
276 IS_GRANTABLE NO
277 GRANTEE 'testuser2'@'localhost'
278 TABLE_CATALOG def
279 PRIVILEGE_TYPE UPDATE
280 IS_GRANTABLE NO
281 GRANTEE 'testuser3'@'localhost'
282 TABLE_CATALOG def
283 PRIVILEGE_TYPE USAGE
284 IS_GRANTABLE NO
285 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
286 WHERE user LIKE 'testuser%' ORDER BY host, user;
287 host localhost
288 user testuser1
289 json_detailed(priv) {
290 "access": 1025,
291 "version_id": VERSION_ID,
292 "plugin": "mysql_native_password",
293 "authentication_string": "",
294 "password_last_changed": #
295 }
296 host localhost
297 user testuser2
298 json_detailed(priv) {
299 "access": 6,
300 "version_id": VERSION_ID,
301 "plugin": "mysql_native_password",
302 "authentication_string": "",
303 "password_last_changed": #
304 }
305 host localhost
306 user testuser3
307 json_detailed(priv) {
308 "access": 0,
309 "version_id": VERSION_ID,
310 "plugin": "mysql_native_password",
311 "authentication_string": "",
312 "password_last_changed": #
313 }
314 connection testuser1;
315 SELECT * FROM information_schema.user_privileges
316 WHERE grantee LIKE '''testuser%'''
317 ORDER BY grantee, table_catalog, privilege_type;
318 GRANTEE 'testuser1'@'localhost'
319 TABLE_CATALOG def
320 PRIVILEGE_TYPE SELECT
321 IS_GRANTABLE YES
322 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
323 WHERE user LIKE 'testuser%' ORDER BY host, user;
324 host localhost
325 user testuser1
326 json_detailed(priv) {
327 "access": 1025,
328 "version_id": VERSION_ID,
329 "plugin": "mysql_native_password",
330 "authentication_string": "",
331 "password_last_changed": #
332 }
333 host localhost
334 user testuser2
335 json_detailed(priv) {
336 "access": 6,
337 "version_id": VERSION_ID,
338 "plugin": "mysql_native_password",
339 "authentication_string": "",
340 "password_last_changed": #
341 }
342 host localhost
343 user testuser3
344 json_detailed(priv) {
345 "access": 0,
346 "version_id": VERSION_ID,
347 "plugin": "mysql_native_password",
348 "authentication_string": "",
349 "password_last_changed": #
350 }
351 SHOW GRANTS;
352 Grants for testuser1@localhost
353 GRANT SELECT ON *.* TO `testuser1`@`localhost` WITH GRANT OPTION
354 GRANT SELECT, UPDATE ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
355 GRANT SELECT ON `mysql`.`global_priv` TO `testuser1`@`localhost`
356 connect testuser2, localhost, testuser2, , db_datadict;
357 SELECT * FROM information_schema.user_privileges
358 WHERE grantee LIKE '''testuser%'''
359 ORDER BY grantee, table_catalog, privilege_type;
360 GRANTEE 'testuser2'@'localhost'
361 TABLE_CATALOG def
362 PRIVILEGE_TYPE INSERT
363 IS_GRANTABLE NO
364 GRANTEE 'testuser2'@'localhost'
365 TABLE_CATALOG def
366 PRIVILEGE_TYPE UPDATE
367 IS_GRANTABLE NO
368 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
369 WHERE user LIKE 'testuser%' ORDER BY host, user;
370 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table `mysql`.`global_priv`
371 SHOW GRANTS;
372 Grants for testuser2@localhost
373 GRANT INSERT, UPDATE ON *.* TO `testuser2`@`localhost`
374 connect testuser3, localhost, testuser3, ,"*NO-ONE*";
375 SELECT * FROM information_schema.user_privileges
376 WHERE grantee LIKE '''testuser%'''
377 ORDER BY grantee, table_catalog, privilege_type;
378 GRANTEE 'testuser3'@'localhost'
379 TABLE_CATALOG def
380 PRIVILEGE_TYPE USAGE
381 IS_GRANTABLE NO
382 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
383 WHERE user LIKE 'testuser%' ORDER BY host, user;
384 ERROR 42000: SELECT command denied to user 'testuser3'@'localhost' for table `mysql`.`global_priv`
385 SHOW GRANTS;
386 Grants for testuser3@localhost
387 GRANT USAGE ON *.* TO `testuser3`@`localhost`
388
389 # Revoke privileges from testuser1;
390 connection default;
391 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
392 SELECT * FROM information_schema.user_privileges
393 WHERE grantee LIKE '''testuser%'''
394 ORDER BY grantee, table_catalog, privilege_type;
395 GRANTEE 'testuser1'@'localhost'
396 TABLE_CATALOG def
397 PRIVILEGE_TYPE USAGE
398 IS_GRANTABLE NO
399 GRANTEE 'testuser2'@'localhost'
400 TABLE_CATALOG def
401 PRIVILEGE_TYPE INSERT
402 IS_GRANTABLE NO
403 GRANTEE 'testuser2'@'localhost'
404 TABLE_CATALOG def
405 PRIVILEGE_TYPE UPDATE
406 IS_GRANTABLE NO
407 GRANTEE 'testuser3'@'localhost'
408 TABLE_CATALOG def
409 PRIVILEGE_TYPE USAGE
410 IS_GRANTABLE NO
411 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
412 WHERE user LIKE 'testuser%' ORDER BY host, user;
413 host localhost
414 user testuser1
415 json_detailed(priv) {
416 "access": 0,
417 "version_id": VERSION_ID,
418 "plugin": "mysql_native_password",
419 "authentication_string": "",
420 "password_last_changed": #
421 }
422 host localhost
423 user testuser2
424 json_detailed(priv) {
425 "access": 6,
426 "version_id": VERSION_ID,
427 "plugin": "mysql_native_password",
428 "authentication_string": "",
429 "password_last_changed": #
430 }
431 host localhost
432 user testuser3
433 json_detailed(priv) {
434 "access": 0,
435 "version_id": VERSION_ID,
436 "plugin": "mysql_native_password",
437 "authentication_string": "",
438 "password_last_changed": #
439 }
440 connection testuser1;
441 SELECT * FROM information_schema.user_privileges
442 WHERE grantee LIKE '''testuser%'''
443 ORDER BY grantee, table_catalog, privilege_type;
444 GRANTEE 'testuser1'@'localhost'
445 TABLE_CATALOG def
446 PRIVILEGE_TYPE USAGE
447 IS_GRANTABLE NO
448 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
449 WHERE user LIKE 'testuser%' ORDER BY host, user;
450 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table `mysql`.`global_priv`
451 SHOW GRANTS;
452 Grants for testuser1@localhost
453 GRANT USAGE ON *.* TO `testuser1`@`localhost`
454 CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
455 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table `db_datadict`.`tb_55`
456 SELECT * FROM information_schema.user_privileges
457 WHERE grantee LIKE '''testuser%'''
458 ORDER BY grantee, table_catalog, privilege_type;
459 GRANTEE 'testuser1'@'localhost'
460 TABLE_CATALOG def
461 PRIVILEGE_TYPE USAGE
462 IS_GRANTABLE NO
463 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
464 WHERE user LIKE 'testuser%' ORDER BY host, user;
465 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table `mysql`.`global_priv`
466 SHOW GRANTS;
467 Grants for testuser1@localhost
468 GRANT USAGE ON *.* TO `testuser1`@`localhost`
469 CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
470 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table `db_datadict`.`tb_66`
471
472 # Add ALL on db_datadict.* (and select on mysql.global_priv) to testuser1;
473 connection default;
474 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
475 GRANT SELECT ON mysql.global_priv TO 'testuser1'@'localhost';
476 SELECT * FROM information_schema.user_privileges
477 WHERE grantee LIKE '''testuser%'''
478 ORDER BY grantee, table_catalog, privilege_type;
479 GRANTEE 'testuser1'@'localhost'
480 TABLE_CATALOG def
481 PRIVILEGE_TYPE USAGE
482 IS_GRANTABLE NO
483 GRANTEE 'testuser2'@'localhost'
484 TABLE_CATALOG def
485 PRIVILEGE_TYPE INSERT
486 IS_GRANTABLE NO
487 GRANTEE 'testuser2'@'localhost'
488 TABLE_CATALOG def
489 PRIVILEGE_TYPE UPDATE
490 IS_GRANTABLE NO
491 GRANTEE 'testuser3'@'localhost'
492 TABLE_CATALOG def
493 PRIVILEGE_TYPE USAGE
494 IS_GRANTABLE NO
495 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
496 WHERE user LIKE 'testuser%' ORDER BY host, user;
497 host localhost
498 user testuser1
499 json_detailed(priv) {
500 "access": 0,
501 "version_id": VERSION_ID,
502 "plugin": "mysql_native_password",
503 "authentication_string": "",
504 "password_last_changed": #
505 }
506 host localhost
507 user testuser2
508 json_detailed(priv) {
509 "access": 6,
510 "version_id": VERSION_ID,
511 "plugin": "mysql_native_password",
512 "authentication_string": "",
513 "password_last_changed": #
514 }
515 host localhost
516 user testuser3
517 json_detailed(priv) {
518 "access": 0,
519 "version_id": VERSION_ID,
520 "plugin": "mysql_native_password",
521 "authentication_string": "",
522 "password_last_changed": #
523 }
524 connection testuser1;
525 SELECT * FROM information_schema.user_privileges
526 WHERE grantee LIKE '''testuser%'''
527 ORDER BY grantee, table_catalog, privilege_type;
528 GRANTEE 'testuser1'@'localhost'
529 TABLE_CATALOG def
530 PRIVILEGE_TYPE USAGE
531 IS_GRANTABLE NO
532 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
533 WHERE user LIKE 'testuser%' ORDER BY host, user;
534 host localhost
535 user testuser1
536 json_detailed(priv) {
537 "access": 0,
538 "version_id": VERSION_ID,
539 "plugin": "mysql_native_password",
540 "authentication_string": "",
541 "password_last_changed": #
542 }
543 host localhost
544 user testuser2
545 json_detailed(priv) {
546 "access": 6,
547 "version_id": VERSION_ID,
548 "plugin": "mysql_native_password",
549 "authentication_string": "",
550 "password_last_changed": #
551 }
552 host localhost
553 user testuser3
554 json_detailed(priv) {
555 "access": 0,
556 "version_id": VERSION_ID,
557 "plugin": "mysql_native_password",
558 "authentication_string": "",
559 "password_last_changed": #
560 }
561 SHOW GRANTS;
562 Grants for testuser1@localhost
563 GRANT USAGE ON *.* TO `testuser1`@`localhost`
564 GRANT ALL PRIVILEGES ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
565 GRANT SELECT ON `mysql`.`global_priv` TO `testuser1`@`localhost`
566 CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
567 ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table `db_datadict`.`tb_56`
568 USE db_datadict;
569 SELECT * FROM information_schema.user_privileges
570 WHERE grantee LIKE '''testuser%'''
571 ORDER BY grantee, table_catalog, privilege_type;
572 GRANTEE 'testuser1'@'localhost'
573 TABLE_CATALOG def
574 PRIVILEGE_TYPE USAGE
575 IS_GRANTABLE NO
576 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
577 WHERE user LIKE 'testuser%' ORDER BY host, user;
578 host localhost
579 user testuser1
580 json_detailed(priv) {
581 "access": 0,
582 "version_id": VERSION_ID,
583 "plugin": "mysql_native_password",
584 "authentication_string": "",
585 "password_last_changed": #
586 }
587 host localhost
588 user testuser2
589 json_detailed(priv) {
590 "access": 6,
591 "version_id": VERSION_ID,
592 "plugin": "mysql_native_password",
593 "authentication_string": "",
594 "password_last_changed": #
595 }
596 host localhost
597 user testuser3
598 json_detailed(priv) {
599 "access": 0,
600 "version_id": VERSION_ID,
601 "plugin": "mysql_native_password",
602 "authentication_string": "",
603 "password_last_changed": #
604 }
605 SHOW GRANTS;
606 Grants for testuser1@localhost
607 GRANT USAGE ON *.* TO `testuser1`@`localhost`
608 GRANT ALL PRIVILEGES ON `db_datadict`.* TO `testuser1`@`localhost` WITH GRANT OPTION
609 GRANT SELECT ON `mysql`.`global_priv` TO `testuser1`@`localhost`
610 CREATE TABLE tb_57 ( c1 TEXT )
611 ENGINE = <other_engine_type>;
612
613 # Revoke privileges from testuser1;
614 connection default;
615 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
616 SELECT * FROM information_schema.user_privileges
617 WHERE grantee LIKE '''testuser%'''
618 ORDER BY grantee, table_catalog, privilege_type;
619 GRANTEE 'testuser1'@'localhost'
620 TABLE_CATALOG def
621 PRIVILEGE_TYPE USAGE
622 IS_GRANTABLE NO
623 GRANTEE 'testuser2'@'localhost'
624 TABLE_CATALOG def
625 PRIVILEGE_TYPE INSERT
626 IS_GRANTABLE NO
627 GRANTEE 'testuser2'@'localhost'
628 TABLE_CATALOG def
629 PRIVILEGE_TYPE UPDATE
630 IS_GRANTABLE NO
631 GRANTEE 'testuser3'@'localhost'
632 TABLE_CATALOG def
633 PRIVILEGE_TYPE USAGE
634 IS_GRANTABLE NO
635 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
636 WHERE user LIKE 'testuser%' ORDER BY host, user;
637 host localhost
638 user testuser1
639 json_detailed(priv) {
640 "access": 0,
641 "version_id": VERSION_ID,
642 "plugin": "mysql_native_password",
643 "authentication_string": "",
644 "password_last_changed": #
645 }
646 host localhost
647 user testuser2
648 json_detailed(priv) {
649 "access": 6,
650 "version_id": VERSION_ID,
651 "plugin": "mysql_native_password",
652 "authentication_string": "",
653 "password_last_changed": #
654 }
655 host localhost
656 user testuser3
657 json_detailed(priv) {
658 "access": 0,
659 "version_id": VERSION_ID,
660 "plugin": "mysql_native_password",
661 "authentication_string": "",
662 "password_last_changed": #
663 }
664 connection testuser1;
665 SELECT * FROM information_schema.user_privileges
666 WHERE grantee LIKE '''testuser%'''
667 ORDER BY grantee, table_catalog, privilege_type;
668 GRANTEE 'testuser1'@'localhost'
669 TABLE_CATALOG def
670 PRIVILEGE_TYPE USAGE
671 IS_GRANTABLE NO
672 SELECT host,user,json_detailed(priv) FROM mysql.global_priv
673 WHERE user LIKE 'testuser%' ORDER BY host, user;
674 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table `mysql`.`global_priv`
675 SHOW GRANTS;
676 Grants for testuser1@localhost
677 GRANT USAGE ON *.* TO `testuser1`@`localhost`
678 CREATE TABLE db_datadict.tb_58 ( c1 TEXT )
679 ENGINE = <other_engine_type>;
680 USE db_datadict;
681 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'db_datadict'
682 CREATE TABLE db_datadict.tb_59 ( c1 TEXT )
683 ENGINE = <other_engine_type>;
684 connection default;
685 disconnect testuser1;
686 disconnect testuser2;
687 disconnect testuser3;
688 DROP USER 'testuser1'@'localhost';
689 DROP USER 'testuser2'@'localhost';
690 DROP USER 'testuser3'@'localhost';
691 DROP DATABASE IF EXISTS db_datadict;
692 ########################################################################################
693 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications
694 ########################################################################################
695 SELECT * FROM information_schema.user_privileges
696 WHERE grantee = '''testuser1''@''localhost''';
697 SHOW GRANTS FOR 'testuser1'@'localhost';
698 ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
699 DROP USER 'testuser1'@'localhost';
700 CREATE USER 'testuser1'@'localhost';
701 SELECT * FROM information_schema.user_privileges
702 WHERE grantee = '''testuser1''@''localhost''';
703 GRANTEE 'testuser1'@'localhost'
704 TABLE_CATALOG def
705 PRIVILEGE_TYPE USAGE
706 IS_GRANTABLE NO
707 SHOW GRANTS FOR 'testuser1'@'localhost';
708 Grants for testuser1@localhost
709 GRANT USAGE ON *.* TO `testuser1`@`localhost`
710 GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost';
711 SELECT * FROM information_schema.user_privileges
712 WHERE grantee = '''testuser1''@''localhost''';
713 GRANTEE 'testuser1'@'localhost'
714 TABLE_CATALOG def
715 PRIVILEGE_TYPE SELECT
716 IS_GRANTABLE NO
717 GRANTEE 'testuser1'@'localhost'
718 TABLE_CATALOG def
719 PRIVILEGE_TYPE FILE
720 IS_GRANTABLE NO
721 SHOW GRANTS FOR 'testuser1'@'localhost';
722 Grants for testuser1@localhost
723 GRANT SELECT, FILE ON *.* TO `testuser1`@`localhost`
724 DROP USER 'testuser1'@'localhost';
725 SELECT * FROM information_schema.user_privileges
726 WHERE grantee = '''testuser1''@''localhost''';
727 SHOW GRANTS FOR 'testuser1'@'localhost';
728 ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
729 ########################################################################
730 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
731 # DDL on INFORMATION_SCHEMA tables are not supported
732 ########################################################################
733 DROP USER 'testuser1'@'localhost';
734 CREATE USER 'testuser1'@'localhost';
735 INSERT INTO information_schema.user_privileges
736 SELECT * FROM information_schema.user_privileges;
737 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
738 UPDATE information_schema.user_privileges
739 SET PRIVILEGE_TYPE = 'gaming';
740 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
741 DELETE FROM information_schema.user_privileges
742 WHERE grantee = '''testuser1''@''localhost''';
743 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
744 TRUNCATE information_schema.user_privileges;
745 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
746 CREATE INDEX i1 ON information_schema.user_privileges(grantee);
747 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
748 ALTER TABLE information_schema.user_privileges ADD f1 INT;
749 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
750 DROP TABLE information_schema.user_privileges;
751 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
752 ALTER TABLE information_schema.user_privileges
753 RENAME db_datadict.user_privileges;
754 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
755 ALTER TABLE information_schema.user_privileges
756 RENAME information_schema.xuser_privileges;
757 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
758 DROP USER 'testuser1'@'localhost';