1 files changed, 293 insertions, 0 deletions
diff --git a/mysql-test/main/public_privileges.test b/mysql-test/main/public_privileges.test
new file mode 100644
index 00000000000..a542376f05c
--- /dev/null
+++ b/mysql-test/main/public_privileges.test
@@ -0,0 +1,293 @@
+--echo #
+--echo # Test DB/TABLE/COLUMN privileges in queries
+--echo #
+
+SHOW GRANTS FOR PUBLIC;
+
+create user testuser;
+create database testdb1;
+use testdb1;
+create table t1 (a int, b int);
+insert into t1 values (1,2);
+create database testdb2;
+use testdb2;
+create table t2 (a int, b int);
+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;
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from testdb1.t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from testdb2.t2;
+--error ER_TABLEACCESS_DENIED_ERROR
+select b from testdb2.t3;
+--error ER_TABLEACCESS_DENIED_ERROR
+select a from testdb2.t3;
+
+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;
+select * from testdb2.t2;
+select b from testdb2.t3;
+--error ER_COLUMNACCESS_DENIED_ERROR
+select a from testdb2.t3;
+
+connection default;
+
+--replace_column 1 # 3 # 6 # 7 #
+SHOW PROCESSLIST;
+
+connection default;
+
+use test;
+disconnect testuser;
+REVOKE SELECT ON testdb1.* from PUBLIC;
+REVOKE SELECT ON testdb2.t2 from PUBLIC;
+REVOKE SELECT (b) ON testdb2.t3 from PUBLIC;
+drop user testuser;
+drop database testdb1;
+drop database testdb2;
+
+--echo #
+--echo # test global process list privilege and EXECUTE db level
+--echo #
+
+create user testuser;
+create database testdb;
+use testdb;
+create procedure p1 () select 1;
+
+connect (testuser,localhost,testuser,,);
+connection testuser;
+
+--replace_column 1 # 3 # 6 # 7 #
+SHOW PROCESSLIST;
+--error ER_PROCACCESS_DENIED_ERROR
+call testdb.p1();
+
+connection default;
+
+GRANT PROCESS ON *.* to PUBLIC;
+GRANT EXECUTE ON testdb.* to PUBLIC;
+
+disconnect testuser;
+connect (testuser,localhost,testuser,,);
+connection testuser;
+
+--replace_column 1 # 3 # 6 # 7 #
+SHOW PROCESSLIST;
+call testdb.p1();
+
+connection default;
+
+--replace_column 1 # 3 # 6 # 7 #
+SHOW PROCESSLIST;
+
+connection default;
+
+use test;
+disconnect testuser;
+REVOKE PROCESS ON *.* from PUBLIC;
+REVOKE EXECUTE ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+
+--echo #
+--echo # test DB privilege to allow USE statement
+--echo #
+
+create user testuser;
+create database testdb;
+
+connect (testuser,localhost,testuser,,);
+connection testuser;
+
+--error ER_DBACCESS_DENIED_ERROR
+use testdb;
+
+connection default;
+
+GRANT LOCK TABLES ON testdb.* to PUBLIC;
+
+disconnect testuser;
+connect (testuser,localhost,testuser,,);
+connection testuser;
+
+use testdb;
+
+connection default;
+
+use test;
+disconnect testuser;
+REVOKE LOCK TABLES ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+
+
+--echo #
+--echo # test DB privilege to allow USE statement (as above)
+--echo # test current db privileges
+--echo #
+
+create user testuser;
+create database testdb;
+use testdb;
+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;
+--error ER_TABLEACCESS_DENIED_ERROR
+update t1 set a=a+1;
+
+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;
+
+connection default;
+select * from testdb.t1;
+
+use test;
+disconnect testuser;
+REVOKE LOCK TABLES ON testdb.* from PUBLIC;
+REVOKE UPDATE,SELECT ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+
+
+--echo #
+--echo # test DB privilege to allow USE statement (as above)
+--echo # test table/column privileges in current DB
+--echo #
+
+create user testuser;
+create database testdb;
+use testdb;
+create table t1 (a int);
+insert into t1 values (1);
+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;
+--error ER_TABLEACCESS_DENIED_ERROR
+delete from t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select b from t2;
+--error ER_TABLEACCESS_DENIED_ERROR
+select a from t2;
+
+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;
+select a from t2;
+--error ER_COLUMNACCESS_DENIED_ERROR
+select b from t2;
+
+connection default;
+select * from testdb.t1;
+
+use test;
+disconnect testuser;
+REVOKE LOCK TABLES ON testdb.* from PUBLIC;
+REVOKE DELETE ON testdb.t1 from PUBLIC;
+REVOKE SELECT (a) ON testdb.t2 from PUBLIC;
+drop user testuser;
+drop database testdb;
+
+--echo #
+--echo # test function privilege
+--echo #
+
+create user testuser;
+create database testdb;
+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";
+--error ER_PROCACCESS_DENIED_ERROR
+select 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";
+--error ER_PROCACCESS_DENIED_ERROR
+select testdb.f1();
+
+connection default;
+
+use test;
+disconnect testuser;
+REVOKE ALTER ROUTINE ON testdb.* from PUBLIC;
+drop function testdb.f1;
+drop user testuser;
+drop database testdb;
+
+--echo #
+--echo # bug with automatically added PUBLIC role
+--echo #
+
+--echo # automaticly added PUBLIC
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+GRANT SELECT on test.* to PUBLIC;
+
+REVOKE SELECT on test.* from PUBLIC;
+
+create user testuser;
+create database testdb1;
+use testdb1;
+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;
+
+connection default;
+ 
+disconnect testuser;
+drop user testuser;
+drop database testdb1;