TopolearnMySQL's user@host accounts are a frequent source of 'access denied' errors and security holes.
A MySQL account is a user name plus a host: 'app'@'10.0.%' and 'app'@'localhost' are two different accounts with possibly different passwords and privileges. '%' matches any host. When a client connects, the server picks the most specific matching account, which explains many confusing "access denied" errors.
Create accounts and grant privileges per database:
CREATE USER 'app'@'%' IDENTIFIED BY 'long-random-password';GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';SHOW GRANTS FOR 'app'@'%';to inspect,REVOKEto remove.
Since 8.0, roles bundle privileges: CREATE ROLE app_read; GRANT SELECT ON shop.* TO app_read; GRANT app_read TO 'analyst'@'%';. Roles are not active automatically — set them with SET DEFAULT ROLE ALL TO 'analyst'@'%' (or turn on activate_all_roles_on_login).
Authentication: the default plugin is caching_sha2_password. In 8.4 the old mysql_native_password plugin is disabled by default, so very old client libraries may fail to connect; upgrade the driver rather than re-enabling it.
Good practice: never let applications use root; give each service its own account limited to its database; avoid GRANT ALL ON *.*; restrict hosts where you can; and require TLS for remote accounts (REQUIRE SSL).
Example
CREATE DATABASE shop;
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'change-me-to-something-long';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'%';
CREATE ROLE 'shop_read';
GRANT SELECT ON shop.* TO 'shop_read';
CREATE USER 'analyst'@'10.0.%' IDENTIFIED BY 'another-long-password';
GRANT 'shop_read' TO 'analyst'@'10.0.%';
SET DEFAULT ROLE ALL TO 'analyst'@'10.0.%';
SHOW GRANTS FOR 'shop_app'@'%';
-- GRANT USAGE ON *.* TO `shop_app`@`%`
-- GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`%`Remember
- An account is user@host; the most specific host match wins
- Grant per database (shop.), never ALL ON .* to applications
- Roles need SET DEFAULT ROLE (or activation) to take effect
Watch out for
- Creating 'app'@'localhost' and then connecting over TCP from another host as 'app'.