Bug #1199 derived tables and table-level privileges

• Submitted: 4 Sep 2003 12:54
• Modified: 18 Sep 2003 10:51
• Reporter: Sergei Golubchik
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• CPU Architecture: Any

[4 Sep 2003 12:54] Sergei Golubchik

Description:
From: Michael Johnson <[e-mail]>
To: [e-mail]
Subject: MySQL 4.1, derived tables, and privileges
Date: Thu, 04 Sep 2003 10:51:09 -0500

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: 'user@localhost'
   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 user@localhost 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.

How to repeat:

[18 Sep 2003 10:51] Indrek Siitan

I wasn't able to repeat this with the latest 4.1 BK tree snapshot. It might be
that the bug has been fixed meanwhile.

mysql> create table t1 ( t1id int, dfltValue char(1) );
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 ( t1id int, k2 char(1), k3 int );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1,'c',1),(1,'d',2),(2,'e',2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> grant select,references on bug.t1 to t1199@localhost identified by 't1199';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,references on bug.t2 to t1199@localhost identified by 't1199';
Query OK, 0 rows affected (0.02 sec)

 --- / log in as t1199@localhost / ---

+-----------------+
| user()          |
+-----------------+
| t1199@localhost |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT t1.t1id as "Key", IFNULL(k2, dfltValue) as "Value" FROM t1 NATURAL JOIN (SELECT 
t1id, k2 FROM t2 WHERE k3=1) as derived;
+------+-------+
| Key  | Value |
+------+-------+
|    1 | c     |
+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT t1.t1id as "Key", IFNULL(k2, dfltValue) as "Value" FROM t1
 NATURAL JOIN (SELECT t1id, k2 FROM t2 WHERE k3=1) as derived;
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref 
 | rows | Extra       |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL |    NULL | NULL
 |    1 |             |
|  1 | PRIMARY     | t1         | ALL    | NULL          | NULL |    NULL | NULL
 |    2 | Using where |
|  2 | DERIVED     | t2         | ALL    | NULL          | NULL |    NULL | NULL
 |    3 | Using where |
+----+-------------+------------+--------+---------------+------+---------+-----
-+------+-------------+
3 rows in set (0.01 sec)