MySQL error 1142: the account may not SELECT from this table
ERROR 1142 (42000): SELECT command denied to user 'app'@'10.0.0.5' for table 'orders'Short answer
Error 1142 means the account MySQL matched for your login has no privilege on that table. Run SELECT CURRENT_USER() to see which account was used, then SHOW GRANTS for it. Grant SELECT at the table or database level to that exact user and host. Do not assume the user@host you meant is the one that matched.
Fix it fast
1Find out which cause you have
Find out which account MySQL really used for this session, then read what that account may do.
SELECT CURRENT_USER(); SHOW GRANTS FOR CURRENT_USER();2Match what you see, then jump to its fix
- 1
SHOW GRANTS lists no SELECT on the database or table named in the errorThe account has no SELECT grant on that table or databaseFix - 2
The host in the error (an IP or a name) is not the host in your GRANTYou connect as a different host than the one you granted toFix - 3
SHOW GRANTS shows SELECT, yet an application that was already connected still gets 1142 after a database-level grantThe grant was made but the open session has not picked it upFix
- 1
What does the error look like?
ERROR 1142 (42000): SELECT command denied to user 'app'@'10.0.0.5' for table 'orders'SELECT command denied to user '<userid>'@'<ip-address>' for table '<table-name>'
Cause 1 of 3
The account has no SELECT grant on that table or database
1Check
The message names the user, the host and the table. Compare them with the output of SHOW GRANTS. Privileges are added together across the global, database and table levels, so a missing SELECT at all three is the cause.
SHOW GRANTS FOR 'app'@'10.0.0.5';2Fix
Grant SELECT at the narrowest level that covers the need: one table, or every table in the database.
GRANT SELECT ON shop.orders TO 'app'@'10.0.0.5';
-- or for the whole database
GRANT SELECT ON shop.* TO 'app'@'10.0.0.5';3Verify
Reconnect as that user and run the query again.
SELECT * FROM shop.orders LIMIT 1;Confirmed by dev.mysql.com
Cause 2 of 3
You connect as a different host than the one you granted to
1Check
An account is a user name plus a host name, so 'app'@'localhost' and 'app'@'%' are separate accounts. When several rows fit, the server uses the first in its sort order, with specific hosts before '%'. CURRENT_USER() shows the winner.
SELECT CURRENT_USER();
SELECT user, host FROM mysql.user WHERE user = 'app';2Fix
Grant to the account that is actually matched, or create the account for the host you connect from.
GRANT SELECT ON shop.* TO 'app'@'10.0.0.5';
-- or, if any host is intended
GRANT SELECT ON shop.* TO 'app'@'%';3Verify
Reconnect and confirm both the matched account and the result.
SELECT CURRENT_USER();
SELECT COUNT(*) FROM shop.orders;Confirmed by dev.mysql.com
Cause 3 of 3
The grant was made but the open session has not picked it up
1Check
Table and column privileges apply with the client's next request. Database privileges apply the next time the client runs USE db_name, and an application may keep the database name cached.
SELECT DATABASE();2Fix
Run USE for the database again, or reconnect. Changes made with GRANT take effect without FLUSH PRIVILEGES; that is only for edits made directly to the grant tables.
USE shop;3Verify
Run the failing query in the same session.
SELECT * FROM orders LIMIT 1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Grant to a named host, check the result with SHOW GRANTS and keep the statements in a migration or setup script.
- Give each application its own account with only the privileges it needs.
- Log SELECT CURRENT_USER() once at app start-up when you debug permission errors.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix