# Grant tests not performed with embedded server -- source include/not_embedded.inc # Save the initial number of concurrent sessions --source include/count_sessions.inc set GLOBAL sql_mode=""; set LOCAL sql_mode=""; SET NAMES binary; # # GRANT tests that require several connections # (usually it's GRANT, reconnect as another user, try something) # # prepare playground before tests --disable_warnings drop database if exists mysqltest; drop database if exists mysqltest_1; --enable_warnings 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,,); connection user_a; grant select on `my\_1`.* to mysqltest_2@localhost; --error ER_DBACCESS_DENIED_ERROR grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; disconnect user_a; connection default; grant update on mysql.* to mysqltest_1@localhost; connect (user_b,localhost,mysqltest_1,,); 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,,); 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; # # wild_compare fun # 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,,); connection user1; select current_user(); grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; --error ER_DBACCESS_DENIED_ERROR grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; # # NO_AUTO_CREATE_USER mode # set @@sql_mode='NO_AUTO_CREATE_USER'; select @@sql_mode; # # GRANT without IDENTIFIED BY does not create new users # --error ER_PASSWORD_NO_MATCH grant select on `my\_1`.* to mysqltest_4@localhost with grant option; 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; show grants for mysqltest_2@localhost; --error ER_NONEXISTING_GRANT show grants for mysqltest_3@localhost; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; # # wild_compare part two - acl_cache # create database mysqltest_1; grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option; connect (user2,localhost,mysqltest_1,,); connection user2; select current_user(); show databases; --error ER_DBACCESS_DENIED_ERROR grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; disconnect user2; connection default; show grants for mysqltest_1@localhost; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; drop database mysqltest_1; flush privileges; # # Bug#6173 One can circumvent missing UPDATE privilege if he has SELECT and # INSERT privilege for table with primary key # 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(); insert into t1 values (1, 'I can''t change it!'); --error ER_TABLEACCESS_DENIED_ERROR update t1 set data='I can change it!' where id = 1; # This should not be allowed since it too require UPDATE privilege. --error ER_TABLEACCESS_DENIED_ERROR insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; select * from t1; 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; --error ER_COLUMNACCESS_DENIED_ERROR grant select (a,b) on t1 to mysqltest_2@localhost; --error ER_TABLEACCESS_DENIED_ERROR grant select on t1 to mysqltest_3@localhost; 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; # # Bug#15775 "drop user" command does not refresh acl_check_hosts # # Create some test users 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 one user drop user mysqltest_3@host3; # This connect failed before fix since the acl_check_hosts list was corrupted by the "drop user" connect (con8,127.0.0.1,root,,test,$MASTER_MYPORT,); disconnect con8; connection default; # Clean up - Drop all of the remaining users at once drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; # Check that it's still possible to connect connect (con9,127.0.0.1,root,,test,$MASTER_MYPORT,); disconnect con9; connection default; # # Bug#16180 Setting SQL_LOG_OFF without SUPER privilege is silently ignored # create database mysqltest_1; grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost; connect (con10,localhost,mysqltest_1,,); connection con10; --error ER_SPECIFIC_ACCESS_DENIED_ERROR set sql_log_off = 1; --error ER_SPECIFIC_ACCESS_DENIED_ERROR set sql_log_bin = 0; 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; # End of 4.1 tests # Create and drop user # set sql_mode='maxdb'; --disable_warnings drop table if exists t1, t2; --enable_warnings create table t1(c1 int); create table t2(c1 int, c2 int); # # Three forms of CREATE USER create user 'mysqltest_1'; --error ER_CANNOT_USER create user '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; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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; show grants for 'mysqltest_1'; show grants for 'mysqltest_2'; # # Drop drop user 'mysqltest_1'; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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; --error ER_NONEXISTING_GRANT show grants for 'mysqltest_1'; # # Rename rename user 'mysqltest_2' to 'mysqltest_1'; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; 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; show grants for 'mysqltest_1'; drop user 'mysqltest_1', 'mysqltest_3'; --error ER_CANNOT_USER drop user 'mysqltest_1'; # # Cleanup drop table t1, t2; # # Add a stray record insert into mysql.db set user='mysqltest_1', db='%', host='%'; flush privileges; --error ER_NONEXISTING_GRANT show grants for 'mysqltest_1'; --error ER_REVOKE_GRANTS revoke all privileges, grant option from 'mysqltest_1'; drop user 'mysqltest_1'; select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user; # # Add a stray record insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; flush privileges; --error ER_NONEXISTING_GRANT show grants for 'mysqltest_1'; 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; # # Add a stray record insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; flush privileges; --error ER_NONEXISTING_GRANT show grants for 'mysqltest_1'; 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; # # Handle multi user lists 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'; --error ER_CANNOT_USER drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a'; # # Let one of multiple users fail create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; --error ER_CANNOT_USER create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a'; --error ER_CANNOT_USER rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b'; drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; --error ER_CANNOT_USER drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b'; # # Obsolete syntax has been dropped create user 'mysqltest_2' identified by 'Mysqltest-2'; --error ER_PARSE_ERROR drop user 'mysqltest_2' identified by 'Mysqltest-2'; drop user 'mysqltest_2'; # # Strange user names create user '%@b'@'b'; show grants for '%@b'@'b'; grant select on mysql.* to '%@b'@'b'; show grants for '%@b'@'b'; rename user '%@b'@'b' to '%@a'@'a'; --error ER_NONEXISTING_GRANT show grants for '%@b'@'b'; show grants for '%@a'@'a'; drop user '%@a'@'a'; # # CREATE USER privilege is enough # create user mysqltest_2@localhost; grant create user on *.* to mysqltest_2@localhost; connect (user3,localhost,mysqltest_2,,); connection user3; --error ER_TABLEACCESS_DENIED_ERROR select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; create user mysqltest_A@'%'; rename user mysqltest_A@'%' to mysqltest_B@'%'; drop user mysqltest_B@'%'; disconnect user3; connection default; drop user mysqltest_2@localhost; # # INSERT/UPDATE/DELETE is ok too create user mysqltest_3@localhost; grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; connect (user4,localhost,mysqltest_3,,); connection user4; show grants; --error ER_TABLEACCESS_DENIED_ERROR select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; insert into mysql.user 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; # # Bug#3309 Test IP addresses with netmask 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(); select * from t1; 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; # # Bug#12302 Hostname resolution preventing password changes # 'SET PASSWORD = ...' didn't work if connecting hostname != # hostname the current user is authenticated as. Note that a test for this # was also added to the test above. # 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(); set password = password('changed'); disconnect b12302; connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; 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(); set password = password('changed'); disconnect b12302_2; connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; 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; --source include/add_anonymous_users.inc # But anonymous users can't change their password connect (n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection n5; --error ER_PASSWORD_ANONYMOUS_USER set password = password("changed"); disconnect n5; connection default; --source include/delete_anonymous_users.inc # Bug#12423 "Deadlock when doing FLUSH PRIVILEGES and GRANT in # multi-threaded environment". We should be able to execute FLUSH # PRIVILEGES and SET PASSWORD simultaneously with other account # management commands (such as GRANT and REVOKE) without causing # deadlocks. To achieve this we should ensure that all account # management commands take table and internal locks in the same order. connect (con2root,localhost,root,,); connect (con3root,localhost,root,,); # Check that we can execute FLUSH PRIVILEGES and GRANT simultaneously # This will check that locks are taken in proper order during both # user/db-level and table/column-level privileges reloading. connection default; lock table mysql.user write; connection con2root; send flush privileges; connection con3root; send grant all on *.* to 'mysqltest_1'@'localhost'; connection default; unlock tables; connection con2root; reap; connection con3root; reap; # Check for simultaneous SET PASSWORD and REVOKE. connection default; lock table mysql.user write; connection con2root; send set password for 'mysqltest_1'@'localhost' = password(''); connection con3root; send revoke all on *.* from 'mysqltest_1'@'localhost'; connection default; unlock tables; connection con2root; reap; connection con3root; reap; connection default; # Clean-up drop user 'mysqltest_1'@'localhost'; disconnect con2root; disconnect con3root; # End of 4.1 tests # # Bug#17279 user with no global privs and with create # priv in db can create databases # 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')); INSERT INTO mysql.db (host, db, user, select_priv) VALUES ('%','TESTDB','mysqltest_1','Y'); FLUSH PRIVILEGES; connect (con1,localhost,mysqltest_1,password,TESTDB); # The user mysqltest_1 should only be allowed access to # database TESTDB, not TEStdb # On system with "lowercase names" we get error "ER_DB_CREATE_EXISTS: Can't create db..." --error ER_DBACCESS_DENIED_ERROR, ER_DB_CREATE_EXISTS create database TEStdb; # Clean-up connection default; disconnect con1; 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; # # Bug#13310 incorrect user parsing by SP # 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); --disable_warnings DROP FUNCTION IF EXISTS f2; --enable_warnings delimiter //; CREATE FUNCTION f2 () RETURNS INT BEGIN DECLARE v INT; SELECT s1 FROM t2 INTO v; RETURN v; END// delimiter ;// SELECT f2(); 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; # # Bug#25578 CREATE TABLE LIKE does not require any privileges on source table # --disable_warnings drop database if exists mysqltest_1; drop database if exists mysqltest_2; --enable_warnings --error 0,ER_CANNOT_USER 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 as user with all rights on mysqltest_1 but with no rights on mysqltest_2. connect (user1,localhost,mysqltest_u1,,mysqltest_1); connection user1; # As expected error is emitted --error ER_TABLEACCESS_DENIED_ERROR show create table mysqltest_2.t1; # This should emit error as well --error ER_TABLEACCESS_DENIED_ERROR create table t1 like mysqltest_2.t1; # Now let us check that SELECT privilege on the source is enough connection default; grant select on mysqltest_2.t1 to mysqltest_u1@localhost; connection user1; show create table mysqltest_2.t1; create table t1 like mysqltest_2.t1; # Clean-up connection default; disconnect user1; use test; drop database mysqltest_1; drop database mysqltest_2; drop user mysqltest_u1@localhost; # # Bug#18660 Can't grant any privileges on single table in database # with underscore char # grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; grant usage on *.* to mysqltest_2@localhost; connect (con18600_1,localhost,mysqltest_1,,); 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,,); connection con3; --error ER_DBACCESS_DENIED_ERROR create database mysqltest_3; use mysqltest_1; create table t2(f1 int); select * from t1; 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; # # Bug#30468 column level privileges not respected when joining tables # 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,,); connection conn1; USE db1; --error ER_COLUMNACCESS_DENIED_ERROR SELECT c FROM t2; --error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM t2; --error ER_COLUMNACCESS_DENIED_ERROR SELECT * FROM t1 JOIN t2 USING (b); connection default; disconnect conn1; USE test; DROP TABLE db1.t1, db1.t2; DROP USER mysqltest1@localhost; DROP DATABASE db1; --echo End of 5.0 tests # # Bug #48319: Server crashes on "GRANT/REVOKE ... TO CURRENT_USER" # # work out who we are. 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; # show current privs. SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; # toggle INSERT 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; # show that GRANT ... TO CURRENT_USER() no longer crashes GRANT INSERT ON *.* TO CURRENT_USER(); SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; # show that GRANT ... TO CURRENT_USER() IDENTIFIED BY ... works now GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; 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; FLUSH PRIVILEGES; USE test; --echo End of 5.1 tests --echo --echo # -- --echo # -- Bug#11746602: 27480 - Extend CREATE TEMPORARY TABLES privilege to --echo # -- allow temp table operations --echo # -- --echo # -- Bug#12771903: User with create temporary tables priv only has full --echo # -- access to a regular table --echo # -- --echo --echo ############################################################################ --echo # Setup environment. --echo ########################################################################### --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; DROP DATABASE IF EXISTS mysqltest_db2; --enable_warnings CREATE DATABASE mysqltest_db1; CREATE DATABASE mysqltest_db2; --echo # mysqltest_u1@localhost has CREATE_TMP_ACL, FILE_ACL and EXECUTE_ACL only --echo # (EXECUTE_ACL is needed to call p0, and FILE_ACL is needed for SELECT --echo # OUTFILE/LOAD DATA INFILE). GRANT FILE ON *.* TO mysqltest_u1@localhost; GRANT CREATE TEMPORARY TABLES, EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; --echo # 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; --echo # mysqltest_u3@localhost has CREATE_TMP_ACL & EXECUTE_ACL. --echo # 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; --echo # mysqltest_u4@localhost has only EXECUTE_ACL. --echo # We need this user to check that once created temporary tables --echo # are accessible by anyone. GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u4@localhost; --echo # mysqltest_u5@localhost has CREATE_TMP_ACL and SELECT_ACL, UPDATE_ACL, --echo # DELETE_ACL on mysqltest_db1; and only CREATE_TMP_ACL on mysqltest_db2. --echo # 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; --echo # Create stored routine to test how privilege checking is done for its --echo # arguments. CREATE PROCEDURE mysqltest_db1.p0(i INT) SELECT i; --echo # 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; --echo # 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); --echo ########################################################################### --echo # Check that CREATE_TMP_ACL is enough to issue almost any supported --echo # SQL-statement against temporary tables (loosely follow order in --echo # sql_command enum). --echo ########################################################################### --connect (con1,localhost,mysqltest_u1,,mysqltest_db1) --echo # --echo # Variants of CREATE TABLE. --echo # 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; --echo # Check that we do *not* allow creation of MERGE table with underlying --echo # temporary table without additional privileges. CREATE TEMPORARY TABLE t5(a INT) ENGINE = MyISAM; --error ER_TABLEACCESS_DENIED_ERROR CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (t5); --echo # Check that we allow creation of MERGE table with no underlying table --echo # without additional privileges. CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (); --echo # --echo # SELECT. --echo # INSERT INTO t1 VALUES (1), (2), (3); SELECT * FROM t1 ORDER BY a; --echo # --echo # CREATE/DROP INDEX. --echo # CREATE INDEX idx1 ON t3(b); DROP INDEX idx1 ON t3; --echo # --echo # ALTER TABLE. --echo # ALTER TABLE t4 ADD COLUMN b INT; --echo # Check that we allow altering of MERGE table with no underlying --echo # without additional privileges. ALTER TABLE t6 UNION = (); --echo # Check that we do *not* allow altering of MERGE table with underlying --echo # temporary table without additional privileges. --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t6 UNION = (t5); --echo # --echo # Simple INSERT and INSERT ... SELECT. --echo # INSERT INTO t1 VALUES (4); INSERT INTO t2 SELECT a FROM t1; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; --echo # --echo # UPDATE and multi-UPDATE. --echo # 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; --echo # --echo # DELETE and multi-DELETE. --echo # 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; --echo # --echo # TRUNCATE TABLE. --echo # TRUNCATE TABLE t1; SELECT * FROM t1 ORDER BY a; --echo # --echo # SHOW COLUMNS/DESCRIBE and SHOW KEYS. --echo # SHOW COLUMNS FROM t1; SHOW KEYS FROM t3; --echo # --echo # SHOW CREATE TABLE. --echo # SHOW CREATE TABLE t1; --echo # --echo # LOAD DATA INFILE (also SELECT INTO OUTFILE). --echo # INSERT INTO t1 VALUES (1), (2), (3); --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --eval SELECT a INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug27480.txt' FROM t1 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug27480.txt' INTO TABLE t1 --remove_file $MYSQLTEST_VARDIR/tmp/bug27480.txt SELECT * FROM t1 ORDER BY a; --echo # --echo # SET. --echo # SET @a := (SELECT COUNT(*) FROM t1); SELECT @a; --echo # --echo # LOCK TABLES. --echo # LOCK TABLES t1 READ; UNLOCK TABLES; --echo # --echo # CHECK/REPAIR/ANALYZE/OPTIMIZE and CHECKSUM. --echo # ANALYZE TABLE t1; CHECK TABLE t1; OPTIMIZE TABLE t1; REPAIR TABLE t1; --echo # --echo # REPLACE and REPLACE ... SELECT. --echo # 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; SELECT * FROM t3 ORDER BY a; --echo # --echo # CACHE and LOAD INDEX. --echo # CACHE INDEX t3 IN keycache1; LOAD INDEX INTO CACHE t3; --echo # --echo # RENAME (doesn't work for temporary tables, thus should fail). --echo # --error ER_TABLEACCESS_DENIED_ERROR RENAME TABLE t3 TO t3_1; --echo # --echo # HANDLER OPEN/READ/CLOSE. --echo # HANDLER t1 OPEN; HANDLER t1 READ NEXT; HANDLER t1 CLOSE; --echo # --echo # DO. --echo # DO (SELECT COUNT(*) FROM t1); --echo # --echo # CHECKSUM TABLE. --echo # DELETE FROM t1; CHECKSUM TABLE t1; --echo # --echo # CALL. --echo # CALL p0((SELECT COUNT(*) FROM t1)); --echo # --echo # PREPARE, EXECUTE and DEALLOCATE. --echo # PREPARE stmt1 FROM 'SELECT * FROM t1 ORDER BY a'; PREPARE stmt2 FROM 'SELECT * FROM t2 ORDER BY a'; EXECUTE stmt1; EXECUTE stmt2; DEALLOCATE PREPARE stmt1; DEALLOCATE PREPARE stmt2; --echo # --echo # DROP TABLE and DROP TEMPORARY TABLE. --echo # DROP TABLE t1; CREATE TEMPORARY TABLE t1(a INT); DROP TEMPORARY TABLE t1; --echo ########################################################################### --echo # - Check that even having all privileges but CREATE_TMP_ACL is not enough --echo # to create temporary tables. --echo # - Check that creation/working with temporary tables is possible via --echo # SUID-stored-routines. --echo # - Check that even outside of SUID context we can access temporary --echo # table once it is created. --echo ########################################################################### --connect (con2,localhost,mysqltest_u2,,mysqltest_db1) --error ER_DBACCESS_DENIED_ERROR CREATE TEMPORARY TABLE t2(a INT); CALL p1(); CALL p2(); CALL p3(); --echo # Check that once table is created it can be accessed even --echo # 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; DROP TEMPORARY TABLE t4; --echo ########################################################################### --echo # - Check that once table is created it can be accessed from within any --echo # context, even by user without any privileges on tables. --echo ########################################################################### --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; DROP TEMPORARY TABLE t4; --echo ########################################################################### --echo # Check special case for MERGE-tables: --echo # - CREATE_TMP_ACL is required to create a temporary merge table; --echo # - SELECT_ACL, UPDATE_ACL and DELETE_ACL are required to include --echo # a temporary table into the underlying-table-list. --echo ########################################################################### --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); --error ER_TABLEACCESS_DENIED_ERROR ALTER TABLE t10 UNION = (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; --echo ########################################################################### --echo # That's all. Cleanup. --echo ########################################################################### --connection default --echo # 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; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc