Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'
Solution 1
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?
Solution 2
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.
Solution 3
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.
Solution 4
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.
Solution 5
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.
Parth Bhatt
I am iPhone and iPad developer. https://github.com/akashraje/BidirectionalCollectionViewLayout ^[-_,A-Za-z0-9]$ NSRegularExpression *reg = [NSRegularExpression regularExpressionWithPattern:@"(#[a-zA-Z0-9_-]+)" options:NSRegularExpressionCaseInsensitive error:nil]; NSString *stringData = @"This is Parth known as #parth and this is an awesome place. I am fan of #scganguly."; int count = [reg numberOfMatchesInString:stringData options:0 range:NSMakeRange(0, [stringData length])]; NSLog(@"%d",count); if(count>0) { NSArray *array = [reg matchesInString:stringData options:0 range:NSMakeRange(0, [stringData length])]; NSLog(@"%@",array); NSMutableArray *stringArray = [[NSMutableArray alloc] init]; for (NSTextCheckingResult *result in array) { NSString *stringFinal = [stringData substringWithRange:result.range]; if(stringFinal != nil) { [stringArray addObject:stringFinal]; } } NSLog(@"stringArray: %@",stringArray); } For @user: @"(@[a-zA-Z0-9_]+)" NSLog(@"Request String: %@", requestString); NSData *requestData = [NSData dataWithBytes: [requestString UTF8String] length: [requestString length]]; // NSString *fileLoc = [[NSBundle mainBundle] pathForResource:@"url" ofType:@"plist" ]; // NSDictionary *fileContents = [[NSDictionary alloc] initWithContentsOfFile:fileLoc]; // NSString *urlLoc = [fileContents objectForKey:@"baseURL"]; NSString *urlLoc = @"http://portal.abc.com/candidate/post-info"; NSLog(@"URL is %@",urlLoc); NSMutableURLRequest *request = [[NSMutableURLRequest alloc] initWithURL: [NSURL URLWithString: urlLoc]]; NSString *postLength = [NSString stringWithFormat:@"%d", [requestData length]]; [request setHTTPMethod: @"POST"]; [request setValue:postLength forHTTPHeaderField:@"Content-Length"]; [request setValue:@"application/json" forHTTPHeaderField:@"Content-Type"]; [request setHTTPBody: requestData]; http://blog.stackoverflow.com/archive/ https://developer.apple.com/library/prerelease/ios/documentation/General/Conceptual/CocoaTouch64BitGuide/Introduction/Introduction.html http://nscookbook.com/2013/03/ios-programming-recipe-19-using-core-motion-to-access-gyro-and-accelerometer/ http://code4app.net/category/coremotion http://www.devx.com/wireless/Article/44799 upload audio file to php ios Android post request on IOS https://developers.facebook.com/docs/ios/share https://github.com/oliverbarreto/FastFavs/blob/master/TODO2.h
Updated on June 15, 2021Comments
-
Parth Bhatt almost 3 years
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();
-
Olivier.Roger about 10 yearsNice catch! I was looking for user permission instead of typos.
-
Alex R almost 9 yearsIn my case (Java Hibernate app) a stray @Table.catalog annotation was overriding the database name supplied in the connection string.
-
Kurt Wagner over 8 yearsThis 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...
-
Reality-Torrent over 8 yearsThank you, you saved me allot of time.
-
Bob Barbara almost 8 yearsI 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.
-
Doug_Ivison almost 8 yearsAs 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 almost 8 yearsIn 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: whendbo.tablename
was changed totablename
, it executed without error. -
SüniÚr about 6 yearssame here yesterday
select * from dbo.table
worked, today onlyselect * from table
if i adddbo
prefix then access deny :) -
grandchild almost 5 yearsThis happens also on cross-database joins if you forget to specify the databasename explicitly on one of the joins.
-
Bharath Parlapalli over 4 yearscould 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".
-
Pavan Tej over 4 yearsSame happened with me. I was using <database>.<table> instead of <Database>.<table>
-
sajin tm almost 4 yearsadding 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.
-
Abdul Saleem almost 4 yearsMysql has gone insane because of my strange error..!
-
descript almost 4 yearsMy problem was using the name of the [Dev database].table, which wasnt there in QA.
-
fede72bari about 3 yearsI 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.
-
Smithee almost 3 yearsHow weird! I ran into the same issue, I had
SELECT q.* FROM q.quote
, and correcting it toSELECT q.* FROM quote AS q
worked. -
BRebey over 2 yearsBAD 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!