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]

Reply via email to