MySQL Error: #1142 - SELECT command denied to user

Question

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.

A typo of mine from mine since I copied it to here from my old database. It wasn't there on the main database so the problem isn't solved through. Also it's not a privilege problem since I got full access. — Seph, Jun 08 '12 at 21:31
It shouldn't matter, but have you tried using qualified names for the fields that don't have a name conflict? So `projecten.titel`, and the like? — Brian Warshaw, Jul 03 '12 at 12:06
another reason might be your account don't have the permission. — Eric, May 26 '23 at 10:59

Vaibs · Accepted Answer · 2015-11-02T10:48:29.150

59

I faced the same situation but its funny that reason for the error was due to the use of the incorrect database or schema name.

Its true that multiple issues can lead to error you have mentioned.

edited Nov 02 '15 at 10:48

answered Aug 19 '15 at 09:38

Vaibs

2,018
22
29

3

+1 Yes, its possible. I hit this too.. In my case my new table name had a '.' accidentally. I wanted the table name to be MAP.DATE where DATE is timestamp. Its made it think like a schema. I replace . with _ eventually and it worked – KeshV Oct 26 '18 at 17:50
2

bit more funny in my case. i just logged out and logged in. `it worked for me :)` – Aman Deep Jun 18 '20 at 11:45
1

Yes, thats true. In my case the schema name was wrong. After changing to correct schema name, query worked – Amit Nargund Jun 16 '21 at 07:20
yes, it is highly possible. I already grant all permission for user but still get `denied` error. – Khaerul Umam Feb 27 '23 at 06:42
Yes correct.I am also faced the same issue. – Ganesan J Apr 03 '23 at 13:23

score 19 · Answer 2 · edited May 23 '17 at 12:18

19

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

edited May 23 '17 at 12:18

Community

1
1

answered Jun 08 '12 at 21:35

khaled_webdev

1,420
15
20

Already tried that and it still isn't working, but thanks anyway. – Seph Jun 08 '12 at 21:48
3

Did you find an answer to this? I have the same issue now. User has privileges, but can't select from this one table. – Kenzo Aug 23 '13 at 12:17
It can be wrong SELECT expression, this occurs in 5.7 (seems more strict) – nvvetal Feb 03 '16 at 13:55
In my case, I used a user that was created only for the task, and this user did not have access to the `temp` database which my normal user can access. I did not understand this. Therefore, I created a new connection to the host in my rdms editor and visually checked whether the connection worked at all, created the connection, and then I saw the missing database. – questionto42 Jan 30 '22 at 22:39

score 10 · Answer 3 · edited Jan 30 '22 at 19:36

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

For instance, when executing 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 properly, syntax solution for above instance:

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

score 5 · Answer 4 · answered May 27 '15 at 12:16

5

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

answered May 27 '15 at 12:16

Erik Schroder

63
1
6

score 4 · Answer 5 · answered Jul 03 '12 at 11:57

4

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

answered Jul 03 '12 at 11:57

Dhileepan

2,037
8
24
28

score 2 · Answer 6 · edited Jul 14 '20 at 18:59

2

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

edited Jul 14 '20 at 18:59

ChrisGPT was on strike

127,765
105
273
257

answered Jul 14 '20 at 18:17

faizah

31
1

score 1 · Answer 7 · answered Jan 23 '14 at 00:04

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.

score 1 · Answer 8 · answered Aug 11 '20 at 06:08

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.

score 1 · Answer 9 · answered Sep 04 '21 at 11:37

I had this error, because I copied stored procedure from localhost and there the db name was different then on the server I wanted to use it. It took me a couple of hours to figure out, why I don't have permissions to execute the procedure... Check you db names pls :D Hope someone finds this useful...

score 1 · Answer 10 · answered Mar 14 '23 at 21:36

I might be late but posting here for newbies. So in my case, I'm using Laravel 9 with 2 Databases (MySQL) and In my localhost, it works perfectly but on live hosting, I am getting this error

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied

so after surfing around I found that I am using the wrong database name which causes the error. So be double sure to check both databases' names as per PHPMyAdmin.

score 0 · Answer 11 · answered Aug 21 '19 at 07:52

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.

score 0 · Answer 12 · answered Nov 11 '20 at 07:49

0

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

answered Nov 11 '20 at 07:49

Venu Reddy

1

Please add this type of answer in the comment. – Pratik Bhajankar Nov 11 '20 at 08:00
1

@PratikBhajankar with reputation < 50, you cannot comment. – questionto42 Jan 30 '22 at 22:32

score 0 · Answer 13 · answered Mar 20 '21 at 10:32

If you using/connect more then one databases then you should use same DB_USERNAME for all databases

I had the same issue solved by using same DB_USERNAME for all databases , because i was use/connect multiple databases in same project and every database DB_USERNAME was different so when i run the query select,update, delete etc from multiple database then was get this error.

because if you use more then one table in query and the database DB_USERNAME is different you will get this error.

score 0 · Answer 14 · answered Feb 24 '22 at 00:14

I tried to issue SELECT * FROM information_schema.INNODB_METRICS; on a so-called "budget host." I got 1142 because reading that particular system table requires the PROCESS privilege and the user didn't have it. I had to fool around to figure out it was PROCESS and not some other privilege that was missing.

(No, the "budget host" won't grant that priv to the user. They probably don't want their customers to see how ridiculously overloaded their servers are. Because MySQL licenses are expens .... wait, no they're free.)

score -3 · Answer 15 · answered Dec 07 '14 at 11:07

-3

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

answered Dec 07 '14 at 11:07

Harry

9

1

the question wasn't about some unspecified browser, just pure MySQL – SBH Dec 07 '14 at 11:17

MySQL Error: #1142 - SELECT command denied to user

15 Answers15

Linked