MySQL 1044: Access denied for user to database

What it means

Unlike error 1045, this isn’t a sign-in failure: the server accepted your user name and password. Error 1044 means the account it matched has no privileges at all on the database in the message, so it refuses to let you use it, or to create or drop a database by that name.

The account in the message is the one the server matched, with its host pattern ('app'@'%'), not the address you connected from. That’s the account whose grants you need to look at.

You get 1044 for statements about a whole database: connecting with it as the default database, USE, SHOW TABLES FROM, CREATE DATABASE and DROP DATABASE. When you can use the database but lack one privilege on a table in it, the error is 1142, command denied instead.

Common causes

The account was never granted that database: a new database, a copy with a different name (shop_prod rather than shop), or a typo in the name.
An application account runs CREATE DATABASE, which needs the CREATE privilege on that name, usually given only to administrators. CREATE DATABASE IF NOT EXISTS needs it too, even when the database already exists.
Importing a dump made with --databases or --all-databases. It starts with CREATE DATABASE IF NOT EXISTS and USE for the original name, so an account that can only use one database fails on the first line, or writes into a database you didn’t mean.
The grants are on a different account. 'app'@'localhost' has them, but you signed in as 'app'@'%'. Each user-and-host pair has its own privileges.

How to fix it

See which account you are and what it may do

SELECT CURRENT_USER();
SHOW GRANTS;

CURRENT_USER()
seo_err_ro@%
Grants for seo_err_ro@%
GRANT USAGE ON *.* TO `seo_err_ro`@`%`
GRANT SELECT ON `seo_err_mysql`.* TO `seo_err_ro`@`%`

USAGE means no privileges. Every database the account can use has its own line, or is covered by a line on *.*. SHOW DATABASES lists only the databases you have some privilege on, so a database missing from it is one you can’t use, or one that doesn’t exist.

Grant the account access

As an administrator, name the exact account from CURRENT_USER():

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

Add CREATE, ALTER, INDEX, DROP if the application runs its own migrations, or use ALL PRIVILEGES ON shop.* for an account that owns the database. There’s no need for FLUSH PRIVILEGES after GRANT; new connections get the privileges at once, and a session that’s already open picks up a database-level grant at its next USE.

Create the database as an administrator

Rather than giving an application account the right to create databases, create it once and grant access to it:

CREATE DATABASE shop;
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'%';

Import a dump into the database you choose

A dump of one database made without --databases has no CREATE DATABASE or USE, so it goes into whichever database you name on the command line:

mysqldump --single-transaction shop > shop.sql
mysql -h <host> -u app -p shop_copy < shop.sql

If you already have a dump with those lines, check its first lines and remove the CREATE DATABASE and USE statements, or import it as an account that may create that database.

Reproduce it

On MySQL 8.4.11, with an account seo_err_app granted only its own database, connecting with another one as the default:

mysql -h127.0.0.1 -useo_err_app -p inlet

ERROR 1044 (42000): Access denied for user 'seo_err_app'@'%' to database 'inlet'

USE inlet and SHOW TABLES FROM inlet gave the same error, and creating or dropping a database named it:

ERROR 1044 (42000): Access denied for user 'seo_err_app'@'%' to database 'seo_err_other'

An account with SELECT on seo_err_mysql.* running CREATE DATABASE IF NOT EXISTS seo_err_mysql, the first statement of a mysqldump --databases dump, for a database that already existed:

ERROR 1044 (42000): Access denied for user 'seo_err_ro'@'%' to database 'seo_err_mysql'

MariaDB 11.4.13 gave the same messages, number and SQLSTATE in every case.

In Inlet

When a connection or a statement is refused, Inlet shows the server’s message and links to this page. Paste a mysql:// URL and Inlet fills in the connection form, so you can check the user and the database it read from the path before you connect.

ERROR 1044 (42000): Access denied for user to database