Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Cannot run GRANT as root: access denied for user 'root'@'localhost'

ErrorERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mydb'

Short answer

GRANT works only for an account that has the GRANT OPTION privilege and holds every privilege it hands out. When it fails, you are usually not the account you think you are: an anonymous row matched first, or the account was created without grant rights. Check CURRENT_USER() and SHOW GRANTS first.

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

Fix it fast

  1. 1Find out which cause you have

    Ask the server which account your session really matched, and what that account may do.

    mysql -u root -p -e "SELECT USER(), CURRENT_USER(); SHOW GRANTS;"
  2. 2Match what you see, then jump to its fix

    1. 1CURRENT_USER() shows an empty name, such as @localhostAn anonymous account matched the connection firstFix
    2. 2SHOW GRANTS lists privileges but no WITH GRANT OPTIONThe account has no GRANT OPTION, or lacks the privilege it is grantingFix
    3. 3Plain SELECTs work, GRANT is refused, and read_only is ONThe server is read-onlyFix
    4. 4ERROR 1045 on every connection as root, with or without a passwordYou no longer have a working root loginFix

What does the error look like?

  • ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mydb'
  • ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  • Access denied for user 'root'@'localhost' while attempting to grant privileges

Cause 1 of 4

An anonymous account matched the connection first

1Check

The server uses the first matching row in its sorted account list. A row with a blank user name and host localhost can come before the row for the user you typed, and then you are treated as an anonymous user with no privileges.

SELECT CURRENT_USER();
SELECT user, host FROM mysql.user ORDER BY user, host;

2Fix

From an administrator account, drop the anonymous row. If you must keep it, connect with a name and host that match the account you want.

DROP USER ''@'localhost';

3Verify

Reconnect and check which account you are.

mysql -u root -p -e "SELECT CURRENT_USER();"

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

The account has no GRANT OPTION, or lacks the privilege it is granting

1Check

To grant a privilege you need the GRANT OPTION privilege and you must hold the privileges you are granting. You cannot hand out something you do not have.

SHOW GRANTS FOR 'root'@'localhost';

2Fix

Connect as an administrator that has the privilege, and give the account the grant option without changing its other privileges.

GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

3Verify

Read the grants back.

SHOW GRANTS FOR 'someuser'@'somehost';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

The server is read-only

1Check

While read_only is enabled, GRANT also requires CONNECTION_ADMIN (or the deprecated SUPER privilege).

SHOW VARIABLES LIKE 'read_only';

2Fix

Run the statement on the writable primary, or grant the account CONNECTION_ADMIN from an administrator.

GRANT CONNECTION_ADMIN ON *.* TO 'someuser'@'somehost';

3Verify

Repeat the failed GRANT.

SHOW GRANTS FOR 'someuser'@'somehost';

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

You no longer have a working root login

1Check

Try the client with an explicit user and password prompt. If that fails too, the root password is unknown.

mysql -u root -p

2Fix

Reset the password with an init file that contains one ALTER USER line, start the server with it, then restart normally and delete the file.

# /home/me/mysql-init contains:
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysqld --init-file=/home/me/mysql-init &

3Verify

Stop the server, start it normally and log in with the new password.

mysql -u root -p -e "SELECT CURRENT_USER();"

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create each application account with CREATE USER, then grant on one database, such as GRANT ALL ON mydb.* TO 'app'@'localhost'.
  • Remove anonymous accounts after installing the server.
  • Keep one administrator account with GRANT OPTION and use it only for account management.

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.