3532 Dmitry Lenev 2011-03-26
Patch for Bug#11746602 (27480: Extend CREATE TEMPORARY TABLES
privilege to allow temp table operations).
The problem was that user with CREATE TEMPORARY TABLES
privilege which was able to create temporary table, needed
additional privileges which also applied to non-temporary
tables to do any useful work on it. As result it was
impossible to allow user to work with temporary tables
without also granting extra privileges on normal tables.
The idea of this patch is to allow any relevant operation
on a temporary table which already exists. Creation of
temporary table still requires CREATE TEMPORARY TABLES
privilege on database in which this table to be created.
@ mysql-test/include/handler.inc
Adjusted test case to the fact that now HANDLER OPEN pre-opens
temporary tables first and only after that checks if it is
called under LOCK TABLES.
@ mysql-test/r/flush_read_lock.result
Adjusted test case to the fact that DROP TABLE no longer
acquires metadata locks if it is going to drop temporary table
and thus doesn't conflict with global read lock in this case.
@ mysql-test/r/grant2.result
Added test coverage for privilege handling for temporary
tables (i.e. for bug#11746602 "27480: Extend CREATE TEMPORARY
TABLES privilege to allow temp table operations").
@ mysql-test/r/handler_innodb.result
Adjusted test case to the fact that now HANDLER OPEN pre-opens
temporary tables first and only after that checks if it is
called under LOCK TABLES.
@ mysql-test/r/handler_myisam.result
Adjusted test case to the fact that now HANDLER OPEN pre-opens
temporary tables first and only after that checks if it is
called under LOCK TABLES.
@ mysql-test/r/merge.result
Since ALTER TABLE now pre-opens temporary table for UNION
clause, ALTER TABLE temp_table UNION=(..., temp_table) is no
longer legal. Adjusted test case to avoid using this construct.
@ mysql-test/t/flush_read_lock.test
Adjusted test case to the fact that DROP TABLE no longer
acquires metadata locks if it is going to drop temporary table
and thus doesn't conflict with global read lock in this case.
@ mysql-test/t/grant2.test
Added test coverage for privilege handling for temporary
tables (i.e. for bug#11746602 "27480: Extend CREATE TEMPORARY
TABLES privilege to allow temp table operations").
@ mysql-test/t/merge.test
Since ALTER TABLE now pre-opens temporary table for UNION
clause, ALTER TABLE temp_table UNION=(..., temp_table) is no
longer legal. Adjusted test case to avoid using this construct.
@ sql/sql_acl.cc
Changed check_grant() to skip its and further privilege checks
on pre-opened temporary tables. This is achieved by
automatically adding all relevant table-level privileges to
the set of granted privileges for every temporary table.
@ sql/sql_acl.h
Added define for set of table-level privileges which are
relevant for temporary tables.
@ sql/sql_alter.cc
Changed code checking privileges for tables from UNION clause
of ALTER TABLE. Now we pre-open temporary tables for this
clause in order properly check privileges for them (or rather
skip privilege checking).
@ sql/sql_base.cc
Now drop_temporary_table() assumes that temporary table to be
dropped was pre-opened using table list element which it gets
as parameter. Adjusted code accordingly.
@ sql/sql_base.h
Adjusted is_temporary_table() function to ignore views and
schema tables so it can properly differentiate real temporary
tables from ones used in view and information schema
implementation.
@ sql/sql_handler.cc
Removed comment which is no longer relevant.
@ sql/sql_insert.cc
Since drop_temporary_table() function now expects that table
to be dropped is pre-opened we can no longer use it to drop
temporary table which we have created but failed to open.
Replaced call to this function with assert since such
situation should not be possible anyway.
@ sql/sql_parse.cc
- Now we pre-open temporary tables for DROP TABLE and HANDLER
OPEN statements. This allows correctly handle (i.e. ignore)
privilege checks in cases when we are going to drop or open
handler for temporary table. As a consequence DROP TABLE now
needs to close open handlers for tables to be dropped before
this pre-opening happens.
- Moved handling of privilege checks for UNION clause of CREATE
TABLE statement inside of create_table_precheck() function.
Adjusted the latter to correctly handle privilege checking in
cases when UNION clause contains temporary tables. Also we
no longer require INSERT privilege for CREATE TEMPORARY TABLE
... SELECT statement. It is enough to have CREATE TEMPORARY
privilege for it.
- Introduced lock_tables_precheck() function which allows to
skip temporary tables when checking privileges for LOCK
TABLES statement (we can't handle LOCK TABLES privilege in
the same way as other privileges for temporary tables as it
is a db-level privilege).
- Changed code in check_show_access() to take into account
temporary tables when checking privileges for SHOW FIELDS
and SHOW KEYS statements.
- Adjusted code in multi_delete_precheck() to update elements
of auxiliary table list from corresponding elements of main
table list. This allows to correctly distinguish temporary
tables when auxiliary table list is used for privilege
checking.
@ sql/sql_prepare.cc
Checking of privileges on tables from UNION clause of CREATE
TABLE statement has been moved into create_table_precheck()
function.
@ sql/sql_table.cc
Changed DROP TABLES implementation to rely on the fact that
temporary tables to be dropped are pre-opened. Pre-opening
has to be done in any case in order to correctly skip those
tables during privilege check for this statement.
modified:
mysql-test/include/handler.inc
mysql-test/r/flush_read_lock.result
mysql-test/r/grant2.result
mysql-test/r/handler_innodb.result
mysql-test/r/handler_myisam.result
mysql-test/r/merge.result
mysql-test/t/flush_read_lock.test
mysql-test/t/grant2.test
mysql-test/t/merge.test
sql/sql_acl.cc
sql/sql_acl.h
sql/sql_alter.cc
sql/sql_base.cc
sql/sql_base.h
sql/sql_handler.cc
sql/sql_insert.cc
sql/sql_parse.cc
sql/sql_prepare.cc
sql/sql_table.cc
3531 Alexander Nozdrin 2011-03-24
Restore default set of MTR test suites running in PB.
modified:
mysql-test/collections/mysql-trunk-bug27480.push
=== modified file 'mysql-test/include/handler.inc'
--- a/mysql-test/include/handler.inc 2010-11-18 16:34:56 +0000
+++ b/mysql-test/include/handler.inc 2011-03-26 10:56:27 +0000
@@ -804,6 +804,7 @@ create table t1 (a int, key a (a));
insert into t1 (a) values (1), (2), (3), (4), (5);
create table t2 (a int, key a (a)) select * from t1;
create temporary table t3 (a int, key a (a)) select * from t2;
+create temporary table t4 like t3;
handler t1 open;
handler t2 open;
handler t3 open;
@@ -818,8 +819,10 @@ handler t1 open;
handler t1 read next;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
handler t2 close;
---error ER_LOCK_OR_ACTIVE_TRANSACTION
+--error ER_CANT_REOPEN_TABLE
handler t3 open;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+handler t4 open;
--echo # After UNLOCK TABLES handlers should be around and
--echo # we should be able to continue reading through them.
unlock tables;
@@ -829,7 +832,7 @@ handler t2 read next;
handler t2 close;
handler t3 read next;
handler t3 close;
-drop temporary table t3;
+drop temporary tables t3, t4;
--echo #
--echo # Other operations that implicitly close handler:
--echo #
=== modified file 'mysql-test/r/flush_read_lock.result'
--- a/mysql-test/r/flush_read_lock.result 2010-11-11 17:11:05 +0000
+++ b/mysql-test/r/flush_read_lock.result 2011-03-26 10:56:27 +0000
@@ -543,11 +543,10 @@ Success: Was not able to run 'drop table
Success: 'drop table t2_base' is blocked by FTWRL active in another connection.
Success: FTWRL is blocked when 'drop table t2_base' is active in another connection.
# 13.1.b) DROP TABLES which affects only temporary tables
-# in theory can be compatible with FTWRL.
-# In practice it is not yet.
-Success: Was not able to run 'drop table t2_temp' under FTWRL.
-Success: 'drop table t2_temp' is blocked by FTWRL active in another connection.
-Success: FTWRL is blocked when 'drop table t2_temp' is active in another connection.
+# is compatible with FTWRL.
+Success: Was able to run 'drop table t2_temp' under FTWRL.
+Success: Was able to run 'drop table t2_temp' with FTWRL active in another connection.
+Success: Was able to run FTWRL while 'drop table t2_temp' was active in another connection.
#
# 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL.
Success: Was able to run 'drop temporary table t2_temp' under FTWRL.
=== modified file 'mysql-test/r/grant2.result'
--- a/mysql-test/r/grant2.result 2010-08-09 08:32:50 +0000
+++ b/mysql-test/r/grant2.result 2011-03-26 10:56:27 +0000
@@ -472,3 +472,330 @@ 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
+# --
+
+############################################################################
+# Setup environment.
+###########################################################################
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+# 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;
+# 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;
+###########################################################################
+# Check that CREATE_TMP_ACL is enough to issue 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;
+# Also check that we 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);
+#
+# 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 underlying
+# temporary table without additional privileges.
+ALTER TABLE t6 UNION = ();
+ALTER TABLE t6 UNION = (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;
+###########################################################################
+# That's all. Cleanup.
+###########################################################################
+
+# -- connection: default
+# -- disconnect con1
+# All remaining temporary tables are automatically dropped.
+# -- disconnect con2
+# -- disconnect con3
+SET GLOBAL keycache1.key_buffer_size = 0;
+DROP DATABASE mysqltest_db1;
+DROP USER mysqltest_u1@localhost;
+DROP USER mysqltest_u2@localhost;
+DROP USER mysqltest_u3@localhost;
+DROP USER mysqltest_u4@localhost;
=== modified file 'mysql-test/r/handler_innodb.result'
--- a/mysql-test/r/handler_innodb.result 2010-11-18 16:34:56 +0000
+++ b/mysql-test/r/handler_innodb.result 2011-03-26 10:56:27 +0000
@@ -784,6 +784,7 @@ create table t1 (a int, key a (a));
insert into t1 (a) values (1), (2), (3), (4), (5);
create table t2 (a int, key a (a)) select * from t1;
create temporary table t3 (a int, key a (a)) select * from t2;
+create temporary table t4 like t3;
handler t1 open;
handler t2 open;
handler t3 open;
@@ -799,6 +800,8 @@ ERROR HY000: Can't execute the given com
handler t2 close;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
handler t3 open;
+ERROR HY000: Can't reopen table: 't3'
+handler t4 open;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
# After UNLOCK TABLES handlers should be around and
# we should be able to continue reading through them.
@@ -815,7 +818,7 @@ handler t3 read next;
a
1
handler t3 close;
-drop temporary table t3;
+drop temporary tables t3, t4;
#
# Other operations that implicitly close handler:
#
=== modified file 'mysql-test/r/handler_myisam.result'
--- a/mysql-test/r/handler_myisam.result 2011-01-17 16:27:07 +0000
+++ b/mysql-test/r/handler_myisam.result 2011-03-26 10:56:27 +0000
@@ -782,6 +782,7 @@ create table t1 (a int, key a (a));
insert into t1 (a) values (1), (2), (3), (4), (5);
create table t2 (a int, key a (a)) select * from t1;
create temporary table t3 (a int, key a (a)) select * from t2;
+create temporary table t4 like t3;
handler t1 open;
handler t2 open;
handler t3 open;
@@ -797,6 +798,8 @@ ERROR HY000: Can't execute the given com
handler t2 close;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
handler t3 open;
+ERROR HY000: Can't reopen table: 't3'
+handler t4 open;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
# After UNLOCK TABLES handlers should be around and
# we should be able to continue reading through them.
@@ -813,7 +816,7 @@ handler t3 read next;
a
1
handler t3 close;
-drop temporary table t3;
+drop temporary tables t3, t4;
#
# Other operations that implicitly close handler:
#
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result 2011-02-15 18:21:37 +0000
+++ b/mysql-test/r/merge.result 2011-03-26 10:56:27 +0000
@@ -2716,13 +2716,14 @@ DROP TABLE tm1, t1, t2, t3, t4, t5;
# Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
#
CREATE TEMPORARY TABLE t1 (c1 INT);
-ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1);
+CREATE TEMPORARY TABLE t2 (c1 INT);
+ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists, t2);
OPTIMIZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 optimize Error Table 'test.t_not_exists' doesn't exist
test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.t1 optimize error Corrupt
-DROP TABLE t1;
+DROP TABLE t1, t2;
#
# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
# More tests with TEMPORARY MERGE table and permanent children.
=== modified file 'mysql-test/t/flush_read_lock.test'
--- a/mysql-test/t/flush_read_lock.test 2010-11-12 13:57:08 +0000
+++ b/mysql-test/t/flush_read_lock.test 2011-03-26 10:56:27 +0000
@@ -706,11 +706,10 @@ let $cleanup_stmt1= create table t2_base
--source include/check_ftwrl_incompatible.inc
--echo # 13.1.b) DROP TABLES which affects only temporary tables
---echo # in theory can be compatible with FTWRL.
---echo # In practice it is not yet.
+--echo # is compatible with FTWRL.
let $statement= drop table t2_temp;
-let $cleanup_stmt1= create temporary table t2_temp(j int);
---source include/check_ftwrl_incompatible.inc
+let $cleanup_stmt= create temporary table t2_temp(j int);
+--source include/check_ftwrl_compatible.inc
--echo #
--echo # 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL.
=== modified file 'mysql-test/t/grant2.test'
--- a/mysql-test/t/grant2.test 2010-08-09 08:32:50 +0000
+++ b/mysql-test/t/grant2.test 2011-03-26 10:56:27 +0000
@@ -667,5 +667,308 @@ 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
+
+--echo ############################################################################
+--echo # Setup environment.
+--echo ###########################################################################
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+--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 # 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;
+
+--echo ###########################################################################
+--echo # Check that CREATE_TMP_ACL is enough to issue any supported SQL-statement
+--echo # against temporary tables (loosely follow order in sql_command enum).
+--echo ###########################################################################
+
+--echo
+--echo # -- connect con1, mysqltest_u1@localhost, mysqltest_db1
+--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 # Also check that we allow creation of MERGE table with underlying
+--echo # temporary table without additional privileges.
+CREATE TEMPORARY TABLE t5(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (t5);
+
+--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 underlying
+--echo # temporary table without additional privileges.
+ALTER TABLE t6 UNION = ();
+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 ###########################################################################
+
+--echo
+--echo # -- connect con2, mysqltest_u2@localhost, mysqltest_db1
+--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 ###########################################################################
+
+--echo
+--echo # -- connect con3, mysqltest_u4@localhost, mysqltest_db1
+--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 # That's all. Cleanup.
+--echo ###########################################################################
+
+--echo
+--echo # -- connection: default
+--connection default
+
+--echo # -- disconnect con1
+--echo # All remaining temporary tables are automatically dropped.
+--disconnect con1
+
+--echo # -- disconnect con2
+--disconnect con2
+
+--echo # -- disconnect con3
+--disconnect con3
+
+SET GLOBAL keycache1.key_buffer_size = 0;
+DROP DATABASE mysqltest_db1;
+DROP USER mysqltest_u1@localhost;
+DROP USER mysqltest_u2@localhost;
+DROP USER mysqltest_u3@localhost;
+DROP USER mysqltest_u4@localhost;
+
+
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
=== modified file 'mysql-test/t/merge.test'
--- a/mysql-test/t/merge.test 2011-02-15 18:21:37 +0000
+++ b/mysql-test/t/merge.test 2011-03-26 10:56:27 +0000
@@ -2194,9 +2194,10 @@ DROP TABLE tm1, t1, t2, t3, t4, t5;
--echo # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
--echo #
CREATE TEMPORARY TABLE t1 (c1 INT);
-ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1);
+CREATE TEMPORARY TABLE t2 (c1 INT);
+ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists, t2);
OPTIMIZE TABLE t1;
-DROP TABLE t1;
+DROP TABLE t1, t2;
--echo #
--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2011-02-18 09:56:51 +0000
+++ b/sql/sql_acl.cc 2011-03-26 10:56:27 +0000
@@ -4617,6 +4617,20 @@ bool check_grant(THD *thd, ulong want_ac
}
continue;
}
+
+ if (is_temporary_table(tl))
+ {
+ /*
+ If this table list element corresponds to a pre-opened temporary
+ table skip checking of all relevant table-level privileges for it.
+ Note that during creation of temporary table we still need to check
+ if user has CREATE_TMP_ACL.
+ */
+ tl->grant.privilege|= TMP_TABLE_ACLS;
+ tl->grant.want_privilege= 0;
+ continue;
+ }
+
GRANT_TABLE *grant_table= table_hash_search(sctx->host, sctx->ip,
tl->get_db_name(),
sctx->priv_user,
=== modified file 'sql/sql_acl.h'
--- a/sql/sql_acl.h 2010-10-21 09:49:16 +0000
+++ b/sql/sql_acl.h 2011-03-26 10:56:27 +0000
@@ -95,6 +95,14 @@
CREATE_ACL | DROP_ACL | ALTER_ACL | INDEX_ACL | \
TRIGGER_ACL | REFERENCES_ACL | GRANT_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL)
+/**
+ Table-level privileges which are automatically "granted" to everyone on
+ existing temporary tables (CREATE_ACL is necessary for ALTER ... RENAME).
+*/
+#define TMP_TABLE_ACLS \
+(SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \
+ INDEX_ACL | ALTER_ACL)
+
/*
Defines to change the above bits to how things are stored in tables
This is needed as the 'host' and 'db' table is missing a few privileges
=== modified file 'sql/sql_alter.cc'
--- a/sql/sql_alter.cc 2010-10-05 09:15:51 +0000
+++ b/sql/sql_alter.cc 2011-03-26 10:56:27 +0000
@@ -16,6 +16,7 @@
#include "sql_parse.h" // check_access
#include "sql_table.h" // mysql_alter_table,
// mysql_exchange_partition
+#include "sql_base.h" // open_temporary_tables
#include "sql_alter.h"
bool Sql_cmd_alter_table::execute(THD *thd)
@@ -64,10 +65,31 @@ bool Sql_cmd_alter_table::execute(THD *t
DBUG_RETURN(TRUE); /* purecov: inspected */
/* If it is a merge table, check privileges for merge children. */
- if (create_info.merge_list.first &&
- check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
- create_info.merge_list.first, FALSE, UINT_MAX, FALSE))
- DBUG_RETURN(TRUE);
+ if (create_info.merge_list.first)
+ {
+ /* Pre-open underlying temporary tables to simplify privilege checking. */
+ if (open_temporary_tables(thd, create_info.merge_list.first))
+ DBUG_RETURN(TRUE);
+
+ if (check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
+ create_info.merge_list.first, FALSE, UINT_MAX, FALSE))
+ DBUG_RETURN(TRUE);
+
+ /*
+ Since one of tables in UNION clause might be already used by table being
+ altered we need to close all tables opened for UNION checking to allow
+ normal open of table being altered.
+ To do this we simply close all tables and then open only tables from the
+ main statement's table list.
+ */
+ close_thread_tables(thd);
+
+ for (TABLE_LIST *tl= lex->query_tables; tl; tl= tl->next_global)
+ tl->table= NULL;
+
+ if (open_temporary_tables(thd, lex->query_tables))
+ DBUG_RETURN(TRUE);
+ }
if (check_grant(thd, priv_needed, first_table, FALSE, UINT_MAX, FALSE))
DBUG_RETURN(TRUE); /* purecov: inspected */
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2011-03-09 12:24:36 +0000
+++ b/sql/sql_base.cc 2011-03-26 10:56:27 +0000
@@ -2059,10 +2059,13 @@ TABLE *find_temporary_table(THD *thd,
thd->temporary_tables list, it's impossible to tell here whether
we're dealing with an internal or a user temporary table.
- If is_trans is not null, we return the type of the table:
+ In is_trans out-parameter, we return the type of the table:
either transactional (e.g. innodb) as TRUE or non-transactional
(e.g. myisam) as FALSE.
+ This function assumes that table to be dropped was pre-opened
+ using table list provided.
+
@retval 0 the table was found and dropped successfully.
@retval 1 the table was not found in the list of temporary tables
of this thread
@@ -2071,14 +2074,15 @@ TABLE *find_temporary_table(THD *thd,
int drop_temporary_table(THD *thd, TABLE_LIST *table_list, bool *is_trans)
{
- TABLE *table;
DBUG_ENTER("drop_temporary_table");
DBUG_PRINT("tmptable", ("closing table: '%s'.'%s'",
table_list->db, table_list->table_name));
- if (!(table= find_temporary_table(thd, table_list)))
+ if (!is_temporary_table(table_list))
DBUG_RETURN(1);
+ TABLE *table= table_list->table;
+
/* Table might be in use by some outer statement. */
if (table->query_id && table->query_id != thd->query_id)
{
@@ -2086,8 +2090,7 @@ int drop_temporary_table(THD *thd, TABLE
DBUG_RETURN(-1);
}
- if (is_trans != NULL)
- *is_trans= table->file->has_transactions();
+ *is_trans= table->file->has_transactions();
/*
If LOCK TABLES list is not empty and contains this table,
@@ -2095,6 +2098,7 @@ int drop_temporary_table(THD *thd, TABLE
*/
mysql_lock_remove(thd, thd->lock, table);
close_temporary_table(thd, table, 1, 1);
+ table_list->table= NULL;
DBUG_RETURN(0);
}
=== modified file 'sql/sql_base.h'
--- a/sql/sql_base.h 2011-03-09 12:24:36 +0000
+++ b/sql/sql_base.h 2011-03-26 10:56:27 +0000
@@ -577,7 +577,21 @@ private:
inline bool is_temporary_table(TABLE_LIST *tl)
{
- return tl->table ? (tl->table->s->tmp_table != NO_TMP_TABLE) : FALSE;
+ if (tl->view || tl->schema_table)
+ return FALSE;
+
+ if (!tl->table)
+ return FALSE;
+
+ /*
+ NOTE: 'table->s' might be NULL for specially constructed TABLE
+ instances. See SHOW TRIGGERS for example.
+ */
+
+ if (!tl->table->s)
+ return FALSE;
+
+ return tl->table->s->tmp_table != NO_TMP_TABLE;
}
=== modified file 'sql/sql_handler.cc'
--- a/sql/sql_handler.cc 2011-01-17 16:27:07 +0000
+++ b/sql/sql_handler.cc 2011-03-26 10:56:27 +0000
@@ -287,10 +287,6 @@ bool mysql_ha_open(THD *thd, TABLE_LIST
*/
DBUG_ASSERT(! hash_tables->table);
- /*
- TODO/FIXME: In the upcoming patch we somehow should handle
- situation with privilege check for temporary table.
- */
error= open_temporary_tables(thd, hash_tables);
if (!error)
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2011-02-15 17:36:23 +0000
+++ b/sql/sql_insert.cc 2011-03-26 10:56:27 +0000
@@ -3799,10 +3799,10 @@ static TABLE *create_table_from_items(TH
{
/*
This shouldn't happen as creation of temporary table should make
- it preparable for open. But let us do close_temporary_table() here
- just in case.
+ it preparable for open. Anyway we can't drop temporary table if
+ we are unable to fint it.
*/
- drop_temporary_table(thd, create_table, NULL);
+ DBUG_ASSERT(0);
}
else
{
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-03-09 12:24:36 +0000
+++ b/sql/sql_parse.cc 2011-03-26 10:56:27 +0000
@@ -114,6 +114,7 @@
static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables);
static void sql_kill(THD *thd, ulong id, bool only_kill_query);
+static bool lock_tables_precheck(THD *thd, TABLE_LIST *tables);
const char *any_db="*any*"; // Special symbol for check_access
@@ -455,6 +456,7 @@ void init_update_queries(void)
pre-opening temporary tables for those statements is somewhat custom.
*/
sql_command_flags[SQLCOM_CREATE_TABLE]|= CF_PREOPEN_TMP_TABLES;
+ sql_command_flags[SQLCOM_DROP_TABLE]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_CREATE_INDEX]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_ALTER_TABLE]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_TRUNCATE]|= CF_PREOPEN_TMP_TABLES;
@@ -472,6 +474,7 @@ void init_update_queries(void)
sql_command_flags[SQLCOM_SELECT]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_SET_OPTION]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_DO]|= CF_PREOPEN_TMP_TABLES;
+ sql_command_flags[SQLCOM_HA_OPEN]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_CALL]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_CHECKSUM]|= CF_PREOPEN_TMP_TABLES;
sql_command_flags[SQLCOM_ANALYZE]|= CF_PREOPEN_TMP_TABLES;
@@ -488,6 +491,7 @@ void init_update_queries(void)
have to be closed before emporary tables are pre-opened.
*/
sql_command_flags[SQLCOM_CREATE_TABLE]|= CF_HA_CLOSE;
+ sql_command_flags[SQLCOM_DROP_TABLE]|= CF_HA_CLOSE;
sql_command_flags[SQLCOM_ALTER_TABLE]|= CF_HA_CLOSE;
sql_command_flags[SQLCOM_TRUNCATE]|= CF_HA_CLOSE;
sql_command_flags[SQLCOM_REPAIR]|= CF_HA_CLOSE;
@@ -2396,19 +2400,6 @@ case SQLCOM_PREPARE:
goto end_with_restore_list;
}
- /*
- Pre-open temporary tables from UNION clause to simplify privilege
- checking for them.
- */
- if (lex->create_info.merge_list.elements)
- {
- if (open_temporary_tables(thd, lex->create_info.merge_list.first))
- {
- res= 1;
- goto end_with_restore_list;
- }
- }
-
if ((res= create_table_precheck(thd, select_tables, create_table)))
goto end_with_restore_list;
@@ -3259,8 +3250,7 @@ end_with_restore_list:
if (open_temporary_tables(thd, all_tables))
goto error;
- if (check_table_access(thd, LOCK_TABLES_ACL | SELECT_ACL, all_tables,
- FALSE, UINT_MAX, FALSE))
+ if (lock_tables_precheck(thd, all_tables))
goto error;
thd->variables.option_bits|= OPTION_TABLE_LOCK;
@@ -3757,6 +3747,9 @@ end_with_restore_list:
DBUG_ASSERT(first_table == all_tables && first_table != 0);
if (check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE))
goto error;
+ /* Close temporary tables which were pre-opened for privilege checking. */
+ close_thread_tables(thd);
+ all_tables->table= NULL;
res= mysql_ha_open(thd, first_table, 0);
break;
case SQLCOM_HA_CLOSE:
@@ -4998,10 +4991,10 @@ static bool check_show_access(THD *thd,
DBUG_ASSERT(dst_table);
/*
- TODO/FIXME: In the upcoming patch we somehow should handle
- situation when table in question is a temporary
- table.
+ Open temporary tables to be able to detect them during privilege check.
*/
+ if (open_temporary_tables(thd, dst_table))
+ return TRUE;
if (check_access(thd, SELECT_ACL, dst_table->db,
&dst_table->grant.privilege,
@@ -5016,6 +5009,9 @@ static bool check_show_access(THD *thd,
if (check_grant(thd, SELECT_ACL, dst_table, TRUE, UINT_MAX, FALSE))
return TRUE; /* Access denied */
+ close_thread_tables(thd);
+ dst_table->table= NULL;
+
/* Access granted */
return FALSE;
}
@@ -6773,6 +6769,19 @@ bool multi_delete_precheck(THD *thd, TAB
TABLE_LIST **save_query_tables_own_last= thd->lex->query_tables_own_last;
DBUG_ENTER("multi_delete_precheck");
+ /*
+ Temporary tables are pre-opened in 'tables' list only. Here we need to
+ initialize TABLE instances in 'aux_tables' list.
+ */
+ for (TABLE_LIST *tl= aux_tables; tl; tl= tl->next_global)
+ {
+ if (tl->table)
+ continue;
+
+ if (tl->correspondent_table)
+ tl->table= tl->correspondent_table->table;
+ }
+
/* sql_yacc guarantees that tables and aux_tables are not zero */
DBUG_ASSERT(aux_tables != 0);
if (check_table_access(thd, SELECT_ACL, tables, FALSE, UINT_MAX, FALSE))
@@ -7041,9 +7050,9 @@ bool create_table_precheck(THD *thd, TAB
CREATE TABLE ... SELECT, also require INSERT.
*/
- want_priv= ((lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) ?
- CREATE_TMP_ACL : CREATE_ACL) |
- (select_lex->item_list.elements ? INSERT_ACL : 0);
+ want_priv= (lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) ?
+ CREATE_TMP_ACL :
+ (CREATE_ACL | (select_lex->item_list.elements ? INSERT_ACL : 0));
if (check_access(thd, want_priv, create_table->db,
&create_table->grant.privilege,
@@ -7052,11 +7061,35 @@ bool create_table_precheck(THD *thd, TAB
goto err;
/* If it is a merge table, check privileges for merge children. */
- if (lex->create_info.merge_list.first &&
- check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
- lex->create_info.merge_list.first,
- FALSE, UINT_MAX, FALSE))
- goto err;
+ if (lex->create_info.merge_list.first)
+ {
+ /*
+ Pre-open temporary tables from UNION clause to simplify privilege
+ checking for them.
+ */
+ if (open_temporary_tables(thd, lex->create_info.merge_list.first))
+ goto err;
+
+ if (check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
+ lex->create_info.merge_list.first,
+ FALSE, UINT_MAX, FALSE))
+ goto err;
+
+ /*
+ Since the MERGE table in question might already exist with exactly
+ the same definition we need to close all tables opened for underlying
+ checking to allow normal open of table being created.
+ To do this we simply close all tables and then open only tables from
+ the main statement's table list.
+ */
+ close_thread_tables(thd);
+
+ for (TABLE_LIST *tl= lex->query_tables; tl; tl= tl->next_global)
+ tl->table= NULL;
+
+ if (open_temporary_tables(thd, lex->query_tables))
+ DBUG_RETURN(TRUE);
+ }
if (want_priv != CREATE_TMP_ACL &&
check_grant(thd, want_priv, create_table, FALSE, 1, FALSE))
@@ -7082,6 +7115,35 @@ err:
/**
+ Check privileges for LOCK TABLES statement.
+
+ @param thd Thread context.
+ @param tables List of tables to be locked.
+
+ @retval FALSE - Success.
+ @retval TRUE - Failure.
+*/
+
+static bool lock_tables_precheck(THD *thd, TABLE_LIST *tables)
+{
+ TABLE_LIST *first_not_own_table= thd->lex->first_not_own_table();
+
+ for (TABLE_LIST *table= tables; table != first_not_own_table && table;
+ table= table->next_global)
+ {
+ if (is_temporary_table(table))
+ continue;
+
+ if (check_table_access(thd, LOCK_TABLES_ACL | SELECT_ACL, table,
+ FALSE, 1, FALSE))
+ return TRUE;
+ }
+
+ return FALSE;
+}
+
+
+/**
negate given expression.
@param thd thread handler
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2011-03-09 12:24:36 +0000
+++ b/sql/sql_prepare.cc 2011-03-26 10:56:27 +0000
@@ -1716,14 +1716,6 @@ static bool mysql_test_create_table(Prep
TABLE_LIST *create_table= lex->query_tables;
TABLE_LIST *tables= lex->create_last_non_select_table->next_global;
- if (lex->create_info.merge_list.elements)
- {
- if (open_temporary_tables(thd, lex->create_info.merge_list.first))
- {
- DBUG_RETURN(TRUE);
- }
- }
-
if (create_table_precheck(thd, tables, create_table))
DBUG_RETURN(TRUE);
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2011-03-09 12:24:36 +0000
+++ b/sql/sql_table.cc 2011-03-26 10:56:27 +0000
@@ -2052,8 +2052,6 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
}
}
- mysql_ha_rm_tables(thd, tables);
-
if (!drop_temporary)
{
if (!thd->locked_tables_mode)
@@ -2062,14 +2060,18 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
thd->variables.lock_wait_timeout, 0))
DBUG_RETURN(true);
for (table= tables; table; table= table->next_local)
+ {
+ if (is_temporary_table(table))
+ continue;
+
tdc_remove_table(thd, TDC_RT_REMOVE_ALL, table->db, table->table_name,
false);
+ }
}
else
{
for (table= tables; table; table= table->next_local)
- if (table->open_type != OT_BASE_ONLY &&
- find_temporary_table(thd, table))
+ if (is_temporary_table(table))
{
/*
A temporary table.
@@ -2136,6 +2138,9 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
@note This function assumes that metadata locks have already been taken.
It is also assumed that the tables have been removed from TDC.
+ @note This function assumes that temporary tables to be dropped have
+ been pre-opened using corresponding table list elements.
+
@todo When logging to the binary log, we should log
tmp_tables and transactional tables as separate statements if we
are in a transaction; This is needed to get these tables into the
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (Dmitry.Lenev:3531 to 3532) Bug#11746602 | Dmitry Lenev | 26 Mar |
