MySQL User Management and Grants

Introduction¶

MySQL access control is 'user'@'host' plus privileges granted at global, database, table, or column scope. Getting app accounts to least privilege — and being able to audit what an account can actually do — is basic hygiene that's often skipped.

user@host¶

An account is identified by both the username and the host pattern it connects from:

CREATE USER 'app'@'10.0.1.%'    IDENTIFIED BY 'strong-secret';   -- from the app subnet
CREATE USER 'app'@'127.0.0.1'   IDENTIFIED BY 'strong-secret';   -- local socket/loopback
CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';

• 'app'@'%' — any host. Convenient, sloppy. Scope to the real source range.
• 'app'@'localhost' matches the Unix socket, not 127.0.0.1 (which is a separate account). This trips people up constantly.
• MySQL picks the most specific matching host row at login; a broad 'app'@'%' with fewer privileges can shadow a specific one — order matters.

Auth plugins¶

CREATE USER 'app'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'secret';
-- MySQL 8 default. Some old clients/drivers only do mysql_native_password:
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';
-- passwordless via socket/OS identity:
CREATE USER 'ops'@'localhost' IDENTIFIED WITH auth_socket;

caching_sha2_password needs TLS or the RSA key exchange for the first auth — "Authentication plugin ... cannot be loaded" from an old client means switch that account to mysql_native_password or update the driver.

Privilege scopes¶

-- global (*.*), e.g. admin/replication/backup roles
GRANT PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'backup'@'localhost';

-- one database — the common app grant
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.1.%';

-- one table
GRANT SELECT ON shop.audit_log TO 'reporting'@'10.0.2.%';

-- column-level (rare, for masking)
GRANT SELECT (id, name, created_at) ON shop.users TO 'reporting'@'10.0.2.%';

Least privilege for an app account: SELECT, INSERT, UPDATE, DELETE on its schema. It usually does not need CREATE, DROP, ALTER, INDEX, GRANT OPTION, FILE, SUPER, or *.* anything. Migrations run under a separate, more-privileged account, in CI/deploy, not the app.

Dangerous privileges to keep off app accounts: FILE (read/write server files), SUPER/SYSTEM_VARIABLES_ADMIN, GRANT OPTION, SHUTDOWN, PROCESS (see other sessions' queries).

Roles (MySQL 8)¶

CREATE ROLE 'app_rw', 'app_ro';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw';
GRANT SELECT ON shop.* TO 'app_ro';

GRANT 'app_rw' TO 'app'@'10.0.1.%';
-- roles aren't active until set as default (or SET ROLE per session)
SET DEFAULT ROLE 'app_rw' TO 'app'@'10.0.1.%';

Roles let you manage privileges centrally and grant them to many users.

Changing and revoking¶

ALTER USER 'app'@'10.0.1.%' IDENTIFIED BY 'new-secret';
ALTER USER 'app'@'10.0.1.%' PASSWORD EXPIRE;                -- force a change on next login
ALTER USER 'app'@'10.0.1.%' ACCOUNT LOCK;                   -- disable without dropping
REVOKE INSERT, UPDATE, DELETE ON shop.* FROM 'reporting'@'10.0.2.%';
DROP USER 'olddev'@'%';

Password rotation with dual accounts (app_a/app_b) enables zero-downtime rotation — see Secrets Manager rotation.

Auditing¶

SHOW GRANTS FOR 'app'@'10.0.1.%';
SHOW GRANTS FOR 'app'@'10.0.1.%' USING 'app_rw';   -- include role privileges

SELECT * FROM information_schema.user_privileges WHERE grantee LIKE "'app'%";
SELECT * FROM mysql.user\G                          -- the raw account table (host, plugin, locked, expiry)
SELECT user, host FROM mysql.user;

For a full audit trail of what accounts actually did, use the audit log plugin (Enterprise, or Percona/MariaDB's) — the general query log is too heavy for production.

Verification and troubleshooting¶

SELECT CURRENT_USER(), USER();   -- CURRENT_USER = the account row matched; USER = what you typed
SHOW GRANTS;                     -- for the current session

• Access denied for user 'app'@'10.0.1.5' — no matching user@host row (the app connects from an IP your host pattern doesn't cover), wrong password, or the account is ACCOUNT LOCKed / PASSWORD EXPIREd. SELECT user, host FROM mysql.user and check the connecting IP.
• CURRENT_USER() is ''@'' or an unexpected account — a broad wildcard row matched instead of the specific one, or the anonymous account exists (drop ''@'localhost' / ''@'%' — they're a security hole).
• App works from one host, not another — host pattern too narrow. Widen to the real subnet, not %.
• caching_sha2_password auth errors — old client; ALTER USER ... IDENTIFIED WITH mysql_native_password or update the driver / enable TLS.
• Grant made but not effective — for roles, they must be activated (SET DEFAULT ROLE or activate_all_roles_on_login=ON); SHOW GRANTS ... USING role to see the effective set.
• localhost vs 127.0.0.1 confusion — a grant on 'app'@'localhost' won't apply when the app connects via TCP to 127.0.0.1. Create both, or force the connection type.
• Too many privileges discovered in an audit — REVOKE the extras; test the app still works (it should, if it only does CRUD).

Related tools and reading¶

• On-site: Password generator.
• Related posts: ProxySQL basics, Connection pooling explained.