Michael Johnson <[EMAIL PROTECTED]> wrote: > 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.
Thanks for bug report! It was added to the bug database: http://bugs.mysql.com/bug.php?id=1199 > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]