Bug #869 'select command denied' error, v4.1.0-alpha, subquery/join
Submitted: 17 Jul 2003 14:55 Modified: 4 Aug 2003 7:16
Reporter: Carl Welch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0-alpha OS:Linux (Linux 2.4.21 kernel)
Assigned to: CPU Architecture:Any

[17 Jul 2003 14:55] Carl Welch
Description:
I am running MySQL v4.1.0-alpha on GNU/Linux 2.4.21 (Slackware 9.0). I am
experiencing a 'select command denied' error when using an inner join on
a subquery. I was not initially encountering this error, but it began
as I was adding table- and column-level privileges to a different
database. The error reads as
select command denied to user: 'apache@localhost' for table 
        '/usr/local/mysql/tmp/#sql_6c6_0'
This problem does not seem to affect the mysql 'root' user.
The mysqld process runs as user 'mysql' which has read access to
/usr/local/mysql/var/tmp

How to repeat:
Appended to this bug report is the output of 
"mysqldump -u root -p --all-databases"
(with the passwords erased), the /etc/my.cnf configuration file used by
the mysqld process, a file containing an SQL statement which results in
the above error (when run by the mysql user 'apache'), and a file 
containing the output of
"ls -l /usr/local/mysql/var"
[17 Jul 2003 14:59] Carl Welch
mysqldump, my.cnf, SQL statement which causes error

Attachment: stuff.txt (text/plain), 48.00 KiB.

[21 Jul 2003 5:18] Alexander Keremidarski
Carl,

Please upload your test case to ftp://support.mysql.com/pub/secret
Use some easily recognized name like bug869.tar.gz

Also describe how did you added privileges. With using GRANT or you changed mysql.* tables manually with INSERT/UPDATE.

In cases like this it is usefull to check output of 
SHOW GRANTS FOR user_in_question@host;

What does it says?
[21 Jul 2003 6:58] Alexander Keremidarski
Sorry,

I didn't noticed stuff.txt in Files section.
No need to upload anyhting.
[22 Jul 2003 8:11] Carl Welch
Here's some follow-up information about bug report #869 regarding the grants questions asked by Alexander Keremidarski and a possible (but not-so-great) workaround:

I did a "use mysql ; delete from user where Password='';" to remove
passwordless entries in the 'user' table, but otherwise used "grant"
statements to assign privileges.

Here's the output from "show grants for apache@localhost":

+------------------------------------------------------------------------------------------+
| Grants for apache@localhost                                                              |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apache'@'localhost' IDENTIFIED BY PASSWORD '<encrypted_password>' |
| GRANT SELECT ON `test`.* TO 'apache'@'localhost'                                         |
| GRANT INSERT ON another_test.blah TO 'apache'@'localhost'                                |
+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

=============================

I've discovered a less-than-wonderful workaround for the problem.
Setting the Select_priv column to 'Y' for the apache@localhost row
eliminates the above error (although it seems that "flush privileges"
isn't enough to make the change take effect: the apache user has
to disconnect and reconnect before access problem goes away).

However, this solution has the unfortunate side effect of making the
encrypted passwords in mysql.user visible to the apache user.
[4 Aug 2003 7:16] Sinisa Milivojevic
Thank you for your bug record.

This bug was fixed in version 4.1.1.

Here is a proof:
mysql> show grants for apache@localhost;
+-----------------------------------------------------------+
| Grants for apache@localhost                               |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apache'@'localhost'                |
| GRANT SELECT ON `bug`.* TO 'apache'@'localhost'           |
| GRANT INSERT ON `telcent`.`mails` TO 'apache'@'localhost' |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

[/mnt/work/mysql-4.1]$ mysql -u apache bug < tmp/dump 
pk  data
1   200
2   300
[6 Aug 2003 6:22] Carl Welch
My thanks to Sinisa Milivojevic and Alexander Keremidarski for investigating my permissions problem.

I'd like to give 4.1.1 a try, but I can't find it in the downloads section. Has it been released? Is there a CVS tree or something from which I can download the 4.1.1 source code?
[6 Aug 2003 7:17] Lenz Grimmer
MySQL 4.1.1 has not been released yet. For info on how to access the MySQL
source tree, please see http://www.mysql.com/doc/en/Installing_source_tree.html

We're working on providing nightly tarball snapshots of all source trees, but this may take a while until it's live.