MySQL Bugs: #36811: JOIN USING(col) gives SELECT command denied error when using --skip-grant-tables

Bug #36811	JOIN USING(col) gives SELECT command denied error when using --skip-grant-tables
Submitted:	20 May 2008 0:20	Modified:	4 Feb 2011 16:08
Reporter:	Sergey Petrunya	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.64,5.0.67, 5.0.85	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.0-bk

Description:
Attempt to use JOIN USING(col) produces a "SELECT command denied" error when the server is started with --skip-grant-tables

How to repeat:
1. Start the server with --skip-grant-tables argument

2. Create the tables:

CREATE TABLE t1 (ID INT);
CREATE TABLE t2 (ID INT, s1 TEXT, s2 TEXT, s3 VARCHAR(10), s4 TEXT, s5 VARCHAR(10));
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1,'test', 'test', 'test', 'test', 'test'), (2,'test', 'test', 'test', 'test', 'test');

3. Run the query and get an error:
mysql>  SELECT * FROM t1 LEFT JOIN t2 USING(ID);
ERROR 1143 (42000): SELECT command denied to user ''@'' for column 'ID' in table 't1'

4. Run the same query with ON syntax:
mysql>  SELECT * FROM t1 LEFT JOIN t2 ON t1.ID=t2.ID; 
+------+------+------+------+------+------+------+
| ID   | ID   | s1   | s2   | s3   | s4   | s5   |
+------+------+------+------+------+------+------+
|    1 |    1 | test | test | test | test | test | 
|    2 |    2 | test | test | test | test | test | 
+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)

and conclude the problem lies in the USING syntax processing.

Thank you for a bug report. Verified just as described with recent 5.0-bk (5.0.64).

I may have some more information on this - it seems to depend on which fields are selected. I am running the currently released 5.0.67.

My server has been started with skip-grant-tables, and I get this output:

WG-Web02:/etc/mysql# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6401
Server version: 5.0.67-0.dotdeb.1-log (Dotdeb)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE webgenius;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT CitizenID FROM EmailLog INNER JOIN Memberships USING(CitizenID) WHERE EmailLog.CustomerID = 94184284 AND Type = 'standard' AND ContactMember = 0 AND TimeSent > '2008-08-01';
+------------+
| CitizenID  |
+------------+
| 2017939412 | 
| 2017939312 | 
| 2018491563 | 
| 2018764307 | 
+------------+
4 rows in set (1 min 20.70 sec)

mysql> SELECT * FROM EmailLog INNER JOIN Memberships USING(CitizenID) WHERE EmailLog.CustomerID = 94184284 AND Type = 'standard' AND ContactMember = 0 AND TimeSent > '2008-08-01';
ERROR 1143 (42000): SELECT command denied to user ''@'' for column 'CitizenID' in table 'EmailLog'
mysql> SELECT CitizenID FROM EmailLog LIMIT 1;
+-----------+
| CitizenID |
+-----------+
|         0 | 
+-----------+
1 row in set (0.01 sec)

As you can see, if I "SELECT *", it fails, but if I just "SELECT CitizenID", it succeeds.

testcase, still repeatable on 5.0.85:

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(col1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1 as a join t1 as t1 using(col1);
ERROR 1143 (42000): SELECT command denied to user ''@'' for column 'col1' in table 'a'
mysql>

5.1.40 works as expected.

Not repeated in 5.1, please close as Won't fix.

Issue does not exist in 5.1+