Build a least-privilege model with roles (CREATE ROLE, DEFAULT ROLE)

Command

CREATE ROLE IF NOT EXISTS 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';

-- Application account: network range, TLS required, roles active by default
CREATE USER 'svc_app'@'10.0.%' IDENTIFIED BY 'S3cure#App2024' DEFAULT ROLE 'app_read', 'app_write' REQUIRE SSL;
CREATE USER 'ana'@'%' IDENTIFIED BY 'An4#Report2024';
GRANT 'app_read' TO 'ana'@'%';
SET DEFAULT ROLE ALL TO 'ana'@'%';

-- Show grants with roles expanded
SHOW GRANTS FOR 'svc_app'@'10.0.%' USING 'app_read', 'app_write';
SELECT FROM_USER, FROM_HOST, TO_USER, TO_HOST FROM mysql.role_edges;

What it does

Grants privileges to roles instead of individual users; accounts get privileges through role membership. Changes happen in one place and auditing and offboarding get easier.

When to use it

When creating new application / team accounts and when cleaning up legacy servers with tangled privileges.

Caution

A role that is not a default role is not active in the session (CURRENT_ROLE() NONE) and the user appears to lack privileges; use SET DEFAULT ROLE ALL or activate_all_roles_on_login=ON. A role is also an account (locked, no password) and shows up in mysql.user. Use a network range instead of '%' for application accounts. Never grant ALL, SUPER, FILE, PROCESS or CONNECTION_ADMIN to an application account. Revoking a privilege from a role affects active sessions too.