HowToCodePage

User management, privileges and security

Create accounts with the narrowest scope that works, enforce encryption in transit, and rotate credentials before an incident forces you to.

Accounts, roles and grants

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-secret';

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON app.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_write';

GRANT 'app_read', 'app_write' TO 'app'@'10.0.0.%';
SET DEFAULT ROLE ALL TO 'app'@'10.0.0.%';

-- column level, for a support tool that must not see everything
GRANT SELECT (id, email) ON app.users TO 'support'@'10.0.0.%';

REVOKE DELETE ON app.* FROM 'app_write';
SHOW GRANTS FOR 'app'@'10.0.0.%';
  • Privileges exist at several scopes: global, database, table, column and routine. Grant at the narrowest scope that lets the code do its job.
  • A role is inactive until it is granted and made the default or activated for the session. That surprises people who grant a role and see no change in behaviour.
  • Never edit the tables under mysql directly. Use CREATE USER, GRANT and REVOKE so the in-memory privilege cache stays correct.

Transport, authentication and safe defaults

CREATE USER 'svc'@'10.0.0.%'
  IDENTIFIED WITH caching_sha2_password BY 'a-long-random-secret'
  REQUIRE SSL;

SHOW STATUS LIKE 'Ssl_cipher';        -- empty means the session is not encrypted
SettingRecommended
bind-addressA private interface; never a public one
TLSRequired for any connection over a network you do not own
Authentication plugincaching_sha2_password; use the legacy plugin only for a client that cannot be upgraded
require_secure_transportON, to refuse unencrypted connections outright
local_infileOFF unless you actually use LOAD DATA LOCAL
Application accountNever root, never with GRANT OPTION
⚠️
A grant on *.* with GRANT OPTION turns a stolen application credential into complete control of the server. Grant the narrowest scope the code needs, and review account and role assignments on a schedule rather than after an incident.

Auditing and credential rotation

SELECT user, host, account_locked, password_expired FROM mysql.user;

-- rotate: create or change first, deploy, then disable the old credential
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-newer-longer-secret';

-- disable immediately without losing the grants
ALTER USER 'app'@'10.0.0.%' ACCOUNT LOCK;

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SELECT * FROM mysql.audit_log_user LIMIT 5;
  • Rotate in an order that never breaks the application: issue the new credential, deploy it everywhere, confirm no session uses the old one, then revoke.
  • ACCOUNT LOCK keeps the grants and blocks authentication, which makes it the fastest way to disable a leaked credential.
  • Watch Aborted_connects and failed logins. A sudden rise is either a misconfiguration or a credential-stuffing attempt, and the two need different responses.
  • Backups contain every table and every password hash. Store them with the same care as the running database, including access control and encryption.

FAQ

Why does the application connect as root?
Almost always because it was convenient during development. It is the most common serious misconfiguration in practice: a leaked config or an injection then has unrestricted access. Create a least-privilege account before the first deployment, not after the first incident.
Are roles worth the trouble?
Yes, as soon as more than one account needs the same privileges. Changing a role changes every account that holds it, instead of hunting down individual grants. Remember that a granted role must also be made the default or activated for the session.

Connecting from applications: drivers and pooling Installation, databases, users and engines

Last refreshed 2026-09-18.