| Bug #27480 | Extend CREATE TEMPORARY TABLES privilege to allow temp table operations | ||
|---|---|---|---|
| Submitted: | 27 Mar 2007 18:22 | Modified: | 12 May 2011 17:21 |
| Reporter: | Mark Leith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 5.0,5.1 | OS: | Any (All) |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[27 Mar 2007 18:22]
Mark Leith
[1 May 2008 22:50]
Tim Gokcen
I also feel this change would be worthwhile. In particular, I have an application that INSERTs, SELECTs, and UPDATEs its 'real' tables, but only needs to ALTER its TEMPORARY tables. I appreciate that the 'CREATE TEMPORARY TABLES' privilege also grants the right to DROP the same tables, but my application also needs to ALTER their schema slightly after creation (typically, to add keys in order to speed up their usage). I would prefer to not have to grant my application's user the ALTER privilege in order to prevent the possibility of it being compromised and used to edit the schema of 'normal' tables.
[11 Jun 2008 16:58]
Andy
I'm adding my vote for this change. What sense does it make for a connection based table to allow create and drop, but not select, insert, delete. etc.? It also does not make sense that a user that creates a temporary table needs insert,delete,etc permissions on the entire database to use the table they just created.
[7 Aug 2008 10:56]
Daniel Fiske
I too am throwing in my vote. The current security model for temporary tables doesn't make much sense. As a side note. I found this method for getting around the "issue" (Credit: Dietrich Feist) "One workaround to give users permissions on temporary tables that you don't want to give them on regular tables is the following. We just have to keep in mind that users have the same access rights on temporary tables that they have on all tables in a particular database: 1) create a dedicated database for temporary tables: mysql> CREATE DATABASE tmp; 2) Give your users all the access privileges that they need to create and use temporary tables: mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE TEMPORARY TABLES ON tmp.* TO user@localhost; Be sure that you do not give them CREATE or GRANT privileges! 3) Have you users create all temporary tables in that 'tmp' database instead of the current database: mysql> USE mydb mysql> CREATE TEMPORARY TABLE tmp.dummy SELECT * from mytable; Your users have to explicitly call their temporary tables as tmp.<tablename> in all requests. There is no problem if two users use the same name for a temporary table since they will not be able to see each other's temporary tables. You can also put the 'tmp' database on a dedicated disk."
[7 Aug 2008 10:58]
Daniel Fiske
I too am throwing in my vote. The current security model for temporary tables doesn't make much sense. As a side note. I found this method for getting around the "issue" (Credit: Dietrich Feist) "One workaround to give users permissions on temporary tables that you don't want to give them on regular tables is the following. We just have to keep in mind that users have the same access rights on temporary tables that they have on all tables in a particular database: 1) create a dedicated database for temporary tables: mysql> CREATE DATABASE tmp; 2) Give your users all the access privileges that they need to create and use temporary tables: mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE TEMPORARY TABLES ON tmp.* TO user@localhost; Be sure that you do not give them CREATE or GRANT privileges! 3) Have you users create all temporary tables in that 'tmp' database instead of the current database: mysql> USE mydb mysql> CREATE TEMPORARY TABLE tmp.dummy SELECT * from mytable; Your users have to explicitly call their temporary tables as tmp.<tablename> in all requests. There is no problem if two users use the same name for a temporary table since they will not be able to see each other's temporary tables. You can also put the 'tmp' database on a dedicated disk."
[22 Jun 2009 15:12]
Valeriy Kravchuk
Bug #45653 was marked as a duplicate of this one.
[30 Jun 2009 17:03]
Valeriy Kravchuk
Bug #12437 was marked as a duplicate of this one.
[26 Jul 2009 16:52]
Valeriy Kravchuk
Bug #14401 was marked as a duplicate of this one.
[18 Sep 2009 16:04]
Valeriy Kravchuk
Bug #2317 was marked as a duplicate of this one. It has many other duplicate in turn...
[18 Sep 2009 16:12]
MySQL Verification Team
Consolidated the following duplicates to this bug for tracking purposes: Bug #2317 Bug #16254 Bug #16664 Bug #21100 Bug #40776 Bug #42540
[28 Sep 2009 8:43]
Sergei Golubchik
bug#47576 too
[13 Dec 2009 9:20]
Gabriele Tozzi
As of MySQL 5.1.16 we can no longer TRUNCATE our temporary tables. This breaks our previously wrking applications. Severity should be raised.
[11 Mar 2010 9:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/102963 2969 Alexander Nozdrin 2010-03-11 Patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). The problem was that CREATE TEMPORARY TABLES did not allow SELECT, INSERT INTO, UPDATE, DELETE FROM statements for temporary tables.
[6 Apr 2010 12:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105075 3140 Alexander Nozdrin 2010-04-06 Another preliminary patch for Bug#27480.
[13 May 2010 18:11]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/108273 3018 Alexander Nozdrin 2010-05-13 Preliminary patch for Bug#27480.
[26 May 2010 10:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/109254 3057 Alexander Nozdrin 2010-05-26 Preliminary patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). All tests pass.
[3 Jun 2010 9:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/110067 3092 Alexander Nozdrin 2010-06-03 Preliminary patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). All tests pass.
[16 Aug 2010 17:10]
Garen Parham
Any movement on this? Doesn't look like it's made it into a release.
[19 Aug 2010 17:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116285 3114 Alexander Nozdrin 2010-08-19 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #1: move a piece of code that sets TABLE instance attributes after it was successfully opened into a separate function. This function will be reused in the following patches.
[19 Aug 2010 17:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116286 3115 Alexander Nozdrin 2010-08-19 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #2: split find_temporary_table() into three forms.
[20 Aug 2010 12:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116388 3119 Alexander Nozdrin 2010-08-20 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #3: rename open_temporary_table() to open_temporary_table_from_disk(). open_temporary_table() will be introduced in following patches to open temporary tables.
[20 Aug 2010 16:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116431 3120 Alexander Nozdrin 2010-08-20 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #4: - move opening of temporary tables out of open_table(); - make open_table() to work with base tables and views only. It will be renamed to open_base_table_or_view() in a follow-up patch. - introduce open_temporary_table() to open temporary tables (similar to open_table()); - introduce open_and_process_temporary_table() to fully prepare temporary tables for use (similar to open_and_process_table()); - introduce a new "command flag" (CF_OPEN_TMP_TABLES) to mark statements that work with temporary tables, thus temporary tables should be opened for those statements; - open temporary tables in a unified way in the beginning of the statements marked with CF_OPEN_TMP_TABLES flag; - introduce a new "command flag" (CF_HA_CLOSE) to mark statements for which open handlers (by HANDLER OPEN) should be closed; - close open handlers in a unified way in the beginning of the statements marked with CF_HA_CLOSE flag.
[20 Aug 2010 16:10]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116432 3121 Alexander Nozdrin 2010-08-20 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #5: rename open_table() to open_base_table_or_view().
[20 Aug 2010 16:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116435 3122 Alexander Nozdrin 2010-08-20 Fix for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). The problem was that CREATE TEMPORARY TABLES privilege was enugh only to create temporary tables. All operations such as SELECT, INSERT, UPDATE, etc. required a separate privileges. The fix is to allow all operations on temporary tables if CREATE TEMPORARY TABLES privilege is granted.
[27 Aug 2010 8:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116961 3123 Alexander Nozdrin 2010-08-27 [merge] Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #1. Move a piece of code that initialiazes TABLE instance after it was successfully opened into a separate function. This function will be reused in the following patches.
[27 Aug 2010 8:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116962 3123 Alexander Nozdrin 2010-08-27 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #1. Move a piece of code that initialiazes TABLE instance after it was successfully opened into a separate function. This function will be reused in the following patches.
[27 Aug 2010 9:22]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116967 3124 Alexander Nozdrin 2010-08-27 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #2. Introduce a new form of find_temporary_table() function: find_temporary_table() by a table key. It will be used in further patches. Replace find_temporary_table(table_list->db, table_list->name) by more appropiate find_temporary_table(table_list) across the codebase.
[27 Aug 2010 11:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116984 3128 Alexander Nozdrin 2010-08-27 Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations) -- prerequisite patch #3. Rename open_temporary_table() to open_table_uncached(). open_temporary_table() will be introduced in following patches to open temporary tables for a statement.
[27 Oct 2010 16:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/122136 3102 Alexander Nozdrin 2010-10-27 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations): - move opening of temporary tables out of open_table(); - make open_table() to work with base tables and views only. It will be renamed to open_base_table_or_view() in a follow-up patch. - introduce open_temporary_table() to open temporary tables (similar to open_table()); - introduce open_and_process_temporary_table() to fully prepare temporary tables for use (similar to open_and_process_table()); - introduce a new "command flag" (CF_PREOPEN_TMP_TABLES) to mark statements that work with temporary tables, thus temporary tables should be opened for those statements; - open temporary tables in a unified way in the beginning of the statements marked with CF_PREOPEN_TMP_TABLES flag; - introduce a new "command flag" (CF_HA_CLOSE) to mark statements for which open handlers (by HANDLER OPEN) should be closed; - close open handlers in a unified way in the beginning of the statements marked with CF_HA_CLOSE flag.
[9 Nov 2010 19:46]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:20]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:29]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)
[22 Nov 2010 14:00]
Kristian Köhntopp
Please remember that a temporary table MUST NOT BE ALLOWED TO SHADOW A BASE TABLE or VIEW, ever. Consider an application schema app where the web user has CREATE TEMPORARY TABLE privilege on app.%, and a table app.user_permissions exist. Consider an SQL injection attack, where the web user shadows app.user_permission with a temporary table, and then escalates their own privileges. Please make sure that CREATE TEMPORARY TABLE cannot be used to shadow BASE TABLE or VIEW to prevent this kind of attach.
[29 Nov 2010 14:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125349 3391 Alexander Nozdrin 2010-11-29 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations): - move opening of temporary tables out of open_table(); - make open_table() to work with base tables and views only. It will be renamed to open_base_table_or_view() in a follow-up patch. - introduce open_temporary_table() to open temporary tables (similar to open_table()); - introduce open_and_process_temporary_table() to fully prepare temporary tables for use (similar to open_and_process_table()); - introduce a new "command flag" (CF_PREOPEN_TMP_TABLES) to mark statements that work with temporary tables, thus temporary tables should be opened for those statements; - open temporary tables in a unified way in the beginning of the statements marked with CF_PREOPEN_TMP_TABLES flag; - introduce a new "command flag" (CF_HA_CLOSE) to mark statements for which open handlers (by HANDLER OPEN) should be closed; - close open handlers in a unified way in the beginning of the statements marked with CF_HA_CLOSE flag.
[30 Nov 2010 8:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125454 3392 Dmitry Lenev 2010-11-30 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress.
[1 Dec 2010 10:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125627 3393 Dmitry Lenev 2010-12-01 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Fixed handling of administrative commands, extended test-coverage.
[2 Dec 2010 6:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125742 3394 Dmitry Lenev 2010-12-02 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Streamline handling of temporary tables for prelocking list elements.
[2 Dec 2010 9:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125779 3396 Dmitry Lenev 2010-12-02 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Fixed issues discovered by flush_read_lock.test.
[3 Dec 2010 6:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125888 3397 Dmitry Lenev 2010-12-03 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Fixed merge.test failures.
[3 Dec 2010 9:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125900 3398 Dmitry Lenev 2010-12-03 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Fixed outdated function description.
[6 Dec 2010 10:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126115 3399 Dmitry Lenev 2010-12-06 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Fixed small issues, removed indentation changes.
[14 Dec 2010 9:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126728 3400 Dmitry Lenev 2010-12-14 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Simplify opening of temporary tables. Get rid of duplicated code.
[14 Dec 2010 15:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126793 3401 Dmitry Lenev 2010-12-14 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Polish handling of OT_TEMPORARY_ONLY flag and streamline process of table opening.
[22 Dec 2010 15:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/127513 3402 Dmitry Lenev 2010-12-22 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress. Final (almost) batch of fixes.
[22 Dec 2010 16:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/127517 3403 Dmitry Lenev 2010-12-22 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations). Review fixes in progress.
[23 Dec 2010 16:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/127565 3471 Alexander Nozdrin 2010-12-23 Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow temp table operations): - move opening of temporary tables out of open_table(); - make open_table() to work with base tables and views only. It will be renamed to open_base_table_or_view() in a follow-up patch. - introduce open_temporary_table() to open temporary tables (similar to open_table()); - introduce open_temporary_tables() to open temporary tables corresponding to table list elements; - introduce a new "command flag" (CF_PREOPEN_TMP_TABLES) to mark statements that work with temporary tables, thus temporary tables should be opened for those statements; - open temporary tables in a unified way in the beginning of the statements marked with CF_PREOPEN_TMP_TABLES flag; - introduce a new "command flag" (CF_HA_CLOSE) to mark statements for which open handlers (by HANDLER OPEN) should be closed; - close open handlers in a unified way in the beginning of the statements marked with CF_HA_CLOSE flag.
[10 Feb 2011 6:53]
MySQL Verification Team
will this fix really go into 5.5 after it's GA ?
[10 Feb 2011 8:22]
Ståle Deraas
Hi Shane, we are working with this fix and aiming it for 5.6.
[12 May 2011 17:21]
Paul DuBois
Noted in 5.6.3 changelog. For temporary tables created with the CREATE TEMPORARY TABLE statement, the privilege model has changed. Previously, the CREATE TEMPORARY TABLES privilege enabled users to create temporary tables with the CREATE TEMPORARY TABLE statement. However, other operations on a temporary table, such as INSERT, UPDATE, or SELECT, required additional privileges for those operations for the database containing the temporary table, or for the nontemporary table of the same name. To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation was to create a database dedicated to the use of temporary tables. Then for that database, a user could be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user. Now, the CREATE TEMPORARY TABLES privilege enables users to create temporary tables with CREATE TEMPORARY TABLE, as before. However, after a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP, INSERT, UPDATE, or SELECT. One implication of this change is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Support that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it. CHANGESET - http://lists.mysql.com/commits/134713
