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