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]
