MDEV-5215 Granted to PUBLIC

1 files changed, 130 insertions, 0 deletions

diff --git a/mysql-test/main/public_basic.test b/mysql-test/main/public_basic.test
new file mode 100644
index 00000000000..ddb4e040fce
--- /dev/null
+++ b/mysql-test/main/public_basic.test
@@ -0,0 +1,130 @@
+SHOW GRANTS FOR PUBLIC;
+
+--echo # it is not PUBLIC but an user
+--echo # (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;
+
+--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;
+--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;
+
+SHOW GRANTS FOR PUBLIC;
+
+--error ER_INVALID_ROLE
+GRANT XXXXXX TO CURRENT_USER;
+--echo # following should fail with the same error as above
+--error ER_INVALID_ROLE
+GRANT PUBLIC TO CURRENT_USER;
+
+--error ER_INVALID_ROLE
+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
+
+drop role XXXXXX;
+--echo # following should fail with the same error as above
+--error ER_CANNOT_USER
+drop role PUBLIC;
+
+--error ER_INVALID_ROLE
+SET ROLE XXXXXX;
+--echo # following should fail with the same error as above
+--error ER_INVALID_ROLE
+SET ROLE PUBLIC;
+
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE XXXXXX;
+--echo # following should fail with the same error as above
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE PUBLIC;
+
+--echo #
+--echo # check prohibition of change security context to PUBLIC
+--echo #
+--echo # be sure that we have PUBLIC
+GRANT SELECT on test.* to PUBLIC;
+--echo # try with a view
+create table t1( a int);
+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
+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;
+
+--echo #
+--echo # check autocreation of PUBLIC on GRAND 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;
+drop role roletest;
+
+
+-- echo # clean up
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;