Edit File: grant2.result
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; call mtr.add_suppression("Did not write failed .* into binary log while " "granting/revoking privileges in databases."); call mtr.add_suppression("Did not write failed .* into binary log while " "revoking all_privileges from a list of users."); 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; 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' grant update on mysql.* to mysqltest_1@localhost; grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; grant select on `my\_1`.* to mysqltest_3@localhost; grant insert on mysql.* to mysqltest_1@localhost; grant select on `my\_1`.* to mysqltest_3@localhost; grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; 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; 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 42000: 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; 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; select current_user(); current_user() mysqltest_1@localhost show databases; Database information_schema mysqltest_1 test 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' 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)); 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 '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 't1' select * from t1; id data 1 I can't change it! 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; 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 't1' 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; drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; create database mysqltest_1; grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost; 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 privilege(s) for this operation 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 from mysql.user where user like 'mysqltest_%' order by host,user,password; host user password % mysqltest_1 % mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 % mysqltest_3 fffffffffffffffffffffffffffffffffffffffff select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; host db user % test mysqltest_2 select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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_%' order by host,db,user,table_name,column_name; 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 from mysql.user where user like 'mysqltest_%' order by host,user,password; host user password % mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 % mysqltest_3 fffffffffffffffffffffffffffffffffffffffff select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; host db user % test mysqltest_2 select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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_%' order by host,db,user,table_name,column_name; 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 from mysql.user where user like 'mysqltest_%' order by host,user,password; host user password % mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 % mysqltest_3 fffffffffffffffffffffffffffffffffffffffff select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; host db user % test mysqltest_1 select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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_%' order by host,db,user,table_name,column_name; 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' order by host,db,user; 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' order by host,db,user,table_name; 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' order by host,db,user,table_name,column_name; 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 MySQL 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; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' create user mysqltest_A@'%'; rename user mysqltest_A@'%' to mysqltest_B@'%'; drop user mysqltest_B@'%'; drop user mysqltest_2@localhost; create user mysqltest_3@localhost; grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; 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 from mysql.user where user like 'mysqltest_%' order by host,user,password; ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' insert into mysql.user set host='%', user='mysqltest_B'; Warnings: Warning 1364 Field 'ssl_cipher' doesn't have a default value Warning 1364 Field 'x509_issuer' doesn't have a default value Warning 1364 Field 'x509_subject' doesn't have a default value create user mysqltest_A@'%'; rename user mysqltest_B@'%' to mysqltest_C@'%'; drop user mysqltest_C@'%'; drop user mysqltest_A@'%'; 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'; 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 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'; select current_user(); current_user() mysqltest_1@127.0.0.1 set password = password('changed'); select host, length(password) from mysql.user where user like 'mysqltest\_1'; host length(password) 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'; select current_user(); current_user() mysqltest_1@127.0.0.0/255.0.0.0 set password = password('changed'); select host, length(password) from mysql.user where user like 'mysqltest\_1'; host length(password) 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; set password = password("changed"); ERROR 42000: Can't find any matching row in the user table lock table mysql.user write; flush privileges; grant all on *.* to 'mysqltest_1'@'localhost'; unlock tables; lock table mysql.user write; set password for 'mysqltest_1'@'localhost' = password(''); revoke all on *.* from 'mysqltest_1'@'localhost'; unlock tables; drop user 'mysqltest_1'@'localhost'; create database TESTDB; create table t2(a int); create temporary table t1 as select * from mysql.user; delete from mysql.user where host='localhost'; INSERT INTO mysql.user (host, user, password) VALUES ('%','mysqltest_1',password('password')); Warnings: Warning 1364 Field 'ssl_cipher' doesn't have a default value Warning 1364 Field 'x509_issuer' doesn't have a default value Warning 1364 Field 'x509_subject' doesn't have a default value INSERT INTO mysql.db (host, db, user, select_priv) VALUES ('%','TESTDB','mysqltest_1','Y'); FLUSH PRIVILEGES; create database TEStdb; Got one of the listed errors delete from mysql.user; delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; insert into mysql.user 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; 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// SELECT f2(); f2() 1 DROP FUNCTION f2; DROP TABLE t2; 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); show create table mysqltest_2.t1; ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1' create table t1 like mysqltest_2.t1; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' grant select on mysqltest_2.t1 to mysqltest_u1@localhost; show create table mysqltest_2.t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create table t1 like mysqltest_2.t1; 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; 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; 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 drop database mysqltest_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; 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 't2' SELECT * FROM t1 JOIN t2 USING (b); ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' 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 password FROM user WHERE user=@u AND host=@h INTO @pwd; SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; user host password insert_priv root localhost Y UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; user host password insert_priv root localhost N GRANT INSERT ON *.* TO CURRENT_USER(); SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; user host password insert_priv root localhost Y UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; user host password insert_priv root localhost *0BB7188CF0DE9B403BA66E9DD810D82652D002EB Y UPDATE user SET password=@pwd WHERE user=@u AND host=@h; SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; user host password insert_priv root localhost Y 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, mysqltest_u1@localhost, 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 '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 '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 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 t3 0 PRIMARY 1 a A 0 NULL NULL BTREE # # 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 # # 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 (doesn't work for temporary tables, thus should fail). # RENAME TABLE t3 TO t3_1; ERROR 42000: DROP, ALTER command denied to user 'mysqltest_u1'@'localhost' for table 't3' # # 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, mysqltest_u2@localhost, 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, mysqltest_u4@localhost, 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, mysqltest_u5@localhost, 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 '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 # -- disconnect con1 # All remaining temporary tables are automatically dropped. # -- 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;