Error select command denied to user userid ip-address for table table-name

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       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     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  lt   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

User · Accepted Answer

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?

User · Answer

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

User · Answer

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   Hope this helps someone

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

try grant privileges again  GRANT ALL PRIVILEGES ON the database   TO  the user      WITH GRANT OPTION  FLUSH PRIVILEGES

User · Answer

A bit late to the party but  should you have root access  you can do the following directly   Log into your mysql as root     mysql -u root -p   Show databases   mysql gt SHOW DATABASES    Select mysql database  which is where all privileges info is located  mysql gt USE mysql    Show tables   mysql gt SHOW TABLES    The table concerning privileges for your case is  db   so let s see what columns it has   mysql gt DESC db    In order to list the users privileges  type the following command  for example   mysql gt 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 gt FLUSH PRIVILEGES    Ciao

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

my issues got fixed after upgrading to MySQL workbench latest version 8 0 18

User · Answer

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

User · Answer

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

[mysql] Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'

Examples related to mysql

Examples related to mysqldatareader

Examples related to mysql-error-1142