Temporary tables, select privilege problem

[toucaninc]

Hi.

SITUATION:
-I have a user named "abc", he is not a root, he cannot grant privileges.

-I have a database named "Toucan".

-I have a table in this database named "Cookies"

-The user doesn't have SELECT privilege on the database, but has some SELECT privileges on some specific tables of this database, including the table "Cookies".

-I run this SQL Query : CREATE TEMPORARY TABLE test AS SELECT * FROM Cookies

THE PROBLEM:
-The creation of the temporary seems to work... but if I try SELECT * FROM test, I get this following error : "select command denied for user: 'abc@localhost' for table 'test'"

I understand what cause the problem, the user doesn't have SELECT privilege on the new created table. Does anyone know how to fix it?

Thanks
Alexandre Thibault

 

[rzs0502]

What version of MySQL are you using?

Does the user abc have Create_tmp_table_priv='Y'?

 

[toucaninc]

I think I run version MySQL-server-4.1.0-0 (that's what I get when I run rpm -q MySQL-server).

I also ran mysql_fix_privilege_tables script.

And yes, the user abc has Create_tmp_table_priv='Y'. He can create the table, but cannot do a Select query on it.

 

[rzs0502]

Are you doing this on the mysql console?
The only way I could get your error is by typing
mysql -u testuser -p
It appears that the user wasn't actually logging in.
I had to use mysql -h localhost -u testuser -p
testuser in this instance has Select and Create Temp Table on the Database.
As far as I can see, the user must have select access across the database.

 

[toucaninc]

Yes, I can reproduce the error in the console, and in my java program.

If I give my user abc the Select privilege to the whole database, I don't get the error. But I cannot do that for security issues, there are some tables in my database that abc must not have access.

Of course, I could give the Select privilege on the table "test". But my real code is this (I posted the previous code only to introduce the problem):

SELECT *
FROM Sections_Pieces INNER JOIN (
SELECT Pieces.lidPiece, sCode, sDescription
FROM Pieces LEFT JOIN PiecesDescriptions
ON Pieces.lidPiece = PiecesDescriptions.lidPiece
) AS PiecesDesc
ON Sections_Pieces.lidPiece = PiecesDesc.lidPiece

This query creates a temporary table to complete the whole query (the temporary table is the second Select between parenthesis), managed by MySQL. But the name it gives to the temporary table is something like '/tmp/#sql_6899_0', and abc doesn't have Select privilege on this table.

A solution I though is to split the query in 2 parts...
- A first part that is the inside-Select statement. I would create a temporary with a given name (e.g. PiecesDesc).
- The second query would be the Select statement linking the tables Sections_Pieces and the temporary table I just created previously, PiecesDesc (on which I specified abc can Select).

I would like to avoid this solution for these reasons :
- I would need 2 SQL Queries instead of 1
- I would have to delete the temporary table after use.
- I would have to add code in my software to allow an admin to add a additionnal privilege to a temporay table, just to be able to execute my query
- All the reasons would add unwanted weight to my software.