summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/grant2.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/grant2.result')
-rw-r--r--mysql-test/main/grant2.result931
1 files changed, 931 insertions, 0 deletions
diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result
new file mode 100644
index 00000000..6ba7ddf1
--- /dev/null
+++ b/mysql-test/main/grant2.result
@@ -0,0 +1,931 @@
+select priv into @root_priv from mysql.global_priv where user='root' and host='localhost';
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
+SET NAMES binary;
+drop database if exists mysqltest;
+drop database if exists mysqltest_1;
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+delete from mysql.tables_priv where user like 'mysqltest\_%';
+delete from mysql.columns_priv where user like 'mysqltest\_%';
+flush privileges;
+grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option;
+grant create user on *.* to mysqltest_1@localhost;
+create user mysqltest_2@localhost;
+connect user_a,localhost,mysqltest_1,,"*NO-ONE*";
+connection user_a;
+grant select on `my\_1`.* to mysqltest_2@localhost;
+grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
+ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql'
+disconnect user_a;
+connection default;
+grant update on mysql.* to mysqltest_1@localhost;
+connect user_b,localhost,mysqltest_1,,"*NO-ONE*";
+connection user_b;
+grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
+grant select on `my\_1`.* to mysqltest_3@localhost;
+disconnect user_b;
+connection default;
+grant insert on mysql.* to mysqltest_1@localhost;
+connect user_c,localhost,mysqltest_1,,"*NO-ONE*";
+connection user_c;
+grant select on `my\_1`.* to mysqltest_3@localhost;
+grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass';
+disconnect user_c;
+connection default;
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+delete from mysql.tables_priv where user like 'mysqltest\_%';
+delete from mysql.columns_priv where user like 'mysqltest\_%';
+flush privileges;
+grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
+grant create user on *.* to mysqltest_1@localhost;
+connect user1,localhost,mysqltest_1,,"*NO-ONE*";
+connection user1;
+select current_user();
+current_user()
+mysqltest_1@localhost
+grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option;
+grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option;
+ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%'
+set @@sql_mode='NO_AUTO_CREATE_USER';
+select @@sql_mode;
+@@sql_mode
+NO_AUTO_CREATE_USER
+grant select on `my\_1`.* to mysqltest_4@localhost with grant option;
+ERROR 28000: Can't find any matching row in the user table
+grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass'
+with grant option;
+disconnect user1;
+connection default;
+show grants for mysqltest_1@localhost;
+Grants for mysqltest_1@localhost
+GRANT CREATE USER ON *.* TO `mysqltest_1`@`localhost`
+GRANT ALL PRIVILEGES ON `my\_%`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION
+show grants for mysqltest_2@localhost;
+Grants for mysqltest_2@localhost
+GRANT USAGE ON *.* TO `mysqltest_2`@`localhost`
+GRANT ALL PRIVILEGES ON `my\_1`.* TO `mysqltest_2`@`localhost` WITH GRANT OPTION
+show grants for mysqltest_3@localhost;
+ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost'
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+flush privileges;
+create database mysqltest_1;
+grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option;
+connect user2,localhost,mysqltest_1,,"*NO-ONE*";
+connection user2;
+select current_user();
+current_user()
+mysqltest_1@localhost
+show databases;
+Database
+information_schema
+mysqltest_1
+grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option;
+ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1'
+disconnect user2;
+connection default;
+show grants for mysqltest_1@localhost;
+Grants for mysqltest_1@localhost
+GRANT USAGE ON *.* TO `mysqltest_1`@`localhost`
+GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+drop database mysqltest_1;
+flush privileges;
+create database mysqltest;
+grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost;
+flush privileges;
+use mysqltest;
+create table t1 (id int primary key, data varchar(255));
+connect mrbad, localhost, mysqltest_1,,mysqltest;
+connection mrbad;
+show grants for current_user();
+Grants for mysqltest_1@localhost
+GRANT USAGE ON *.* TO `mysqltest_1`@`localhost`
+GRANT SELECT, INSERT ON `mysqltest`.* TO `mysqltest_1`@`localhost`
+insert into t1 values (1, 'I can''t change it!');
+update t1 set data='I can change it!' where id = 1;
+ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table `mysqltest`.`t1`
+insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!';
+ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table `mysqltest`.`t1`
+select * from t1;
+id data
+1 I can't change it!
+disconnect mrbad;
+connection default;
+drop table t1;
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+flush privileges;
+create table t1 (a int, b int);
+grant select (a) on t1 to mysqltest_1@localhost with grant option;
+connect mrugly, localhost, mysqltest_1,,mysqltest;
+connection mrugly;
+grant select (a,b) on t1 to mysqltest_2@localhost;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1'
+grant select on t1 to mysqltest_3@localhost;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table `mysqltest`.`t1`
+disconnect mrugly;
+connection default;
+drop table t1;
+delete from mysql.user where user like 'mysqltest\_%';
+delete from mysql.db where user like 'mysqltest\_%';
+delete from mysql.tables_priv where user like 'mysqltest\_%';
+delete from mysql.columns_priv where user like 'mysqltest\_%';
+flush privileges;
+drop database mysqltest;
+use test;
+create user mysqltest_1@host1;
+create user mysqltest_2@host2;
+create user mysqltest_3@host3;
+create user mysqltest_4@host4;
+create user mysqltest_5@host5;
+create user mysqltest_6@host6;
+create user mysqltest_7@host7;
+flush privileges;
+drop user mysqltest_3@host3;
+connect con8,127.0.0.1,root,,test,$MASTER_MYPORT,;
+disconnect con8;
+connection default;
+drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4,
+mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7;
+connect con9,127.0.0.1,root,,test,$MASTER_MYPORT,;
+disconnect con9;
+connection default;
+create database mysqltest_1;
+grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost;
+connect con10,localhost,mysqltest_1,,"*NO-ONE*";
+connection con10;
+set sql_log_off = 1;
+ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
+set sql_log_bin = 0;
+ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation
+disconnect con10;
+connection default;
+delete from mysql.user where user like 'mysqltest\_1';
+delete from mysql.db where user like 'mysqltest\_1';
+drop database mysqltest_1;
+flush privileges;
+set sql_mode='maxdb';
+drop table if exists t1, t2;
+create table t1(c1 int);
+create table t2(c1 int, c2 int);
+create user 'mysqltest_1';
+create user 'mysqltest_1';
+ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%'
+create user 'mysqltest_2' identified by 'Mysqltest-2';
+create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
+grant select on *.* to 'mysqltest_2';
+grant insert on test.* to 'mysqltest_2';
+grant update on test.t1 to 'mysqltest_2';
+grant update (c2) on test.t2 to 'mysqltest_2';
+select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%';
+Host User Password plugin authentication_string
+% mysqltest_1 mysql_native_password
+% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
+% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff
+select host,db,user from mysql.db where user like 'mysqltest_%';
+host db user
+% test mysqltest_2
+select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%';
+host db user table_name
+% test mysqltest_2 t1
+% test mysqltest_2 t2
+select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%';
+host db user table_name column_name
+% test mysqltest_2 t2 c2
+show grants for 'mysqltest_1';
+Grants for mysqltest_1@%
+GRANT USAGE ON *.* TO "mysqltest_1"@"%"
+show grants for 'mysqltest_2';
+Grants for mysqltest_2@%
+GRANT SELECT ON *.* TO "mysqltest_2"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
+GRANT INSERT ON "test".* TO "mysqltest_2"@"%"
+GRANT UPDATE ("c2") ON "test"."t2" TO "mysqltest_2"@"%"
+GRANT UPDATE ON "test"."t1" TO "mysqltest_2"@"%"
+drop user 'mysqltest_1';
+select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%';
+Host User Password plugin authentication_string
+% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
+% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff
+select host,db,user from mysql.db where user like 'mysqltest_%';
+host db user
+% test mysqltest_2
+select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%';
+host db user table_name
+% test mysqltest_2 t1
+% test mysqltest_2 t2
+select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%';
+host db user table_name column_name
+% test mysqltest_2 t2 c2
+show grants for 'mysqltest_1';
+ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
+rename user 'mysqltest_2' to 'mysqltest_1';
+select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ;
+Host User Password plugin authentication_string
+% mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
+% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff
+select host,db,user from mysql.db where user like 'mysqltest_%' ;
+host db user
+% test mysqltest_1
+select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' ;
+host db user table_name
+% test mysqltest_1 t1
+% test mysqltest_1 t2
+select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' ;
+host db user table_name column_name
+% test mysqltest_1 t2 c2
+show grants for 'mysqltest_1';
+Grants for mysqltest_1@%
+GRANT SELECT ON *.* TO "mysqltest_1"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
+GRANT INSERT ON "test".* TO "mysqltest_1"@"%"
+GRANT UPDATE ("c2") ON "test"."t2" TO "mysqltest_1"@"%"
+GRANT UPDATE ON "test"."t1" TO "mysqltest_1"@"%"
+drop user 'mysqltest_1', 'mysqltest_3';
+drop user 'mysqltest_1';
+ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%'
+drop table t1, t2;
+insert into mysql.db set user='mysqltest_1', db='%', host='%';
+flush privileges;
+show grants for 'mysqltest_1';
+ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
+revoke all privileges, grant option from 'mysqltest_1';
+ERROR HY000: Can't revoke all privileges for one or more of the requested users
+drop user 'mysqltest_1';
+select host,db,user from mysql.db where user = 'mysqltest_1' ;
+host db user
+insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1';
+flush privileges;
+show grants for 'mysqltest_1';
+ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
+drop user 'mysqltest_1';
+select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' ;
+host db user table_name
+insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1';
+flush privileges;
+show grants for 'mysqltest_1';
+ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
+drop user 'mysqltest_1';
+select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' ;
+host db user table_name column_name
+create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
+drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
+create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
+rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a';
+drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
+ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%'
+drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a';
+create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
+create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a';
+ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%'
+rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b';
+ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%'
+drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
+drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b';
+ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%'
+create user 'mysqltest_2' identified by 'Mysqltest-2';
+drop user 'mysqltest_2' identified by 'Mysqltest-2';
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'identified by 'Mysqltest-2'' at line 1
+drop user 'mysqltest_2';
+create user '%@b'@'b';
+show grants for '%@b'@'b';
+Grants for %@b@b
+GRANT USAGE ON *.* TO "%@b"@"b"
+grant select on mysql.* to '%@b'@'b';
+show grants for '%@b'@'b';
+Grants for %@b@b
+GRANT USAGE ON *.* TO "%@b"@"b"
+GRANT SELECT ON "mysql".* TO "%@b"@"b"
+rename user '%@b'@'b' to '%@a'@'a';
+show grants for '%@b'@'b';
+ERROR 42000: There is no such grant defined for user '%@b' on host 'b'
+show grants for '%@a'@'a';
+Grants for %@a@a
+GRANT USAGE ON *.* TO "%@a"@"a"
+GRANT SELECT ON "mysql".* TO "%@a"@"a"
+drop user '%@a'@'a';
+create user mysqltest_2@localhost;
+grant create user on *.* to mysqltest_2@localhost;
+connect user3,localhost,mysqltest_2,,"*NO-ONE*";
+connection user3;
+select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ;
+ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table `mysql`.`user`
+create user mysqltest_A@'%';
+rename user mysqltest_A@'%' to mysqltest_B@'%';
+drop user mysqltest_B@'%';
+disconnect user3;
+connection default;
+drop user mysqltest_2@localhost;
+create user mysqltest_3@localhost;
+grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost;
+connect user4,localhost,mysqltest_3,,"*NO-ONE*";
+connection user4;
+show grants;
+Grants for mysqltest_3@localhost
+GRANT USAGE ON *.* TO `mysqltest_3`@`localhost`
+GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `mysqltest_3`@`localhost`
+select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ;
+ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table `mysql`.`user`
+insert into mysql.global_priv set host='%', user='mysqltest_B';
+create user mysqltest_A@'%';
+rename user mysqltest_B@'%' to mysqltest_C@'%';
+drop user mysqltest_C@'%';
+drop user mysqltest_A@'%';
+disconnect user4;
+connection default;
+drop user mysqltest_3@localhost;
+set @@sql_mode='';
+create database mysqltest_1;
+create table mysqltest_1.t1 (i int);
+insert into mysqltest_1.t1 values (1),(2),(3);
+GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0';
+connect n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK;
+connection n1;
+show grants for current_user();
+Grants for mysqltest_1@127.0.0.0/255.0.0.0
+GRANT USAGE ON *.* TO `mysqltest_1`@`127.0.0.0/255.0.0.0`
+GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO `mysqltest_1`@`127.0.0.0/255.0.0.0`
+select * from t1;
+i
+1
+2
+3
+disconnect n1;
+connection default;
+REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0';
+delete from mysql.user where user like 'mysqltest\_1';
+flush privileges;
+drop table mysqltest_1.t1;
+grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1';
+connect b12302,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,;
+connection b12302;
+select current_user();
+current_user()
+mysqltest_1@127.0.0.1
+set password = password('changed');
+disconnect b12302;
+connection default;
+select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1';
+Host length(authentication_string)
+127.0.0.1 41
+revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1';
+delete from mysql.user where user like 'mysqltest\_1';
+flush privileges;
+grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0';
+connect b12302_2,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,;
+connection b12302_2;
+select current_user();
+current_user()
+mysqltest_1@127.0.0.0/255.0.0.0
+set password = password('changed');
+disconnect b12302_2;
+connection default;
+select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1';
+Host length(authentication_string)
+127.0.0.0/255.0.0.0 41
+revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0';
+delete from mysql.user where user like 'mysqltest\_1';
+flush privileges;
+drop database mysqltest_1;
+connect n5,localhost,test,,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
+connection n5;
+set password = password("changed");
+ERROR 42000: You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings
+disconnect n5;
+connection default;
+connect con2root,localhost,root,,;
+connect con3root,localhost,root,,;
+connection default;
+lock table mysql.user write;
+connection con2root;
+flush privileges;
+connection con3root;
+grant all on *.* to 'mysqltest_1'@'localhost';
+connection default;
+unlock tables;
+connection con2root;
+connection con3root;
+connection default;
+lock table mysql.user write;
+connection con2root;
+set password for 'mysqltest_1'@'localhost' = password('');
+connection con3root;
+revoke all on *.* from 'mysqltest_1'@'localhost';
+connection default;
+unlock tables;
+connection con2root;
+connection con3root;
+connection default;
+drop user 'mysqltest_1'@'localhost';
+disconnect con2root;
+disconnect con3root;
+create database TESTDB;
+create table t2(a int);
+create temporary table t1 as select * from mysql.global_priv;
+delete from mysql.global_priv where host='localhost';
+INSERT INTO mysql.global_priv (host, user, priv) VALUES
+('%','mysqltest_1',json_object('authentication_string', password('password')));
+INSERT INTO mysql.db (host, db, user, select_priv) VALUES
+('%','TESTDB','mysqltest_1','Y');
+FLUSH PRIVILEGES;
+connect con1,localhost,mysqltest_1,password,TESTDB;
+create database TEStdb;
+Got one of the listed errors
+connection default;
+disconnect con1;
+delete from mysql.global_priv;
+delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB';
+insert into mysql.global_priv select * from t1;
+drop table t1, t2;
+drop database TESTDB;
+flush privileges;
+SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
+SET GLOBAL log_bin_trust_function_creators = 1;
+GRANT ALL PRIVILEGES ON test.* TO `a@`@localhost;
+GRANT EXECUTE ON * TO `a@`@localhost;
+connect bug13310,localhost,'a@',,test;
+connection bug13310;
+CREATE TABLE t2 (s1 INT);
+INSERT INTO t2 VALUES (1);
+DROP FUNCTION IF EXISTS f2;
+CREATE FUNCTION f2 () RETURNS INT
+BEGIN DECLARE v INT; SELECT s1 FROM t2 INTO v; RETURN v; END//
+Warnings:
+Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+SELECT f2();
+f2()
+1
+DROP FUNCTION f2;
+DROP TABLE t2;
+disconnect bug13310;
+connection default;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost;
+DROP USER `a@`@localhost;
+SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
+drop database if exists mysqltest_1;
+drop database if exists mysqltest_2;
+drop user mysqltest_u1@localhost;
+create database mysqltest_1;
+create database mysqltest_2;
+grant all on mysqltest_1.* to mysqltest_u1@localhost;
+use mysqltest_2;
+create table t1 (i int);
+connect user1,localhost,mysqltest_u1,,mysqltest_1;
+connection user1;
+show create table mysqltest_2.t1;
+ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_2`.`t1`
+create table t1 like mysqltest_2.t1;
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_2`.`t1`
+connection default;
+grant select on mysqltest_2.t1 to mysqltest_u1@localhost;
+connection user1;
+show create table mysqltest_2.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+create table t1 like mysqltest_2.t1;
+connection default;
+disconnect user1;
+use test;
+drop database mysqltest_1;
+drop database mysqltest_2;
+drop user mysqltest_u1@localhost;
+grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option;
+grant usage on *.* to mysqltest_2@localhost;
+connect con18600_1,localhost,mysqltest_1,,"*NO-ONE*";
+create database mysqltest_1;
+use mysqltest_1;
+create table t1 (f1 int);
+grant create on `mysqltest\_1`.* to mysqltest_2@localhost;
+grant select on mysqltest_1.t1 to mysqltest_2@localhost;
+connect con3,localhost,mysqltest_2,,"*NO-ONE*";
+connection con3;
+create database mysqltest_3;
+ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest_3'
+use mysqltest_1;
+create table t2(f1 int);
+select * from t1;
+f1
+connection default;
+drop database mysqltest_1;
+connection default;
+disconnect con3;
+disconnect con18600_1;
+revoke all privileges, grant option from mysqltest_1@localhost;
+revoke all privileges, grant option from mysqltest_2@localhost;
+drop user mysqltest_1@localhost;
+drop user mysqltest_2@localhost;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 (b INT, c INT);
+INSERT INTO t2 VALUES (1,100),(2,200);
+GRANT SELECT ON t1 TO mysqltest1@localhost;
+GRANT SELECT (b) ON t2 TO mysqltest1@localhost;
+connect conn1,localhost,mysqltest1,,"*NO-ONE*";
+connection conn1;
+USE db1;
+SELECT c FROM t2;
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2'
+SELECT * FROM t2;
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for table `db1`.`t2`
+SELECT * FROM t1 JOIN t2 USING (b);
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2'
+connection default;
+disconnect conn1;
+USE test;
+DROP TABLE db1.t1, db1.t2;
+DROP USER mysqltest1@localhost;
+DROP DATABASE db1;
+End of 5.0 tests
+USE mysql;
+SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u;
+SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h;
+SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h;
+User Host Password plugin authentication_string Insert_priv
+root localhost Y
+GRANT INSERT ON *.* TO CURRENT_USER();
+SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h;
+User Host Password plugin authentication_string Insert_priv
+root localhost mysql_native_password Y
+GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose';
+SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h;
+User Host Password plugin authentication_string Insert_priv
+root localhost *0BB7188CF0DE9B403BA66E9DD810D82652D002EB mysql_native_password *0BB7188CF0DE9B403BA66E9DD810D82652D002EB Y
+UPDATE global_priv SET priv=@root_priv where user='root' and host='localhost';
+FLUSH PRIVILEGES;
+USE test;
+End of 5.1 tests
+
+# --
+# -- Bug#11746602: 27480 - Extend CREATE TEMPORARY TABLES privilege to
+# -- allow temp table operations
+# --
+# -- Bug#12771903: User with create temporary tables priv only has full
+# -- access to a regular table
+# --
+
+############################################################################
+# Setup environment.
+###########################################################################
+DROP DATABASE IF EXISTS mysqltest_db1;
+DROP DATABASE IF EXISTS mysqltest_db2;
+CREATE DATABASE mysqltest_db1;
+CREATE DATABASE mysqltest_db2;
+# mysqltest_u1@localhost has CREATE_TMP_ACL, FILE_ACL and EXECUTE_ACL only
+# (EXECUTE_ACL is needed to call p0, and FILE_ACL is needed for SELECT
+# OUTFILE/LOAD DATA INFILE).
+GRANT FILE ON *.* TO mysqltest_u1@localhost;
+GRANT CREATE TEMPORARY TABLES, EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+# mysqltest_u2@localhost has all privileges but CREATE_TMP_ACL.
+GRANT ALL PRIVILEGES ON mysqltest_db1.* TO mysqltest_u2@localhost;
+REVOKE CREATE TEMPORARY TABLES ON mysqltest_db1.* FROM mysqltest_u2@localhost;
+# mysqltest_u3@localhost has CREATE_TMP_ACL & EXECUTE_ACL.
+# This user is required to check SUID-stored-routines.
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u3@localhost;
+GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u3@localhost;
+# mysqltest_u4@localhost has only EXECUTE_ACL.
+# We need this user to check that once created temporary tables
+# are accessible by anyone.
+GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u4@localhost;
+# mysqltest_u5@localhost has CREATE_TMP_ACL and SELECT_ACL, UPDATE_ACL,
+# DELETE_ACL on mysqltest_db1; and only CREATE_TMP_ACL on mysqltest_db2.
+# By means of this user we check privileges required for merge tables.
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u5@localhost;
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db2.* TO mysqltest_u5@localhost;
+GRANT SELECT, UPDATE, DELETE ON mysqltest_db1.* TO mysqltest_u5@localhost;
+# Create stored routine to test how privilege checking is done for its
+# arguments.
+CREATE PROCEDURE mysqltest_db1.p0(i INT) SELECT i;
+# Create SUID-stored-routines.
+CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p1()
+CREATE TEMPORARY TABLE t4(x INT);
+CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p2()
+INSERT INTO t4 VALUES (1), (2), (3);
+CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p3()
+SELECT * FROM t4 ORDER BY x;
+# We need separate key cache to test CACHE INDEX and LOAD INDEX.
+SET GLOBAL keycache1.key_buffer_size = 128 * 1024;
+CREATE TABLE mysqltest_db2.t2_1(a INT);
+###########################################################################
+# Check that CREATE_TMP_ACL is enough to issue almost any supported
+# SQL-statement against temporary tables (loosely follow order in
+# sql_command enum).
+###########################################################################
+connect con1,localhost,mysqltest_u1,,mysqltest_db1;
+#
+# Variants of CREATE TABLE.
+#
+CREATE TEMPORARY TABLE t1(a INT);
+CREATE TEMPORARY TABLE t2 LIKE t1;
+CREATE TEMPORARY TABLE t3(a INT, b INT, PRIMARY KEY (a));
+CREATE TEMPORARY TABLE t4 SELECT * FROM t1;
+# Check that we do *not* allow creation of MERGE table with underlying
+# temporary table without additional privileges.
+CREATE TEMPORARY TABLE t5(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (t5);
+ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_db1`.`t5`
+# Check that we allow creation of MERGE table with no underlying table
+# without additional privileges.
+CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = ();
+#
+# SELECT.
+#
+INSERT INTO t1 VALUES (1), (2), (3);
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+3
+#
+# CREATE/DROP INDEX.
+#
+CREATE INDEX idx1 ON t3(b);
+DROP INDEX idx1 ON t3;
+#
+# ALTER TABLE.
+#
+ALTER TABLE t4 ADD COLUMN b INT;
+# Check that we allow altering of MERGE table with no underlying
+# without additional privileges.
+ALTER TABLE t6 UNION = ();
+# Check that we do *not* allow altering of MERGE table with underlying
+# temporary table without additional privileges.
+ALTER TABLE t6 UNION = (t5);
+ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_db1`.`t5`
+#
+# Simple INSERT and INSERT ... SELECT.
+#
+INSERT INTO t1 VALUES (4);
+INSERT INTO t2 SELECT a FROM t1;
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+3
+4
+SELECT * FROM t2 ORDER BY a;
+a
+1
+2
+3
+4
+#
+# UPDATE and multi-UPDATE.
+#
+UPDATE t1 SET a = a * 10;
+UPDATE t1 SET a = 100 WHERE a = 10;
+UPDATE t1, t2 SET t1.a = 200 WHERE t1.a = t2.a * 10 AND t1.a = 20;
+SELECT * FROM t1 ORDER BY a;
+a
+30
+40
+100
+200
+#
+# DELETE and multi-DELETE.
+#
+DELETE FROM t1 WHERE a = 100;
+DELETE t1 FROM t1, t2 WHERE t1.a = t2.a * 100 AND t1.a = 200;
+SELECT * FROM t1 ORDER BY a;
+a
+30
+40
+#
+# TRUNCATE TABLE.
+#
+TRUNCATE TABLE t1;
+SELECT * FROM t1 ORDER BY a;
+a
+#
+# SHOW COLUMNS/DESCRIBE and SHOW KEYS.
+#
+SHOW COLUMNS FROM t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+SHOW KEYS FROM t3;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
+t3 0 PRIMARY 1 a A 0 NULL NULL BTREE NO
+#
+# SHOW CREATE TABLE.
+#
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TEMPORARY TABLE `t1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+#
+# LOAD DATA INFILE (also SELECT INTO OUTFILE).
+#
+INSERT INTO t1 VALUES (1), (2), (3);
+SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' FROM t1 ;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' INTO TABLE t1;
+SELECT * FROM t1 ORDER BY a;
+a
+1
+1
+2
+2
+3
+3
+#
+# SET.
+#
+SET @a := (SELECT COUNT(*) FROM t1);
+SELECT @a;
+@a
+6
+#
+# LOCK TABLES.
+#
+LOCK TABLES t1 READ;
+UNLOCK TABLES;
+#
+# CHECK/REPAIR/ANALYZE/OPTIMIZE and CHECKSUM.
+#
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+mysqltest_db1.t1 analyze status OK
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+mysqltest_db1.t1 check status OK
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+mysqltest_db1.t1 optimize status Table is already up to date
+REPAIR TABLE t1;
+Table Op Msg_type Msg_text
+mysqltest_db1.t1 repair status OK
+#
+# REPLACE and REPLACE ... SELECT.
+#
+INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333);
+REPLACE INTO t3 VALUES (1, 1111), (4, 444), (0, 001);
+REPLACE INTO t2 SELECT b FROM t3;
+SELECT * FROM t2 ORDER BY a;
+a
+1
+1
+2
+3
+4
+222
+333
+444
+1111
+SELECT * FROM t3 ORDER BY a;
+a b
+0 1
+1 1111
+2 222
+3 333
+4 444
+#
+# CACHE and LOAD INDEX.
+#
+CACHE INDEX t3 IN keycache1;
+Table Op Msg_type Msg_text
+mysqltest_db1.t3 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t3;
+Table Op Msg_type Msg_text
+mysqltest_db1.t3 preload_keys status OK
+#
+# RENAME should work for temporary tables
+#
+RENAME TABLE t3 TO t3_1;
+#
+# HANDLER OPEN/READ/CLOSE.
+#
+HANDLER t1 OPEN;
+HANDLER t1 READ NEXT;
+a
+1
+HANDLER t1 CLOSE;
+#
+# DO.
+#
+DO (SELECT COUNT(*) FROM t1);
+#
+# CHECKSUM TABLE.
+#
+DELETE FROM t1;
+CHECKSUM TABLE t1;
+Table Checksum
+mysqltest_db1.t1 0
+#
+# CALL.
+#
+CALL p0((SELECT COUNT(*) FROM t1));
+i
+0
+#
+# PREPARE, EXECUTE and DEALLOCATE.
+#
+PREPARE stmt1 FROM 'SELECT * FROM t1 ORDER BY a';
+PREPARE stmt2 FROM 'SELECT * FROM t2 ORDER BY a';
+EXECUTE stmt1;
+a
+EXECUTE stmt2;
+a
+1
+1
+2
+3
+4
+222
+333
+444
+1111
+DEALLOCATE PREPARE stmt1;
+DEALLOCATE PREPARE stmt2;
+#
+# DROP TABLE and DROP TEMPORARY TABLE.
+#
+DROP TABLE t1;
+CREATE TEMPORARY TABLE t1(a INT);
+DROP TEMPORARY TABLE t1;
+###########################################################################
+# - Check that even having all privileges but CREATE_TMP_ACL is not enough
+# to create temporary tables.
+# - Check that creation/working with temporary tables is possible via
+# SUID-stored-routines.
+# - Check that even outside of SUID context we can access temporary
+# table once it is created.
+###########################################################################
+connect con2,localhost,mysqltest_u2,,mysqltest_db1;
+CREATE TEMPORARY TABLE t2(a INT);
+ERROR 42000: Access denied for user 'mysqltest_u2'@'localhost' to database 'mysqltest_db1'
+CALL p1();
+CALL p2();
+CALL p3();
+x
+1
+2
+3
+# Check that once table is created it can be accessed even
+# outside of such a SUID context.
+INSERT INTO t4 VALUES (4);
+UPDATE t4 SET x = 10 WHERE x = 1;
+DELETE FROM t4 WHERE x < 3;
+SELECT * FROM t4 ORDER BY x;
+x
+3
+4
+10
+DROP TEMPORARY TABLE t4;
+###########################################################################
+# - Check that once table is created it can be accessed from within any
+# context, even by user without any privileges on tables.
+###########################################################################
+connect con3,localhost,mysqltest_u4,,mysqltest_db1;
+CALL p1();
+INSERT INTO t4 VALUES (4);
+UPDATE t4 SET x = 10 WHERE x = 1;
+DELETE FROM t4 WHERE x < 3;
+SELECT * FROM t4 ORDER BY x;
+x
+4
+DROP TEMPORARY TABLE t4;
+###########################################################################
+# Check special case for MERGE-tables:
+# - CREATE_TMP_ACL is required to create a temporary merge table;
+# - SELECT_ACL, UPDATE_ACL and DELETE_ACL are required to include
+# a temporary table into the underlying-table-list.
+###########################################################################
+connect con4,localhost,mysqltest_u5,,mysqltest_db1;
+CREATE TEMPORARY TABLE t7(a INT);
+CREATE TEMPORARY TABLE t8(a INT);
+CREATE TEMPORARY TABLE t9(a INT);
+CREATE TEMPORARY TABLE t10(a INT) ENGINE = MERGE UNION = (t7, t8);
+ALTER TABLE t10 UNION = (t9);
+ALTER TABLE t10 UNION = (mysqltest_db2.t2_1);
+ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u5'@'localhost' for table `mysqltest_db2`.`t2_1`
+CREATE TEMPORARY TABLE mysqltest_db2.t2_2(a INT) ENGINE = MERGE UNION = (t7, t8);
+ALTER TABLE mysqltest_db2.t2_2 UNION = (t9);
+ALTER TABLE mysqltest_db2.t2_2 UNION = ();
+DROP TEMPORARY TABLE mysqltest_db2.t2_2;
+DROP TEMPORARY TABLE t10;
+DROP TEMPORARY TABLE t7;
+DROP TEMPORARY TABLE t8;
+DROP TEMPORARY TABLE t9;
+###########################################################################
+# That's all. Cleanup.
+###########################################################################
+connection default;
+# All remaining temporary tables are automatically dropped.
+disconnect con1;
+disconnect con2;
+disconnect con3;
+disconnect con4;
+SET GLOBAL keycache1.key_buffer_size = 0;
+DROP DATABASE mysqltest_db1;
+DROP DATABASE mysqltest_db2;
+DROP USER mysqltest_u1@localhost;
+DROP USER mysqltest_u2@localhost;
+DROP USER mysqltest_u3@localhost;
+DROP USER mysqltest_u4@localhost;
+DROP USER mysqltest_u5@localhost;
+set GLOBAL sql_mode=default;