MDEV-5215 post-review fixes

* "public" should work in any letter case
* PUBLIC is not a valid definer
* granting to public should auto-create an entry in mysql.global_priv
* SHOW GRANTS should show privileges obtained via PUBLIC
* LEX_USER::is_public was often uninitialized
* comments, whitespaces, typos, etc

7 files changed, 175 insertions, 227 deletions

diff --git a/mysql-test/main/public_basic.result b/mysql-test/main/public_basic.result
index 5832180d2cf..9506fdfff00 100644
--- a/mysql-test/main/public_basic.result
+++ b/mysql-test/main/public_basic.result
@@ -1,3 +1,6 @@
+#
+# MDEV-5215 Granted to PUBLIC
+#
 SHOW GRANTS FOR PUBLIC;
 Grants for PUBLIC
 # it is not PUBLIC but an user
@@ -7,39 +10,6 @@ create user PUBLIC@localhost;
 GRANT SELECT on test.* to PUBLIC@localhost;
 drop user PUBLIC@localhost;
 drop user PUBLIC;
-# preinstalled PUBLIC
-GRANT SELECT on test.* to PUBLIC;
-GRANT SELECT on mysql.db to PUBLIC;
-select * from mysql.global_priv where user="PUBLIC" ;
-Host	User	Priv
-	PUBLIC	{"access":0,"version_id":VERSION,"is_role":true}
-SHOW GRANTS FOR PUBLIC;
-Grants for PUBLIC
-GRANT SELECT ON `test`.* TO `PUBLIC`
-GRANT SELECT ON `mysql`.`db` TO `PUBLIC`
-GRANT UPDATE on test.* to PUBLIC;
-GRANT UPDATE on mysql.db to PUBLIC;
-SHOW GRANTS FOR PUBLIC;
-Grants for PUBLIC
-GRANT SELECT, UPDATE ON `test`.* TO `PUBLIC`
-GRANT SELECT, UPDATE ON `mysql`.`db` TO `PUBLIC`
-REVOKE SELECT on test.* from PUBLIC;
-REVOKE SELECT on mysql.db from PUBLIC;
-SHOW GRANTS FOR PUBLIC;
-Grants for PUBLIC
-GRANT UPDATE ON `test`.* TO `PUBLIC`
-GRANT UPDATE ON `mysql`.`db` TO `PUBLIC`
-REVOKE UPDATE on test.* from PUBLIC;
-REVOKE UPDATE on mysql.db from PUBLIC;
-REVOKE UPDATE on test.* from PUBLIC;
-ERROR 42000: There is no such grant defined for user 'PUBLIC' on host ''
-REVOKE UPDATE on mysql.db from PUBLIC;
-ERROR 42000: There is no such grant defined for user 'PUBLIC' on host '' on table 'db'
-SHOW GRANTS FOR PUBLIC;
-Grants for PUBLIC
-# automaticly added PUBLIC
-delete from mysql.global_priv where user="PUBLIC";
-flush privileges;
 select * from mysql.global_priv where user="PUBLIC" ;
 Host	User	Priv
 GRANT SELECT on test.* to PUBLIC;
@@ -52,12 +22,12 @@ Grants for PUBLIC
 GRANT SELECT ON `test`.* TO `PUBLIC`
 GRANT SELECT ON `mysql`.`db` TO `PUBLIC`
 GRANT UPDATE on test.* to PUBLIC;
-GRANT UPDATE on mysql.db to PUBLIC;
-SHOW GRANTS FOR PUBLIC;
+grant update on mysql.db to public;
+show grants for public;
 Grants for PUBLIC
 GRANT SELECT, UPDATE ON `test`.* TO `PUBLIC`
 GRANT SELECT, UPDATE ON `mysql`.`db` TO `PUBLIC`
-REVOKE SELECT on test.* from PUBLIC;
+revoke select on test.* from public;
 REVOKE SELECT on mysql.db from PUBLIC;
 SHOW GRANTS FOR PUBLIC;
 Grants for PUBLIC
@@ -72,15 +42,15 @@ ERROR OP000: Invalid role specification `XXXXXX`
 # following should fail with the same error as above
 GRANT PUBLIC TO CURRENT_USER;
 ERROR OP000: Invalid role specification `PUBLIC`
-REVOKE XXXXXX FROM CURRENT_USER;
-ERROR OP000: Invalid role specification `XXXXXX`
+revoke xxxxxx from current_user;
+ERROR OP000: Invalid role specification `xxxxxx`
 # following should fail with the same error as above
-REVOKE PUBLIC FROM CURRENT_USER;
-ERROR OP000: Invalid role specification `PUBLIC`
+revoke public from current_user;
+ERROR OP000: Invalid role specification `public`
 drop role XXXXXX;
 ERROR HY000: Operation DROP ROLE failed for 'XXXXXX'
 # following should fail with the same error as above
-drop role PUBLIC;
+drop role public;
 ERROR HY000: Operation DROP ROLE failed for PUBLIC
 SET ROLE XXXXXX;
 ERROR OP000: Invalid role specification `XXXXXX`
@@ -92,6 +62,8 @@ ERROR OP000: Invalid role specification `XXXXXX`
 # following should fail with the same error as above
 SET DEFAULT ROLE PUBLIC;
 ERROR OP000: Invalid role specification `PUBLIC`
+set default role public;
+ERROR OP000: Invalid role specification `public`
 #
 # check prohibition of change security context to PUBLIC
 #
@@ -100,39 +72,34 @@ GRANT SELECT on test.* to PUBLIC;
 # try with a view
 create table t1( a int);
 create definer = PUBLIC view v1 as select * from t1;
-Warnings:
-Note	1449	The user specified as a definer ('PUBLIC'@'') does not exist
-show create view v1;
-View	Create View	character_set_client	collation_connection
-v1	CREATE ALGORITHM=UNDEFINED DEFINER=`PUBLIC` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
-Warnings:
-Note	1449	The user specified as a definer ('PUBLIC'@'') does not exist
-select * from v1;
-ERROR HY000: The user specified as a definer ('PUBLIC'@'') does not exist
-drop view v1;
+ERROR OP000: Invalid role specification `PUBLIC`
 drop table t1;
-# try with a view
+# try with a stored procedure
 create definer='PUBLIC' PROCEDURE p1() SELECT 1;
-Warnings:
-Note	1449	The user specified as a definer ('PUBLIC'@'') does not exist
-show create procedure p1;
-Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`PUBLIC` PROCEDURE `p1`()
-SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
-call p1();
-ERROR HY000: The user specified as a definer ('PUBLIC'@'') does not exist
-drop procedure p1;
+ERROR OP000: Invalid role specification `PUBLIC`
 # this test cleanup
-REVOKE SELECT on test.* from PUBLIC;
+revoke select on test.* from public;
 #
-# check autocreation of PUBLIC on GRAND role TO PUBLIC
+# check autocreation of PUBLIC on GRANT role TO PUBLIC
 #
 # make sure that the privilege will be added automatically
 delete from mysql.global_priv where user="PUBLIC";
 flush privileges;
 create role roletest;
-GRANT roletest TO PUBLIC;
+grant roletest to public;
 drop role roletest;
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant select on mysql.global_priv to public;
+revoke select on mysql.global_priv from public;
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant select (user) on mysql.global_priv to public;
+revoke select (user) on mysql.global_priv from public;
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant execute on procedure mtr.add_suppression to public;
+revoke execute on procedure mtr.add_suppression from public;
 # clean up
 delete from mysql.global_priv where user="PUBLIC";
 flush privileges;
diff --git a/mysql-test/main/public_basic.test b/mysql-test/main/public_basic.test
index ddb4e040fce..99952c54e94 100644
--- a/mysql-test/main/public_basic.test
+++ b/mysql-test/main/public_basic.test
@@ -1,3 +1,8 @@
+--source include/not_embedded.inc
+--echo #
+--echo # MDEV-5215 Granted to PUBLIC
+--echo #
+
 SHOW GRANTS FOR PUBLIC;
 
 --echo # it is not PUBLIC but an user
@@ -8,37 +13,6 @@ GRANT SELECT on test.* to PUBLIC@localhost;
 drop user PUBLIC@localhost;
 drop user PUBLIC;
 
---echo # preinstalled PUBLIC
-GRANT SELECT on test.* to PUBLIC;
-GRANT SELECT on mysql.db to PUBLIC;
---replace_regex /"version_id"\:[0-9]+/"version_id":VERSION/
-select * from mysql.global_priv where user="PUBLIC" ;
-
-SHOW GRANTS FOR PUBLIC;
-
-GRANT UPDATE on test.* to PUBLIC;
-GRANT UPDATE on mysql.db to PUBLIC;
-
-SHOW GRANTS FOR PUBLIC;
-
-REVOKE SELECT on test.* from PUBLIC;
-REVOKE SELECT on mysql.db from PUBLIC;
-
-SHOW GRANTS FOR PUBLIC;
-
-REVOKE UPDATE on test.* from PUBLIC;
-REVOKE UPDATE on mysql.db from PUBLIC;
-
---error ER_NONEXISTING_GRANT
-REVOKE UPDATE on test.* from PUBLIC;
---error ER_NONEXISTING_TABLE_GRANT
-REVOKE UPDATE on mysql.db from PUBLIC;
-
-SHOW GRANTS FOR PUBLIC;
-
---echo # automaticly added PUBLIC
-delete from mysql.global_priv where user="PUBLIC";
-flush privileges;
 select * from mysql.global_priv where user="PUBLIC" ;
 GRANT SELECT on test.* to PUBLIC;
 GRANT SELECT on mysql.db to PUBLIC;
@@ -48,11 +22,11 @@ select * from mysql.global_priv where user="PUBLIC" ;
 SHOW GRANTS FOR PUBLIC;
 
 GRANT UPDATE on test.* to PUBLIC;
-GRANT UPDATE on mysql.db to PUBLIC;
+grant update on mysql.db to public;
 
-SHOW GRANTS FOR PUBLIC;
+show grants for public;
 
-REVOKE SELECT on test.* from PUBLIC;
+revoke select on test.* from public;
 REVOKE SELECT on mysql.db from PUBLIC;
 
 SHOW GRANTS FOR PUBLIC;
@@ -69,16 +43,16 @@ GRANT XXXXXX TO CURRENT_USER;
 GRANT PUBLIC TO CURRENT_USER;
 
 --error ER_INVALID_ROLE
-REVOKE XXXXXX FROM CURRENT_USER;
+revoke xxxxxx from current_user;
 --echo # following should fail with the same error as above
 --error ER_INVALID_ROLE
-REVOKE PUBLIC FROM CURRENT_USER;
---error ER_CANNOT_USER
+revoke public from current_user;
 
+--error ER_CANNOT_USER
 drop role XXXXXX;
 --echo # following should fail with the same error as above
 --error ER_CANNOT_USER
-drop role PUBLIC;
+drop role public;
 
 --error ER_INVALID_ROLE
 SET ROLE XXXXXX;
@@ -91,6 +65,8 @@ SET DEFAULT ROLE XXXXXX;
 --echo # following should fail with the same error as above
 --error ER_INVALID_ROLE
 SET DEFAULT ROLE PUBLIC;
+--error ER_INVALID_ROLE
+set default role public;
 
 --echo #
 --echo # check prohibition of change security context to PUBLIC
@@ -99,31 +75,39 @@ SET DEFAULT ROLE PUBLIC;
 GRANT SELECT on test.* to PUBLIC;
 --echo # try with a view
 create table t1( a int);
+--error ER_INVALID_ROLE
 create definer = PUBLIC view v1 as select * from t1;
-show create view v1;
---error ER_NO_SUCH_USER
-select * from v1;
-drop view v1;
 drop table t1;
---echo # try with a view
+--echo # try with a stored procedure
+--error ER_INVALID_ROLE
 create definer='PUBLIC' PROCEDURE p1() SELECT 1;
-show create procedure p1;
---error ER_NO_SUCH_USER
-call p1();
-drop procedure p1;
 --echo # this test cleanup
-REVOKE SELECT on test.* from PUBLIC;
+revoke select on test.* from public;
 
 --echo #
---echo # check autocreation of PUBLIC on GRAND role TO PUBLIC
+--echo # check autocreation of PUBLIC on GRANT role TO PUBLIC
 --echo #
 --echo # make sure that the privilege will be added automatically
 delete from mysql.global_priv where user="PUBLIC";
 flush privileges;
 create role roletest;
-GRANT roletest TO PUBLIC;
+grant roletest to public;
 drop role roletest;
 
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant select on mysql.global_priv to public;
+revoke select on mysql.global_priv from public;
+
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant select (user) on mysql.global_priv to public;
+revoke select (user) on mysql.global_priv from public;
+
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+grant execute on procedure mtr.add_suppression to public;
+revoke execute on procedure mtr.add_suppression from public;
 
 -- echo # clean up
 delete from mysql.global_priv where user="PUBLIC";
diff --git a/mysql-test/main/public_privileges.result b/mysql-test/main/public_privileges.result
index ae50ab78553..5f48297e009 100644
--- a/mysql-test/main/public_privileges.result
+++ b/mysql-test/main/public_privileges.result
@@ -1,4 +1,7 @@
 #
+# MDEV-5215 Granted to PUBLIC
+#
+#
 # Test DB/TABLE/COLUMN privileges in queries
 #
 SHOW GRANTS FOR PUBLIC;
@@ -15,7 +18,6 @@ insert into t2 values (1,2);
 create table t3 (a int, b int);
 insert into t3 values (1,2);
 connect  testuser,localhost,testuser,,;
-connection testuser;
 select * from testdb1.t1;
 ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
 select * from testdb2.t2;
@@ -28,8 +30,6 @@ connection default;
 GRANT SELECT ON testdb1.* to PUBLIC;
 GRANT SELECT ON testdb2.t2 to PUBLIC;
 GRANT SELECT (b) ON testdb2.t3 to PUBLIC;
-disconnect testuser;
-connect  testuser,localhost,testuser,,;
 connection testuser;
 select * from testdb1.t1;
 a	b
@@ -42,12 +42,20 @@ b
 2
 select a from testdb2.t3;
 ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'a' in table 't3'
+show grants;
+Grants for testuser@%
+GRANT USAGE ON *.* TO `testuser`@`%`
+GRANT SELECT ON `testdb1`.* TO `PUBLIC`
+GRANT SELECT ON `testdb2`.`t2` TO `PUBLIC`
+GRANT SELECT (b) ON `testdb2`.`t3` TO `PUBLIC`
+show grants for testuser@'%';
+Grants for testuser@%
+GRANT USAGE ON *.* TO `testuser`@`%`
 connection default;
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 select * from testdb1.t1;
 a	b
 1	2
@@ -76,10 +84,8 @@ create database testdb;
 use testdb;
 create procedure p1 () select 1;
 connect  testuser,localhost,testuser,,;
-connection testuser;
-SHOW PROCESSLIST;
-Id	User	Host	db	Command	Time	State	Info	Progress
-#	testuser	#	NULL	Query	#	#	SHOW PROCESSLIST	0.000
+select user,db from information_schema.processlist where user='root';
+user	db
 call testdb.p1();
 ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.p1'
 connection default;
@@ -87,33 +93,24 @@ GRANT PROCESS ON *.* to PUBLIC;
 GRANT EXECUTE ON testdb.* to PUBLIC;
 disconnect testuser;
 connect  testuser,localhost,testuser,,;
-connection testuser;
-SHOW PROCESSLIST;
-Id	User	Host	db	Command	Time	State	Info	Progress
-#	root	#	testdb	Sleep	#	#	NULL	0.000
-#	testuser	#	NULL	Query	#	#	SHOW PROCESSLIST	0.000
+select user,db from information_schema.processlist where user='root';
+user	db
+root	testdb
 call testdb.p1();
 1
 1
 connection default;
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,;
-connection testuser;
-SHOW PROCESSLIST;
-Id	User	Host	db	Command	Time	State	Info	Progress
-#	root	#	testdb	Sleep	#	#	NULL	0.000
-#	testuser	#	NULL	Query	#	#	SHOW PROCESSLIST	0.000
+select user,db from information_schema.processlist where user='root';
+user	db
+root	testdb
 call testdb.p1();
 1
 1
 connection default;
-SHOW PROCESSLIST;
-Id	User	Host	db	Command	Time	State	Info	Progress
-#	root	#	testdb	Query	#	#	SHOW PROCESSLIST	0.000
-#	testuser	#	NULL	Sleep	#	#	NULL	0.000
-connection default;
 use test;
 disconnect testuser;
 REVOKE PROCESS ON *.* from PUBLIC;
@@ -126,21 +123,17 @@ drop database testdb;
 create user testuser;
 create database testdb;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 use testdb;
 ERROR 42000: Access denied for user 'testuser'@'%' to database 'testdb'
 connection default;
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
-disconnect testuser;
-connect  testuser,localhost,testuser,,;
 connection testuser;
 use testdb;
 connection default;
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 use testdb;
 connection default;
 use test;
@@ -159,14 +152,11 @@ create table t1 (a int);
 insert into t1 values (1);
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 use testdb;
 update t1 set a=a+1;
 ERROR 42000: UPDATE command denied to user 'testuser'@'localhost' for table `testdb`.`t1`
 connection default;
 GRANT UPDATE,SELECT ON testdb.* to PUBLIC;
-disconnect testuser;
-connect  testuser,localhost,testuser,,;
 connection testuser;
 use testdb;
 update t1 set a=a+1;
@@ -193,7 +183,6 @@ create table t2 (a int, b int);
 insert into t2 values (1,2);
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 use testdb;
 delete from t1;
 ERROR 42000: DELETE command denied to user 'testuser'@'localhost' for table `testdb`.`t1`
@@ -204,8 +193,6 @@ ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `tes
 connection default;
 GRANT DELETE ON testdb.t1 to PUBLIC;
 GRANT SELECT (a) ON testdb.t2 to PUBLIC;
-disconnect testuser;
-connect  testuser,localhost,testuser,,;
 connection testuser;
 use testdb;
 delete from t1;
@@ -219,10 +206,9 @@ select * from testdb.t1;
 a
 insert into t1 values (1);
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 use testdb;
 delete from t1;
 select a from t2;
@@ -248,25 +234,21 @@ create database testdb;
 use testdb;
 create function f1() returns int return 2;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 alter function testdb.f1 comment "A stupid function";
 ERROR 42000: alter routine command denied to user 'testuser'@'%' for routine 'testdb.f1'
 select testdb.f1();
 ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.f1'
 connection default;
 GRANT ALTER ROUTINE ON testdb.* to PUBLIC;
-disconnect testuser;
-connect  testuser,localhost,testuser,,;
 connection testuser;
 alter function testdb.f1 comment "A stupid function";
 select testdb.f1();
 ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.f1'
 connection default;
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,;
-connection testuser;
 alter function testdb.f1 comment "A stupid function";
 select testdb.f1();
 ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.f1'
@@ -291,7 +273,6 @@ use testdb1;
 create table t1 (a int, b int);
 insert into t1 values (1,2);
 connect  testuser,localhost,testuser,,;
-connection testuser;
 select * from testdb1.t1;
 ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
 connection default;
@@ -307,33 +288,27 @@ use testdb1;
 create table t1 (a int, b int);
 # check that user do not have rights
 connect  testuser,localhost,testuser,,*NO-ONE*;
-connection testuser;
 select * from testdb1.t1;
 ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
 connection default;
-disconnect testuser;
 give rights to everyone via assigning the role to public
 create role roletest;
 GRANT SELECT ON testdb1.* TO roletest;
 GRANT roletest TO PUBLIC;
-connect  testuser,localhost,testuser,,*NO-ONE*;
 connection testuser;
 select * from testdb1.t1;
 a	b
 connection default;
 disconnect testuser;
-# check that the privilegas correctly read by acl_load
+# check that the privileges are correctly read by acl_load
 flush privileges;
 connect  testuser,localhost,testuser,,*NO-ONE*;
-connection testuser;
 select * from testdb1.t1;
 a	b
 connection default;
-disconnect testuser;
 # drop role...
 drop role roletest;
-# ... and check that user do not have rights again
-connect  testuser,localhost,testuser,,*NO-ONE*;
+# ... and check that user does not have rights again
 connection testuser;
 select * from testdb1.t1;
 ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
diff --git a/mysql-test/main/public_privileges.test b/mysql-test/main/public_privileges.test
index 2de23b5c81a..e6f803f1038 100644
--- a/mysql-test/main/public_privileges.test
+++ b/mysql-test/main/public_privileges.test
@@ -1,3 +1,8 @@
+--source include/not_embedded.inc
+--echo #
+--echo # MDEV-5215 Granted to PUBLIC
+--echo #
+
 --echo #
 --echo # Test DB/TABLE/COLUMN privileges in queries
 --echo #
@@ -17,7 +22,6 @@ create table t3 (a int, b int);
 insert into t3 values (1,2);
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 --error ER_TABLEACCESS_DENIED_ERROR
 select * from testdb1.t1;
 --error ER_TABLEACCESS_DENIED_ERROR
@@ -33,8 +37,6 @@ GRANT SELECT ON testdb1.* to PUBLIC;
 GRANT SELECT ON testdb2.t2 to PUBLIC;
 GRANT SELECT (b) ON testdb2.t3 to PUBLIC;
 
-disconnect testuser;
-connect (testuser,localhost,testuser,,);
 connection testuser;
 select * from testdb1.t1;
 select * from testdb2.t2;
@@ -42,14 +44,16 @@ select b from testdb2.t3;
 --error ER_COLUMNACCESS_DENIED_ERROR
 select a from testdb2.t3;
 
+show grants;
+show grants for testuser@'%';
+
 connection default;
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 select * from testdb1.t1;
 select * from testdb2.t2;
 select b from testdb2.t3;
@@ -76,10 +80,8 @@ use testdb;
 create procedure p1 () select 1;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
---replace_column 1 # 3 # 6 # 7 #
-SHOW PROCESSLIST;
+select user,db from information_schema.processlist where user='root';
 --error ER_PROCACCESS_DENIED_ERROR
 call testdb.p1();
 
@@ -88,34 +90,25 @@ connection default;
 GRANT PROCESS ON *.* to PUBLIC;
 GRANT EXECUTE ON testdb.* to PUBLIC;
 
+# need to reconnect because of PROCESS
 disconnect testuser;
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
---replace_column 1 # 3 # 6 # 7 #
-SHOW PROCESSLIST;
+select user,db from information_schema.processlist where user='root';
 call testdb.p1();
 
 connection default;
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
---replace_column 1 # 3 # 6 # 7 #
-SHOW PROCESSLIST;
+select user,db from information_schema.processlist where user='root';
 call testdb.p1();
 
 connection default;
-
---replace_column 1 # 3 # 6 # 7 #
-SHOW PROCESSLIST;
-
-connection default;
-
 use test;
 disconnect testuser;
 REVOKE PROCESS ON *.* from PUBLIC;
@@ -131,7 +124,6 @@ create user testuser;
 create database testdb;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 --error ER_DBACCESS_DENIED_ERROR
 use testdb;
@@ -140,8 +132,6 @@ connection default;
 
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
 
-disconnect testuser;
-connect (testuser,localhost,testuser,,);
 connection testuser;
 
 use testdb;
@@ -149,11 +139,10 @@ use testdb;
 connection default;
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 use testdb;
 
@@ -179,7 +168,6 @@ insert into t1 values (1);
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 use testdb;
 --error ER_TABLEACCESS_DENIED_ERROR
@@ -189,8 +177,6 @@ connection default;
 
 GRANT UPDATE,SELECT ON testdb.* to PUBLIC;
 
-disconnect testuser;
-connect (testuser,localhost,testuser,,);
 connection testuser;
 
 use testdb;
@@ -222,7 +208,6 @@ insert into t2 values (1,2);
 GRANT LOCK TABLES ON testdb.* to PUBLIC;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 use testdb;
 --error ER_TABLEACCESS_DENIED_ERROR
@@ -237,8 +222,6 @@ connection default;
 GRANT DELETE ON testdb.t1 to PUBLIC;
 GRANT SELECT (a) ON testdb.t2 to PUBLIC;
 
-disconnect testuser;
-connect (testuser,localhost,testuser,,);
 connection testuser;
 
 use testdb;
@@ -252,11 +235,10 @@ select * from testdb.t1;
 insert into t1 values (1);
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 use testdb;
 delete from t1;
@@ -286,7 +268,6 @@ use testdb;
 create function f1() returns int return 2;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 --error ER_PROCACCESS_DENIED_ERROR
 alter function testdb.f1 comment "A stupid function";
@@ -297,8 +278,6 @@ connection default;
 
 GRANT ALTER ROUTINE ON testdb.* to PUBLIC;
 
-disconnect testuser;
-connect (testuser,localhost,testuser,,);
 connection testuser;
 
 alter function testdb.f1 comment "A stupid function";
@@ -308,11 +287,10 @@ select testdb.f1();
 connection default;
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 
 alter function testdb.f1 comment "A stupid function";
 --error ER_PROCACCESS_DENIED_ERROR
@@ -345,7 +323,6 @@ create table t1 (a int, b int);
 insert into t1 values (1,2);
 
 connect (testuser,localhost,testuser,,);
-connection testuser;
 --error ER_TABLEACCESS_DENIED_ERROR
 select * from testdb1.t1;
 
@@ -365,38 +342,32 @@ create table t1 (a int, b int);
 
 --echo # check that user do not have rights
 connect (testuser,localhost,testuser,,*NO-ONE*);
-connection testuser;
 --error ER_TABLEACCESS_DENIED_ERROR
 select * from testdb1.t1;
 connection default;
-disconnect testuser;
 
 --echo give rights to everyone via assigning the role to public
 create role roletest;
 GRANT SELECT ON testdb1.* TO roletest;
 GRANT roletest TO PUBLIC;
 
-connect (testuser,localhost,testuser,,*NO-ONE*);
 connection testuser;
 select * from testdb1.t1;
 connection default;
 disconnect testuser;
 
---echo # check that the privilegas correctly read by acl_load
+--echo # check that the privileges are correctly read by acl_load
 flush privileges;
 
 connect (testuser,localhost,testuser,,*NO-ONE*);
-connection testuser;
 select * from testdb1.t1;
 connection default;
-disconnect testuser;
 
 
 --echo # drop role...
 drop role roletest;
 
---echo # ... and check that user do not have rights again
-connect (testuser,localhost,testuser,,*NO-ONE*);
+--echo # ... and check that user does not have rights again
 connection testuser;
 --error ER_TABLEACCESS_DENIED_ERROR
 select * from testdb1.t1;
diff --git a/mysql-test/suite/perfschema/r/connection_type_win.result b/mysql-test/suite/perfschema/r/connection_type_win.result
index 8dc064babff..c6c8b6cc77b 100644
--- a/mysql-test/suite/perfschema/r/connection_type_win.result
+++ b/mysql-test/suite/perfschema/r/connection_type_win.result
@@ -55,7 +55,7 @@ select user_host, command_type, argument from mysql.general_log
 where command_type = "Connect";
 user_host	[root] @ santa.claus.ipv4.example.com [192.0.2.4]
 command_type	Connect
-argument	root@santa.claus.ipv4.example.com on test using TCP/IP
+argument	root@santa.claus.ipv4.example.com on  using TCP/IP
 user_host	[root] @ santa.claus.ipv4.example.com [192.0.2.4]
 command_type	Connect
 argument	root@santa.claus.ipv4.example.com on test using TCP/IP
@@ -65,7 +65,6 @@ argument	rootssl@santa.claus.ipv4.example.com on test using SSL/TLS
 select substring(argument, locate("Connect", argument)) from test_log
 where argument like "%Connect%on test%";
 substring(argument, locate("Connect", argument))	Connect	root@santa.claus.ipv4.example.com on test using TCP/IP
-substring(argument, locate("Connect", argument))	Connect	root@santa.claus.ipv4.example.com on test using TCP/IP
 substring(argument, locate("Connect", argument))	Connect	rootssl@santa.claus.ipv4.example.com on test using SSL/TLS
 DROP TABLE test_log;
 SET GLOBAL general_log_file=  @old_general_log_file;
diff --git a/mysql-test/suite/roles/none_public.result b/mysql-test/suite/roles/none_public.result
index 4acba8bd133..bb8e3c0782a 100644
--- a/mysql-test/suite/roles/none_public.result
+++ b/mysql-test/suite/roles/none_public.result
@@ -5,6 +5,8 @@ create role public;
 ERROR OP000: Invalid role specification `public`
 drop role none;
 ERROR HY000: Operation DROP ROLE failed for 'none'
+drop role public;
+ERROR HY000: Operation DROP ROLE failed for PUBLIC
 grant none to role1;
 ERROR OP000: Invalid role specification `none`
 grant role1 to none;
@@ -14,6 +16,7 @@ ERROR OP000: Invalid role specification `none`
 grant public to role1;
 ERROR OP000: Invalid role specification `public`
 grant role1 to public;
+grant select on *.* to public;
 grant role1 to current_role;
 ERROR OP000: Invalid role specification `NONE`
 revoke none from role1;
@@ -25,13 +28,43 @@ ERROR OP000: Invalid role specification `none`
 revoke public from role1;
 ERROR OP000: Invalid role specification `public`
 revoke role1 from public;
-ERROR HY000: Cannot revoke role 'role1' from: 'public'@'%'
 revoke select on *.* from public;
 show grants for none;
 ERROR OP000: Invalid role specification `none`
+show grants for public;
+Grants for PUBLIC
 create definer=none view test.v1 as select 1;
 ERROR OP000: Invalid role specification `none`
+create definer=public view test.v1 as select 1;
+ERROR OP000: Invalid role specification `public`
 drop role role1;
+create user foo@localhost;
+connect foo,localhost,foo;
+set default role public;
+ERROR OP000: Invalid role specification `public`
+disconnect foo;
+connection default;
+update mysql.global_priv set priv=json_insert(priv, '$.default_role', 'none') where user='foo';
+connect foo,localhost,foo;
+show grants;
+Grants for foo@localhost
+GRANT USAGE ON *.* TO `foo`@`localhost`
+select * from information_schema.enabled_roles;
+ROLE_NAME
+NULL
+disconnect foo;
+connection default;
+update mysql.global_priv set priv=json_insert(priv, '$.default_role', 'public') where user='foo';
+connect foo,localhost,foo;
+show grants;
+Grants for foo@localhost
+GRANT USAGE ON *.* TO `foo`@`localhost`
+select * from information_schema.enabled_roles;
+ROLE_NAME
+NULL
+disconnect foo;
+connection default;
+drop user foo@localhost;
 insert mysql.global_priv values ('', 'none', '{"is_role":true}');
 flush privileges;
 Warnings:
diff --git a/mysql-test/suite/roles/none_public.test b/mysql-test/suite/roles/none_public.test
index 21e9dacf166..e88ed45c492 100644
--- a/mysql-test/suite/roles/none_public.test
+++ b/mysql-test/suite/roles/none_public.test
@@ -8,6 +8,8 @@ create role none;
 create role public;
 --error ER_CANNOT_USER
 drop role none;
+--error ER_CANNOT_USER
+drop role public;
 
 --error ER_INVALID_ROLE
 grant none to role1;
@@ -18,9 +20,7 @@ grant select on *.* to none;
 --error ER_INVALID_ROLE
 grant public to role1;
 grant role1 to public;
-# PUBLIC is legal role
-#--error ER_INVALID_ROLE
-#grant select on *.* to public;
+grant select on *.* to public;
 
 --error ER_INVALID_ROLE
 grant role1 to current_role;
@@ -33,24 +33,43 @@ revoke role1 from none;
 revoke select on *.* from none;
 --error ER_INVALID_ROLE
 revoke public from role1;
---error ER_CANNOT_REVOKE_ROLE
 revoke role1 from public;
 revoke select on *.* from public;
 
 --error ER_INVALID_ROLE
 show grants for none;
-# PUBLIC is legal role
-#--error ER_INVALID_ROLE
-#show grants for public;
+show grants for public;
 
 --error ER_INVALID_ROLE
 create definer=none view test.v1 as select 1;
-# PUBLIC is legal role
-#--error ER_INVALID_ROLE
-#create definer=public view test.v1 as select 1;
+--error ER_INVALID_ROLE
+create definer=public view test.v1 as select 1;
 
 drop role role1;
 
+create user foo@localhost;
+connect foo,localhost,foo;
+--error ER_INVALID_ROLE
+set default role public;
+disconnect foo;
+
+connection default;
+update mysql.global_priv set priv=json_insert(priv, '$.default_role', 'none') where user='foo';
+connect foo,localhost,foo;
+show grants;
+select * from information_schema.enabled_roles;
+disconnect foo;
+
+connection default;
+update mysql.global_priv set priv=json_insert(priv, '$.default_role', 'public') where user='foo';
+connect foo,localhost,foo;
+show grants;
+select * from information_schema.enabled_roles;
+disconnect foo;
+
+connection default;
+drop user foo@localhost;
+
 insert mysql.global_priv values ('', 'none', '{"is_role":true}');
 flush privileges;
 delete from mysql.global_priv where host='';