Bug #58392 Prevent TEMPORARY tables from shadowing regular tables
Submitted: 22 Nov 2010 16:31
Reporter: Leandro Morgado Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.5+ OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2010 16:31] Leandro Morgado
Description:
As a result of bug 27480 , a client that has CREATE TEMPORARY TABLE privs will now have INSERT/UPDATE/DELETE on the TEMPORARY TABLE. Therefore, the TEMPORARY table can "override" an existing table with the same name. We should be able to turn off this override/shadow, i.e, have separate namespaces for each type of table. Here is the use case presented:

"Bugfix to http://bugs.mysql.com/bug.php?id=27480 (in 5.5.8 upcoming) is welcome, and correct.

It creates a potential security problem, though:

Consider a web application with a schema app, and a web user which has CREATE TEMPORARY TABLE on app.%. Also, consider a table app.user_privileges which does what the name
says.

Consider an SQL injection problem in that app. An attacker can now CREATE TEMPORARY TABLE app.user_privileges, and that table will shadow the existing app.user_privileges, and
will now guaranteed to be writeable for the web user. Hence web user can escalate their privileges within the security model of the app.

The fix for this would be to not allow TEMPORARY TABLES to shadow any BASE TABLE or VIEW, ever, at the cost of leaking information about the existence of any base table or
view due to the CREATE TEMPORARY TABLE failing for invisible tables (but this is the smaller problem of the two possible outcomes)."

How to repeat:
Once 27480 is pushed, create a regular table, then create a TEMPORARY table with a similar name, and do DML on the TEMPORARY table.

Suggested fix:
If regular app.tbl exists, prevent CREATE TEMPORARY TABLE app.tbl
[14 Oct 2017 10:57] Aleks Aleks
The obvious workaround is to create user_privileges in separate schema. I guess, TEMPORARY shadowing was planned as useful feature.