Error: select command denied to user '

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

Answer 1

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 conection string.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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