Error: select command denied to user ''@'' for table ''

Question

In my website, I am using MySQL database. I am using a webservice where in I do all my database related manipulations.

Now In one of the methods of that webservice, 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 the 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();

score 197 · Answer 1 · edited Dec 23 '22 at 08:47

197

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.

edited Dec 23 '22 at 08:47

starball

20,030
7
43
238

answered Apr 14 '11 at 19:29

pisces22

1,979
2
11
2

1

In my case (Java Hibernate app) a stray @Table.catalog annotation was overriding the database name supplied in the connection string. – Alex R Jun 01 '15 at 01:54
2

This was also the root cause for me, a typo. From a security perspective it makes sense to give an access denied error because if a cracker is trying to figure out table names by trying various names getting a “doesn’t exist” error would indicate which table names do in fact exist at which point they could apply brute force to figure out the username and password. Given in my experience how simple usernames and passwords are (usually <10 characters of concat'd dictionary words) it probably wouldn't take that long either... – Kurt Wagner Sep 24 '15 at 16:21
4

In my case, it was because some query code (like the data) had been copied from a SQL Server database, and the `dbo.` didn't work in the MySQL database: when `dbo.tablename` was changed to `tablename`, it executed without error. – Doug_Ivison Jun 14 '16 at 13:35
1

same here yesterday `select * from dbo.table` worked, today only `select * from table` if i add `dbo` prefix then access deny :) – SüniÚr Mar 14 '18 at 18:05
This happens also on cross-database joins if you forget to specify the databasename explicitly on one of the joins. – grandchild Jul 05 '19 at 13:43
could you please add "selecting from wrong database schema" as well to the answer? Instead of the error saying I had no such database schema, the error kept saying "user has no select privileges". – Bharath Parlapalli Aug 21 '19 at 12:47
Same happened with me. I was using . instead of .
– Pavan Tej Jan 21 '20 at 16:35
My problem was using the name of the [Dev database].table, which wasnt there in QA. – descript Jul 21 '20 at 22:37
I was driving crazy, the problem was that i changed the name of an external DB referred by the query through a join. Wrong error message! FIXED! Thanks. – fede72bari Feb 22 '21 at 13:09
How weird! I ran into the same issue, I had `SELECT q.* FROM q.quote`, and correcting it to `SELECT q.* FROM quote AS q` worked. – Smithee May 25 '21 at 08:49
BAD CASE! @PavanTej exemplified it, but subtly. Thought I'd post here more blatantly that bad Case on a Case-sensitive file system (Unix, etc.) will cause this. Reading all the answers here is a bit daunting. SUMMARY: Yes, it could be permission. Change permissions. However, irritatingly, it seems that ANY BAD REFERENCE to a Schema or Table will cause this - misspellings/wrong special characters, or bad CASE on a Case Sensitive system. RANT: it's absurd that this not only useless, but very misleading, error message remains to this day (2021-Novemer). Oracle: C'Mon, Man! – BRebey Nov 04 '21 at 06:53

Shakti Singh · Accepted Answer · 2011-01-22T09:30:59.463

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

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?

score 11 · Answer 3 · answered Oct 13 '15 at 21:11

11

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.

answered Oct 13 '15 at 21:11

muttonUp

6,351
2
42
54

1

I had a catalog attribute set to the wrong schema name in the XML mapping of an old application - different flavour, but in the end a similar culprit. Thanks for pointing me in the right direction. – Giulio Piancastelli May 12 '16 at 08:35

score 10 · Answer 4 · edited Jan 31 '16 at 07:50

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.

score 3 · Answer 5 · answered Dec 09 '14 at 15:51

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

score 3 · Answer 6 · edited Nov 25 '19 at 07:23

3

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.

edited Nov 25 '19 at 07:23

Omkar

3,253
3
20
36

answered Jan 15 '15 at 18:34

U. Busto

194
1
17

score 3 · Answer 7 · answered Jan 22 '11 at 09:17

3

You need to grant SELECT permissions to the MySQL user who is connecting to MySQL. See:

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

answered Jan 22 '11 at 09:17

gregjor

21,802
1
23
16

Pathros · Answer 8 · 2021-04-06T03:45:36.117

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.

score 2 · Answer 9 · answered May 10 '20 at 10:04

2

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.

answered May 10 '20 at 10:04

sajin tm

323
3
9

adding a point to that, when we generate the SQL from DB tools, there may be a chance that it will add database names to the query. Please consider a review on generated SQL since it will not work on remote machines since database names may vary. – sajin tm May 19 '20 at 01:10

score 2 · Answer 10 · answered Dec 16 '20 at 22:06

2

try grant privileges again.

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

answered Dec 16 '20 at 22:06

Márcio Rossato

971
1
11
20

score 1 · Answer 11 · answered Apr 22 '12 at 04:05

1

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.

answered Apr 22 '12 at 04:05

Reza

11
1

score 1 · Answer 12 · answered Jul 15 '14 at 12:07

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

score 1 · Answer 13 · answered Oct 18 '19 at 16:56

1

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

answered Oct 18 '19 at 16:56

Narayan P

139
1
3

score 1 · Answer 14 · answered Jul 10 '23 at 14:05

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.

Juna serbaserbi · Answer 15 · 2015-11-10T17:09:41.993

0

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.

edited Nov 10 '15 at 17:09

answered Nov 10 '15 at 17:02

Juna serbaserbi

205
2
12

As with Pisces22 (and myself), another problem that actually had nothing to do with the error message re' permissions. (Note: others have just as much difficulty *seeing* this difference, as you did, so for others: in the `ON` clause, `,a.email` was corrected to `=a.username`) – Doug_Ivison Jun 14 '16 at 13:31

score 0 · Answer 16 · answered Jun 02 '21 at 02:49

0

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.

answered Jun 02 '21 at 02:49

Peggy

394
6
22

score -1 · Answer 17 · answered Jun 19 '14 at 12:32

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.

Error: select command denied to user ''@'' for table ''

17 Answers17

Linked

Related