MySQL Error 1142

Error number

1142

SQLSTATE

42000

Symbol

ER_TABLEACCESS_DENIED_ERROR

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 (8.0.46 names the table without its database)

Category

Access & Privileges

Last reviewed

24 October 2026

What it means

Error 1142 means the account is logged in but does not have the privilege this statement needs on that table, so MySQL refused the statement.

After a login succeeds, MySQL checks every statement against the privileges of the account that matched the login (CURRENT_USER(), which can differ from the name you typed). Privileges can be granted globally (ON *.*), per database (ON shop.*), per table or per column, and through roles. When none of these gives the needed privilege for the table, the statement fails with 1142. The message tells you three things: the statement type that was denied (SELECT, INSERT, UPDATE, DELETE, DROP, ALTER and so on), the account as user@host, and the table. In MySQL 8.0.46 the table is named without its database, so check which database the statement used.

SQLSTATE 42000 is shared with syntax errors, which is why PHP PDO labels it "Syntax error or access violation"; check the error number. Some system tables refuse certain statements to everyone: performance_schema tables such as processlist and global_status cannot be changed with DELETE or UPDATE even by an administrative account, and they raise 1142 too. Being refused a whole database, for example by USE or by the default database of a connection, gives error 1044 (Access denied for user to database) instead, and a missing global privilege such as PROCESS or SYSTEM_VARIABLES_ADMIN gives error 1227.

Common causes

• The application account was granted only some privileges (for example SELECT) and the code now writes, deletes or changes the schema.
• The grant is on another database or table than the one used: a test database, a renamed table, or a statement that names a table in a second database.
• The login matched another account than you think: grants were made to 'app'@'%' but the login matches 'app'@'localhost', which has its own (smaller) privileges.
• The privileges come from a role that is granted but not active in the session (roles are active only if they are default roles, activated with SET ROLE, or activate_all_roles_on_login is ON).
• The statement targets a system table that does not allow it, such as UPDATE or DELETE on a performance_schema table.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT USER() AS sent_as, CURRENT_USER() AS checked_as;
SHOW GRANTS FOR CURRENT_USER();

SELECT CURRENT_ROLE();
SHOW VARIABLES LIKE 'activate_all_roles_on_login';

SELECT grantee, table_schema, privilege_type
FROM information_schema.schema_privileges
WHERE grantee LIKE '''app''@%'
ORDER BY table_schema, privilege_type;

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee LIKE '''app''@%'
ORDER BY table_schema, table_name, privilege_type;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

GRANT INSERT, UPDATE ON shop.orders TO 'app'@'localhost';

-- or, for the whole application database:
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';

SET DEFAULT ROLE ALL TO 'app'@'localhost';

-- or, in the current session only:
SET ROLE ALL;

Examples

An application account that can only read

Shown as documented in the MySQL server error message reference (ER_TABLEACCESS_DENIED_ERROR); not reproduced here, because it needs a restricted account and the lab may not create accounts. The table is named without its database, as MySQL 8.0.46 printed it in our tests on system tables.

-- The account was given read access only:
-- GRANT SELECT ON shop.* TO 'app'@'localhost';

-- Connected as app@localhost:
INSERT INTO orders (customer_id, total) VALUES (7, 19.99);

ERROR 1142 (42000): INSERT command denied to user 'app'@'localhost' for table 'orders'

How to prevent it

Write down what each account needs and grant exactly that: the application account gets SELECT, INSERT, UPDATE and DELETE on its own database, a separate migration account gets the schema privileges, and reporting accounts get SELECT only. Grant to the same host pattern the application logs in with, and set default roles when you use roles.

Run the application's tests with the same restricted account as production, so a new statement that needs another privilege fails in testing, not in front of users. Handle 1142 in code as a configuration error, not something to retry.

Common questions

Learn more on CodeWithSQL

• SQL security
• SQL commands: DDL, DML, DCL, TCL and DQL

Official documentation

• MySQL 8.0 server error message reference
• Privileges provided by MySQL
• GRANT statement
• Using roles

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.