MySQL 错误：#1142 - 拒绝用户的 SELECT 命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10956195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error: #1142 - SELECT command denied to user
提问by Seph
I'm having troubles with a certain query on one of my servers. On all other places I've tested it it works completely fine but on the server i want to use it it isn't working.
我在我的一台服务器上遇到了某个查询的问题。在我测试过的所有其他地方，它完全正常，但在我想使用的服务器上，它不起作用。
It's about the following SQL:
这是关于以下SQL：
SELECT facturen.id AS fid,
projecten.id AS pid,
titel,
facturen.totaal_bedrag AS totaal,
betaald,
datum
FROM facturen,
projecten
WHERE facturen.project_id = projecten.id
AND projecten.eigenaar = '1'
ORDER BY datum DESC
This is the error code I get from it:
这是我从中得到的错误代码：
SELECT command denied to user 'marco'@'localhost' for table 'projecten'
SELECT command denied to user 'marco'@'localhost' for table 'projecten'
The tables: facturen:
表： facturen：
CREATE TABLE IF NOT EXISTS `facturen` (
`id` int(11) NOT NULL auto_increment,
`project_id` int(11) NOT NULL,
`datum` int(11) NOT NULL,
`lever_datum` int(11) NOT NULL,
`totaal_bedrag` decimal(9,2) NOT NULL,
`btw` decimal(9,2) NOT NULL,
`bedrijf` varchar(40) NOT NULL,
`contactpersoon` varchar(60) NOT NULL,
`adres` varchar(60) NOT NULL,
`postcode` varchar(7) NOT NULL,
`plaats` varchar(30) NOT NULL,
`betaald` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=201200006 ;
projecten:
投影：
CREATE TABLE IF NOT EXISTS `projecten` (
`id` int(11) NOT NULL auto_increment,
`titel` varchar(80) NOT NULL,
`eigenaar` int(11) NOT NULL,
`creatie_datum` int(11) NOT NULL,
`eind_datum` int(11) NOT NULL,
`totaal_bedrag` decimal(9,2) NOT NULL,
`btw` decimal(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=201200004 ;
The strange part is that every other query on both the 'projecten' table and the 'facturen' table works completely fine, also this query works fine on two other servers of mine.
奇怪的是，'projecten' 表和'facturen' 表上的所有其他查询都可以正常工作，这个查询在我的另外两台服务器上也可以正常工作。
采纳答案by Vaibs
I faced the same situation but its funny that reason for the error was due to the use of the incorrect database or schema name.
我遇到了同样的情况，但有趣的是，错误的原因是由于使用了不正确的数据库或模式名称。
Its true that multiple issues can lead to error you have mentioned.
确实，多个问题会导致您提到的错误。
回答by khaled_webdev
You need to grant SELECT permissions to the MySQL user who is connecting to MySQL
您需要向连接 MySQL 的 MySQL 用户授予 SELECT 权限
same question as here Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'
与此处相同的问题错误：选择命令拒绝用户 '<userid>'@'<ip-address>' for table '<table-name>'
see answers of the link ;)
请参阅链接的答案;)
回答by veena v.k.
This error also arises for a syntax error occurred due to aliasing tablename.
由于别名表名而发生的语法错误也会出现此错误。
For instance, when executed below query,
例如，当执行下面的查询时，
select * from a.table1, b.table2 where a.table1= b.table2
select * from a.table1, b.table2 where a.table1= b.table2
below error occurs:
出现以下错误：
MySQL Error: #1142. Response form the database. SELECT command denied to user "username@ip" for table "table1"
MySQL 错误：#1142。响应来自数据库。SELECT 命令拒绝用户“username@ip”用于表“table1”
Solution: Syntax to alias tablename should be used proper, syntax solution for above instance >select * from table1 a, table2 b where a.table1= b.table2
解决方案：应正确使用别名表名的语法，上述实例的语法解决方案>select * from table1 a, table2 b where a.table1= b.table2
回答by John Lamberis
I had this problem too and for me, the problem was that I moved to a new server and the database I was trying to connect to with my PHP code changed from "my_Database" to "my_database".
我也有这个问题，对我来说，问题是我搬到了一个新服务器，我试图用我的 PHP 代码连接的数据库从“my_Database”更改为“my_database”。
回答by Dhileepan
This is th privileges issue in your database users. first check and grant permission to user 'marco' in localhost
这是您的数据库用户的权限问题。首先检查并授予本地主机中用户“marco”的权限
回答by CodeJohnny
So the issue I ran into was this... the application I used to grant the permissions converted the Schema.TableName into a single DB statement in the wrong table, so the grant was indeed wrong, but looked correct when we did a SHOW GRANTS FOR UserName if you weren't paying very close attention to GRANT SELECT vs GRANT TABLE SELECT. Manually correcting the Grant Select on Table w/ proper escaping of Schema.Tablesolved my issue.
所以我遇到的问题是……我用来授予权限的应用程序将 Schema.TableName 转换为错误表中的单个 DB 语句，因此授予确实是错误的，但是当我们执行 SHOW GRANTS 时看起来是正确的FOR UserName 如果您没有非常关注 GRANT SELECT 与 GRANT TABLE SELECT。手动更正表上的授予选择，并正确转义Schema. Table解决了我的问题。
May be unrelated, but I can imagine if one client does this wrong, another might too.
可能不相关，但我可以想象如果一个客户做错了，另一个可能也会。
Hope that's helpful.
希望这有帮助。
回答by xyz
This error happened on my server when I imported a view with an invalid definer.
当我使用无效定义器导入视图时，此错误发生在我的服务器上。
Removing the faulty view fixed the error.
删除错误视图修复了错误。
The error message didn't say anything about the view in question, but was "complaining" about one of the tables, that was used in the view.
错误消息没有说明有关视图的任何内容，而是“抱怨”了视图中使用的其中一个表。
回答by Harry
In MySQL query browser go to Tools tab>MySQL Administrator > User Administration and then give the privileges to user.
在 MySQL 查询浏览器中，转到“工具”选项卡 >“MySQL 管理员”>“用户管理”，然后将权限授予用户。
回答by u5211732
it's about the privallage of user in the mysql,so you should set the bigger grants with your users.
这是关于mysql中用户的privallage，所以你应该为你的用户设置更大的授权。
do this !
做这个 ！
In the Terminal, put code
mysql> show grants;
在终端中，输入代码
mysql> show grants;
then change the following code like this :
然后像这样更改以下代码：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3F9DF5A32114E05C12C50A83EAE02991016C917B' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3F9DF5A32114E05C12C50A83EAE02991016C917B' WITH GRANT OPTION;
Then it will be done.
然后它就会完成。
