The pager went off because a deploy had "removed" the reporting team's access. Nobody had revoked anything. What actually happened: the previous DBA had granted everyone a nicely curated report_ro role, the connection pool was rebuilt during the deploy, and every new connection came up without the role active — because no one had ever run SET DEFAULT ROLE. Three hundred analysts, all holding the right privileges on paper, all getting ERROR 1142 at 9:12 on a Monday. Roles in MariaDB are genuinely good. The activation model is the part that bites, and it bites exactly once per team, at the worst moment.
How do MariaDB roles actually work?
Roles arrived in MariaDB 10.0.5 and the model is refreshingly simple: CREATE ROLE makes a named bundle, you GRANT privileges to the bundle, and you GRANT the bundle to users. Roles can be granted to other roles, so an app_rw role can include app_ro and inherit its reads without duplicating a single grant. The assignments live in the mysql.roles_mapping table, and SHOW GRANTS works on a role exactly the way it works on a user, which makes roles self-documenting in a way per-user grants never are. WITH ADMIN OPTION on a role grant lets the recipient pass the role onward — reserve it for whoever owns role administration, because an admin-option holder can hand the role to anyone on the server.
CREATE ROLE app_ro, app_rw, report_ro;
GRANT SELECT ON app.* TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO app_rw;
GRANT app_ro TO app_rw; -- writers inherit reads
GRANT app_rw TO 'svc_app'@'10.0.%';
GRANT report_ro TO 'analyst_kim'@'%';
SHOW GRANTS FOR app_rw;Why does a granted role do nothing at login?
Because in MariaDB, being granted a role and having the role active are two different states, and the gap between them is the single most common roles incident. By default, a freshly connected session has no roles active; the user must issue SET ROLE app_rw inside the session before the role's privileges apply. For a human at a prompt that is a nuisance. For an application it is a guarantee of failure, because connection pools open, recycle, and re-authenticate sessions constantly, and nothing remembers to SET ROLE on every checkout. The fix is SET DEFAULT ROLE, available since MariaDB 10.1: it records, per user, a role the server activates automatically at connect time. There is no server-wide activate-everything switch — activate_all_roles_on_login is a MySQL 8.0 variable and does not exist on MariaDB, which is just as well, because activation per account is the granularity you want anyway. One migration note for MySQL refugees: a MariaDB session holds one current role at a time rather than a stack of simultaneously active roles, so an account that needs two roles' powers at once should hold a single role that nests the other.
One debugging note that saves hours: SHOW GRANTS FOR a user lists the role membership, which looks like access, but says nothing about activation. When someone reports that the grant is right there but the query is denied, check CURRENT_ROLE() in the failing session first, and check whether the application reconnects through a pooler or proxy that resets session state between checkouts. Every hour-long mystery of this kind I have debugged ended in one of those two places, and neither of them is visible in the grant tables that everyone stares at first.
-- the one line that would have prevented my Monday:
SET DEFAULT ROLE app_rw FOR 'svc_app'@'10.0.%';
-- what is active right now, in this session?
SELECT CURRENT_ROLE();
SELECT * FROM information_schema.ENABLED_ROLES;
-- what could an account activate if it asked? APPLICABLE_ROLES answers
-- for the CONNECTED user only, so run it as that account -- as a DBA
-- auditing someone else, read the assignments straight from the source:
SELECT USER, HOST, ROLE, ADMIN_OPTION
FROM mysql.roles_mapping
WHERE USER = 'svc_app' AND HOST = '10.0.%';What changed with PUBLIC in MariaDB 10.11?
MariaDB 10.11 added GRANT ... TO PUBLIC: privileges granted to PUBLIC apply to every account on the server, present and future, with no activation step at all — PUBLIC behaves as an always-on role that everyone holds. SHOW GRANTS FOR PUBLIC displays the current set. Used with restraint, it is the right home for the handful of things every session legitimately needs. Used casually, it is the fastest route to handing the entire server a privilege nobody reviewed, and every account created next quarter inherits it silently. My rule: PUBLIC gets nothing I would not give a brand-new account on its first day, and every grant to it goes through the same review as a grant to root. Audit it explicitly, because privileges arriving via PUBLIC are easy to miss when you inspect users one at a time — which is exactly how surprise access hides for years.
How do you audit what a user can actually do?
Effective privilege is the union of direct grants, active roles, roles nested inside those roles, and PUBLIC — and no single view hands you that union. My audit pass starts with SHOW GRANTS FOR the user, which shows direct grants and role memberships, then walks each role's own SHOW GRANTS recursively, because roles-in-roles is the nesting everyone forgets until the incident review. Cross-check information_schema.APPLICABLE_ROLES against ENABLED_ROLES: the gap between what an account may activate and what is actually active in its sessions is where "worked in testing, denied in production" comes from. The privilege tables — information_schema.SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, COLUMN_PRIVILEGES — answer the inverse question, who can touch this schema, which is the one auditors actually ask.
-- every account holding a role, and whether it fires at login
SELECT r.USER, r.HOST, r.ROLE,
COALESCE(NULLIF(u.default_role, ''), '(none)') AS default_role
FROM mysql.roles_mapping r
JOIN mysql.user u ON u.User = r.USER AND u.Host = r.HOST
ORDER BY r.ROLE, r.USER;
-- every grant recorded on the billing schema; GRANTEE lists accounts AND
-- role names, so expand the roles through mysql.roles_mapping to reach users
SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'billing';That default_role column on mysql.user is the early-warning system: any account holding a role with no default set is one pool rebuild away from the login trap. Query it before every deploy that touches connection infrastructure.
How do you migrate from copy-paste privilege sprawl?
Every long-lived MariaDB server accumulates the same archaeology: forty users with forty slightly different hand-copied grant sets, and nobody willing to touch any of them because nobody knows what breaks. The migration that worked for me was inventory-first. Dump SHOW GRANTS for every account, normalize the output, and cluster identical grant sets — in my last cleanup, forty-one users collapsed into six real access patterns. Create one role per pattern, grant it to the matching users, and set it as each user's DEFAULT ROLE in the same change. That ordering matters: the role must be active before you remove what it replaces. A week later, once the error log confirms nothing depended on a stray direct grant the pattern missed, revoke the direct grants in a second pass.
For service accounts, the end state should be boring and strict: one user per service, one default role holding exactly the privileges that service uses, no WITH ADMIN OPTION anywhere outside the DBA-owned admin role, and a quarterly diff of mysql.roles_mapping so drift surfaces in review instead of in an incident. The sprawl took years to grow; give yourself two careful passes to remove it, not one heroic one.
Where MonPG fits
Access drift is a monitoring problem as much as a security one: the teams that get paged on ERROR 1142 storms are the ones with no baseline of what grants existed yesterday. Disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and the operational signals around access, like connection failures by user and host, are part of what it is being built to keep continuous. Until it ships, the APPLICABLE_ROLES and default_role queries above, run on a schedule, are the honest substitute. And if PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview and the blog.