Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 344 insertions, 0 deletions

diff --git a/mysql-test/main/public_privileges.result b/mysql-test/main/public_privileges.result
new file mode 100644
index 00000000..18a45a12
--- /dev/null
+++ b/mysql-test/main/public_privileges.result
@@ -0,0 +1,344 @@
+#
+# MDEV-5215 Granted to PUBLIC
+#
+#
+# Test DB/TABLE/COLUMN privileges in queries
+#
+SHOW GRANTS FOR PUBLIC;
+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,,;
+select * from testdb1.t1;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
+select * from testdb2.t2;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb2`.`t2`
+select b from testdb2.t3;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb2`.`t3`
+select a from testdb2.t3;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `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;
+connection testuser;
+select * from testdb1.t1;
+a	b
+1	2
+select * from testdb2.t2;
+a	b
+1	2
+select b from testdb2.t3;
+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 privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,testuser,,;
+select * from testdb1.t1;
+a	b
+1	2
+select * from testdb2.t2;
+a	b
+1	2
+select b from testdb2.t3;
+b
+2
+select a from testdb2.t3;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'a' in table 't3'
+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;
+#
+# test global process list privilege and EXECUTE db level
+#
+create user testuser;
+create database testdb;
+use testdb;
+create procedure p1 () select 1;
+connect  testuser,localhost,testuser,,;
+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;
+GRANT PROCESS ON *.* to PUBLIC;
+GRANT EXECUTE ON testdb.* to PUBLIC;
+disconnect testuser;
+connect  testuser,localhost,testuser,,;
+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 privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,testuser,,;
+select user,db from information_schema.processlist where user='root';
+user	db
+root	testdb
+call testdb.p1();
+1
+1
+connection default;
+use test;
+disconnect testuser;
+REVOKE PROCESS ON *.* from PUBLIC;
+REVOKE EXECUTE ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+#
+# test DB privilege to allow USE statement
+#
+create user testuser;
+create database testdb;
+connect  testuser,localhost,testuser,,;
+use testdb;
+ERROR 42000: Access denied for user 'testuser'@'%' to database 'testdb'
+connection default;
+GRANT LOCK TABLES ON testdb.* to PUBLIC;
+connection testuser;
+use testdb;
+connection default;
+disconnect testuser;
+# check that the privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,testuser,,;
+use testdb;
+connection default;
+use test;
+disconnect testuser;
+REVOKE LOCK TABLES ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+#
+# test DB privilege to allow USE statement (as above)
+# test current db privileges
+#
+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,,;
+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;
+connection testuser;
+use testdb;
+update t1 set a=a+1;
+connection default;
+select * from testdb.t1;
+a
+2
+use test;
+disconnect testuser;
+REVOKE LOCK TABLES ON testdb.* from PUBLIC;
+REVOKE UPDATE,SELECT ON testdb.* from PUBLIC;
+drop user testuser;
+drop database testdb;
+#
+# test DB privilege to allow USE statement (as above)
+# test table/column privileges in current DB
+#
+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,,;
+use testdb;
+delete from t1;
+ERROR 42000: DELETE command denied to user 'testuser'@'localhost' for table `testdb`.`t1`
+select b from t2;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb`.`t2`
+select a from t2;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb`.`t2`
+connection default;
+GRANT DELETE ON testdb.t1 to PUBLIC;
+GRANT SELECT (a) ON testdb.t2 to PUBLIC;
+connection testuser;
+use testdb;
+delete from t1;
+select a from t2;
+a
+1
+select b from t2;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't2'
+connection default;
+select * from testdb.t1;
+a
+insert into t1 values (1);
+disconnect testuser;
+# check that the privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,testuser,,;
+use testdb;
+delete from t1;
+select a from t2;
+a
+1
+select b from t2;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't2'
+connection default;
+select * from testdb.t1;
+a
+use test;
+disconnect testuser;
+REVOKE ALL PRIVILEGES, GRANT OPTION from `PUBLIC`;
+SHOW GRANTS FOR PUBLIC;
+Grants for PUBLIC
+drop user testuser;
+drop database testdb;
+#
+# test function privilege
+#
+create user testuser;
+create database testdb;
+use testdb;
+create function f1() returns int return 2;
+connect  testuser,localhost,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;
+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 privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,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;
+use test;
+disconnect testuser;
+REVOKE ALTER ROUTINE ON testdb.* from PUBLIC;
+drop function testdb.f1;
+drop user testuser;
+drop database testdb;
+#
+# bug with automatically added PUBLIC role
+#
+# 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,,;
+select * from testdb1.t1;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
+connection default;
+disconnect testuser;
+drop user testuser;
+drop database testdb1;
+#
+# check assigning privileges via GRAND role TO PUBLIC
+#
+create user testuser;
+create database testdb1;
+use testdb1;
+create table t1 (a int, b int);
+# check that user do not have rights
+connect  testuser,localhost,testuser,,*NO-ONE*;
+select * from testdb1.t1;
+ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `testdb1`.`t1`
+connection default;
+give rights to everyone via assigning the role to public
+create role roletest;
+GRANT SELECT ON testdb1.* TO roletest;
+GRANT roletest TO PUBLIC;
+connection testuser;
+select * from testdb1.t1;
+a	b
+connection default;
+disconnect testuser;
+# check that the privileges are correctly read by acl_load
+flush privileges;
+connect  testuser,localhost,testuser,,*NO-ONE*;
+select * from testdb1.t1;
+a	b
+connection default;
+# drop role...
+drop role roletest;
+# ... 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`
+connection default;
+disconnect testuser;
+drop user testuser;
+drop database testdb1;
+# clean up
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;
+#
+# MDEV-29752 SHOW GRANTS FOR PUBLIC should work for all users
+#
+create database dbtest;
+create user `testuser`@`%`;
+GRANT USAGE ON *.* TO `testuser`@`%`;
+GRANT ALL PRIVILEGES ON `dbtest`.* TO `PUBLIC`;
+connect  testuser,localhost,testuser,,;
+show grants for public;
+Grants for PUBLIC
+GRANT ALL PRIVILEGES ON `dbtest`.* TO PUBLIC
+show grants for testuser;
+Grants for testuser@%
+GRANT USAGE ON *.* TO `testuser`@`%`
+connection default;
+disconnect testuser;
+REVOKE ALL PRIVILEGES ON `dbtest`.* FROM `PUBLIC`;
+REVOKE USAGE ON *.* FROM `testuser`@`%`;
+drop user `testuser`@`%`;
+drop database dbtest;
+# clean up
+delete from mysql.global_priv where user="PUBLIC";
+flush privileges;