MySQL 1142: command denied to user for table

What it means

MySQL and MariaDB check privileges per statement. Error 1142 names the privilege the statement needed (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, CREATE VIEW…), your account, and the table, and says the account doesn’t have it. Nothing ran.

Two details in the message:

The account is shown as user and client address ('app'@'127.0.0.1'), not the account’s host pattern ('app'@'%'). SELECT CURRENT_USER(); shows which account’s grants apply.
The table may not exist. The server gives the same error for a table you couldn’t see anyway, so it doesn’t reveal which tables exist to accounts without access. A typo in a table name can look like a permissions problem.

Common causes

A read-only or limited account running a write or DDL statement: a reporting user trying INSERT, or an application user running a migration that needs ALTER or CREATE.
Privileges on some tables only, or on some columns only, and the statement touches another. SELECT * on a table where you may only read some columns is refused for the whole table.
System schemas: performance_schema tables such as data_locks, and mysql.*, need privileges that application accounts usually don’t have.
A table in another database, named with other_db.table, that the account was never granted.
A misspelt table name, reported as denied rather than missing.

How to fix it

Check the account’s grants

SELECT CURRENT_USER();
SHOW GRANTS;

Grants for seo_err_one@%
GRANT USAGE ON *.* TO `seo_err_one`@`%`
GRANT SELECT (`email`, `id`) ON `seo_err_mysql`.`emails` TO `seo_err_one`@`%`
GRANT SELECT ON `seo_err_mysql`.`users2` TO `seo_err_one`@`%`

Here the account may read two columns of emails and all of users2, and nothing else.

Grant the missing privilege

As an administrator, to the account from CURRENT_USER():

GRANT INSERT, UPDATE, DELETE ON shop.orders TO 'app'@'%';
-- or for every table in the database:
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';

Grant only what the account needs; keep DDL privileges (CREATE, ALTER, DROP) for the account that runs migrations. No FLUSH PRIVILEGES is needed after GRANT.

Select only the columns you may read

With column privileges, list the columns:

SELECT id, email FROM emails;

Check the table name

Your own account can’t tell a missing table from a forbidden one, so compare the name with SHOW TABLES run by an account that can see the whole database. On Linux servers, table names are case-sensitive: Orders and orders are different tables.

Reproduce it

On MySQL 8.4.11, with an account seo_err_ro that has SELECT on seo_err_mysql.*:

INSERT INTO emails (email) VALUES ('q');
UPDATE emails SET score = 1 WHERE id = 1;
DROP TABLE emails;
CREATE TABLE t (id int);
ALTER TABLE emails ADD COLUMN z int;
CREATE VIEW v_ro AS SELECT 1;

ERROR 1142 (42000): INSERT command denied to user 'seo_err_ro'@'127.0.0.1' for table 'emails'
ERROR 1142 (42000): UPDATE command denied to user 'seo_err_ro'@'127.0.0.1' for table 'emails'
ERROR 1142 (42000): DROP command denied to user 'seo_err_ro'@'127.0.0.1' for table 'emails'
ERROR 1142 (42000): CREATE command denied to user 'seo_err_ro'@'127.0.0.1' for table 't'
ERROR 1142 (42000): ALTER command denied to user 'seo_err_ro'@'127.0.0.1' for table 'emails'
ERROR 1142 (42000): CREATE VIEW command denied to user 'seo_err_ro'@'127.0.0.1' for table 'v_ro'

With the column grant shown above, SELECT * was refused for the table while SELECT id, email returned rows; a table that didn’t exist, and one in performance_schema, gave the same error:

ERROR 1142 (42000): SELECT command denied to user 'seo_err_one'@'127.0.0.1' for table 'emails'
ERROR 1142 (42000): SELECT command denied to user 'seo_err_one'@'127.0.0.1' for table 'nosuch'
ERROR 1142 (42000): SELECT command denied to user 'seo_err_app'@'127.0.0.1' for table 'data_locks'

Calling a stored function without EXECUTE is a different number, 1370 (execute command denied to user … for routine).

MariaDB 11.4.13 gave the same errors, naming the table with its database:

ERROR 1142 (42000): INSERT command denied to user 'seo_err_ro'@'127.0.0.1' for table `seo_err_mysql`.`emails`

In Inlet

When a statement is refused, Inlet shows the server’s error and links to this page. Connected as an administrator, you can look at the server’s accounts in Inlet’s accounts view for MySQL and MariaDB. Separately, connections tagged production open read-only, so a write there is refused by the server with a different error, whatever the account’s privileges.

ERROR 1142 (42000): SELECT command denied to user for table