MySQL Error 1142 — Granted Permissions, Why Still Access Denied?
Resolving Error 1142/1143: USER() vs CURRENT_USER() discrepancy, host-specific account precedence, table vs column grants, and activating MySQL 8 default roles.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
ERROR 1142 (42000): SELECT command denied to user 'app'@'10.0.2.15' for table 'siparis'
ERROR 1143 (42000): SELECT command denied to user 'app'@'10.0.2.15' for column 'tutar' in table 'siparis'SQLSTATE code: 42000.
This guide represents the MySQL counterpart to PostgreSQL's H-34 (permission denied for table) and SQL Server's H-39 (Msg 229: SELECT permission was denied), completing the cross-engine Object Authorization Triad.
Distinguish between the two error codes:
- •1142: Access denied at the table or view level.
- •1143: Access denied at the granular column level. If querying a sensitive column without explicit column-level rights, MySQL emits 1143.
app, but strictly the `'user'@'host'` pair. 'app'@'localhost' and 'app'@'10.0.2.%' represent two distinct accounts possessing independent privilege grants. The vast majority of "I granted permissions but still got denied" inquiries stem from granting permissions to a host definition that does not match the client's actual matched account.▸Diagnostic Step: `USER()` vs `CURRENT_USER()`
SELECT USER() AS client_requested, CURRENT_USER() AS server_matched;- •`USER()`: The identity string and source IP submitted by the client connection.
- •`CURRENT_USER()`: The specific privilege table record that MySQL's ACL resolver matched against.
If they differ, you are granting permissions to the wrong account! If CURRENT_USER() resolves to app@%, but you granted permissions on 'app'@'10.0.2.15', MySQL continues using the restricted % ruleset.
Inspecting Effective Grants
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'app'@'10.0.2.%';▸The MySQL 8 Role Activation Trap
In MySQL 8.0, roles were introduced:
GRANT r_reader TO 'app'@'10.0.2.%';This assigns the role, but does not activate it by default! When the user connects:
SELECT CURRENT_ROLE();
-- Returns NONE!Even though SHOW GRANTS displays the role membership, executing a SELECT still fails with Error 1142 until the role is activated.
▸Step-by-Step Resolution
1. Grant Least-Privilege Access to the Matched Account
GRANT SELECT ON app.orders TO 'app'@'10.0.2.%';2. Stop Unnecessary FLUSH PRIVILEGES Invocations
GRANT and REVOKE modify in-memory access control structures instantaneously. Executing FLUSH PRIVILEGES is completely redundant unless you directly mutate mysql.user via raw INSERT/UPDATE statements.
3. Enforce Automatic Role Activation
Make assigned roles active upon login using SET DEFAULT ROLE:
CREATE ROLE r_reader;
GRANT SELECT ON app.* TO r_reader;
GRANT r_reader TO 'app'@'10.0.2.%';
-- Enforce default activation:
SET DEFAULT ROLE ALL TO 'app'@'10.0.2.%';▸Related Error Guides & Hub Links
- •PostgreSQL Permission Denied for Table (H-34) — PostgreSQL bilateral counterpart
- •SQL Server Msg 229 Permission Denied (H-39) — SQL Server bilateral counterpart
- •MySQL Access Denied 1045 (H-03) — Login and authentication failures
- •MySQL Hub — Security engineering and privilege architecture
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
MySQL ERROR 1045 Access denied: root Password Reset Guide
Four causes of MySQL 1045, meaning of using password: YES/NO, and safe credential recovery via --init-file.
MySQL Deadlock ERROR 1213: Graph Analysis and Permanent Prevention
Root causes of MySQL 1213 deadlock, parsing LATEST DETECTED DEADLOCK output, gap lock concurrency issues, and resilient application retry design.
MySQL Lock wait timeout exceeded: Finding Blocking Sessions
Finding the blocking transaction using performance_schema, safe KILL decision matrices, and permanent locking reduction.