[mysql] MySQL Error: #1142 - SELECT command denied to user

I'm having troubles with a certain query on one of my servers. On all other places I've tested it it works completely fine but on the server i want to use it it isn't working.

It's about the following SQL:

SELECT facturen.id            AS fid, 
       projecten.id           AS pid, 
       titel, 
       facturen.totaal_bedrag AS totaal, 
       betaald, 
       datum 
FROM   facturen, 
       projecten 
WHERE  facturen.project_id = projecten.id 
       AND projecten.eigenaar = '1' 
ORDER  BY datum DESC 

This is the error code I get from it:

SELECT command denied to user 'marco'@'localhost' for table 'projecten'

The tables: facturen:

CREATE TABLE IF NOT EXISTS `facturen` (
  `id` int(11) NOT NULL auto_increment,
  `project_id` int(11) NOT NULL,
  `datum` int(11) NOT NULL,
  `lever_datum` int(11) NOT NULL,
  `totaal_bedrag` decimal(9,2) NOT NULL,
  `btw` decimal(9,2) NOT NULL,
  `bedrijf` varchar(40) NOT NULL,
  `contactpersoon` varchar(60) NOT NULL,
  `adres` varchar(60) NOT NULL,
  `postcode` varchar(7) NOT NULL,
  `plaats` varchar(30) NOT NULL,
  `betaald` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=201200006 ;

projecten:

CREATE TABLE IF NOT EXISTS `projecten` (
  `id` int(11) NOT NULL auto_increment,
  `titel` varchar(80) NOT NULL,
  `eigenaar` int(11) NOT NULL,
  `creatie_datum` int(11) NOT NULL,
  `eind_datum` int(11) NOT NULL,
  `totaal_bedrag` decimal(9,2) NOT NULL,
  `btw` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=201200004 ;

The strange part is that every other query on both the 'projecten' table and the 'facturen' table works completely fine, also this query works fine on two other servers of mine.

This question is related to mysql select command

The answer is


This is th privileges issue in your database users. first check and grant permission to user 'marco' in localhost


This error also arises for a syntax error occurred due to aliasing tablename.

For instance, when executed below query,

select * from a.table1, b.table2 where a.table1= b.table2

below error occurs:

MySQL Error: #1142. Response form the database. SELECT command denied to user "username@ip" for table "table1"

Solution : Syntax to alias tablename should be used proper, syntax solution for above instance >select * from table1 a, table2 b where a.table1= b.table2


So the issue I ran into was this... the application I used to grant the permissions converted the Schema.TableName into a single DB statement in the wrong table, so the grant was indeed wrong, but looked correct when we did a SHOW GRANTS FOR UserName if you weren't paying very close attention to GRANT SELECT vs GRANT TABLE SELECT. Manually correcting the Grant Select on Table w/ proper escaping of Schema.Table solved my issue.

May be unrelated, but I can imagine if one client does this wrong, another might too.

Hope that's helpful.


This error happened on my server when I imported a view with an invalid definer.

Removing the faulty view fixed the error.

The error message didn't say anything about the view in question, but was "complaining" about one of the tables, that was used in the view.


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

same question as here Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'

see answers of the link ;)


I had this problem too and for me, the problem was that I moved to a new server and the database I was trying to connect to with my PHP code changed from "my_Database" to "my_database".


You should have to just clear sessions data thats it everything will work


You need to give privileges to the particular user by giving the command mysql> GRANT ALL PRIVILEGES . To 'username'@'localhost'; and then give FLUSH PRIVILEGES; command. Then it won't give this error.., hope it helps thank you..!


I just emptied my session data then it worked again. Here is where you find the button:


I run into this problem as well, the case with me was incorrect naming . I was migrating from local server to online server. my SQL command had "database.tablename.column" structure. the name of database in online server was different. for example my code was "pet.item.name" while it needed to be "pet_app.item.name" changing database name solved my problem.


In MySQL query browser go to Tools tab>MySQL Administrator > User Administration and then give the privileges to user.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to command

'ls' is not recognized as an internal or external command, operable program or batch file Command to run a .bat file how to run python files in windows command prompt? Run a command shell in jenkins How to recover the deleted files using "rm -R" command in linux server? Split text file into smaller multiple text file using command line ansible : how to pass multiple commands Jmeter - Run .jmx file through command line and get the summary report in a excel cocoapods - 'pod install' takes forever Daemon not running. Starting it now on port 5037