🚨 MySQL Error Guide: 'command denied to user' — Fix ERROR 1142

Overview

ERROR 1142 means the connection authenticated successfully but the account lacks the privilege for the operation it attempted on a specific object:

ERROR 1142 (42000): SELECT command denied to user 'reporting'@'10.0.2.15' for table 'orders'

This is an authorization failure, not an authentication one — unlike ERROR 1045 (access denied at login) or 1698 (socket auth). The message tells you exactly what you need: the command (SELECT), the user and host it evaluated (reporting@10.0.2.15), and the object (orders). The related ERROR 1143 is the column-level variant.

Symptoms

• A query fails with “SELECT/INSERT/UPDATE/DELETE/CREATE command denied to user … for table …”.
• The user connects fine and can run some statements but not others.
• Access works on one database or table but is denied on another.
• A new table or database is inaccessible to an app that could read the old ones.
• The denial names a host that differs from what you expected, hinting at the wrong grant row matching.

Common Root Causes

• Missing privilege on the object — the account was never granted SELECT/INSERT/etc. on that table or database.
• Grant scoped too narrowly — privileges given on db1.* but the query touches db2, or on specific tables but not a newly added one.
• Host mismatch — the account is reporting@10.0.% but the client connects from an IP that matches a different, less-privileged row (e.g. reporting@%).
• Views/stored programs and DEFINER rights — a view or routine runs with a definer that itself lacks the privilege.
• Privileges granted but not effective — a grant made directly in mysql.* tables without FLUSH PRIVILEGES, or the session predates the grant.
• Revoked or role-based access — a role granting the privilege is not activated for the session.

Diagnostic Workflow

Read the exact user@host and object from the error, then dump that account’s effective grants:

SHOW GRANTS FOR 'reporting'@'10.0.2.15';
-- If unsure which row matched, check the identity MySQL sees for the session:
SELECT CURRENT_USER(), USER();

CURRENT_USER() is the account row MySQL matched (which may be broader, like reporting@%); USER() is how you connected. A mismatch explains “denied” despite a grant on a more specific host. Inspect the privilege tables for all rows that could match:

SELECT user, host FROM mysql.user WHERE user = 'reporting';
SELECT user, host, db FROM mysql.db WHERE user = 'reporting';
SELECT * FROM information_schema.table_privileges
WHERE grantee = "'reporting'@'10.0.2.15'";

If roles are in use, check what is active:

SELECT CURRENT_ROLE();
SHOW GRANTS FOR CURRENT_USER() USING `reporting_ro`;

Then grant precisely what is missing (least privilege) and re-check:

GRANT SELECT ON shop.orders TO 'reporting'@'10.0.2.15';
FLUSH PRIVILEGES;

Example Root Cause Analysis

A reporting service could read most tables but failed on one:

ERROR 1142 (42000): SELECT command denied to user 'reporting'@'%' for table 'orders'

The DBA had run GRANT SELECT ON shop.orders TO 'reporting'@'10.0.0.0/255.255.0.0', so they were sure the grant existed. But the error named reporting@%, not the subnet account. SELECT CURRENT_USER() confirmed the session matched the broader reporting@% row because MySQL’s host-matching picked it, and that row had grants on the older tables but not on the newly added orders. Two accounts for the same user with overlapping hosts caused the wrong one to win. The fix was to consolidate to a single, correctly scoped account and grant SELECT ON shop.orders to the row that actually matched. The lesson: CURRENT_USER() reveals which grant row is really in play, and overlapping host patterns cause “impossible” 1142s.

Prevention Best Practices

• Follow least privilege: grant exactly the commands and objects an account needs, no ALL PRIVILEGES on *.*.
• Avoid overlapping host patterns for the same user; MySQL matches the most specific host, which may not be the one you granted on.
• Use CURRENT_USER() when debugging to see which account row actually matched the session.
• Prefer roles (MySQL 8.0) to bundle and consistently apply privileges, and confirm the role is activated.
• When adding tables or databases, update the relevant grants so new objects are not silently inaccessible.
• Manage grants through version-controlled scripts so effective privileges are auditable and reproducible.

Quick Command Reference

SELECT CURRENT_USER(), USER();                       -- matched row vs how you connected
SHOW GRANTS FOR CURRENT_USER();                      -- effective privileges
SELECT user, host FROM mysql.user WHERE user='reporting';  -- overlapping host rows?
GRANT SELECT ON shop.orders TO 'reporting'@'10.0.2.15';    -- least-privilege grant
FLUSH PRIVILEGES;

Conclusion

ERROR 1142 is an authorization gap, not a login failure: the account connected but lacks the named privilege on the named object. Read the command, user@host, and table from the message, then use CURRENT_USER() to see which grant row MySQL actually matched — overlapping host patterns are a frequent culprit. Grant precisely the missing privilege following least privilege, consolidate ambiguous accounts, and keep grants in version control so access stays auditable and predictable.