MySQL "command denied to user for table"错误和用户权限

Question

当我运行以下命令时:

SELECT p.*,
   (SELECT i.image
    FROM pimage i
    WHERE p.id = i.pid 
    ORDER BY i.id ASC
    LIMIT 1
   ) as image
FROM  products p
WHERE p.categories LIKE '%$subCatId%'
ORDER BY p.id ASC

在具有下表的数据库上:

pImage
products
sidebar
specs

我收到错误:

#1142 - SELECT command denied to user 'database_user'@'localhost' for table 'pimage'

不幸的是，今天下午它在我的制作网站上以纯文本形式打印了十几次。

我在网站的本地版本上发布之前对其进行了测试，它运行得很好。我很快恢复了更改，并在开发网站上进行了测试。当存储在 PHP 代码中时，它在开发服务器上运行得很好。我还在开发服务器的 phpMyAdmin 界面上测试了它，它工作得很好。我登录到生产网站的 phpMyAdmin 界面，并在那里运行查询，但失败并出现相同的错误。我分别对每个表运行查询，它们顺利通过，没有错误。

什么给了？!？

制作网站相当过时。它运行 FreeBSD 6.4(是的，EOL 是 2010 年 11 月 30 日...)和 MySQL 5.1(是的，EOL 是 2013 年 12 月 31 日...)。我正在努力将其从共享托管中移出，以便我可以更新它。开发服务器是当前的Bitnami LAMP堆栈虚拟机，运行Ubuntu 14.04和MySQL 5.5。到目前为止，我还没有遇到过兼容性问题。据我所知，该查询没有使用两个版本之间不同的任何语法或特殊功能。

Answer 1

我以管理用户身份登录数据库，它返回错误:

#1146 - Table 'company.pimage' doesn't exist

这很快就解决了原始问题:pimage 和 pImage 之间出现愚蠢的大小写错误。

区分大小写

之前未检测到此错误，因为 Bitnami 设备已更改默认值以使其表名称不区分大小写，并且 MySQL 中非管理员用户的错误消息不会泄漏信息。

MySQL 文档，section 9.2.2 "Identifier Case Sensitivity"阅读:

By default, table aliases are case sensitive on Unix

但这可以通过 lower_case_table_names 进行更改系统变量。

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase...
On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On OS X, the default value is 2.

在我的开发服务器上，SHOW VARIABLES 表明 Bitnami 设备已将此值设置为 1。mysqld --verbose --help 建议编译的默认值具有此值值设置为零，但是 as described on the bitnami forums , /opt/bitnami/mysql/scripts/ctl.sh 设置 --lower-case-table-names=1。

隐秘的错误消息

非管理员用户的错误消息很神秘:

#1142 - SELECT command denied to user 'user@localhost' for table 'mispelled_table'

但是管理员用户的错误消息:

#1146 - Table 'company.mispelled_table' doesn't exist

很容易理解。管理员用户拥有 SHOW GRANTS FOR CURRENT_USER 权限:

GRANT USAGE ON *.* TO 'admin_user'@'%' IDENTIFIED BY PASSWORD [redacted]
GRANT ALL PRIVILEGES ON `company`.* TO 'admin_user'@'%' WITH GRANT OPTION

但普通用户拥有权限:

GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD [redacted]
GRANT SELECT ON `schap`.`pImage` TO 'user'@'localhost'
GRANT SELECT, SELECT (view_count), UPDATE (view_count) ON `company`.`products` TO 'user'@'localhost'
...etc.

这最后一点只是猜测，因为我没有必要的权限来更改和测试它，但我认为 SHOW_DATABASES privilege会给用户良好的错误消息。避免列出所有数据库名称可能是个好策略，因为它可以防止黑客轻松确定哪些表存在，但它肯定会造成很多困惑!