#
# MDEV-5215 Granted to PUBLIC
#
SHOW GRANTS FOR PUBLIC;
Grants for PUBLIC
# it is not PUBLIC but an user
# (this should work as it allowed for roles for example)
create user PUBLIC;
create user PUBLIC@localhost;
GRANT SELECT on test.* to PUBLIC@localhost;
drop user PUBLIC@localhost;
drop user PUBLIC;
select * from mysql.global_priv where user="PUBLIC" ;
Host	User	Priv
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;
SHOW GRANTS FOR PUBLIC;
Grants for PUBLIC
GRANT XXXXXX TO CURRENT_USER;
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`
# following should fail with the same error as above
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;
ERROR HY000: Operation DROP ROLE failed for PUBLIC
SET ROLE XXXXXX;
ERROR OP000: Invalid role specification `XXXXXX`
# following should fail with the same error as above
SET ROLE PUBLIC;
ERROR OP000: Invalid role specification `PUBLIC`
SET DEFAULT ROLE XXXXXX;
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
#
# be sure that we have PUBLIC
GRANT SELECT on test.* to PUBLIC;
# try with a view
create table t1( a int);
create definer = PUBLIC view v1 as select * from t1;
ERROR OP000: Invalid role specification `PUBLIC`
drop table t1;
# try with a stored procedure
create definer='PUBLIC' PROCEDURE p1() SELECT 1;
ERROR OP000: Invalid role specification `PUBLIC`
# this test cleanup
revoke select on test.* from 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;
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;
#
# MDEV-30154: Assertion `strcasecmp(rolename, public_name.str) ||
# acl_public == role' failed in acl_update_role on GRANT ... TO PUBLIC
#
call mtr.add_suppression("Can't open and lock privilege tables");
USE test;
GRANT SELECT ON *.* TO PUBLIC;
LOCK TABLES mysql.time_zone WRITE,mysql.proc WRITE;
FLUSH PRIVILEGES;
ERROR HY000: Table 'db' was not locked with LOCK TABLES
LOCK TABLE nonexisting WRITE;
ERROR 42S02: Table 'test.nonexisting' doesn't exist
GRANT SELECT ON *.* TO PUBLIC;
REVOKE SELECT ON *.* FROM PUBLIC;
#
# End of 10.11 test
#
# clean up
delete from mysql.global_priv where user="PUBLIC";
flush privileges;