MySQL 4.1, derived tables, and privileges

[Michael Johnson]

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]