Six applications with six database users means six sets of grants maintained by hand, and the reporting tool ends up with write access because copying an existing user was quicker.
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';
CREATE USER 'reports'@'10.0.%' IDENTIFIED BY '...';
GRANT 'app_read' TO 'reports'@'10.0.%';
SET DEFAULT ROLE ALL TO 'reports'@'10.0.%';The SET DEFAULT ROLE line is the one that gets forgotten: a granted role is not active until the session activates it, so without a default the user connects with no privileges at all and the application reports a permissions error that looks like the grant did not work. Roles also make an audit answerable — SHOW GRANTS for a role is one statement rather than a diff across six users.