MySQL 错误：选择命令拒绝用户 '<userid>'@'<ip-address>' 表 '<table-name>'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4767055/
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
Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'
提问by Parth Bhatt
In my website, I am using MySQL database. I am using a webservice where in I do all my database related manipulations.
在我的网站中，我使用的是 MySQL 数据库。我正在使用一个网络服务，我在其中执行所有与数据库相关的操作。
Now In one of the methods of that webservice, I get the following Error.
现在，在该网络服务的其中一种方法中，我收到以下错误。
select command denied to user ''@'' for table ''
选择命令拒绝用户 ''@'' 表 ''
What could be wrong?
可能有什么问题？
Below is the code where I get that error. I tried debugging and found that it fails at the line
下面是我收到该错误的代码。我试过调试，发现在线路上失败了
MySqlDataReader result1 = command1.ExecuteReader();
MySqlDataReader result1 = command1.ExecuteReader();
Here is my code:
这是我的代码：
String addSQL = "Select Max(`TradeID`) from `jsontest`.`tbl_Positions";
MySqlConnection objMyCon = new MySqlConnection(strProvider);
objMyCon.Open();
MySqlCommand command = objMyCon.CreateCommand();
command.CommandText = addSQL;
MySqlDataReader result = command.ExecuteReader();
//int j = command.ExecuteNonQuery();
while (result.Read())
{
MaxTradeID = Convert.ToInt32(result[0]);
}
objMyCon.Close();
for (i = 1; i <= MaxTradeID; i++)
{
String newSQL = "Select `Strike`,`LongShort`,`Current`,`TPLevel`,`SLLevel` from `json`.`tbl_Position` where `TradeID` = '" + i + "'";
MySqlConnection objMyCon1 = new MySqlConnection(strProvider);
objMyCon1.Open();
MySqlCommand command1 = objMyCon1.CreateCommand();
command1.CommandText = newSQL;
MySqlDataReader result1 = command1.ExecuteReader();
objMyCon2.Close();
采纳答案by Shakti Singh
database user does not have the permission to do select query.
数据库用户没有选择查询的权限。
you can grant the permission to the user if you have root access to mysql
如果您对 mysql 具有 root 访问权限，则可以向用户授予权限
http://dev.mysql.com/doc/refman/5.1/en/grant.html
http://dev.mysql.com/doc/refman/5.1/en/grant.html
Your second query is on different database on different table.
您的第二个查询在不同表上的不同数据库上。
String newSQL = "Select `Strike`,`LongShort`,`Current`,`TPLevel`,`SLLevel` from `json`.`tbl_Position` where `TradeID` = '" + i + "'";
And the user you are connecting with does not have permission to access data from this database or this particular table.
并且您所连接的用户无权访问此数据库或此特定表中的数据。
Have you consider this thing?
你考虑过这件事吗？
回答by pisces22
I'm sure the original poster's issue has long since been resolved. However, I had this same issue, so I thought I'd explain what was causing this problem for me.
我相信原始海报的问题早已得到解决。但是，我遇到了同样的问题，所以我想我会解释是什么导致了这个问题。
I was doing a union query with two tables -- 'foo' and 'foo_bar'. However, in my SQL statement, I had a typo: 'foo.bar'
我正在对两个表进行联合查询——'foo' 和 'foo_bar'。但是，在我的 SQL 语句中，我有一个错字：'foo.bar'
So, instead of telling me that the 'foo.bar' table doesn't exist, the error message indicates that the command was denied -- as though I don't have permissions.
因此，不是告诉我 'foo.bar' 表不存在，错误消息表明命令被拒绝——好像我没有权限。
Hope this helps someone.
希望这可以帮助某人。
回答by Mukesh kumar
select command denied to user ''@'' for table ''
选择命令拒绝用户 ''@'' 表 ''
This problem is a basically generated after join condition are wrong database name in your join query. So please check the your select query in join table name after database.
这个问题基本上是在连接查询中连接条件错误的数据库名称后生成的。因此，请在数据库后的连接表名称中检查您的选择查询。
Then solve it for example its correct ans ware
然后解决它，例如它的正确 ans ware
string g = " SELECT `emptable`.`image` , `applyleave`.`id` , `applyleave`.`empid` , `applyleave`.`empname` , `applyleave`.`dateapply` , `applyleave`.`leavename` , `applyleave`.`fromdate` , `applyleave`.`todate` , `applyleave`.`resion` , `applyleave`.`contact` , `applyleave`.`leavestatus` , `applyleave`.`username` , `applyleave`.`noday` FROM `DataEMP_ems`.`applyleave` INNER JOIN `DataEMP_ems`.`emptable` ON ( `applyleave`.`empid` = `emptable`.`empid` ) WHERE ( `applyleave`.`leavestatus` = 'panding' ) ";
The join table is imputableand applyleaveon the same database but online database name is diffrent then given error on this problem.
连接表imputable和applyleave在同一个数据库上，但在线数据库名称不同，然后在这个问题上给出错误。
回答by muttonUp
This problem happened to me because I had the hibernate.default_schemaset to a different database than the one in the DataSource.
这个问题发生在我身上，因为我hibernate.default_schema设置的数据库与 DataSource 中的数据库不同。
Being strict on my mysql user permissions, when hibernate tried to query a table it queried the one in the hibernate.default_schemadatabase for which the user had no permissions.
对我的 mysql 用户权限很严格，当 hibernate 尝试查询一个表时，它会查询hibernate.default_schema数据库中用户没有权限的表。
Its unfortunate that mysql does not correctly specify the database in this error message, as that would've cleared things up straight away.
不幸的是，mysql 没有在此错误消息中正确指定数据库，因为这会立即解决问题。
回答by gregjor
You need to grant SELECT permissions to the MySQL user who is connecting to MySQL. See:
您需要向连接到 MySQL 的 MySQL 用户授予 SELECT 权限。看：
http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html
http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
回答by superaesomedad
I had the exact same error message doing a database export via Sequel Pro on a mac. I was the root user so i knew it wasn't permissions. Then i tried it with mysqldump and got a different error message: Got error: 1449: The user specified as a definer ('joey'@'127.0.0.1') does not exist when using LOCK TABLES
在 Mac 上通过 Sequel Pro 进行数据库导出时，我收到了完全相同的错误消息。我是 root 用户，所以我知道这不是权限。然后我用 mysqldump 试了一下，得到了一个不同的错误信息： Got error: 1449: The user specified as adefiner ('joey'@'127.0.0.1') does not exist when using LOCK TABLES
Ahh, I had restored this database from a backup on the dev site and I hadn't created that user on this machine. "grant all on .to 'joey'@'127.0.0.1' identified by 'joeypass'; " did the trick.
啊，我已经从开发站点的备份中恢复了这个数据库，但我没有在这台机器上创建那个用户。“将所有权限授予.到 'joey'@'127.0.0.1' 由 'joeypass' 标识；”成功了。
hth
第
回答by U. Busto
If you are working from a windows forms application this worked for me
如果您使用 Windows 窗体应用程序工作，这对我有用
"server=localhost; user id=dbuser; password=password; database=dbname; Use Procedure Bodies=false;"
Just add the "Use Procedure Bodies=false"at the end of your connection string.
只需"Use Procedure Bodies=false"在连接字符串的末尾添加。
回答by deepika
I had the same problem. This is related to hibernate. I changed the database from dev to production in hibernate.cfg.xml but there were catalog attribute in other hbm.xml files with the old database name and it was causing the issue.
我有同样的问题。这与休眠有关。我在 hibernate.cfg.xml 中将数据库从 dev 更改为 production，但其他 hbm.xml 文件中存在具有旧数据库名称的目录属性，这导致了问题。
Instead of telling incorrect database name, it showed Permission denied error.
它没有告诉错误的数据库名称，而是显示 Permission denied 错误。
So make sure to change the database name everywhere or just remove the catalog attribute
因此，请确保随处更改数据库名称或仅删除目录属性
回答by Reza
The problem is most probably between a . and a _. Say in my query I put
问题很可能在 . 和一个_。在我的查询中说我把
SELECT ..... FROM LOCATION.PT
选择 ..... 从 LOCATION.PT
instead of
代替
SELECT ..... FROM LOCATION_PT
选择 ..... 从 LOCATION_PT
So I think MySQL would think LOCATION as a database name and was giving access privilege error.
所以我认为 MySQL 会将 LOCATION 视为数据库名称，并给出访问权限错误。
回答by Narayan P
my issues got fixed after upgrading to MySQL workbench latest version 8.0.18
升级到 MySQL Workbench 最新版本 8.0.18 后，我的问题得到了解决
