Bug #1393 Complicated SQL queries can not work without specific privilege
Submitted: 24 Sep 2003 14:35 Modified: 28 Oct 2003 7:47
Reporter: Oleg Ivanov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.0alpha OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[24 Sep 2003 14:35] Oleg Ivanov
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>
[28 Oct 2003 7:47] Dean Ellis
I cannot repeat this using the latest MySQL 4.1.1 sources.

You can access the 4.1.1 sources from our BitKeeper repository as described here:

http://www.mysql.com/doc/en/Installing_source_tree.html

Or you can download a snapshot from http://downloads.mysql.com

If this occurs for you using 4.1.1 sources, please submit a test script which demonstrates the problem.

Thank you