MySQL – Users, Privileges and Roles

Connecting as root is fine on a laptop and is how a lot of applications end up in production. The cost of not fixing it is that every SQL injection, every mistaken migration and every stray DROP has the run of the whole server.

⚠️ Everything here creates server-level accounts, not database objects. They outlive the database you were connected to, so each example ends by cleaning up after itself.

An account is a user AND a host

DROP USER IF EXISTS 'pizza_app'@'%';
CREATE USER 'pizza_app'@'%' IDENTIFIED BY 'demo-password';

'pizza_app'@'%' and 'pizza_app'@'localhost' are two different accounts with two different passwords and two different sets of privileges. This is the single most common source of "I granted it and it still says access denied".

MySQL matches the most specific host pattern, and it is the matched row that decides your privileges. That is why CURRENT_USER() and USER() can differ — see server and session functions. When a grant seems not to apply, compare them first.

Least privilege

GRANT SELECT, INSERT, UPDATE, DELETE ON pizza.* TO 'pizza_app'@'%';

SHOW GRANTS FOR 'pizza_app'@'%';

+----------------------------------------------------------------------+
| Grants for pizza_app@%                                               |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `pizza_app`@`%`                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `pizza`.* TO `pizza_app`@`%` |
+----------------------------------------------------------------------+

Four verbs on one database. USAGE means "may connect and nothing else" — it is what every account has, and it is not a privilege you grant.

Note what is absent: no DROP, no ALTER, no CREATE. An application that does not run migrations does not need them, and the difference is real:

-- unavailable: run while connected AS pizza_app. As an admin account it simply succeeds.
-- ERROR 1142 (42000): DROP command denied to user 'pizza_app'@'...' for table 'crust'
DROP TABLE crust;

Give migrations their own account with DDL rights, used by the migration tool and nothing else. Then a compromised application cannot drop a table however badly it is exploited.

Granularity

-- unavailable: illustrative syntax. 'x'@'%' is not a real account, and MySQL 8 will not
-- create one implicitly through GRANT.
GRANT SELECT               ON *.*                 TO 'x'@'%';   -- every database
GRANT SELECT               ON pizza.*             TO 'x'@'%';   -- one database
GRANT SELECT               ON pizza.customer_order TO 'x'@'%';  -- one table
GRANT SELECT (id, email)   ON pizza.app_user      TO 'x'@'%';   -- named columns only

Column-level grants are the neat answer to "support needs to look up a customer but must not see the password hash":

DROP USER IF EXISTS 'support'@'%';
CREATE USER 'support'@'%' IDENTIFIED BY 'demo-password';
GRANT SELECT (id, email, full_name) ON pizza.app_user TO 'support'@'%';

-- unavailable: run while connected AS support. As an admin account it simply succeeds.
-- ERROR 1143 (42000): SELECT command denied to user 'support'@'...'
-- for column 'password_hash' in table 'app_user'
SELECT password_hash FROM app_user;

A view does the same job with more flexibility — it can compute, mask and join — and is usually the better tool once more than a couple of columns are involved.

Roles

MySQL 8 added roles: a named bundle of privileges you grant to accounts.

DROP ROLE IF EXISTS 'pizza_readonly';
CREATE ROLE 'pizza_readonly';
GRANT SELECT ON pizza.* TO 'pizza_readonly';

DROP USER IF EXISTS 'reporting'@'%';
CREATE USER 'reporting'@'%' IDENTIFIED BY 'demo-password';
GRANT 'pizza_readonly' TO 'reporting'@'%';

⚠️ A granted role is not an active role

This is the MySQL 8 behaviour that wastes an afternoon. The grant above is in place, and the account still cannot read anything:

$ mysql -u reporting -p pizza
ERROR 1044 (42000): Access denied for user 'reporting'@'%' to database 'pizza'

SHOW GRANTS FOR 'reporting'@'%';

+-----------------------------------------------+
| Grants for reporting@%                        |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `reporting`@`%`         |
| GRANT `pizza_readonly`@`%` TO `reporting`@`%` |
+-----------------------------------------------+

The role is granted but not activated. Roles must be switched on, per session, and the fix is to make that automatic:

SET DEFAULT ROLE ALL TO 'reporting'@'%';

Now the connection works. (Within a session, SET ROLE switches roles on and off, and SHOW GRANTS FOR CURRENT_USER() USING 'pizza_readonly' shows what a role actually carries.) Always pair GRANT role with SET DEFAULT ROLE unless you specifically want the account to opt in each time.

Authentication plugins

SELECT user, host, plugin FROM mysql.user WHERE user IN ('root','pizza_app') ORDER BY user, host;

+-----------+-----------+-----------------------+
| user      | host      | plugin                |
+-----------+-----------+-----------------------+
| pizza_app | %         | caching_sha2_password |
| root      | %         | caching_sha2_password |
| root      | localhost | caching_sha2_password |
+-----------+-----------+-----------------------+

caching_sha2_password is the MySQL 8 default and is stronger than the old mysql_native_password. It is also why an older client may fail to connect, and why the demo application's JDBC URL carries allowPublicKeyRetrieval=true — that flag exists to let the client fetch the server's public key over an unencrypted connection. With TLS on, you do not need it. See connections.

Downgrading an account to mysql_native_password to make an old client work is a real temptation and a real step backwards; MySQL 8.4 removes that plugin by default. Upgrade the client.

Revoking, and clearing up

REVOKE DELETE ON pizza.* FROM 'pizza_app'@'%';
ALTER USER 'pizza_app'@'%' IDENTIFIED BY 'a-new-password';

DROP USER IF EXISTS 'pizza_app'@'%';
DROP USER IF EXISTS 'reporting'@'%';
DROP USER IF EXISTS 'support'@'%';
DROP ROLE IF EXISTS 'pizza_readonly';

FLUSH PRIVILEGES is not needed after GRANT, REVOKE or CREATE USER — those update the in-memory tables directly. It is only required when you have modified the mysql tables with raw SQL, which is exactly what the root password reset does.

A checklist

• One account per application, not one shared account.
• Separate accounts for the application and for migrations.
• Narrow the host as far as your network allows.
• No GRANT ALL, no WITH GRANT OPTION, no *.*.
• An admin account for humans, distinct from the application's — see connections on the reserved connection slot.
• Audit periodically: SELECT user, host FROM mysql.user, then SHOW GRANTS for anything unfamiliar.

What to remember

• User + host is the identity. 'app'@'%' and 'app'@'localhost' are different accounts.
• Grant the verbs the application uses and nothing else; keep DDL for the migration account.
• A granted role does nothing until it is activated — SET DEFAULT ROLE ALL.
• FLUSH PRIVILEGES is only for raw edits to the mysql tables.