Bug #81857 Renaming a temporary table requires more privileges than necessary

• Submitted: 15 Jun 2016 8:32
• Reporter: Guilhem Bichot
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.10
• OS: Any
• CPU Architecture: Any

[15 Jun 2016 8:32] Guilhem Bichot

Description:
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_create-temporary-tab...
"The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement. 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 TABLE, INSERT, UPDATE, or SELECT. "

I observe that a user having CREATE TEMPORARY TABLES privilege can indeed create a tmp table, insert into it, drop it, but cannot rename it. This contradicts the above.
It has been reported to me by a user. It breaks the SP I gave in:
http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html

How to repeat:
# As root:

create user foo;
create database db;
grant create temporary tables on db.* to foo;

# As foo:

use db;
create temporary table t(a int);
insert into t values(1);
alter table t rename to u;
# returns ERROR 1142 (42000): INSERT, CREATE command denied to user 'foo'@'localhost' for table 'u',
# it's the bug.

# Just for fun, as root, add INSERT,CREATE privilege on 'db.u'
# (which doesn't exist, I'm doing an absurd thing):

grant create,insert on db.u to foo;

# As foo:
use db;
create temporary table t(a int);
alter table t rename to u;
# Now it works, it shows that mysql is trying to find privileges for this tmp table
# in the privilege database, which is an absurd idea.

# and the reverse fails:
alter table u rename to t;
# ERROR 1142 (42000): INSERT, CREATE command denied to user 'foo'@'localhost' for table 't'

Suggested fix:
ALTER should not require any privilege on the tmp table, like other DDLs/DMLs.