MySQL Security: The Secret of the Temporary Table

After reading the headline, I guess you now wonder what’s so special about a temporary table. And if you are a programmer you might even think “Heck, I’m using temporary tables all the time.”. Good for you, if you really do but chances are that you don’t.
The key here is the definition of what a temporary table in MySQL actually is. To most programmers it’s just a table they create for temporary use and delete afterwards. Well, sorry guys. That’s not a temporary table at all but just your well-known, friendly MySQL table as every other table in your database. It’s just a bit short-lived, that’s all. Now let’s talk about real temporary MySQL tables.

{openx:6}

To create a real temporary table with MySQL you have to use the

CREATE TEMPORARY TABLE ...

syntax instead of the normal

CREATE TABLE ...

one. This may seem trivial but it really has some advantages.

Let me quote the MySQL documentation here (amphasis by me):

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

Isn’t that cool? This makes live so easy. No more worries about how to create unique table names. Just use the same name all over your code (well, not really but you get the drift). Remeber the name of your temporary table in a variable just so you can drop the table once you’re done with it? MySQL will handle that for you. If you wanted to, you could even “hide” a real table from your applications view temporarily, just to move in other data.

And as an added bonus, you get a bit more security if you want to. That’s because to create a “real” table, the account of the MySQL user needs to have the “Create_priv” flag set either in the

user

or the

db

table. That essentially means that using that account any new table could be created and used without ever being dropped again. To create a temporary table, however, only the “Create_temp” privilege is needed. At least this will help to keep your MySQL server clean of the other type of “temporary” tables.

The only downside is that most programmers just don’t use temporary tables. So expect to see some nice error messages if you try to remove the “Create_priv” from accounts that normally shouldn’t need it.