Description:
There is a complicated query and MySql internally uses TEMPORARY table to execute this query. User have ALL PRIVILEGES on the database with tables in SELECT statement.
There is two different issues wich prevent query execution.
1) It is impossible to execute this SELECT without GLOBAL privileges:
mysql.user.Select_priv='Y'
mysql.user.Create_tmp_table_priv=Y'
2) It is impossible to execute query without GRANT privileges on tables to user by hostname AND IP address, i.e.:
user@'127.0.0.1'
user@'localhost'
user@'hostname'
user@'hostname.domain.com'
The BUG is in:
user connect to MySql authenticating by IP address, but access to TEMPORARY table internally created is acquired only by HOSTNAME or FQDN.
How to repeat:
I can give you screen log wich prove it. To verify this bug you need to create table with data and query wich will create temporary table during execution.
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[mysql@lina mysql]$ mysql -uoln -pxxx oln
Your MySQL connection id is 76 to server version: 4.1.0-alpha-standard
mysql> SELECT A.REF_ID, B.TITLE, A.ID, DATE_FORMAT(A.CRE_DATE,'%d-%b-%Y %k:%i'), DATE_FORMAT(C.END_DATE,'%d-%b-%Y'), A.TITLE FROM MSG A JOIN MSG B ON (A.REF_ID=B.ID) LEFT OUTER JOIN (SELECT END_DATE, COURSE_ID FROM COURSERIGHTS WHERE USER_ID=1) C ON (A.ID=C.COURSE_ID) WHERE A.TYPE=14 AND (A.TITLE LIKE '%' OR C.END_DATE IS NOT NULL) ORDER BY B.TITLE;
ERROR 1142: select command denied to user: 'oln@localhost' for table '/tmp/#sql_76e_0'
mysql> quit
Bye
[mysql@lina mysql]$ mysql -uroot -pxxx oln
mysql> update mysql.user set Select_priv='Y' where user='oln';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.db;
+---------------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |
+---------------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| localhost | oln | oln | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| 127.0.0.1 | oln | oln | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| myhost.domain.com | oln | oln | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| myhost | oln | oln | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
+---------------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
6 rows in set (0.00 sec)
mysql> quit
Bye
[mysql@lina mysql]$ mysql -uoln -pxxx oln
mysql> SELECT A.REF_ID, B.TITLE, A.ID, DATE_FORMAT(A.CRE_DATE,'%d-%b-%Y %k:%i'), DATE_FORMAT(C.END_DATE,'%d-%b-%Y'), A.TITLE FROM MSG A JOIN MSG B ON (A.REF_ID=B.ID) LEFT OUTER JOIN (SELECT END_DATE, COURSE_ID FROM COURSERIGHTS WHERE USER_ID=1) C ON (A.ID=C.COURSE_ID) WHERE A.TYPE=14 AND (A.TITLE LIKE '%' OR C.END_DATE IS NOT NULL) ORDER BY B.TITLE;
+--------+------------------------------------------------------------+-----+------------------------------------------+------------------------------------+-----------------------------------------------------+
| REF_ID | TITLE | ID | DATE_FORMAT(A.CRE_DATE,'%d-%b-%Y %k:%i') | DATE_FORMAT(C.END_DATE,'%d-%b-%Y') | TITLE |
+--------+------------------------------------------------------------+-----+------------------------------------------+------------------------------------+-----------------------------------------------------+
| 48 | ok | 55 | 18-Jul-2003 15:40 | 18-Sep-2003 | ok |
+--------+------------------------------------------------------------+-----+------------------------------------------+------------------------------------+-----------------------------------------------------+
1 rows in set (0.01 sec)
mysql>
