Grant least privilege in MySQL

The fix

CREATE USER 'app'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'10.0.1.%';

-- migrations run as a different account, used briefly
CREATE USER 'app_ddl'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'other-secret';
GRANT ALL ON appdb.* TO 'app_ddl'@'10.0.1.%';

FLUSH PRIVILEGES;

Why it matters

The shape almost every MySQL setup starts with is:

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' WITH GRANT OPTION;   -- don't

Three separate mistakes, each of which matters on its own:

• *.* — every database, including mysql itself. The app can read the password hash table.
• '%' — from any host. A leaked credential works from anywhere on earth.
• ALL ... WITH GRANT OPTION — including FILE, SUPER, and the ability to hand those to others.

FILE is the one that turns a SQL injection into a host problem. It permits SELECT ... INTO OUTFILE and LOAD_FILE(), which read and write files as the mysql OS user — see restricting file privileges and the threat page. An application never needs it.

The three columns to narrow

Each is a layer that survives a leaked password:

Narrow	Effect
Database (appdb.* not *.*)	The credential cannot touch mysql or other apps
Host (10.0.1.% not %)	The credential is useless from outside your subnet
Privileges (SELECT, INSERT, … not ALL)	No DROP, no FILE, no GRANT

Host scoping is the one people skip because '%' “just works”, and it is the one that most reduces the value of a stolen password.

Separate DDL from runtime

MySQL has no owner concept like Postgres, so the separation is done with two accounts rather than ownership.

The runtime account gets SELECT, INSERT, UPDATE, DELETE — enough to run the application and nothing more. A separate account holds ALL ON appdb.* and is used only by migrations, briefly, with a credential the running service does not hold.

The payoff: a SQL injection against the runtime account cannot DROP TABLE users or ALTER anything. It’s the difference between a data breach and a destroyed database.

Roles make this maintainable

MySQL 8 and MariaDB 10.0.5+ both have roles, which is what makes least privilege survive contact with a growing team:

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

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

SET DEFAULT ROLE is the step people miss. Without it a granted role is not active on connect, the application gets permission errors, and the usual response is to abandon roles and GRANT ALL — which is how you end up back at the top of this page.

Verify it worked

Find every over-privileged account. This is the audit:

SELECT user, host FROM mysql.user
WHERE Super_priv = 'Y' OR File_priv = 'Y' OR Grant_priv = 'Y'
   OR Shutdown_priv = 'Y' OR Process_priv = 'Y'
ORDER BY user;

Anything here that isn’t a named admin is a finding. Applications should not appear at all.

Find wildcard hosts:

SELECT user, host FROM mysql.user WHERE host = '%' ORDER BY user;

Read what an account can actually do:

SHOW GRANTS FOR 'app'@'10.0.1.%';

Expect exactly one scoped line, no ON *.*, no WITH GRANT OPTION.

Then prove the limit — connected as the app:

DROP TABLE appdb.users;
SELECT * FROM mysql.user;

Expect access denied on both. If either succeeds, the grant is wider than you think.

Before you apply this

Narrowing grants breaks the application at its next query, not at connect time — so the failure lands in the middle of a request, and only for the code path that used the privilege you removed.

Find out what the account actually uses before you cut. If the performance schema is enabled, this shows the statement types it has run:

SELECT DISTINCT event_name
FROM performance_schema.events_statements_summary_by_account_by_event_name
WHERE user = 'app' AND count_star > 0;

That catches the once-a-month report that uses CREATE TEMPORARY TABLE, which is exactly the kind of thing a manual review misses.

Two specific traps:

• REVOKE is immediate, but existing connections keep their privileges for the life of the connection in some cases. A pool that holds connections for hours will appear to still work, then break on the next reconnect — often long after you’ve concluded the change was clean. FLUSH PRIVILEGES and watch for a full pool cycle before declaring victory.
• Changing the host part means creating a new account. 'app'@'%' and 'app'@'10.0.1.%' are different accounts with separate grants — you cannot edit the host. Create the scoped one, migrate, confirm it’s in use with CURRENT_USER(), then drop the wildcard one.

Related

• Restrict file privileges — why FILE matters so much.
• Remove anonymous users — the accounts you shouldn’t have at all.