diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2020-02-11 14:40:35 +0100 |
|---|---|---|
| committer | Oleksandr Byelkin <sanja@mariadb.com> | 2020-02-11 14:40:35 +0100 |
| commit | 646d1ec83a57d9a5b380079afc3612c1d9acadd5 (patch) | |
| tree | b46ec54915361f3baa221bcd01b4308a55c59c48 /mysql-test/main | |
| parent | c1eaa385ffb44bdf6264d2cc4b4cc0e10284c88a (diff) | |
| parent | 58b70dc13630d2f2f0244359d6351085d70fd5dd (diff) | |
| download | mariadb-git-646d1ec83a57d9a5b380079afc3612c1d9acadd5.tar.gz | |
Merge branch '10.3' into 10.4
Diffstat (limited to 'mysql-test/main')
44 files changed, 921 insertions, 548 deletions
diff --git a/mysql-test/main/connect.result b/mysql-test/main/connect.result index ba8448b30ab..ee1844bc3ee 100644 --- a/mysql-test/main/connect.result +++ b/mysql-test/main/connect.result @@ -103,12 +103,12 @@ update mysql.user set plugin="", authentication_string="", password=old_password flush privileges; show grants for test@localhost; Grants for test@localhost -GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '2f27438961437573' +GRANT ALL PRIVILEGES ON *.* TO `test`@`localhost` IDENTIFIED BY PASSWORD '2f27438961437573' update mysql.user set plugin='mysql_old_password' where user='test'; flush privileges; show grants for test@localhost; Grants for test@localhost -GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '2f27438961437573' +GRANT ALL PRIVILEGES ON *.* TO `test`@`localhost` IDENTIFIED BY PASSWORD '2f27438961437573' connect con10,localhost,test,gambling2,; connect con5,localhost,test,gambling2,mysql; set password=""; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index fe074c2a8fd..7dea6a81e04 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16823,6 +16823,95 @@ id username id userid logindate 2 user2 3 2 2017-06-19 12:17:02 set join_cache_level=default; DROP TABLE t1,t2; +# +# MDEV-21614: potentially splittable materialized derived/view +# within materialized semi-join +# +create table t1 ( +id int not null auto_increment primary key, +a int not null +) engine=myisam; +create table t2 ( +id int not null auto_increment primary key, +ro_id int not null, +flag int not null, key (ro_id) +) engine=myisam; +insert into t1(a) select seq+100 from seq_1_to_20; +insert into t2(ro_id,flag) select seq, 1 from seq_1_to_20; +insert into t2(ro_id,flag) select seq, 0 from seq_1_to_20; +create view v1 as +select t1.* from t1 left join t2 on (t1.id = t2.ro_id AND t2.flag = 1) +group by t1.id; +select id, a from t1 where id in (select id from v1); +id a +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +10 110 +11 111 +12 112 +13 113 +14 114 +15 115 +16 116 +17 117 +18 118 +19 119 +20 120 +explain extended select id, a from t1 where id in (select id from v1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 +1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`v1`) where `v1`.`id` = `test`.`t1`.`id` +select id, a from t1 +where id in (select id +from (select t1.* from t1 left join t2 +on (t1.id = t2.ro_id AND t2.flag = 1) +group by t1.id) dt); +id a +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +10 110 +11 111 +12 112 +13 113 +14 114 +15 115 +16 116 +17 117 +18 118 +19 119 +20 120 +explain extended select id, a from t1 +where id in (select id +from (select t1.* from t1 left join t2 +on (t1.id = t2.ro_id AND t2.flag = 1) +group by t1.id) dt); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 +1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where `test`.`t1`.`id` = `test`.`t1`.`id` group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id` +drop view v1; +drop table t1,t2; # End of 10.3 tests # # MDEV-18679: materialized view with SELECT S containing materialized diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 1441989e2cc..152a130b8af 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -1,3 +1,4 @@ +--source include/have_sequence.inc --source include/default_optimizer_switch.inc let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_derived=off' for; set @@join_buffer_size=256*1024; @@ -3328,6 +3329,48 @@ set join_cache_level=default; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-21614: potentially splittable materialized derived/view +--echo # within materialized semi-join +--echo # + +create table t1 ( + id int not null auto_increment primary key, + a int not null +) engine=myisam; + +create table t2 ( + id int not null auto_increment primary key, + ro_id int not null, + flag int not null, key (ro_id) +) engine=myisam; + +insert into t1(a) select seq+100 from seq_1_to_20; +insert into t2(ro_id,flag) select seq, 1 from seq_1_to_20; +insert into t2(ro_id,flag) select seq, 0 from seq_1_to_20; + +create view v1 as +select t1.* from t1 left join t2 on (t1.id = t2.ro_id AND t2.flag = 1) + group by t1.id; + +let $q1= +select id, a from t1 where id in (select id from v1); +eval $q1; +eval explain extended $q1; + +let $q2= +select id, a from t1 + where id in (select id + from (select t1.* from t1 left join t2 + on (t1.id = t2.ro_id AND t2.flag = 1) + group by t1.id) dt); +eval $q2; +eval explain extended $q2; + +drop view v1; +drop table t1,t2; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/events_bugs.result b/mysql-test/main/events_bugs.result index 4870c464fff..666361188b9 100644 --- a/mysql-test/main/events_bugs.result +++ b/mysql-test/main/events_bugs.result @@ -618,7 +618,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp USE test; SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION SET GLOBAL event_scheduler = ON; CREATE TABLE events_test.event_log @@ -632,9 +632,9 @@ GRANT create, insert, select, event ON events_test.* TO evtest1@localhost; GRANT select,insert ON test.* TO evtest1@localhost; SHOW GRANTS FOR evtest1@localhost; Grants for evtest1@localhost -GRANT USAGE ON *.* TO 'evtest1'@'localhost' IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32' -GRANT SELECT, INSERT ON `test`.* TO 'evtest1'@'localhost' -GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO 'evtest1'@'localhost' +GRANT USAGE ON *.* TO `evtest1`@`localhost` IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32' +GRANT SELECT, INSERT ON `test`.* TO `evtest1`@`localhost` +GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO `evtest1`@`localhost` connect e1,localhost,evtest1,ev1,events_test,$MASTER_MYPORT,$MASTER_MYSOCK; CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND DO BEGIN diff --git a/mysql-test/main/events_grant.result b/mysql-test/main/events_grant.result index c4f459dbe5b..5bdbbf0f8f1 100644 --- a/mysql-test/main/events_grant.result +++ b/mysql-test/main/events_grant.result @@ -23,9 +23,9 @@ USER() DATABASE() ev_test@localhost events_test2 SHOW GRANTS; Grants for ev_test@localhost -GRANT USAGE ON *.* TO 'ev_test'@'localhost' -GRANT ALL PRIVILEGES ON `events_test`.* TO 'ev_test'@'localhost' -GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, DELETE HISTORY ON `events_test2`.* TO 'ev_test'@'localhost' +GRANT USAGE ON *.* TO `ev_test`@`localhost` +GRANT ALL PRIVILEGES ON `events_test`.* TO `ev_test`@`localhost` +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, DELETE HISTORY ON `events_test2`.* TO `ev_test`@`localhost` "Here comes an error:"; SHOW EVENTS; ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2' diff --git a/mysql-test/main/fix_priv_tables.result b/mysql-test/main/fix_priv_tables.result index dea1ccdbd18..3f1830aadbc 100644 --- a/mysql-test/main/fix_priv_tables.result +++ b/mysql-test/main/fix_priv_tables.result @@ -10,25 +10,25 @@ SELECT * FROM testdb.t1; GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost'; SHOW GRANTS FOR 'show_view_tbl'@'localhost'; Grants for show_view_tbl@localhost -GRANT USAGE ON *.* TO 'show_view_tbl'@'localhost' -GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO 'show_view_tbl'@'localhost' +GRANT USAGE ON *.* TO `show_view_tbl`@`localhost` +GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO `show_view_tbl`@`localhost` GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost; SHOW GRANTS FOR 'select_only_c1'@'localhost'; Grants for select_only_c1@localhost -GRANT USAGE ON *.* TO 'select_only_c1'@'localhost' -GRANT SELECT (c1) ON `testdb`.`v1` TO 'select_only_c1'@'localhost' +GRANT USAGE ON *.* TO `select_only_c1`@`localhost` +GRANT SELECT (c1) ON `testdb`.`v1` TO `select_only_c1`@`localhost` "after fix privs" SHOW GRANTS FOR 'show_view_tbl'@'localhost'; Grants for show_view_tbl@localhost -GRANT USAGE ON *.* TO 'show_view_tbl'@'localhost' -GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO 'show_view_tbl'@'localhost' +GRANT USAGE ON *.* TO `show_view_tbl`@`localhost` +GRANT CREATE VIEW, SHOW VIEW ON `testdb`.`v1` TO `show_view_tbl`@`localhost` SHOW GRANTS FOR 'select_only_c1'@'localhost'; Grants for select_only_c1@localhost -GRANT USAGE ON *.* TO 'select_only_c1'@'localhost' -GRANT SELECT (c1) ON `testdb`.`v1` TO 'select_only_c1'@'localhost' +GRANT USAGE ON *.* TO `select_only_c1`@`localhost` +GRANT SELECT (c1) ON `testdb`.`v1` TO `select_only_c1`@`localhost` DROP USER 'show_view_tbl'@'localhost'; DROP USER 'select_only_c1'@'localhost'; diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result index d544c97e636..e83083be4ed 100644 --- a/mysql-test/main/grant.result +++ b/mysql-test/main/grant.result @@ -14,8 +14,8 @@ flush privileges; grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` grant delete on mysqltest.* to mysqltest_1@localhost; select * from mysql.user where user="mysqltest_1"; Host localhost @@ -67,27 +67,27 @@ default_role max_statement_time 0.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' -GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT, DELETE ON `mysqltest`.* TO `mysqltest_1`@`localhost` revoke delete on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` grant select on mysqltest.* to mysqltest_1@localhost require NONE; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "Monty Program Ab"; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'Monty Program Ab' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` REQUIRE ISSUER 'Monty Program Ab' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` revoke all privileges on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'Monty Program Ab' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` REQUIRE ISSUER 'Monty Program Ab' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' delete from mysql.user where user='mysqltest_1'; flush privileges; delete from mysql.user where user='mysqltest_1'; @@ -143,7 +143,7 @@ default_role max_statement_time 60.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_STATEMENT_TIME 60.000000 +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` WITH MAX_QUERIES_PER_HOUR 10 MAX_STATEMENT_TIME 60.000000 grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30 max_statement_time 0; select * from mysql.user where user="mysqltest_1"; Host localhost @@ -195,48 +195,48 @@ default_role max_statement_time 0.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 flush privileges; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 delete from mysql.user where user='mysqltest_1'; flush privileges; grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO `mysqltest_1`@`localhost` flush privileges; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO `mysqltest_1`@`localhost` revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT LOCK TABLES ON `mysqltest`.* TO `mysqltest_1`@`localhost` grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; flush privileges; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `mysqltest`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; grant usage on test.* to mysqltest_1@localhost with grant option; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION -GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT USAGE ON `mysqltest`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION +GRANT USAGE ON `test`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; @@ -249,21 +249,21 @@ GRANT select,update,insert on t1 to mysqltest_1@localhost; GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO `mysqltest_1`@`localhost` select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; table_priv column_priv Select,Insert,Update Select,Insert,Update,References REVOKE select (a), update on t1 from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO `mysqltest_1`@`localhost` REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT REFERENCES (a) ON `test`.`t1` TO `mysqltest_1`@`localhost` GRANT select,references on t1 to mysqltest_1@localhost; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; table_priv column_priv @@ -272,22 +272,22 @@ grant all on test.* to mysqltest_3@localhost with grant option; revoke all on test.* from mysqltest_3@localhost; show grants for mysqltest_3@localhost; Grants for mysqltest_3@localhost -GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' -GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_3`@`localhost` +GRANT USAGE ON `test`.* TO `mysqltest_3`@`localhost` WITH GRANT OPTION revoke grant option on test.* from mysqltest_3@localhost; show grants for mysqltest_3@localhost; Grants for mysqltest_3@localhost -GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_3`@`localhost` grant all on test.t1 to mysqltest_2@localhost with grant option; revoke all on test.t1 from mysqltest_2@localhost; show grants for mysqltest_2@localhost; Grants for mysqltest_2@localhost -GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost' -GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_2`@`localhost` +GRANT USAGE ON `test`.`t1` TO `mysqltest_2`@`localhost` WITH GRANT OPTION revoke grant option on test.t1 from mysqltest_2@localhost; show grants for mysqltest_2@localhost; Grants for mysqltest_2@localhost -GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_2`@`localhost` delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; @@ -306,16 +306,16 @@ alter table mysql.db order by db asc; flush privileges; show grants for test11@localhost; Grants for test11@localhost -GRANT USAGE ON *.* TO 'test11'@'localhost' -GRANT SELECT ON `ab%`.* TO 'test11'@'localhost' -GRANT SELECT ON `a%`.* TO 'test11'@'localhost' +GRANT USAGE ON *.* TO `test11`@`localhost` +GRANT SELECT ON `ab%`.* TO `test11`@`localhost` +GRANT SELECT ON `a%`.* TO `test11`@`localhost` alter table mysql.db order by db desc; flush privileges; show grants for test11@localhost; Grants for test11@localhost -GRANT USAGE ON *.* TO 'test11'@'localhost' -GRANT SELECT ON `ab%`.* TO 'test11'@'localhost' -GRANT SELECT ON `a%`.* TO 'test11'@'localhost' +GRANT USAGE ON *.* TO `test11`@`localhost` +GRANT SELECT ON `ab%`.* TO `test11`@`localhost` +GRANT SELECT ON `a%`.* TO `test11`@`localhost` delete from mysql.user where user='test11'; delete from mysql.db where user='test11'; create database mysqltest1; @@ -328,7 +328,7 @@ create table t1 (a int); grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; show grants for drop_user2@localhost; Grants for drop_user2@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `drop_user2`@`localhost` WITH GRANT OPTION revoke all privileges, grant option from drop_user2@localhost; drop user drop_user2@localhost; grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; @@ -336,44 +336,44 @@ grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; grant select(a) on test.t1 to drop_user@localhost; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO `drop_user`@`localhost` WITH GRANT OPTION +GRANT ALL PRIVILEGES ON `test`.* TO `drop_user`@`localhost` WITH GRANT OPTION +GRANT SELECT (a) ON `test`.`t1` TO `drop_user`@`localhost` set sql_mode=ansi_quotes; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO "drop_user"@"localhost" WITH GRANT OPTION +GRANT ALL PRIVILEGES ON "test".* TO "drop_user"@"localhost" WITH GRANT OPTION +GRANT SELECT (a) ON "test"."t1" TO "drop_user"@"localhost" set sql_mode=default; set sql_quote_show_create=0; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO drop_user@localhost WITH GRANT OPTION +GRANT ALL PRIVILEGES ON test.* TO drop_user@localhost WITH GRANT OPTION +GRANT SELECT (a) ON test.t1 TO drop_user@localhost set sql_mode="ansi_quotes"; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO drop_user@localhost WITH GRANT OPTION +GRANT ALL PRIVILEGES ON test.* TO drop_user@localhost WITH GRANT OPTION +GRANT SELECT (a) ON test.t1 TO drop_user@localhost set sql_quote_show_create=1; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO "drop_user"@"localhost" WITH GRANT OPTION +GRANT ALL PRIVILEGES ON "test".* TO "drop_user"@"localhost" WITH GRANT OPTION +GRANT SELECT (a) ON "test"."t1" TO "drop_user"@"localhost" set sql_mode=""; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO `drop_user`@`localhost` WITH GRANT OPTION +GRANT ALL PRIVILEGES ON `test`.* TO `drop_user`@`localhost` WITH GRANT OPTION +GRANT SELECT (a) ON `test`.`t1` TO `drop_user`@`localhost` revoke all privileges, grant option from drop_user@localhost; show grants for drop_user@localhost; Grants for drop_user@localhost -GRANT USAGE ON *.* TO 'drop_user'@'localhost' +GRANT USAGE ON *.* TO `drop_user`@`localhost` drop user drop_user@localhost; revoke all privileges, grant option from drop_user@localhost; ERROR HY000: Can't revoke all privileges for one or more of the requested users @@ -394,8 +394,8 @@ grant usage on *.* to mysqltest_1@localhost identified by "password"; grant select, update, insert on test.* to mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' -GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' +GRANT SELECT, INSERT, UPDATE ON `test`.* TO `mysqltest_1`@`localhost` drop user mysqltest_1@localhost; SET NAMES koi8r; CREATE DATABASE ÂÄ; @@ -404,20 +404,20 @@ CREATE TABLE ÔÁÂ (ËÏÌ INT); GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; SHOW GRANTS FOR ÀÚÅÒ@localhost; Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT ON `ÂÄ`.* TO 'ÀÚÅÒ'@'localhost' +GRANT USAGE ON *.* TO `ÀÚÅÒ`@`localhost` +GRANT SELECT ON `ÂÄ`.* TO `ÀÚÅÒ`@`localhost` REVOKE SELECT ON ÂÄ.* FROM ÀÚÅÒ@localhost; GRANT SELECT ON ÂÄ.ÔÁÂ TO ÀÚÅÒ@localhost; SHOW GRANTS FOR ÀÚÅÒ@localhost; Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT ON `ÂÄ`.`ÔÁÂ` TO 'ÀÚÅÒ'@'localhost' +GRANT USAGE ON *.* TO `ÀÚÅÒ`@`localhost` +GRANT SELECT ON `ÂÄ`.`ÔÁÂ` TO `ÀÚÅÒ`@`localhost` REVOKE SELECT ON ÂÄ.ÔÁÂ FROM ÀÚÅÒ@localhost; GRANT SELECT (ËÏÌ) ON ÂÄ.ÔÁÂ TO ÀÚÅÒ@localhost; SHOW GRANTS FOR ÀÚÅÒ@localhost; Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT (ËÏÌ) ON `ÂÄ`.`ÔÁÂ` TO 'ÀÚÅÒ'@'localhost' +GRANT USAGE ON *.* TO `ÀÚÅÒ`@`localhost` +GRANT SELECT (ËÏÌ) ON `ÂÄ`.`ÔÁÂ` TO `ÀÚÅÒ`@`localhost` REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁÂ FROM ÀÚÅÒ@localhost; DROP USER ÀÚÅÒ@localhost; DROP DATABASE ÂÄ; @@ -476,7 +476,7 @@ GRANT SELECT (a) ON test.t10 TO testuser@localhost; REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; SHOW GRANTS FOR testuser@localhost; Grants for testuser@localhost -GRANT USAGE ON *.* TO 'testuser'@'localhost' +GRANT USAGE ON *.* TO `testuser`@`localhost` DROP USER testuser@localhost; DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; DROP DATABASE testdb1; @@ -493,8 +493,8 @@ create table t1(a int, b int, c int, d int); grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; show grants for grant_user@localhost; Grants for grant_user@localhost -GRANT USAGE ON *.* TO 'grant_user'@'localhost' -GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost' +GRANT USAGE ON *.* TO `grant_user`@`localhost` +GRANT INSERT (a, d, c, b) ON `test`.`t1` TO `grant_user`@`localhost` select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; Host Db User Table_name Column_name Column_priv localhost test grant_user t1 a Insert @@ -504,7 +504,7 @@ localhost test grant_user t1 d Insert revoke ALL PRIVILEGES on t1 from grant_user@localhost; show grants for grant_user@localhost; Grants for grant_user@localhost -GRANT USAGE ON *.* TO 'grant_user'@'localhost' +GRANT USAGE ON *.* TO `grant_user`@`localhost` select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; Host Db User Table_name Column_name Column_priv drop user grant_user@localhost; @@ -656,9 +656,9 @@ GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO `dummy`@`localhost` use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE @@ -669,9 +669,9 @@ mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DELETE HISTORY, DROP, IN FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO `dummy`@`localhost` SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; @@ -700,9 +700,9 @@ GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost; GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE @@ -713,9 +713,9 @@ mysqltest dummyview CREATE VIEW FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; @@ -734,9 +734,9 @@ GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost; GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` use INFORMATION_SCHEMA; SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE @@ -747,9 +747,9 @@ mysqltest dummyview SHOW VIEW FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' +GRANT USAGE ON *.* TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; @@ -778,27 +778,27 @@ hex(Db) D0B1D0B4 show grants for root@localhost; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -GRANT SELECT ON `ÂÄ`.* TO 'root'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION +GRANT SELECT ON `ÂÄ`.* TO `root`@`localhost` GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION flush privileges; show grants for root@localhost; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -GRANT SELECT ON `ÂÄ`.* TO 'root'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION +GRANT SELECT ON `ÂÄ`.* TO `root`@`localhost` GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION drop database ÂÄ; revoke all privileges on ÂÄ.* from root@localhost; show grants for root@localhost; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION set names latin1; create user mysqltest_7@; set password for mysqltest_7@ = password('systpass'); show grants for mysqltest_7@; Grants for mysqltest_7@% -GRANT USAGE ON *.* TO 'mysqltest_7'@'%' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517' +GRANT USAGE ON *.* TO `mysqltest_7`@`%` IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517' drop user mysqltest_7@; show grants for mysqltest_7@; ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host '%' @@ -809,12 +809,12 @@ GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; show grants for mysqltest1@'192.%'; Grants for mysqltest1@192.% -GRANT USAGE ON *.* TO 'mysqltest1'@'192.%' -GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%' +GRANT USAGE ON *.* TO `mysqltest1`@`192.%` +GRANT SELECT ON `mysqltest`.`t1` TO `mysqltest1`@`192.%` show grants for mysqltest1@'%'; Grants for mysqltest1@% -GRANT USAGE ON *.* TO 'mysqltest1'@'%' -GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%' +GRANT USAGE ON *.* TO `mysqltest1`@`%` +GRANT DELETE ON `mysqltest`.`t1` TO `mysqltest1`@`%` delete from mysql.user where user='mysqltest1'; delete from mysql.db where user='mysqltest1'; delete from mysql.tables_priv where user='mysqltest1'; @@ -854,18 +854,18 @@ Schema privileges grant select on mysqltest.* to mysqltest_8@''; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_8`@`%` grant select on mysqltest.* to mysqltest_8@; show grants for mysqltest_8@; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_8`@`%` grant select on mysqltest.* to mysqltest_8; show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_8`@`%` select * from information_schema.schema_privileges where grantee like "'mysqltest_8'%"; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE @@ -878,26 +878,26 @@ connection master; revoke select on mysqltest.* from mysqltest_8@''; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` select * from information_schema.schema_privileges where grantee like "'mysqltest_8'%"; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8@; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` grant select on mysqltest.* to mysqltest_8@''; flush privileges; show grants for mysqltest_8@; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_8`@`%` revoke select on mysqltest.* from mysqltest_8@''; flush privileges; Column privileges @@ -905,21 +905,21 @@ grant update (a) on t1 to mysqltest_8@''; grant update (a) on t1 to mysqltest_8; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` select * from information_schema.column_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_8'@'%' def test t1 a UPDATE NO @@ -931,39 +931,39 @@ connection master; revoke update (a) on t1 from mysqltest_8@''; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` select * from information_schema.column_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` Table privileges grant update on t1 to mysqltest_8@''; grant update on t1 to mysqltest_8; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE ON `test`.`t1` TO `mysqltest_8`@`%` flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT UPDATE ON `test`.`t1` TO `mysqltest_8`@`%` select * from information_schema.table_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_8'@'%' def test t1 UPDATE NO @@ -975,19 +975,19 @@ connection master; revoke update on t1 from mysqltest_8@''; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` select * from information_schema.table_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` "DROP USER" should clear privileges grant all privileges on mysqltest.* to mysqltest_8@''; grant select on mysqltest.* to mysqltest_8@''; @@ -996,14 +996,14 @@ grant update (a) on t1 to mysqltest_8@''; grant all privileges on mysqltest.* to mysqltest_8; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_8`@`%` +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_8`@`%` +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` select * from information_schema.user_privileges where grantee like "'mysqltest_8'%"; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE @@ -1017,14 +1017,14 @@ connection master; flush privileges; show grants for mysqltest_8@''; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_8`@`%` +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` show grants for mysqltest_8; Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_8`@`%` +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO `mysqltest_8`@`%` drop user mysqltest_8@''; show grants for mysqltest_8@''; ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%' @@ -1139,8 +1139,8 @@ USER() mysqltest_1@localhost SHOW GRANTS; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest1`.`t1` TO `mysqltest_1`@`localhost` RENAME TABLE t1 TO t2; ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' ALTER TABLE t1 RENAME TO t2; @@ -1159,8 +1159,8 @@ GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost; connect conn42,localhost,mysqltest_1,,mysqltest1; SHOW GRANTS; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, DROP, ALTER ON `mysqltest1`.`t1` TO `mysqltest_1`@`localhost` RENAME TABLE t1 TO t2; ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' ALTER TABLE t1 RENAME TO t2; @@ -1171,8 +1171,8 @@ GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; connect conn42,localhost,mysqltest_1,,mysqltest1; SHOW GRANTS; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO `mysqltest_1`@`localhost` disconnect conn42; connection default; GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost; @@ -1180,9 +1180,9 @@ DROP TABLE mysqltest1.t2; connect conn42,localhost,mysqltest_1,,mysqltest1; SHOW GRANTS; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO `mysqltest_1`@`localhost` RENAME TABLE t1 TO t2; RENAME TABLE t2 TO t1; ALTER TABLE t1 RENAME TO t2; @@ -1194,9 +1194,9 @@ REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; connect conn42,localhost,mysqltest_1,,mysqltest1; SHOW GRANTS; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t1` TO `mysqltest_1`@`localhost` +GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO `mysqltest_1`@`localhost` RENAME TABLE t1 TO t2; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' ALTER TABLE t1 RENAME TO t2; @@ -1532,9 +1532,9 @@ grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; # in privilege tables and in in-memory structures. show grants for mysqluser1@localhost; Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost' +GRANT USAGE ON *.* TO `mysqluser1`@`localhost` +GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO `mysqluser1`@`localhost` +GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO `mysqluser1`@`localhost` select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; db routine_name routine_type proc_priv mysqltest1 f1 FUNCTION Execute @@ -1565,7 +1565,7 @@ connection default; create user mysqluser1@localhost; show grants for mysqluser1@localhost; Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT USAGE ON *.* TO `mysqluser1`@`localhost` select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; db routine_name routine_type proc_priv connect bug36544_con2,localhost,mysqluser1,,; @@ -1599,11 +1599,11 @@ grant select on mysqltest1.t11 to mysqluser1@localhost; # in privilege tables and in in-memory structures. show grants for mysqluser1@localhost; Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost' +GRANT USAGE ON *.* TO `mysqluser1`@`localhost` +GRANT SELECT ON `mysqltest1`.`t11` TO `mysqluser1`@`localhost` +GRANT SELECT ON `mysqltest1`.`t22` TO `mysqluser1`@`localhost` +GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO `mysqluser1`@`localhost` +GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO `mysqluser1`@`localhost` select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; db routine_name routine_type proc_priv mysqltest1 f1 FUNCTION Execute @@ -1645,7 +1645,7 @@ connection default; create user mysqluser1@localhost; show grants for mysqluser1@localhost; Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT USAGE ON *.* TO `mysqluser1`@`localhost` select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; db routine_name routine_type proc_priv select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; @@ -1669,11 +1669,11 @@ connection default; # show grants for mysqluser10@localhost; Grants for mysqluser10@localhost -GRANT USAGE ON *.* TO 'mysqluser10'@'localhost' -GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser10'@'localhost' -GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser10'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser10'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser10'@'localhost' +GRANT USAGE ON *.* TO `mysqluser10`@`localhost` +GRANT SELECT ON `mysqltest1`.`t11` TO `mysqluser10`@`localhost` +GRANT SELECT ON `mysqltest1`.`t22` TO `mysqluser10`@`localhost` +GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO `mysqluser10`@`localhost` +GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO `mysqluser10`@`localhost` select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; db routine_name routine_type proc_priv mysqltest1 f1 FUNCTION Execute @@ -1813,8 +1813,8 @@ GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost -GRANT USAGE ON *.* TO 'user1'@'localhost' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' +GRANT USAGE ON *.* TO `user1`@`localhost` +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO `user1`@`localhost` connect con1,localhost,user1,,; ** Connect as user1 and create a procedure. ** The creation will imply implicitly assigned @@ -1845,14 +1845,14 @@ END ;|| connection default; SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost -GRANT USAGE ON *.* TO 'user1'@'localhost' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc1` TO 'user1'@'localhost' +GRANT USAGE ON *.* TO `user1`@`localhost` +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO `user1`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc1` TO `user1`@`localhost` SHOW GRANTS FOR 'user2'; Grants for user2@% -GRANT USAGE ON *.* TO 'user2'@'%' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO 'user2'@'%' +GRANT USAGE ON *.* TO `user2`@`%` +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO `user2`@`%` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO `user2`@`%` disconnect con1; disconnect con2; DROP PROCEDURE db1.proc1; @@ -1873,19 +1873,19 @@ ERROR 3D000: No database selected GRANT ALL ON *.* TO mysqltest_1; SHOW GRANTS FOR mysqltest_1; Grants for mysqltest_1@% -GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%' +GRANT ALL PRIVILEGES ON *.* TO `mysqltest_1`@`%` DROP USER mysqltest_1; USE test; GRANT ALL ON * TO mysqltest_1; SHOW GRANTS FOR mysqltest_1; Grants for mysqltest_1@% -GRANT USAGE ON *.* TO 'mysqltest_1'@'%' -GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'%' +GRANT USAGE ON *.* TO `mysqltest_1`@`%` +GRANT ALL PRIVILEGES ON `test`.* TO `mysqltest_1`@`%` DROP USER mysqltest_1; GRANT ALL ON *.* TO mysqltest_1; SHOW GRANTS FOR mysqltest_1; Grants for mysqltest_1@% -GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%' +GRANT ALL PRIVILEGES ON *.* TO `mysqltest_1`@`%` DROP USER mysqltest_1; connection default; disconnect conn1; @@ -1958,8 +1958,8 @@ GRANT USAGE ON *.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `mysqltest_u1`@`localhost` +GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -1971,7 +1971,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global SELECT allows SHOW CREATE TABLE. @@ -1981,8 +1981,8 @@ GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SELECT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT SELECT ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -1997,7 +1997,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global INSERT allows SHOW CREATE TABLE. @@ -2007,8 +2007,8 @@ GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INSERT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT INSERT ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2023,7 +2023,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global UPDATE allows SHOW CREATE TABLE. @@ -2033,8 +2033,8 @@ GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT UPDATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT UPDATE ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2049,7 +2049,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global DELETE allows SHOW CREATE TABLE. @@ -2059,8 +2059,8 @@ GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DELETE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT DELETE ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2075,7 +2075,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global CREATE allows SHOW CREATE TABLE. @@ -2085,8 +2085,8 @@ GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT CREATE ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2101,7 +2101,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global DROP allows SHOW CREATE TABLE. @@ -2111,8 +2111,8 @@ GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DROP ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT DROP ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2127,7 +2127,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global ALTER allows SHOW CREATE TABLE. @@ -2137,8 +2137,8 @@ GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT ALTER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT ALTER ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2153,7 +2153,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global INDEX allows SHOW CREATE TABLE. @@ -2163,8 +2163,8 @@ GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INDEX ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT INDEX ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2179,7 +2179,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global REFERENCES allows SHOW CREATE TABLE. @@ -2189,8 +2189,8 @@ GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT REFERENCES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT REFERENCES ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2205,7 +2205,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global GRANT OPTION allows SHOW CREATE TABLE. @@ -2215,8 +2215,8 @@ GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT USAGE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT USAGE ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` WITH GRANT OPTION connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2231,7 +2231,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global CREATE VIEW allows SHOW CREATE TABLE. @@ -2241,8 +2241,8 @@ GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT CREATE VIEW ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2257,7 +2257,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that global SHOW VIEW allows SHOW CREATE TABLE. @@ -2267,8 +2267,8 @@ GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SHOW VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT SHOW VIEW ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2283,7 +2283,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level SELECT allows SHOW CREATE TABLE. @@ -2293,8 +2293,8 @@ GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SELECT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT SELECT ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2309,7 +2309,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level INSERT allows SHOW CREATE TABLE. @@ -2319,8 +2319,8 @@ GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INSERT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT INSERT ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2335,7 +2335,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level UPDATE allows SHOW CREATE TABLE. @@ -2345,8 +2345,8 @@ GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT UPDATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT UPDATE ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2361,7 +2361,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level DELETE allows SHOW CREATE TABLE. @@ -2371,8 +2371,8 @@ GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DELETE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT DELETE ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2387,7 +2387,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level CREATE allows SHOW CREATE TABLE. @@ -2397,8 +2397,8 @@ GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT CREATE ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2413,7 +2413,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level DROP allows SHOW CREATE TABLE. @@ -2423,8 +2423,8 @@ GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DROP ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT DROP ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2439,7 +2439,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level ALTER allows SHOW CREATE TABLE. @@ -2449,8 +2449,8 @@ GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT ALTER ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT ALTER ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2465,7 +2465,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level INDEX allows SHOW CREATE TABLE. @@ -2475,8 +2475,8 @@ GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INDEX ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT INDEX ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2491,7 +2491,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level REFERENCES allows SHOW CREATE TABLE. @@ -2501,8 +2501,8 @@ GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT REFERENCES ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT REFERENCES ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2517,7 +2517,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. @@ -2527,8 +2527,8 @@ GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT USAGE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT USAGE ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` WITH GRANT OPTION connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2543,7 +2543,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. @@ -2553,8 +2553,8 @@ GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT CREATE VIEW ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2569,7 +2569,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. @@ -2579,8 +2579,8 @@ GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SHOW VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT SHOW VIEW ON `mysqltest_db1`.`t1` TO `mysqltest_u1`@`localhost` connect con1,localhost,mysqltest_u1,,mysqltest_db1; connection con1; @@ -2595,7 +2595,7 @@ disconnect con1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` # -- # -- Cleanup. @@ -2625,7 +2625,7 @@ grant select on mysqltest_db1.t1 to mysqltest_u1; ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist show grants for mysqltest_u1; Grants for mysqltest_u1@% -GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' +GRANT USAGE ON *.* TO `mysqltest_u1`@`%` drop database mysqltest_db1; drop user mysqltest_u1; # @@ -2668,28 +2668,28 @@ connect con1,localhost,foo; create procedure fooproc() select 'i am fooproc'; show grants; Grants for foo@localhost -GRANT USAGE ON *.* TO 'foo'@'localhost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO `foo`@`localhost` disconnect con1; connection default; rename table mysql.procs_priv to mysql.procs_priv1; flush privileges; show grants for foo@localhost; Grants for foo@localhost -GRANT USAGE ON *.* TO 'foo'@'localhost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` rename table mysql.procs_priv1 to mysql.procs_priv; show grants for foo@localhost; Grants for foo@localhost -GRANT USAGE ON *.* TO 'foo'@'localhost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` flush privileges; show grants for foo@localhost; Grants for foo@localhost -GRANT USAGE ON *.* TO 'foo'@'localhost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO `foo`@`localhost` drop user foo@localhost; drop procedure fooproc; drop database foodb; @@ -2704,7 +2704,7 @@ GRANT USAGE ON *.* TO untrusted@localhost; connect con1, localhost, untrusted; SHOW GRANTS; Grants for untrusted@localhost -GRANT USAGE ON *.* TO 'untrusted'@'localhost' +GRANT USAGE ON *.* TO `untrusted`@`localhost` SHOW DATABASES; Database information_schema @@ -2761,11 +2761,11 @@ CREATE USER ten2; GRANT ALL ON *.* TO ten2; SHOW GRANTS FOR ten2; Grants for ten2@% -GRANT ALL PRIVILEGES ON *.* TO 'ten2'@'%' +GRANT ALL PRIVILEGES ON *.* TO `ten2`@`%` FLUSH PRIVILEGES; SHOW GRANTS FOR ten2; Grants for ten2@% -GRANT ALL PRIVILEGES ON *.* TO 'ten2'@'%' +GRANT ALL PRIVILEGES ON *.* TO `ten2`@`%` DROP USER ten2; # # End of 10.3 tests diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result index 5d168a04455..d238d261006 100644 --- a/mysql-test/main/grant2.result +++ b/mysql-test/main/grant2.result @@ -60,12 +60,12 @@ disconnect user1; connection default; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT CREATE USER ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `my\_%`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION show grants for mysqltest_2@localhost; Grants for mysqltest_2@localhost -GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost' -GRANT ALL PRIVILEGES ON `my\_1`.* TO 'mysqltest_2'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_2`@`localhost` +GRANT ALL PRIVILEGES ON `my\_1`.* TO `mysqltest_2`@`localhost` WITH GRANT OPTION show grants for mysqltest_3@localhost; ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost' delete from mysql.user where user like 'mysqltest\_%'; @@ -89,8 +89,8 @@ disconnect user2; connection default; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; drop database mysqltest_1; @@ -104,8 +104,8 @@ connect mrbad, localhost, mysqltest_1,,mysqltest; connection mrbad; show grants for current_user(); Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, INSERT ON `mysqltest`.* TO `mysqltest_1`@`localhost` insert into t1 values (1, 'I can''t change it!'); update t1 set data='I can change it!' where id = 1; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' @@ -199,13 +199,13 @@ host db user table_name column_name % test mysqltest_2 t2 c2 show grants for 'mysqltest_1'; Grants for mysqltest_1@% -GRANT USAGE ON *.* TO 'mysqltest_1'@'%' +GRANT USAGE ON *.* TO "mysqltest_1"@"%" show grants for 'mysqltest_2'; Grants for mysqltest_2@% -GRANT SELECT ON *.* TO 'mysqltest_2'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' -GRANT INSERT ON "test".* TO 'mysqltest_2'@'%' -GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_2'@'%' -GRANT UPDATE ON "test"."t1" TO 'mysqltest_2'@'%' +GRANT SELECT ON *.* TO "mysqltest_2"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO "mysqltest_2"@"%" +GRANT UPDATE (c2) ON "test"."t2" TO "mysqltest_2"@"%" +GRANT UPDATE ON "test"."t1" TO "mysqltest_2"@"%" drop user 'mysqltest_1'; select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%'; Host User Password plugin authentication_string @@ -240,10 +240,10 @@ host db user table_name column_name % test mysqltest_1 t2 c2 show grants for 'mysqltest_1'; Grants for mysqltest_1@% -GRANT SELECT ON *.* TO 'mysqltest_1'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' -GRANT INSERT ON "test".* TO 'mysqltest_1'@'%' -GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%' -GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%' +GRANT SELECT ON *.* TO "mysqltest_1"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO "mysqltest_1"@"%" +GRANT UPDATE (c2) ON "test"."t2" TO "mysqltest_1"@"%" +GRANT UPDATE ON "test"."t1" TO "mysqltest_1"@"%" drop user 'mysqltest_1', 'mysqltest_3'; drop user 'mysqltest_1'; ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%' @@ -293,19 +293,19 @@ drop user 'mysqltest_2'; create user '%@b'@'b'; show grants for '%@b'@'b'; Grants for %@b@b -GRANT USAGE ON *.* TO '%@b'@'b' +GRANT USAGE ON *.* TO "%@b"@"b" grant select on mysql.* to '%@b'@'b'; show grants for '%@b'@'b'; Grants for %@b@b -GRANT USAGE ON *.* TO '%@b'@'b' -GRANT SELECT ON "mysql".* TO '%@b'@'b' +GRANT USAGE ON *.* TO "%@b"@"b" +GRANT SELECT ON "mysql".* TO "%@b"@"b" rename user '%@b'@'b' to '%@a'@'a'; show grants for '%@b'@'b'; ERROR 42000: There is no such grant defined for user '%@b' on host 'b' show grants for '%@a'@'a'; Grants for %@a@a -GRANT USAGE ON *.* TO '%@a'@'a' -GRANT SELECT ON "mysql".* TO '%@a'@'a' +GRANT USAGE ON *.* TO "%@a"@"a" +GRANT SELECT ON "mysql".* TO "%@a"@"a" drop user '%@a'@'a'; create user mysqltest_2@localhost; grant create user on *.* to mysqltest_2@localhost; @@ -325,8 +325,8 @@ connect user4,localhost,mysqltest_3,,; connection user4; show grants; Grants for mysqltest_3@localhost -GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' -GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_3`@`localhost` +GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `mysqltest_3`@`localhost` select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' insert into mysql.global_priv set host='%', user='mysqltest_B'; @@ -346,8 +346,8 @@ connect n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK; connection n1; show grants for current_user(); Grants for mysqltest_1@127.0.0.0/255.0.0.0 -GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' -GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +GRANT USAGE ON *.* TO `mysqltest_1`@`127.0.0.0/255.0.0.0` +GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO `mysqltest_1`@`127.0.0.0/255.0.0.0` select * from t1; i 1 diff --git a/mysql-test/main/grant3.result b/mysql-test/main/grant3.result index 1bc6e7572c5..63e343aaf4d 100644 --- a/mysql-test/main/grant3.result +++ b/mysql-test/main/grant3.result @@ -143,9 +143,9 @@ connect conn1,localhost,mysqltest1,,; connection conn1; SHOW GRANTS; Grants for mysqltest1@% -GRANT USAGE ON *.* TO 'mysqltest1'@'%' -GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%' -GRANT UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%' +GRANT USAGE ON *.* TO `mysqltest1`@`%` +GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO `mysqltest1`@`%` +GRANT UPDATE ON `mysqltest_1`.* TO `mysqltest1`@`%` SELECT * FROM mysqltest_1.t1; a disconnect conn1; @@ -166,20 +166,20 @@ RENAME USER 'user1'@'%' TO 'user2'@'%'; # Show privileges after rename and BEFORE grant SHOW GRANTS FOR 'user2'@'%'; Grants for user2@% -GRANT USAGE ON *.* TO 'user2'@'%' +GRANT USAGE ON *.* TO `user2`@`%` GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%'; # Show privileges after rename and grant SHOW GRANTS FOR 'user2'@'%'; Grants for user2@% -GRANT USAGE ON *.* TO 'user2'@'%' -GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%' +GRANT USAGE ON *.* TO `user2`@`%` +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO `user2`@`%` # Connect as the renamed user connect conn1, localhost, user2,,; connection conn1; SHOW GRANTS; Grants for user2@% -GRANT USAGE ON *.* TO 'user2'@'%' -GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%' +GRANT USAGE ON *.* TO `user2`@`%` +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO `user2`@`%` SELECT a FROM temp.t1; a 1 diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result index 2f16c5d630a..43b58b82231 100644 --- a/mysql-test/main/grant5.result +++ b/mysql-test/main/grant5.result @@ -8,11 +8,11 @@ connect conn_1, localhost, test,,; set role foo; show grants for test; Grants for test@% -GRANT foo TO 'test'@'%' -GRANT USAGE ON *.* TO 'test'@'%' +GRANT `foo` TO `test`@`%` +GRANT USAGE ON *.* TO `test`@`%` show grants for foo; Grants for foo -GRANT USAGE ON *.* TO 'foo' +GRANT USAGE ON *.* TO `foo` show grants for foo@'%'; ERROR 42000: Access denied for user 'test'@'%' to database 'mysql' connection default; @@ -25,6 +25,101 @@ ERROR HY000: Table 'procs_priv' was not locked with LOCK TABLES REVOKE PROCESS ON *.* FROM u; ERROR HY000: Table 'db' was not locked with LOCK TABLES DROP TABLE t1; +# +# MDEV-20076: SHOW GRANTS does not quote role names properly +# +create role 'role1'; +create role 'fetch'; +create role 'role-1'; +create role 'rock\'n\'roll'; +create user 'user1'@'localhost'; +create user 'fetch'@'localhost'; +create user 'user-1'@'localhost'; +create user 'O\'Brien'@'localhost'; +grant select on mysql.user to role1; +grant select on mysql.user to 'fetch'; +grant select on mysql.user to 'role-1'; +grant select on mysql.user to 'rock\'n\'roll'; +GRANT 'role1' TO 'user1'@'localhost'; +GRANT 'fetch' TO 'fetch'@'localhost'; +GRANT 'role-1' TO 'user-1'@'localhost'; +GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost'; +show grants for 'role1'; +Grants for role1 +GRANT USAGE ON *.* TO `role1` +GRANT SELECT ON `mysql`.`user` TO `role1` +show grants for 'fetch'; +Grants for fetch +GRANT USAGE ON *.* TO `fetch` +GRANT SELECT ON `mysql`.`user` TO `fetch` +show grants for 'role-1'; +Grants for role-1 +GRANT USAGE ON *.* TO `role-1` +GRANT SELECT ON `mysql`.`user` TO `role-1` +show grants for 'rock\'n\'roll'; +Grants for rock'n'roll +GRANT USAGE ON *.* TO `rock'n'roll` +GRANT SELECT ON `mysql`.`user` TO `rock'n'roll` +show grants for 'user1'@'localhost'; +Grants for user1@localhost +GRANT `role1` TO `user1`@`localhost` +GRANT USAGE ON *.* TO `user1`@`localhost` +show grants for 'fetch'@'localhost'; +Grants for fetch@localhost +GRANT `fetch` TO `fetch`@`localhost` +GRANT USAGE ON *.* TO `fetch`@`localhost` +show grants for 'user-1'@'localhost'; +Grants for user-1@localhost +GRANT `role-1` TO `user-1`@`localhost` +GRANT USAGE ON *.* TO `user-1`@`localhost` +show grants for 'O\'Brien'@'localhost'; +Grants for O'Brien@localhost +GRANT `rock'n'roll` TO `O'Brien`@`localhost` +GRANT USAGE ON *.* TO `O'Brien`@`localhost` +set @save_sql_quote_show_create= @@sql_quote_show_create; +set @@sql_quote_show_create= OFF; +show grants for 'role1'; +Grants for role1 +GRANT USAGE ON *.* TO role1 +GRANT SELECT ON `mysql`.`user` TO role1 +show grants for 'fetch'; +Grants for fetch +GRANT USAGE ON *.* TO `fetch` +GRANT SELECT ON `mysql`.`user` TO `fetch` +show grants for 'role-1'; +Grants for role-1 +GRANT USAGE ON *.* TO `role-1` +GRANT SELECT ON `mysql`.`user` TO `role-1` +show grants for 'rock\'n\'roll'; +Grants for rock'n'roll +GRANT USAGE ON *.* TO `rock'n'roll` +GRANT SELECT ON `mysql`.`user` TO `rock'n'roll` +show grants for 'user1'@'localhost'; +Grants for user1@localhost +GRANT role1 TO user1@localhost +GRANT USAGE ON *.* TO user1@localhost +show grants for 'fetch'@'localhost'; +Grants for fetch@localhost +GRANT `fetch` TO `fetch`@localhost +GRANT USAGE ON *.* TO `fetch`@localhost +show grants for 'user-1'@'localhost'; +Grants for user-1@localhost +GRANT `role-1` TO `user-1`@localhost +GRANT USAGE ON *.* TO `user-1`@localhost +show grants for 'O\'Brien'@'localhost'; +Grants for O'Brien@localhost +GRANT `rock'n'roll` TO `O'Brien`@localhost +GRANT USAGE ON *.* TO `O'Brien`@localhost +set @@sql_quote_show_create= @save_sql_quote_show_create; +drop role 'role1'; +drop role 'fetch'; +drop role 'role-1'; +drop role 'rock\'n\'roll'; +drop user 'user1'@'localhost'; +drop user 'fetch'@'localhost'; +drop user 'user-1'@'localhost'; +drop user 'O\'Brien'@'localhost'; +# End of 10.3 tests create user u1@h identified with 'mysql_native_password' using 'pwd'; ERROR HY000: Password hash should be a 41-digit hexadecimal number create user u1@h identified with 'mysql_native_password' using password('pwd'); @@ -61,28 +156,28 @@ update mysql.global_priv set priv=json_set(priv, '$.plugin', 'nonexistent') wher flush privileges; show create user u1@h; CREATE USER for u1@h -CREATE USER 'u1'@'h' IDENTIFIED BY PASSWORD 'bad' +CREATE USER `u1`@`h` IDENTIFIED BY PASSWORD 'bad' show create user u2@h; CREATE USER for u2@h -CREATE USER 'u2'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' +CREATE USER `u2`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u3@h; CREATE USER for u3@h -CREATE USER 'u3'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' +CREATE USER `u3`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u4@h; CREATE USER for u4@h -CREATE USER 'u4'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' +CREATE USER `u4`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' show create user u5@h; CREATE USER for u5@h -CREATE USER 'u5'@'h' IDENTIFIED BY PASSWORD 'bad' +CREATE USER `u5`@`h` IDENTIFIED BY PASSWORD 'bad' show create user u6@h; CREATE USER for u6@h -CREATE USER 'u6'@'h' IDENTIFIED BY PASSWORD '78a302dd267f6044' +CREATE USER `u6`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044' show create user u7@h; CREATE USER for u7@h -CREATE USER 'u7'@'h' IDENTIFIED BY PASSWORD '78a302dd267f6044' +CREATE USER `u7`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044' show create user u8@h; CREATE USER for u8@h -CREATE USER 'u8'@'h' IDENTIFIED VIA nonexistent USING '78a302dd267f6044' +CREATE USER `u8`@`h` IDENTIFIED VIA nonexistent USING '78a302dd267f6044' grant select on *.* to u1@h; grant select on *.* to u2@h; grant select on *.* to u3@h; @@ -130,3 +225,4 @@ drop user twg@'%'; insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select'); flush privileges; delete from mysql.tables_priv where db=''; +# End of 10.4 tests diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test index 43f5b9c180f..307549ec9d8 100644 --- a/mysql-test/main/grant5.test +++ b/mysql-test/main/grant5.test @@ -34,6 +34,56 @@ REVOKE EXECUTE ON PROCEDURE sp FROM u; REVOKE PROCESS ON *.* FROM u; DROP TABLE t1; +--echo # +--echo # MDEV-20076: SHOW GRANTS does not quote role names properly +--echo # + +create role 'role1'; +create role 'fetch'; +create role 'role-1'; +create role 'rock\'n\'roll'; +create user 'user1'@'localhost'; +create user 'fetch'@'localhost'; +create user 'user-1'@'localhost'; +create user 'O\'Brien'@'localhost'; +grant select on mysql.user to role1; +grant select on mysql.user to 'fetch'; +grant select on mysql.user to 'role-1'; +grant select on mysql.user to 'rock\'n\'roll'; +GRANT 'role1' TO 'user1'@'localhost'; +GRANT 'fetch' TO 'fetch'@'localhost'; +GRANT 'role-1' TO 'user-1'@'localhost'; +GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost'; +show grants for 'role1'; +show grants for 'fetch'; +show grants for 'role-1'; +show grants for 'rock\'n\'roll'; +show grants for 'user1'@'localhost'; +show grants for 'fetch'@'localhost'; +show grants for 'user-1'@'localhost'; +show grants for 'O\'Brien'@'localhost'; +set @save_sql_quote_show_create= @@sql_quote_show_create; +set @@sql_quote_show_create= OFF; +show grants for 'role1'; +show grants for 'fetch'; +show grants for 'role-1'; +show grants for 'rock\'n\'roll'; +show grants for 'user1'@'localhost'; +show grants for 'fetch'@'localhost'; +show grants for 'user-1'@'localhost'; +show grants for 'O\'Brien'@'localhost'; +set @@sql_quote_show_create= @save_sql_quote_show_create; +drop role 'role1'; +drop role 'fetch'; +drop role 'role-1'; +drop role 'rock\'n\'roll'; +drop user 'user1'@'localhost'; +drop user 'fetch'@'localhost'; +drop user 'user-1'@'localhost'; +drop user 'O\'Brien'@'localhost'; + +--echo # End of 10.3 tests + # # MDEV-12321 authentication plugin: SET PASSWORD support # @@ -131,3 +181,5 @@ drop user twg@'%'; insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select'); flush privileges; delete from mysql.tables_priv where db=''; + +--echo # End of 10.4 tests diff --git a/mysql-test/main/grant_cache_no_prot.result b/mysql-test/main/grant_cache_no_prot.result index 04eb2469732..780fb254b76 100644 --- a/mysql-test/main/grant_cache_no_prot.result +++ b/mysql-test/main/grant_cache_no_prot.result @@ -14,11 +14,11 @@ connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; connection root; show grants for current_user; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION show grants; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION create database if not exists mysqltest; create table mysqltest.t1 (a int,b int,c int); @@ -65,8 +65,8 @@ connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; connection user1; show grants for current_user(); Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 6 @@ -131,7 +131,7 @@ connect unkuser,localhost,unkuser,,,$MASTER_MYPORT,$MASTER_MYSOCK; connection unkuser; show grants for current_user(); Grants for @localhost -GRANT USAGE ON *.* TO ''@'localhost' +GRANT USAGE ON *.* TO ``@`localhost` connect user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; connection user2; select "user2"; @@ -197,8 +197,8 @@ user4 user4 show grants; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` select a from t1; ERROR 3D000: No database selected select * from mysqltest.t1,test.t1; diff --git a/mysql-test/main/grant_cache_ps_prot.result b/mysql-test/main/grant_cache_ps_prot.result index 3bc51430fd0..9dcf8c4aa8f 100644 --- a/mysql-test/main/grant_cache_ps_prot.result +++ b/mysql-test/main/grant_cache_ps_prot.result @@ -14,11 +14,11 @@ connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; connection root; show grants for current_user; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION show grants; Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION create database if not exists mysqltest; create table mysqltest.t1 (a int,b int,c int); @@ -65,8 +65,8 @@ connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; connection user1; show grants for current_user(); Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 6 @@ -131,7 +131,7 @@ connect unkuser,localhost,unkuser,,,$MASTER_MYPORT,$MASTER_MYSOCK; connection unkuser; show grants for current_user(); Grants for @localhost -GRANT USAGE ON *.* TO ''@'localhost' +GRANT USAGE ON *.* TO ``@`localhost` connect user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; connection user2; select "user2"; @@ -197,8 +197,8 @@ user4 user4 show grants; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost` select a from t1; ERROR 3D000: No database selected select * from mysqltest.t1,test.t1; diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index b4d2d065d4a..e69a24e5422 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -1052,8 +1052,8 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' def USAGE NO show grants; Grants for user1@localhost -GRANT USAGE ON *.* TO 'user1'@'localhost' -GRANT SELECT (f1) ON `mysqltest`.`t1` TO 'user1'@'localhost' +GRANT USAGE ON *.* TO `user1`@`localhost` +GRANT SELECT (f1) ON `mysqltest`.`t1` TO `user1`@`localhost` connection con2; select * from information_schema.column_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE @@ -1067,8 +1067,8 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' def USAGE NO show grants; Grants for user2@localhost -GRANT USAGE ON *.* TO 'user2'@'localhost' -GRANT SELECT ON `mysqltest`.`t2` TO 'user2'@'localhost' +GRANT USAGE ON *.* TO `user2`@`localhost` +GRANT SELECT ON `mysqltest`.`t2` TO `user2`@`localhost` connection con3; select * from information_schema.column_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE @@ -1082,8 +1082,8 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' def USAGE NO show grants; Grants for user3@localhost -GRANT USAGE ON *.* TO 'user3'@'localhost' -GRANT SELECT ON `mysqltest`.* TO 'user3'@'localhost' +GRANT USAGE ON *.* TO `user3`@`localhost` +GRANT SELECT ON `mysqltest`.* TO `user3`@`localhost` connection con4; select * from information_schema.column_privileges where grantee like '\'user%' order by grantee; @@ -1106,7 +1106,7 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user4'@'localhost' def SELECT NO show grants; Grants for user4@localhost -GRANT SELECT ON *.* TO 'user4'@'localhost' +GRANT SELECT ON *.* TO `user4`@`localhost` connection default; disconnect con1; disconnect con2; diff --git a/mysql-test/main/invisible_field_grant_completely.result b/mysql-test/main/invisible_field_grant_completely.result index 208a9f24ddf..aa0a768c51c 100644 --- a/mysql-test/main/invisible_field_grant_completely.result +++ b/mysql-test/main/invisible_field_grant_completely.result @@ -2,7 +2,7 @@ set @old_debug= @@debug_dbug; create user user_1; show grants for user_1; Grants for user_1@% -GRANT USAGE ON *.* TO 'user_1'@'%' +GRANT USAGE ON *.* TO `user_1`@`%` # create user create database d; use d; diff --git a/mysql-test/main/invisible_field_grant_system.result b/mysql-test/main/invisible_field_grant_system.result index 77acfdae744..b69478c58b3 100644 --- a/mysql-test/main/invisible_field_grant_system.result +++ b/mysql-test/main/invisible_field_grant_system.result @@ -1,7 +1,7 @@ create user user_1; show grants for user_1; Grants for user_1@% -GRANT USAGE ON *.* TO 'user_1'@'%' +GRANT USAGE ON *.* TO `user_1`@`%` # create user create database d; use d; diff --git a/mysql-test/main/ipv4_and_ipv6.result b/mysql-test/main/ipv4_and_ipv6.result index 447c20536e2..9ae55c56f65 100644 --- a/mysql-test/main/ipv4_and_ipv6.result +++ b/mysql-test/main/ipv4_and_ipv6.result @@ -4,8 +4,8 @@ CREATE USER testuser@'::1' identified by '1234'; GRANT ALL ON test.* TO testuser@'::1'; SHOW GRANTS FOR testuser@'::1'; Grants for testuser@::1 -GRANT USAGE ON *.* TO 'testuser'@'::1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'::1' +GRANT USAGE ON *.* TO `testuser`@`::1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`::1` SET @nip= inet_aton('::1'); SELECT @nip; @nip @@ -36,8 +36,8 @@ CREATE USER testuser@'127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'127.0.0.1'; SHOW GRANTS FOR testuser@'127.0.0.1'; Grants for testuser@127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`127.0.0.1` SET @nip= inet_aton('127.0.0.1'); SELECT @nip; @nip diff --git a/mysql-test/main/ipv4_as_ipv6.result b/mysql-test/main/ipv4_as_ipv6.result index b92b545da86..3b523454b3b 100644 --- a/mysql-test/main/ipv4_as_ipv6.result +++ b/mysql-test/main/ipv4_as_ipv6.result @@ -4,8 +4,8 @@ CREATE USER testuser@'127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'127.0.0.1'; SHOW GRANTS FOR testuser@'127.0.0.1'; Grants for testuser@127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`127.0.0.1` SET @nip= inet_aton('127.0.0.1'); SELECT @nip; @nip @@ -36,8 +36,8 @@ CREATE USER testuser@'0:0:0:0:0:FFFF:127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'0:0:0:0:0:FFFF:127.0.0.1'; SHOW GRANTS FOR testuser@'0:0:0:0:0:FFFF:127.0.0.1'; Grants for testuser@0:0:0:0:0:ffff:127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'0:0:0:0:0:ffff:127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0:0:0:0:0:ffff:127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`0:0:0:0:0:ffff:127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0:0:0:0:0:ffff:127.0.0.1` SET @nip= inet_aton('0:0:0:0:0:FFFF:127.0.0.1'); SELECT @nip; @nip @@ -68,8 +68,8 @@ CREATE USER testuser@'0000:0000:0000:0000:0000:FFFF:127.0.0.1' identified by '12 GRANT ALL ON test.* TO testuser@'0000:0000:0000:0000:0000:FFFF:127.0.0.1'; SHOW GRANTS FOR testuser@'0000:0000:0000:0000:0000:FFFF:127.0.0.1'; Grants for testuser@0000:0000:0000:0000:0000:ffff:127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'0000:0000:0000:0000:0000:ffff:127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0000:0000:0000:0000:0000:ffff:127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`0000:0000:0000:0000:0000:ffff:127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0000:0000:0000:0000:0000:ffff:127.0.0.1` SET @nip= inet_aton('0000:0000:0000:0000:0000:FFFF:127.0.0.1'); SELECT @nip; @nip @@ -100,8 +100,8 @@ CREATE USER testuser@'0:0000:0000:0:0000:FFFF:127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'0:0000:0000:0:0000:FFFF:127.0.0.1'; SHOW GRANTS FOR testuser@'0:0000:0000:0:0000:FFFF:127.0.0.1'; Grants for testuser@0:0000:0000:0:0000:ffff:127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'0:0000:0000:0:0000:ffff:127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0:0000:0000:0:0000:ffff:127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`0:0000:0000:0:0000:ffff:127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0:0000:0000:0:0000:ffff:127.0.0.1` SET @nip= inet_aton('0:0000:0000:0:0000:FFFF:127.0.0.1'); SELECT @nip; @nip @@ -132,8 +132,8 @@ CREATE USER testuser@'0::0000:FFFF:127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'0::0000:FFFF:127.0.0.1'; SHOW GRANTS FOR testuser@'0::0000:FFFF:127.0.0.1'; Grants for testuser@0::0000:ffff:127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'0::0000:ffff:127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0::0000:ffff:127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`0::0000:ffff:127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0::0000:ffff:127.0.0.1` SET @nip= inet_aton('0::0000:FFFF:127.0.0.1'); SELECT @nip; @nip @@ -165,8 +165,8 @@ CREATE USER testuser@'::FFFF:127.0.0.1' identified by '1234'; GRANT ALL ON test.* TO testuser@'::FFFF:127.0.0.1'; SHOW GRANTS FOR testuser@'::FFFF:127.0.0.1'; Grants for testuser@::ffff:127.0.0.1 -GRANT USAGE ON *.* TO 'testuser'@'::ffff:127.0.0.1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'::ffff:127.0.0.1' +GRANT USAGE ON *.* TO `testuser`@`::ffff:127.0.0.1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`::ffff:127.0.0.1` SET @nip= inet_aton('::FFFF:127.0.0.1'); SELECT @nip; @nip diff --git a/mysql-test/main/ipv6.result b/mysql-test/main/ipv6.result index 1540b4825e5..31a4857659c 100644 --- a/mysql-test/main/ipv6.result +++ b/mysql-test/main/ipv6.result @@ -4,8 +4,8 @@ CREATE USER testuser@'::1' identified by '1234'; GRANT ALL ON test.* TO testuser@'::1'; SHOW GRANTS FOR testuser@'::1'; Grants for testuser@::1 -GRANT USAGE ON *.* TO 'testuser'@'::1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'::1' +GRANT USAGE ON *.* TO `testuser`@`::1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`::1` SET @nip= inet_aton('::1'); SELECT @nip; @nip @@ -37,8 +37,8 @@ CREATE USER testuser@'0000:0000:0000:0000:0000:0000:0000:0001' identified by '12 GRANT ALL ON test.* TO testuser@'0000:0000:0000:0000:0000:0000:0000:0001'; SHOW GRANTS FOR testuser@'0000:0000:0000:0000:0000:0000:0000:0001'; Grants for testuser@0000:0000:0000:0000:0000:0000:0000:0001 -GRANT USAGE ON *.* TO 'testuser'@'0000:0000:0000:0000:0000:0000:0000:0001' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0000:0000:0000:0000:0000:0000:0000:0001' +GRANT USAGE ON *.* TO `testuser`@`0000:0000:0000:0000:0000:0000:0000:0001` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0000:0000:0000:0000:0000:0000:0000:0001` SET @nip= inet_aton('0000:0000:0000:0000:0000:0000:0000:0001'); SELECT @nip; @nip @@ -69,8 +69,8 @@ CREATE USER testuser@'0:0:0:0:0:0:0:1' identified by '1234'; GRANT ALL ON test.* TO testuser@'0:0:0:0:0:0:0:1'; SHOW GRANTS FOR testuser@'0:0:0:0:0:0:0:1'; Grants for testuser@0:0:0:0:0:0:0:1 -GRANT USAGE ON *.* TO 'testuser'@'0:0:0:0:0:0:0:1' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' -GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'0:0:0:0:0:0:0:1' +GRANT USAGE ON *.* TO `testuser`@`0:0:0:0:0:0:0:1` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' +GRANT ALL PRIVILEGES ON `test`.* TO `testuser`@`0:0:0:0:0:0:0:1` SET @nip= inet_aton('0:0:0:0:0:0:0:1'); SELECT @nip; @nip diff --git a/mysql-test/main/locale.opt b/mysql-test/main/locale.opt new file mode 100644 index 00000000000..0ea17c2e0a8 --- /dev/null +++ b/mysql-test/main/locale.opt @@ -0,0 +1 @@ +--lc-messages=de_DE diff --git a/mysql-test/main/locale.result b/mysql-test/main/locale.result index b750d038205..f136e9e99ab 100644 --- a/mysql-test/main/locale.result +++ b/mysql-test/main/locale.result @@ -51,7 +51,7 @@ DROP TABLE t1; # SET lc_messages=sr_YU; Warnings: -Warning 1287 'sr_YU' is deprecated and will be removed in a future release. Please use sr_RS instead +Warning 1287 'sr_YU' ist veraltet. Bitte benutzen Sie 'sr_RS' SHOW VARIABLES LIKE 'lc_messages'; Variable_name Value lc_messages sr_RS diff --git a/mysql-test/main/lock_user.result b/mysql-test/main/lock_user.result index a8740e8ad37..bc32ee986b3 100644 --- a/mysql-test/main/lock_user.result +++ b/mysql-test/main/lock_user.result @@ -41,12 +41,12 @@ Note 1396 Operation ALTER USER failed for 'inexistentUser'@'localhost' # show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` create user user1@localhost account lock; ERROR HY000: Operation CREATE USER failed for 'user1'@'localhost' show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` # # Passing multiple users should lock them all # @@ -83,19 +83,19 @@ localhost user1 0 # show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` alter user user1@localhost account lock; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' ACCOUNT LOCK +CREATE USER `user1`@`localhost` ACCOUNT LOCK alter user user1@localhost account unlock; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` create user newuser@localhost account lock; show create user newuser@localhost; CREATE USER for newuser@localhost -CREATE USER 'newuser'@'localhost' ACCOUNT LOCK +CREATE USER `newuser`@`localhost` ACCOUNT LOCK drop user newuser@localhost; # # Users should be able to lock themselves @@ -121,7 +121,7 @@ connection con1; alter user user1@localhost account unlock; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` disconnect con1; connection default; # diff --git a/mysql-test/main/lowercase_table_grant.result b/mysql-test/main/lowercase_table_grant.result index 9ba542ec22d..45b6ab0410f 100644 --- a/mysql-test/main/lowercase_table_grant.result +++ b/mysql-test/main/lowercase_table_grant.result @@ -4,8 +4,8 @@ create database MYSQLtest; grant all on MySQLtest.* to mysqltest_1@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_1`@`localhost` select * from db where user = 'mysqltest_1'; Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y Y @@ -13,8 +13,8 @@ update db set db = 'MYSQLtest' where db = 'mysqltest' and user = 'mysqltest_1' a flush privileges; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.* TO `mysqltest_1`@`localhost` select * from db where user = 'mysqltest_1'; Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y Y diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index bd70c71e041..f71ce087ba1 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -313,8 +313,8 @@ Phase 7/7: Running 'FLUSH PRIVILEGES' OK SHOW GRANTS FOR 'user3'@'%'; Grants for user3@% -GRANT USAGE ON *.* TO 'user3'@'%' -GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%' +GRANT USAGE ON *.* TO `user3`@`%` +GRANT ALL PRIVILEGES ON `roelt`.`test2` TO `user3`@`%` DROP USER 'user3'@'%'; # End of 5.1 tests The --upgrade-system-tables option was used, user tables won't be touched. @@ -586,8 +586,8 @@ Run mysql_upgrade with all privileges on a user flush privileges; SHOW GRANTS FOR 'user3'@'%'; Grants for user3@% -GRANT USAGE ON *.* TO 'user3'@'%' -GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%' +GRANT USAGE ON *.* TO `user3`@`%` +GRANT ALL PRIVILEGES ON `roelt`.`test2` TO `user3`@`%` DROP USER 'user3'@'%'; update mysql.db set Delete_history_priv='Y' where db like 'test%'; drop table mysql.global_priv; @@ -597,7 +597,7 @@ drop view mysql.user_bak; create user 'user3'@'localhost' identified with mysql_native_password as password('a_password'); show create user user3@localhost; CREATE USER for user3@localhost -CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' +CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' update mysql.user set password=authentication_string, authentication_string='' where user='user3'; select password,plugin,authentication_string from mysql.user where user='user3'; password plugin authentication_string @@ -605,7 +605,7 @@ password plugin authentication_string flush privileges; show create user user3@localhost; CREATE USER for user3@localhost -CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' PASSWORD EXPIRE NEVER +CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' PASSWORD EXPIRE NEVER connect con1,localhost,user3,a_password; select current_user(); current_user() @@ -615,7 +615,7 @@ connection default; # mysql_upgrade --force --silent 2>&1 show create user user3@localhost; CREATE USER for user3@localhost -CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' +CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' connect con1,localhost,user3,a_password; select current_user(); current_user() diff --git a/mysql-test/main/mysqlbinlog.result b/mysql-test/main/mysqlbinlog.result index c4d95dc6ff7..decd095bbdf 100644 --- a/mysql-test/main/mysqlbinlog.result +++ b/mysql-test/main/mysqlbinlog.result @@ -654,8 +654,8 @@ CREATE USER untrusted@localhost; GRANT SELECT ON mysqltest1.* TO untrusted@localhost; SHOW GRANTS FOR untrusted@localhost; Grants for untrusted@localhost -GRANT USAGE ON *.* TO 'untrusted'@'localhost' -GRANT SELECT ON `mysqltest1`.* TO 'untrusted'@'localhost' +GRANT USAGE ON *.* TO `untrusted`@`localhost` +GRANT SELECT ON `mysqltest1`.* TO `untrusted`@`localhost` USE mysqltest1; CREATE TABLE t1 (a INT, b CHAR(64)); flush logs; diff --git a/mysql-test/main/mysqld--help,win.rdiff b/mysql-test/main/mysqld--help,win.rdiff index 6aba3acab28..77f6f76858f 100644 --- a/mysql-test/main/mysqld--help,win.rdiff +++ b/mysql-test/main/mysqld--help,win.rdiff @@ -1,6 +1,6 @@ ---- mysqld--help.result 2018-08-12 00:10:13.694793500 +0100 -+++ mysqld--help,win.reject 2018-08-16 20:31:08.353317200 +0100 -@@ -386,7 +386,6 @@ +--- a/mysql-test/r/mysqld--help.result ++++ b/mysql-test/r/mysqld--help.result +@@ -419,7 +419,6 @@ The number of segments in a key cache -L, --language=name Client error messages in given language. May be given as a full path. Deprecated. Use --lc-messages-dir instead. @@ -8,7 +8,7 @@ --lc-messages=name Set the language used for the error messages. -L, --lc-messages-dir=name Directory where error messages are -@@ -608,6 +607,7 @@ +@@ -647,6 +646,7 @@ Use MySQL-5.6 (instead of MariaDB-5.3) format for TIME, DATETIME, TIMESTAMP columns. (Defaults to on; use --skip-mysql56-temporal-format to disable.) @@ -16,7 +16,7 @@ --net-buffer-length=# Buffer length for TCP/IP and socket communication --net-read-timeout=# -@@ -1188,6 +1188,10 @@ +@@ -1236,6 +1236,10 @@ Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other slow log options @@ -27,15 +27,15 @@ --socket=name Socket file to use for connection --sort-buffer-size=# Each thread that needs to do a sort allocates a buffer of -@@ -1207,6 +1211,7 @@ - EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT +@@ -1260,6 +1264,7 @@ + deleting or updating every row in a table. --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) + --standalone Dummy option to start as a standalone program (NT). --standard-compliant-cte Allow only CTEs compliant to SQL standard (Defaults to on; use --skip-standard-compliant-cte to disable.) -@@ -1277,6 +1282,11 @@ +@@ -1330,6 +1335,11 @@ --thread-pool-max-threads=# Maximum allowed number of worker threads in the thread pool @@ -47,7 +47,7 @@ --thread-pool-oversubscribe=# How many additional active worker threads in a group are allowed. -@@ -1315,8 +1325,8 @@ +@@ -1370,8 +1380,8 @@ automatically convert it to an on-disk MyISAM or Aria table. -t, --tmpdir=name Path for temporary files. Several paths may be specified, @@ -58,7 +58,7 @@ --transaction-alloc-block-size=# Allocation block size for transactions to be stored in binary log -@@ -1451,7 +1461,6 @@ +@@ -1513,7 +1523,6 @@ key-cache-division-limit 100 key-cache-file-hash-size 512 key-cache-segments 0 @@ -66,7 +66,7 @@ lc-messages en_US lc-messages-dir MYSQL_SHAREDIR/ lc-time-names en_US -@@ -1523,6 +1532,7 @@ +@@ -1587,6 +1596,7 @@ myisam-stats-method NULLS_UNEQUAL myisam-use-mmap FALSE mysql56-temporal-format TRUE @@ -74,15 +74,15 @@ net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 -@@ -1660,6 +1670,7 @@ +@@ -1726,6 +1736,7 @@ slave-type-conversions slow-launch-time 2 slow-query-log FALSE +slow-start-timeout 15000 sort-buffer-size 2097152 sql-mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION - stack-trace TRUE -@@ -1674,9 +1685,9 @@ + sql-safe-updates FALSE +@@ -1741,10 +1752,10 @@ sync-relay-log-info 10000 sysdate-is-now FALSE system-versioning-alter-history ERROR @@ -90,11 +90,13 @@ +table-cache 2000 table-definition-cache 400 -table-open-cache 421 +-table-open-cache-instances 1 +table-open-cache 2000 - table-open-cache-instances 8 ++table-open-cache-instances 8 tc-heuristic-recover OFF tcp-keepalive-interval 0 -@@ -1686,6 +1697,8 @@ + tcp-keepalive-probes 0 +@@ -1753,6 +1764,8 @@ thread-cache-size 151 thread-pool-idle-timeout 60 thread-pool-max-threads 65536 diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index f9dfa604d23..f184b1b60ff 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1744,7 +1744,7 @@ system-versioning-alter-history ERROR table-cache 421 table-definition-cache 400 table-open-cache 421 -table-open-cache-instances 8 +table-open-cache-instances 1 tc-heuristic-recover OFF tcp-keepalive-interval 0 tcp-keepalive-probes 0 diff --git a/mysql-test/main/partition_grant.result b/mysql-test/main/partition_grant.result index cd8f8a970d0..41934896aeb 100644 --- a/mysql-test/main/partition_grant.result +++ b/mysql-test/main/partition_grant.result @@ -10,8 +10,8 @@ grant select,alter on mysqltest_1.* to mysqltest_1@localhost; connect conn1,localhost,mysqltest_1,,mysqltest_1; show grants for current_user; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, ALTER ON `mysqltest_1`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT SELECT, ALTER ON `mysqltest_1`.* TO `mysqltest_1`@`localhost` alter table t1 add b int; alter table t1 drop partition p2; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 2cb9e3f4ab8..261a05e7023 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -1015,3 +1015,47 @@ select * from t1 partition (p1); d 2000-01-01 00:00:01.000000 DROP TABLE t1, t2; +# +# MDEV-21195 INSERT chooses wrong partition for RANGE partitioning by DECIMAL column +# +create or replace table t ( +d decimal(2,1)) partition by range (d) +(partition p1 values less than (10)); +insert into t values (9.9); +create or replace table t ( +d decimal(2,1)) partition by range (d) +(partition p1 values less than (10), +partition p2 values less than (20)); +insert into t values (9.9); +select * from t partition (p1); +d +9.9 +select * from t partition (p2); +d +create or replace table t ( +d decimal(2,1)) partition by range (d) +(partition p1 values less than (-3)); +insert into t values (-3.3); +create or replace table t ( +d decimal(2,1)) partition by range (d+1) +(partition p1 values less than (10), +partition p2 values less than (20)); +insert into t values (8.9); +select * from t partition (p1); +d +8.9 +select * from t partition (p2); +d +set time_zone='+00:00'; +create or replace table t ( +d timestamp(1)) partition by range (unix_timestamp(d)) +(partition p1 values less than (1577836800), +partition p2 values less than (2000000000)); +insert into t values (from_unixtime(1577836799.9)); +select * from t partition (p1); +d +2019-12-31 23:59:59.9 +select * from t partition (p2); +d +set time_zone=default; +drop table t; diff --git a/mysql-test/main/partition_range.test b/mysql-test/main/partition_range.test index 37702db052b..52497764241 100644 --- a/mysql-test/main/partition_range.test +++ b/mysql-test/main/partition_range.test @@ -1001,3 +1001,49 @@ select * from t1 partition (p0); select * from t1 partition (p1); DROP TABLE t1, t2; + +--echo # +--echo # MDEV-21195 INSERT chooses wrong partition for RANGE partitioning by DECIMAL column +--echo # +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (10)); + +insert into t values (9.9); + +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (10), + partition p2 values less than (20)); + +insert into t values (9.9); +select * from t partition (p1); +select * from t partition (p2); + +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (-3)); + +insert into t values (-3.3); + +create or replace table t ( + d decimal(2,1)) partition by range (d+1) + (partition p1 values less than (10), + partition p2 values less than (20)); + +insert into t values (8.9); +select * from t partition (p1); +select * from t partition (p2); + +set time_zone='+00:00'; +create or replace table t ( + d timestamp(1)) partition by range (unix_timestamp(d)) + (partition p1 values less than (1577836800), + partition p2 values less than (2000000000)); + +insert into t values (from_unixtime(1577836799.9)); +select * from t partition (p1); +select * from t partition (p2); + +set time_zone=default; +drop table t; diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result index 73c436aecd2..bb39d8788dc 100644 --- a/mysql-test/main/password_expiration.result +++ b/mysql-test/main/password_expiration.result @@ -103,32 +103,32 @@ drop user user1@localhost; create user user1@localhost; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE set password for user1@localhost= password(''); alter user user1@localhost password expire default; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` alter user user1@localhost password expire never; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost password expire interval 123 day; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE set password for user1@localhost= password(''); show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY drop user user1@localhost; # # Incorrect INTERVAL values should be rejected @@ -141,52 +141,52 @@ ERROR HY000: Incorrect DAY value: '0' create user user1@localhost; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE set password for user1@localhost= password(''); alter user user1@localhost password expire default; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost password expire never; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost password expire interval 123 day; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE flush privileges; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +CREATE USER `user1`@`localhost` PASSWORD EXPIRE set global disconnect_on_expired_password=ON; connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK); connect con1,localhost,user1; diff --git a/mysql-test/main/plugin_auth.result b/mysql-test/main/plugin_auth.result index 8472891cd44..244e048c517 100644 --- a/mysql-test/main/plugin_auth.result +++ b/mysql-test/main/plugin_auth.result @@ -178,18 +178,18 @@ connection default; disconnect proxy_admin_con; SHOW GRANTS FOR grant_plug; Grants for grant_plug@% -GRANT ALL PRIVILEGES ON *.* TO 'grant_plug'@'%' IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' +GRANT ALL PRIVILEGES ON *.* TO `grant_plug`@`%` IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' GRANT PROXY ON 'future_user'@'%' TO 'grant_plug'@'%' REVOKE PROXY ON future_user FROM grant_plug; SHOW GRANTS FOR grant_plug; Grants for grant_plug@% -GRANT ALL PRIVILEGES ON *.* TO 'grant_plug'@'%' IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' +GRANT ALL PRIVILEGES ON *.* TO `grant_plug`@`%` IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' ## testing drop user CREATE USER test_drop@localhost; GRANT PROXY ON future_user TO test_drop@localhost; SHOW GRANTS FOR test_drop@localhost; Grants for test_drop@localhost -GRANT USAGE ON *.* TO 'test_drop'@'localhost' +GRANT USAGE ON *.* TO `test_drop`@`localhost` GRANT PROXY ON 'future_user'@'%' TO 'test_drop'@'localhost' DROP USER test_drop@localhost; SELECT * FROM mysql.proxies_priv WHERE Host = 'test_drop' AND User = 'localhost'; diff --git a/mysql-test/main/ps_grant.result b/mysql-test/main/ps_grant.result index 0e296cdc699..ab21c198b98 100644 --- a/mysql-test/main/ps_grant.result +++ b/mysql-test/main/ps_grant.result @@ -11,8 +11,8 @@ grant select on mysqltest.t9 to second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` connect con3,localhost,second_user,looser,mysqltest; connection con3; select current_user(); @@ -20,8 +20,8 @@ current_user() second_user@localhost show grants for current_user(); Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` prepare s_t9 from 'select c1 as my_col from t9 where c1= 1' ; execute s_t9 ; @@ -34,21 +34,21 @@ grant select on mysqltest.t1 to second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` +GRANT SELECT ON `mysqltest`.`t1` TO `second_user`@`localhost` drop table mysqltest.t9 ; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` +GRANT SELECT ON `mysqltest`.`t1` TO `second_user`@`localhost` connection con3; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` +GRANT SELECT ON `mysqltest`.`t1` TO `second_user`@`localhost` prepare s_t1 from 'select a as my_col from t1' ; execute s_t1 ; my_col @@ -63,13 +63,13 @@ connection default; revoke all privileges on mysqltest.t1 from second_user@localhost; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` connection con3; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT SELECT ON `mysqltest`.`t9` TO `second_user`@`localhost` execute s_t1 ; ERROR 42000: SELECT command denied to user 'second_user'@'localhost' for table 't1' connection default; @@ -77,7 +77,7 @@ disconnect con3; revoke all privileges, grant option from second_user@localhost ; show grants for second_user@localhost ; Grants for second_user@localhost -GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' +GRANT USAGE ON *.* TO `second_user`@`localhost` IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' drop user second_user@localhost ; commit ; show grants for second_user@localhost ; diff --git a/mysql-test/main/set_password.result b/mysql-test/main/set_password.result index bb1124e09d4..eef7c6eccea 100644 --- a/mysql-test/main/set_password.result +++ b/mysql-test/main/set_password.result @@ -168,7 +168,7 @@ user() current_user() foo@localhost foo@localhost show grants; Grants for foo@localhost -GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +GRANT USAGE ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' disconnect foo; connection default; select user,host,password,plugin,authentication_string from mysql.user where user='foo'; diff --git a/mysql-test/main/show_create_user.result b/mysql-test/main/show_create_user.result index 1205b658b6e..e7d68041a1d 100644 --- a/mysql-test/main/show_create_user.result +++ b/mysql-test/main/show_create_user.result @@ -1,27 +1,27 @@ create user foo; show create user foo; CREATE USER for foo@% -CREATE USER 'foo'@'%' +CREATE USER `foo`@`%` create user foo@test; show create user foo@test; CREATE USER for foo@test -CREATE USER 'foo'@'test' +CREATE USER `foo`@`test` create user foo2@test identified by 'password'; show create user foo2@test; CREATE USER for foo2@test -CREATE USER 'foo2'@'test' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' +CREATE USER `foo2`@`test` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' alter user foo2@test identified with 'mysql_old_password' as '0123456789ABCDEF'; show create user foo2@test; CREATE USER for foo2@test -CREATE USER 'foo2'@'test' IDENTIFIED BY PASSWORD '0123456789ABCDEF' +CREATE USER `foo2`@`test` IDENTIFIED BY PASSWORD '0123456789ABCDEF' create user foo3@test require SSL; show create user foo3@test; CREATE USER for foo3@test -CREATE USER 'foo3'@'test' REQUIRE SSL +CREATE USER `foo3`@`test` REQUIRE SSL create user foo4@test require cipher 'text' issuer 'foo_issuer' subject 'foo_subject'; show create user foo4@test; CREATE USER for foo4@test -CREATE USER 'foo4'@'test' REQUIRE ISSUER 'foo_issuer' SUBJECT 'foo_subject' CIPHER 'text' +CREATE USER `foo4`@`test` REQUIRE ISSUER 'foo_issuer' SUBJECT 'foo_subject' CIPHER 'text' create user foo5@test require SSL with MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 @@ -30,7 +30,7 @@ MAX_USER_CONNECTIONS 40 MAX_STATEMENT_TIME 0.5; show create user foo5@test; CREATE USER for foo5@test -CREATE USER 'foo5'@'test' REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 MAX_USER_CONNECTIONS 40 MAX_STATEMENT_TIME 0.500000 +CREATE USER `foo5`@`test` REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 MAX_USER_CONNECTIONS 40 MAX_STATEMENT_TIME 0.500000 drop user foo5@test; drop user foo4@test; drop user foo3@test; diff --git a/mysql-test/main/skip_grants.result b/mysql-test/main/skip_grants.result index 5ecb783ac43..154f77fff76 100644 --- a/mysql-test/main/skip_grants.result +++ b/mysql-test/main/skip_grants.result @@ -94,21 +94,21 @@ ERROR HY000: The MariaDB server is running with the --skip-grant-tables option s flush privileges; show create user root@localhost; CREATE USER for root@localhost -CREATE USER 'root'@'localhost' +CREATE USER `root`@`localhost` show create user bar@foo; CREATE USER for bar@foo -CREATE USER 'bar'@'foo' +CREATE USER `bar`@`foo` show create user baz@baz; CREATE USER for baz@baz -CREATE USER 'baz'@'baz' IDENTIFIED VIA baz +CREATE USER `baz`@`baz` IDENTIFIED VIA baz set password for bar@foo = password("pass word"); show create user bar@foo; CREATE USER for bar@foo -CREATE USER 'bar'@'foo' IDENTIFIED BY PASSWORD '*EDBBEA7F4E7B5D8B0BC8D7AC5D1936FB7DA10611' +CREATE USER `bar`@`foo` IDENTIFIED BY PASSWORD '*EDBBEA7F4E7B5D8B0BC8D7AC5D1936FB7DA10611' alter user baz@baz identified with mysql_native_password as password("baz"); show create user baz@baz; CREATE USER for baz@baz -CREATE USER 'baz'@'baz' IDENTIFIED BY PASSWORD '*E52096EF8EB0240275A7FE9E069101C33F98CF07' +CREATE USER `baz`@`baz` IDENTIFIED BY PASSWORD '*E52096EF8EB0240275A7FE9E069101C33F98CF07' drop user bar@foo; drop user baz@baz; # restart diff --git a/mysql-test/main/skip_name_resolve.result b/mysql-test/main/skip_name_resolve.result index 1362e482717..9a903ebf472 100644 --- a/mysql-test/main/skip_name_resolve.result +++ b/mysql-test/main/skip_name_resolve.result @@ -2,8 +2,8 @@ CREATE USER mysqltest_1@'127.0.0.1/255.255.255.255'; GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255'; SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255'; Grants for mysqltest_1@127.0.0.1/255.255.255.255 -GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' -GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' +GRANT USAGE ON *.* TO `mysqltest_1`@`127.0.0.1/255.255.255.255` +GRANT ALL PRIVILEGES ON `test`.* TO `mysqltest_1`@`127.0.0.1/255.255.255.255` REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; connect con1, localhost, root, , test, $MASTER_MYPORT, ; diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result index 217fae0538e..5050955c806 100644 --- a/mysql-test/main/sp-security.result +++ b/mysql-test/main/sp-security.result @@ -195,14 +195,14 @@ grant insert on t1 to usera@localhost; grant execute on procedure sptest.p1 to usera@localhost; show grants for usera@localhost; Grants for usera@localhost -GRANT USAGE ON *.* TO 'usera'@'localhost' -GRANT INSERT ON `test`.`t1` TO 'usera'@'localhost' -GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'usera'@'localhost' +GRANT USAGE ON *.* TO `usera`@`localhost` +GRANT INSERT ON `test`.`t1` TO `usera`@`localhost` +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `usera`@`localhost` grant execute on procedure sptest.p1 to userc@localhost with grant option; show grants for userc@localhost; Grants for userc@localhost -GRANT USAGE ON *.* TO 'userc'@'localhost' -GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `userc`@`localhost` +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION connect con2usera,localhost,usera,,; connect con3userb,localhost,userb,,; connect con4userc,localhost,userc,,; @@ -239,18 +239,18 @@ userb@localhost 4 grant all privileges on procedure sptest.p1 to userc@localhost; show grants for userc@localhost; Grants for userc@localhost -GRANT USAGE ON *.* TO 'userc'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `userc`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION show grants for userb@localhost; Grants for userb@localhost -GRANT USAGE ON *.* TO 'userb'@'localhost' -GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userb'@'localhost' +GRANT USAGE ON *.* TO `userb`@`localhost` +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userb`@`localhost` connection con4userc; revoke all privileges on procedure sptest.p1 from userb@localhost; connection con1root; show grants for userb@localhost; Grants for userb@localhost -GRANT USAGE ON *.* TO 'userb'@'localhost' +GRANT USAGE ON *.* TO `userb`@`localhost` disconnect con4userc; disconnect con3userb; disconnect con2usera; @@ -721,14 +721,14 @@ user() current_user() foo@localhost foo@local_ost show grants; Grants for foo@local_ost -GRANT USAGE ON *.* TO 'foo'@'local_ost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +GRANT USAGE ON *.* TO `foo`@`local_ost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost` create procedure fooproc() select 'i am fooproc'; show grants; Grants for foo@local_ost -GRANT USAGE ON *.* TO 'foo'@'local_ost' -GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'local_ost' +GRANT USAGE ON *.* TO `foo`@`local_ost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO `foo`@`local_ost` disconnect con1; connection default; drop user foo@local_ost; @@ -773,12 +773,12 @@ set password=password('foobar'); create procedure sp1() select 1; show grants; Grants for u1@localhost -GRANT ALL PRIVILEGES ON *.* TO 'u1'@'localhost' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION grant execute on procedure sp1 to current_user() identified by 'barfoo'; show grants; Grants for u1@localhost -GRANT ALL PRIVILEGES ON *.* TO 'u1'@'localhost' IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION -GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO 'u1'@'localhost' +GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION +GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO `u1`@`localhost` drop procedure sp1; disconnect u1; connection default; diff --git a/mysql-test/main/sp_notembedded.result b/mysql-test/main/sp_notembedded.result index 518dfc6bfdb..3cf066f5b4c 100644 --- a/mysql-test/main/sp_notembedded.result +++ b/mysql-test/main/sp_notembedded.result @@ -8,11 +8,11 @@ show grants for 'root'@'localhost'; end| call bug4902()| Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION call bug4902()| Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION drop procedure bug4902| drop procedure if exists bug4902_2| @@ -159,11 +159,11 @@ create procedure 15298_2 () sql security definer show grants; connect con1,localhost,mysqltest_1,,test; call 15298_1(); Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION call 15298_2(); Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION connection default; disconnect con1; @@ -328,21 +328,21 @@ update mysql.user set authentication_string = replace(authentication_string, '*' connect foo,localhost,foo1,foo; show grants; Grants for foo1@localhost -GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' -GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost' +GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost` connection default; flush privileges; connection foo; show grants; Grants for foo1@localhost -GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' -GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost' +GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost` create procedure spfoo() select 1; show grants; Grants for foo1@localhost -GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' -GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO 'foo1'@'localhost' +GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO `foo1`@`localhost` connection default; disconnect foo; drop procedure spfoo; diff --git a/mysql-test/main/system_mysql_db_507.result b/mysql-test/main/system_mysql_db_507.result index 767b8fee102..00b53a5798d 100644 --- a/mysql-test/main/system_mysql_db_507.result +++ b/mysql-test/main/system_mysql_db_507.result @@ -79,13 +79,13 @@ create user ioo identified with "mysql_old_password" as "7a8f886d28473e85"; # show grants for foo; Grants for foo@% -GRANT USAGE ON *.* TO 'foo'@'%' +GRANT USAGE ON *.* TO `foo`@`%` show grants for goo; Grants for goo@% -GRANT USAGE ON *.* TO 'goo'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT USAGE ON *.* TO `goo`@`%` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' show grants for ioo; Grants for ioo@% -GRANT USAGE ON *.* TO 'ioo'@'%' IDENTIFIED BY PASSWORD '7a8f886d28473e85' +GRANT USAGE ON *.* TO `ioo`@`%` IDENTIFIED BY PASSWORD '7a8f886d28473e85' select user, host, select_priv, plugin, authentication_string from mysql.user where user like "%oo" order by user; @@ -99,13 +99,13 @@ ioo % N mysql_old_password 7a8f886d28473e85 SET PASSWORD FOR foo=PASSWORD("bar"); show grants for foo; Grants for foo@% -GRANT USAGE ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +GRANT USAGE ON *.* TO `foo`@`%` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' show grants for goo; Grants for goo@% -GRANT USAGE ON *.* TO 'goo'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT USAGE ON *.* TO `goo`@`%` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' show grants for ioo; Grants for ioo@% -GRANT USAGE ON *.* TO 'ioo'@'%' IDENTIFIED BY PASSWORD '7a8f886d28473e85' +GRANT USAGE ON *.* TO `ioo`@`%` IDENTIFIED BY PASSWORD '7a8f886d28473e85' select user, host, select_priv, plugin, authentication_string from mysql.user where user like "%oo" order by user; @@ -119,13 +119,13 @@ ioo % N mysql_old_password 7a8f886d28473e85 flush privileges; show grants for foo; Grants for foo@% -GRANT USAGE ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +GRANT USAGE ON *.* TO `foo`@`%` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' show grants for goo; Grants for goo@% -GRANT USAGE ON *.* TO 'goo'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT USAGE ON *.* TO `goo`@`%` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' show grants for ioo; Grants for ioo@% -GRANT USAGE ON *.* TO 'ioo'@'%' IDENTIFIED BY PASSWORD '7a8f886d28473e85' +GRANT USAGE ON *.* TO `ioo`@`%` IDENTIFIED BY PASSWORD '7a8f886d28473e85' # # Test granting of privileges. # @@ -134,26 +134,26 @@ grant select on *.* to goo; grant select on *.* to ioo; show grants for foo; Grants for foo@% -GRANT SELECT ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +GRANT SELECT ON *.* TO `foo`@`%` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' show grants for goo; Grants for goo@% -GRANT SELECT ON *.* TO 'goo'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT SELECT ON *.* TO `goo`@`%` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' show grants for ioo; Grants for ioo@% -GRANT SELECT ON *.* TO 'ioo'@'%' IDENTIFIED BY PASSWORD '7a8f886d28473e85' +GRANT SELECT ON *.* TO `ioo`@`%` IDENTIFIED BY PASSWORD '7a8f886d28473e85' # # Check to see if grants are stable on flush. # flush privileges; show grants for foo; Grants for foo@% -GRANT SELECT ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +GRANT SELECT ON *.* TO `foo`@`%` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' show grants for goo; Grants for goo@% -GRANT SELECT ON *.* TO 'goo'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' +GRANT SELECT ON *.* TO `goo`@`%` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' show grants for ioo; Grants for ioo@% -GRANT SELECT ON *.* TO 'ioo'@'%' IDENTIFIED BY PASSWORD '7a8f886d28473e85' +GRANT SELECT ON *.* TO `ioo`@`%` IDENTIFIED BY PASSWORD '7a8f886d28473e85' # # Check internal table representation. # @@ -177,46 +177,46 @@ connect con1,localhost,user1; ERROR HY000: Access denied, this account is locked show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' ACCOUNT LOCK +CREATE USER `user1`@`localhost` ACCOUNT LOCK alter user user1@localhost account unlock; connect con1,localhost,user1; disconnect con1; connection default; show create user user1@localhost; CREATE USER for user1@localhost -CREATE USER 'user1'@'localhost' +CREATE USER `user1`@`localhost` # # Test password expiration fields are loaded correctly # create user user@localhost; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' +CREATE USER `user`@`localhost` alter user user@localhost password expire; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' PASSWORD EXPIRE +CREATE USER `user`@`localhost` PASSWORD EXPIRE set password for user@localhost= password(''); alter user user@localhost password expire default; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' +CREATE USER `user`@`localhost` alter user user@localhost password expire never; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' PASSWORD EXPIRE NEVER +CREATE USER `user`@`localhost` PASSWORD EXPIRE NEVER alter user user@localhost password expire interval 123 day; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +CREATE USER `user`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY alter user user@localhost password expire; show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' PASSWORD EXPIRE +CREATE USER `user`@`localhost` PASSWORD EXPIRE set password for user@localhost= password(''); show create user user@localhost; CREATE USER for user@localhost -CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +CREATE USER `user`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY drop user user@localhost; # # Reset to final original state. diff --git a/mysql-test/main/timezone_grant.result b/mysql-test/main/timezone_grant.result index 42ce458d00f..bacc8348e89 100644 --- a/mysql-test/main/timezone_grant.result +++ b/mysql-test/main/timezone_grant.result @@ -13,8 +13,8 @@ connect tzuser, localhost, mysqltest_1,,; connection tzuser; show grants for current_user(); Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `test`.* TO `mysqltest_1`@`localhost` set time_zone= '+00:00'; set time_zone= 'Europe/Moscow'; select convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC'); @@ -39,9 +39,9 @@ connect tzuser2, localhost, mysqltest_1,,; connection tzuser2; show grants for current_user(); Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `test`.`t2` TO 'mysqltest_1'@'localhost' -GRANT ALL PRIVILEGES ON `test`.`t1` TO 'mysqltest_1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `test`.`t2` TO `mysqltest_1`@`localhost` +GRANT ALL PRIVILEGES ON `test`.`t1` TO `mysqltest_1`@`localhost` set time_zone= '+00:00'; set time_zone= 'Europe/Moscow'; select convert_tz('2004-11-31 12:00:00', 'Europe/Moscow', 'UTC'); diff --git a/mysql-test/main/trigger_notembedded.result b/mysql-test/main/trigger_notembedded.result index 3ee84c3c308..94e651b1340 100644 --- a/mysql-test/main/trigger_notembedded.result +++ b/mysql-test/main/trigger_notembedded.result @@ -194,8 +194,8 @@ use mysqltest_db1; GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' +GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` +GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` connection bug15166_u1_con; use mysqltest_db1; CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1 diff --git a/mysql-test/main/user_limits.result b/mysql-test/main/user_limits.result index bbf73d7c617..ffb8bb204a6 100644 --- a/mysql-test/main/user_limits.result +++ b/mysql-test/main/user_limits.result @@ -108,11 +108,11 @@ connection default; grant usage on *.* to mysqltest_1@localhost with max_user_connections -1; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1 +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` WITH MAX_USER_CONNECTIONS -1 flush user_resources; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1 +GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` WITH MAX_USER_CONNECTIONS -1 connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK); connect muc5, localhost, mysqltest_1,,; ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: -1) diff --git a/mysql-test/main/view_grant.result b/mysql-test/main/view_grant.result index 2fa898d9060..8af27de141f 100644 --- a/mysql-test/main/view_grant.result +++ b/mysql-test/main/view_grant.result @@ -4,12 +4,12 @@ create user test@localhost; grant create view on test.* to test@localhost; show grants for test@localhost; Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' -GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' +GRANT USAGE ON *.* TO `test`@`localhost` +GRANT CREATE VIEW ON `test`.* TO `test`@`localhost` revoke create view on test.* from test@localhost; show grants for test@localhost; Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' +GRANT USAGE ON *.* TO `test`@`localhost` drop user test@localhost; connect root,localhost,root,,test; connection root; |