Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

The GRANT ran without an error, but the user is still refused

ErrorERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'shop'

Short answer

A GRANT that ran cleanly is applied at once, so the cause lies elsewhere. Three things explain it: the grant tables were edited by hand without a reload, an open session has not picked up the change yet, or the application connects as a different account or host row than the one you granted. Check CURRENT_USER() from the failing connection first.

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

Fix it fast

  1. 1Find out which cause you have

    Ask the server which account it really matched for the failing connection, and what that account may do. Run both from the connection that fails, not from your admin session.

    SELECT CURRENT_USER();
    SHOW GRANTS;
  2. 2Match what you see, then jump to its fix

    1. 1You edited rows in mysql.user or mysql.db by hand, and nothing changed until the server restartedThe privilege tables were changed with INSERT or UPDATE and never reloadedFix
    2. 2New connections work, but the long-lived connection or the pool of the application keeps being refusedA connected session has not picked up the new privilege yetFix
    3. 3SHOW GRANTS FOR the account you used in GRANT looks right, but CURRENT_USER() from the application prints another user or hostThe application matches a different account than the one you grantedFix

What does the error look like?

  • ERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'shop'
  • ERROR 1045 (28000): Access denied for user 'app'@'10.0.0.5' (using password: YES)
  • SHOW GRANTS shows the privilege, but the application still gets denied

Cause 1 of 3

The privilege tables were changed with INSERT or UPDATE and never reloaded

1Check

GRANT, REVOKE, SET PASSWORD and RENAME USER make the server reload the grant tables immediately. Direct INSERT, UPDATE or DELETE on those tables does not, and the manual recommends against it.

SELECT User, Host FROM mysql.user WHERE User = 'app';

2Fix

Reload the grant tables once. From then on use GRANT and CREATE USER or ALTER USER instead of editing the tables.

FLUSH PRIVILEGES;
-- or, from a shell:
-- mysqladmin -u root -p flush-privileges

3Verify

Connect again as the user and run the statement that failed.

SHOW GRANTS FOR 'app'@'localhost';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A connected session has not picked up the new privilege yet

1Check

After a reload, table and column privileges apply from the next request. Database privileges apply the next time the client runs USE db_name, and a client may cache the database name. Static global privileges and passwords do not change for sessions that are already connected.

SELECT DATABASE(), CURRENT_USER();

2Fix

Switch to another database and back, or reconnect. For an application, restart the process or recycle its connection pool so that it opens new sessions.

USE mysql;
USE shop;

3Verify

The statement that was refused now runs in the same session.

SHOW TABLES;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The application matches a different account than the one you granted

1Check

An account is a user name plus a host. The server picks the first matching row in its sorted list, with the most specific host first, and uses only that row's privileges. 'app'@'localhost' and 'app'@'%' are two accounts, and a connection from the same machine matches the localhost one.

SELECT User, Host FROM mysql.user WHERE User = 'app';
SHOW GRANTS FOR 'app'@'%';

2Fix

Grant to the account the connection really matches, or create the missing account and grant to it. Use the CURRENT_USER() value from the failing connection.

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';

3Verify

The failing connection and the granted account are now the same one.

SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Manage accounts only with CREATE USER, ALTER USER, GRANT and REVOKE, so the server reloads the tables by itself.
  • After changing privileges, restart or recycle the connection pool of every application that uses the account.
  • Keep one account per application and host, and write its exact name in your deployment notes.
  • Run SELECT CURRENT_USER() from the application's own connection while debugging, not from an admin session.

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

Databases

ERROR 1044: Access denied for user to database

ERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'shop'

Error 1044 is different from 1045: the login worked, but the account MySQL matched has no privileges on the database you named.

3 causes, each with a fix

Next step

Want this handled for good?

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