Dude ... You got the same name as me ... So does that mean I can blame any idiotic statements I make on you ?
We got a popular name. Mike -----Original Message----- From: Michael Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 8:51 AM To: [EMAIL PROTECTED] Subject: MySQL 4.1, derived tables, and privileges I hope someone can help me on this. I've searched the mailing list archives and the manual and can't find anything, except for user comments in the manual that confirm what I'm finding. I'm using derived tables users with restricted privileges. The only way I can get the derived tables to work though is to give the user global SELECT privileges. I don't want to do this. Am I missing something? If not, is there a way around this problem? The error I get is: #1142 - select command denied to user: '[EMAIL PROTECTED]' for table '/tmp/#sql_135_0' My select looks something like: SELECT t1id as "Key", IFNULL(k2, dfltValue) as "Value" FROM t1 NATURAL JOIN (SELECT t1id, k2 FROM t2 WHERE k3=1) as derived; My tables have the columns: t1: t1id, dfltValue 1 a 2 b t2: t1id, k2, k3 1 c 1 1 d 2 2 e 2 Note that not all values of t1id exist in t2 for a given k3. Hence, this select gets all t1id keys with a default value if it doesn't exist in t2. Desired result: Key Value 1 c 2 b The privileges for [EMAIL PROTECTED] are: No global priveleges (setting Create_tmp_table_priv makes no difference) t1: SELECT, REFERENCES t2: SELECT, INSERT, UPDATE, REFERENCES If I grant global SELECT it works as expected. Again, create_tmp_table_priv is irrelevant at this point. Any thoughts on why this is working like this? Thanks, Michael PS If there is a better way to do this query, I'd like to know that, too, but I've run into this problem at other places where I *know* I have to use derived tables, so the problem is still pertinent to my use. -- Michael Johnson < [EMAIL PROTECTED] > Internet Application Programmer, Pitsco, Inc. 620-231-2424x516 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
