错误:选择命令被拒绝给用户 ''@''对于表 '<表名>'

发布于 2024-10-13 08:02:53 字数 1383 浏览 4 评论 0原文

在我的网站中,我使用 MySQL 数据库。我正在使用 Web 服务,在其中执行所有与数据库相关的操作。

现在,在该 Web 服务的一种方法中,我收到以下错误。

select 命令被拒绝给用户“”@'”对于表“<表名>”

可能出什么问题了?

下面是我收到该错误的代码。 行失败,

我尝试调试,发现它在MySqlDataReader result1 = command1.ExecuteReader();

这是我的代码:

        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();

In my website, I am using a MySQL database. I am using a Web service, where I do all my database-related manipulations.

Now, in one of the methods of that Web service, I get the following error.

select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'

What could be wrong?

Below is the code where I get that error. I tried debugging and found that it fails at line

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();

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(18

风苍溪 2024-10-20 08:02:54

我在 Mac 上通过 Sequel Pro 进行数据库导出时遇到了完全相同的错误消息。我是 root 用户,所以我知道这不是权限。然后我尝试使用 mysqldump 并收到不同的错误消息:
出现错误:1449:使用 LOCK TABLES 时指定为定义者的用户 ('joey'@'127.0.0.1') 不存在

啊,我已从开发站点上的备份恢复了此数据库而且我没有在这台机器上创建该用户。 “将 . 上的所有内容授予由 'joeypass' 识别的 'joey'@'127.0.0.1';”成功了。

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

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.

hth

驱逐舰岛风号 2024-10-20 08:02:54

如果您正在使用 Windows 窗体应用程序,这对我有用,

"server=localhost; user id=dbuser; password=password; database=dbname; Use Procedure Bodies=false;"

只需在连接字符串末尾添加 “Use procedure Bodies=false” 即可。

If you are working from a windows forms application this worked for me

"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.

行至春深 2024-10-20 08:02:54

对于我来说,我不小心在 SQL 查询中包含了本地数据库名称,因此在部署时出现了访问被拒绝的问题。

我从 SQL 查询中删除了数据库名称,它得到了修复。

For me, I accidentally included my local database name inside the SQL query, hence the access denied issue came up when I deployed.

I removed the database name from the SQL query and it got fixed.

定格我的天空 2024-10-20 08:02:54

免责声明

  • 先备份。
  • 在执行之前检查您的查询语句。
  • 确保您在更新之前添加了 WHERE(过滤器)子句。

如果您有 root 访问权限或足够的权限,您可以直接执行以下操作:

以 root 身份登录 MySQL,

$ mysql -u root -p

显示数据库;

mysql>SHOW DATABASES;

选择 MySQL 数据库,这是所有权限信息所在的位置

mysql>USE mysql;

显示表。

mysql>SHOW TABLES;

与您的情况相关的权限表是“db”,所以让我们看看它有哪些列:

mysql>DESC db;

为了列出用户的权限,请键入以下命令,例如:

mysql>SELECT user, host, db, Select_priv, Insert_priv, Update_priv, Delete_priv FROM db ORDER BY user, db;

如果您找不到该用户或者如果您看到该用户该用户在 Select_priv 列中具有“N”,则您必须相应地插入或更新:

插入:

INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('localhost','DBname','UserName','Y' ,'N','N','N');

更新:

UPDATE db SET Select_priv = 'Y' WHERE User = 'UserName' AND Db = 'DBname' AND Host='localhost';

最后,键入以下命令:

mysql>FLUSH PRIVILEGES;

Ciao。

Disclaimer

  • Backup first.
  • Check your query sentence before executing.
  • Make sure you've added a WHERE (filter) clause before updating.

In case you have root access or enough privileges, you can do the following directly:

Log into your MySQL as root,

$ mysql -u root -p

Show databases;

mysql>SHOW DATABASES;

Select MySQL database, which is where all privileges info is located

mysql>USE mysql;

Show tables.

mysql>SHOW TABLES;

The table concerning privileges for your case is 'db', so let's see what columns it has:

mysql>DESC db;

In order to list the users' privileges, type the following command, for example:

mysql>SELECT user, host, db, Select_priv, Insert_priv, Update_priv, Delete_priv FROM db ORDER BY user, db;

If you can't find that user or if you see that that user has a 'N' in the Select_priv column, then you have to either INSERT or UPDATE accordingly:

INSERT:

INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('localhost','DBname','UserName','Y' ,'N','N','N');

UPDATE:

UPDATE db SET Select_priv = 'Y' WHERE User = 'UserName' AND Db = 'DBname' AND Host='localhost';

Finally, type the following command:

mysql>FLUSH PRIVILEGES;

Ciao.

虫児飞 2024-10-20 08:02:54

我有这样的场景:

我在本地主机上开发了一个数据库,名为 test
然后我购买了一个带有 cpanel 的托管计划,我需要创建一个带有一些前缀的新数据库(前缀是我的帐户名)。所以新的数据库名称是user_test

在我的程序中,我编写了如下语句:

select * from test.t_table_name;

因此,当我导入数据库时​​,我忘记用 user_test 更改 test ,它给了我以下消息:SELECT 命令拒绝用户“user”@“localhost”获取表“t_table_name”

所以发生的情况是服务器尝试访问其他数据库(在我的例子中是测试数据库),但该数据库未创建在 cpanel 中(仅存在 user_test ),服务器给了我这个错误。

我认为最好告诉该服务器上没有这样的数据库,因为这是实际需要更改的内容。

希望它能帮助某人。

干杯。

I had this scenario:

I developed a database on my localhost, named test.
Then I bought a hosting plan with cpanel where I needed to create a new database with some prefix (the prefix was my account name). So the new database name was user_test.

In my procedures, I wrote the statements like:

select * from test.t_table_name;

So when I imported the database I forgot to change the test with user_test and it gave me this message: SELECT command denied to user 'user'@'localhost' for table 't_table_name'

So what happened is that the server tried to access some other database (in my case test database) which was not created in cpanel (where only user_test existed) and the server gave me this error.

I think it will be better to tell that there isn't such a database on this server because this is the actual thing that needs to be changed.

Hope it helps someone.

Cheers.

沫离伤花 2024-10-20 08:02:54

问题很可能是在 .和一个_。说在我的查询中,我输入

SELECT ..... FROM LOCATION.PT

而不是

SELECT ..... FROM LOCATION_PT

所以我认为 MySQL 会将 LOCATION 视为数据库名称,并给出访问权限错误。

The problem is most probably between a . and a _. Say in my query I put

SELECT ..... FROM LOCATION.PT

instead of

SELECT ..... FROM LOCATION_PT

So I think MySQL would think LOCATION as a database name and was giving access privilege error.

霞映澄塘 2024-10-20 08:02:54

我也有同样的问题。这与休眠有关。我在 hibernate.cfg.xml 中将数据库从开发更改为生产,但其他 hbm.xml 文件中存在具有旧数据库名称的目录属性,这导致了问题。

它没有显示错误的数据库名称,而是显示权限被拒绝错误。

因此,请确保在各处更改数据库名称或仅删除目录属性

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.

Instead of telling incorrect database name, it showed Permission denied error.

So make sure to change the database name everywhere or just remove the catalog attribute

我不会写诗 2024-10-20 08:02:54

升级到 MySQL Workbench 最新版本 8.0.18 后我的问题得到解决

my issues got fixed after upgrading to MySQL workbench latest version 8.0.18

雄赳赳气昂昂 2024-10-20 08:02:54

我也有同样的问题。我对此感到非常沮丧。也许这不是回答问题,但我只是想分享我的错误经历,可能还有其他人和我一样遭受苦难。显然这只是我的准确性低。

我有这样的:

SELECT t_comment.username,a.email FROM t_comment
LEFT JOIN (
    SELECT username,email FROM t_un
) a
ON t_comment.username,a.email

应该是这样的:

SELECT t_comment.username,a.email FROM t_comment
LEFT JOIN (
    SELECT username,email FROM t_un
) a
ON t_comment.username=a.username

然后我的问题那天就解决了,我已经挣扎了两个小时,就为了这个问题。

I had the same problem. I was very frustrating with it. Maybe this is not answering the question, but I just want to share my error experience, and there may be others who suffered like me. Evidently it was just my low accuracy.

I had this:

SELECT t_comment.username,a.email FROM t_comment
LEFT JOIN (
    SELECT username,email FROM t_un
) a
ON t_comment.username,a.email

which is supposed to be like this:

SELECT t_comment.username,a.email FROM t_comment
LEFT JOIN (
    SELECT username,email FROM t_un
) a
ON t_comment.username=a.username

Then my problem was resolved on that day, I'd been struggled in two hours, just for this issue.

∞梦里开花 2024-10-20 08:02:54

我确信这个问题已经解决,只是想指出我在数据库名称中有一个拼写错误,并且它仍然在表名称上抛出此错误。因此,在这种情况下,您可能需要检查是否有拼写错误。

I am sure this has been resolved, just want to point out I had a typo in the database name and it was still throwing this error on the table name. So you might want to check for typos in this case.

や三分注定 2024-10-20 08:02:54

尝试此解决错误:SELECT 命令被拒绝给用户“myuser”@“132.11.%”

1. CREATE USER 'myuser'@'%' IDENTIFIED BY 'Password@123';
2. GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
3. FLUSH PRIVILEGES;

Try this to resolve error: SELECT command denied to user 'myuser'@'132.11.%'

1. CREATE USER 'myuser'@'%' IDENTIFIED BY 'Password@123';
2. GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
3. FLUSH PRIVILEGES;
白馒头 2024-10-20 08:02:54

与其他答案类似,我错过了输入查询。

我有 -

SELECT t.id FROM t.table LEFT JOIN table2 AS t2 ON t.id = t2.table_id

应该是

SELECT t.id FROM table AS t LEFT JOIN table2 AS t2 ON t.id = t2.table_id

Mysql 试图找到一个名为 t 的数据库,而用户没有权限。

Similar to other answers I had miss typed the query.

I had -

SELECT t.id FROM t.table LEFT JOIN table2 AS t2 ON t.id = t2.table_id

Should have been

SELECT t.id FROM table AS t LEFT JOIN table2 AS t2 ON t.id = t2.table_id

Mysql was trying to find a database called t which the user didn't have permission for.

月亮坠入山谷 2024-10-20 08:02:53

我确信原发帖者的问题早已得到解决。但是,我遇到了同样的问题,所以我想我应该向我解释一下是什么导致了这个问题。

我正在对两个表进行联合查询——“foo”和“foo_bar”。然而,在我的 SQL 语句中,我有一个拼写错误:“foo.bar”,

因此,错误消息并没有告诉我“foo.bar”表不存在,而是指示该命令被拒绝——就好像我没有权限。

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'

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.

假面具 2024-10-20 08:02:53

数据库用户没有执行选择查询的权限。如果您具有 mysql 的 root 访问权限,则可以向该用户授予权限。

http://dev.mysql.com/doc/refman/5.1/ en/grant.html

您的第二个查询位于不同的数据库和不同的表上。

 String newSQL = "Select `Strike`,`LongShort`,`Current`,`TPLevel`,`SLLevel` from `json`.`tbl_Position` where `TradeID` = '" + i + "'";

并且您正在连接的用户没有访问此数据库或此特定表中的数据的权限。

你考虑过这个吗?

The database user does not have the permission to do a select query. You can grant the permission to the user, if you have root access to mysql.

http://dev.mysql.com/doc/refman/5.1/en/grant.html

Your second query is on a different database and on a 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 a permission to access data from this database or this particular table.

Have you consider this?

一口甜 2024-10-20 08:02:53

这个问题发生在我身上,因为我将 hibernate.default_schema 设置为与数据源中的数据库不同的数据库。

由于对 mysql 用户权限严格,当 hibernate 尝试查询表时,它会查询 hibernate.default_schema 数据库中用户没有权限的表。

不幸的是,mysql 没有在此错误消息中正确指定数据库,因为这会立即清除问题。

This problem happened to me because I had the hibernate.default_schema set to a different database than the one in the DataSource.

Being strict on my mysql user permissions, when hibernate tried to query a table it queried the one in the hibernate.default_schema database for which the user had no permissions.

Its unfortunate that mysql does not correctly specify the database in this error message, as that would've cleared things up straight away.

折戟 2024-10-20 08:02:53

用户“@”对于表“”的选择命令被拒绝

此问题基本上是在连接查询中的连接条件错误的数据库名称后生成的。因此,请检查数据库后连接表名中的选择查询。

然后解决它,例如它的正确答案

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' ) ";

连接表是imputableapplyleave在同一数据库上,但在线数据库名称不同,然后在这个问题上给出错误。

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

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 imputable and applyleave on the same database but online database name is diffrent then given error on this problem.

樱娆 2024-10-20 08:02:53

再次尝试授予权限。

GRANT ALL PRIVILEGES ON the_database.* TO 'the_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

try grant privileges again.

GRANT ALL PRIVILEGES ON the_database.* TO 'the_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文