MySQL 用户的更新命令被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7980032/
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
update command is denied for user
提问by pahnin
I've been using thisfree hosting site for development and testing.
我一直在使用这个免费的托管站点进行开发和测试。
I couldn't use an UPDATEMySQL command to change the database values, even though the user is supposed to be allowed to use all commands from cPanel.
我无法使用UPDATEMySQL 命令来更改数据库值，即使应该允许用户使用来自 cPanel 的所有命令。
Then, I've tested the same with a default user, it still won't work. However, it works fine on my system.
然后，我用默认用户进行了相同的测试，它仍然无法正常工作。但是，它在我的系统上运行良好。
The MySQL error is
MySQL错误是
UPDATE command denied to user 'test'@'localhost' for table 'content'
UPDATE command denied to user 'test'@'localhost' for table 'content'
Other commands are working fine.
其他命令工作正常。
Why is this happening? And how can it be prevented? Or any solution for this?
为什么会这样？以及如何预防？或者对此有什么解决方案？
And I am very sure that users have permission to use the UPDATEcommand because I can use phpMyAdmin with the same user and modify the MySQL fields.
而且我非常确定用户有权使用该UPDATE命令，因为我可以使用同一用户的 phpMyAdmin 并修改 MySQL 字段。
I don't understand why some MySQL commands from PHP are denied for a user who was given all priviliges and can do everything via phpMyAdmin. Given that script, phpMyAdmin and the SQL host are on the same server.
我不明白为什么某些来自 PHP 的 MySQL 命令被授予所有权限并且可以通过 phpMyAdmin 执行所有操作的用户被拒绝。鉴于该脚本，phpMyAdmin 和 SQL 主机在同一台服务器上。
回答by pahnin
For everyone who have tried answering this question here is my sincere thanks. I have found the problem and solution.
对于在这里尝试回答这个问题的每个人，我表示衷心的感谢。我找到了问题和解决方案。
my sql query is like this
我的sql查询是这样的
UPDATE `dblayer`.`test` SET `title` = 'hello a' WHERE `test`.`id` =1;
which I got from phpmyadmin and it works perfectly on my system. But when I work on the servers it doesn't work and it says command denied may be because
这是我从 phpmyadmin 获得的，它在我的系统上运行良好。但是当我在服务器上工作时它不起作用并且它说命令被拒绝可能是因为
`dblayer`.`test`
I am trying to select the table globally (or something like that, I'm not sure) but if my sql query is
我正在尝试全局选择表（或类似的东西，我不确定）但是如果我的 sql 查询是
UPDATE `test` SET `title` = 'hello a' WHERE `test`.`id` =1;
it works on my server too.
它也适用于我的服务器。
回答by Alexey Gerasimov
As everyone else said, it's permission issue. I am sure you probably checked, but just in case. After you log into phpmyadmin, run the following:
正如其他人所说，这是许可问题。我相信你可能检查过，但以防万一。登录 phpmyadmin 后，运行以下命令：
SELECT USER();
That should spit out 'test'@'localhost' as indicated in all the comments above
这应该吐出 'test'@'localhost' 如上面所有评论中所示
Then run
然后运行
SHOW GRANTS FOR 'test'@'localhost'
That should give you all privs for that user. When you get results, select 'Options', Full Text and click go to get full text.
这应该为您提供该用户的所有权限。获得结果后，选择“选项”、“全文”并单击“转到”以获取全文。
Make sure permissions in the output have something like that:
确保输出中的权限具有以下内容：
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `your_database`.* TO 'test'@'localhost'
You might not be able to get GRANT ALL on some hosting setups but as long as you got INSERT, UPDATE, DELETE you should definitely be able to update the data
您可能无法在某些托管设置上获得 GRANT ALL 但只要您获得 INSERT、UPDATE、DELETE，您就应该能够更新数据
回答by Alexey Gerasimov
Your user doesn't have the right permissions. You need to give it access to the UPDATE command, like so:
您的用户没有正确的权限。您需要授予它访问 UPDATE 命令的权限，如下所示：
GRANT UPDATE ON database.* TO test@'localhost' IDENTIFIED BY 'password';
If you are using a graphical tool to manage the database - e.g. PHPMyAdmin or SQLYog, etc - you should be able to use those tools to enable these permissions too.
如果您使用图形工具来管理数据库 - 例如 PHPMyAdmin 或 SQLYog 等 - 您也应该能够使用这些工具来启用这些权限。
回答by deleted
I had the same problem, the permission was right but it was working in some environments and not in others. I was using a table name like
我遇到了同样的问题，权限是正确的，但它在某些环境中有效，而在其他环境中无效。我正在使用一个表名
scheme.TABLE_NAME.
scheme.TABLE_NAME。
Without changing the permission but using just
不改变权限，只使用
table_name
表名
worked in all environments.
在所有环境中工作。
回答by SG 86
Sometimes it is a case sensitive problem.
有时这是一个区分大小写的问题。
MySQL also says access denied even if the table is not available. Please make sure that you have no case sensitive issues.
即使表不可用，MySQL 也表示拒绝访问。请确保您没有区分大小写的问题。
回答by Icarus
the my sql error is UPDATE command denied to user 'test'@'localhost' for table 'content'
why is this happening? And how to prevent it? Or any solution for this?
我的 sql 错误是 UPDATE 命令拒绝用户 'test'@'localhost' 用于表 'content'
为什么会这样？以及如何预防？或者对此有什么解决方案？
It's happening for what it says it's happening: the user testdoes not have update permissions on table content. Something like this should grant the user the required permission:
它正在发生它所说的它正在发生的事情：用户test对 table 没有更新权限content。这样的事情应该授予用户所需的权限：
GRANT UPDATE ON database.content TO test@'localhost' IDENTIFIED BY 'password';
*password above is just a place holder. You should use the real one.
*上面的密码只是一个占位符。你应该使用真正的。
回答by Chris Trynkiewicz
A UPDATE command deniederror can on some web hosts be the result of reaching the MySQL database space limit.
UPDATE command denied某些 Web 主机上的错误可能是达到 MySQL 数据库空间限制的结果。
回答by DRoberts
This error came up for me when I was creating triggers, which included an update clause, for remote MySql instance (via JawsDB).
当我为远程 MySql 实例（通过 JawsDB）创建触发器时出现了这个错误，其中包括一个更新子句。
Instead of setting up trigger this:
而不是设置触发器：
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``mq6swfzd39ygjejl``.``users``
SET
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
END
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``mq6swfzd39ygjejl``.``users``
SET
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
END
I set it up like this
我是这样设置的
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``users``
SET
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
END
Basically, I just removed the database name in the UPDATE line and left the table name by itself.
CREATE TRIGGER updateRecentDateFromProcGen
After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration``
For Each Row
BEGIN
UPDATE ``users``
SET
mostRecentDateAllActivities = NEW.date
WHERE
NEW.userID = users.ID;
END
基本上，我只是在 UPDATE 行中删除了数据库名称并单独保留了表名。
Note, wherever you see the `` there should only be one tilde not two. If anyone knows how to fix that in stack overflow's markup please let me know!
请注意，无论您在哪里看到 `` 都应该只有一个波浪号而不是两个。如果有人知道如何在堆栈溢出的标记中修复它，请告诉我！
回答by Thillai Narayanan
Provide the full access to test user with ip as localhost.Check the user table from mysql db.
提供对 ip 作为 localhost 的测试用户的完全访问权限。检查 mysql db 中的用户表。
Login as root and enter into mysql database and then to user table.You will be finding the current privileges of your test user.
以 root 身份登录并进入 mysql 数据库，然后进入用户表。您将找到测试用户的当前权限。
回答by Ben
With mysql you can just do SELECT * mysql.user
使用 mysql 你可以只做 SELECT * mysql.user
Look at the privileges and then update the ones you want. I like that more personally then using the above commands because I can see all of my options available in that table.
查看权限，然后更新您想要的权限。我个人更喜欢使用上述命令，因为我可以在该表中看到所有可用的选项。
UPDATE mysql.user
SET Insert_priv = 'Y',
Update_priv = 'Y'
WHERE user.Host = 'localhost' AND user.User = 'test';
After updating the privileges I noticed I would have to restart the mysql server for the client to see them:
更新权限后，我注意到我必须重新启动 mysql 服务器才能让客户端看到它们：
/etc/init.d/mysql restart
Then it would work fine
然后它会正常工作
