Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL error 1142: the account may not SELECT from this table

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

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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();
  2. 2Match what you see, then jump to its fix

    1. 1SHOW GRANTS lists no SELECT on the database or table named in the errorThe account has no SELECT grant on that table or databaseFix
    2. 2The 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. 3SHOW 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.