✏️ Explanatory Question
Level: Hard — Roles are the modern solution to the "50 users, same permissions" management nightmare.
Scenario: You have 30 developers who all need read-only access to the reporting database, and 10 services that need read-write on the app database. Managing individual GRANTs for 40 accounts is error-prone — when permissions change, you must edit them one by one. How do roles solve this?
A role is a named collection of privileges. Instead of granting privileges directly to each user, you grant them to a role once, then assign that role to many users. Change the role's privileges, and every user with that role is updated automatically.
The core benefit: Roles turn permission management from "per-user" into "per-group." Define app_readwrite once; assign it to 10 services. Later, adding a new privilege to all 10 is a single statement on the role, not 10 edits.
-- Create roles (they look like users but cannot log in by default)
CREATE ROLE 'app_readwrite', 'report_readonly', 'db_admin';
-- Grant privileges TO the roles (once)
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_readwrite';
GRANT SELECT ON reporting.* TO 'report_readonly';
GRANT ALL PRIVILEGES ON shop.* TO 'db_admin';-- Create users
CREATE USER 'service1'@'10.0.%' IDENTIFIED BY 'pass1';
CREATE USER 'service2'@'10.0.%' IDENTIFIED BY 'pass2';
CREATE USER 'analyst_ravi'@'%' IDENTIFIED BY 'pass3';
-- Assign roles instead of individual privileges
GRANT 'app_readwrite' TO 'service1'@'10.0.%', 'service2'@'10.0.%';
GRANT 'report_readonly' TO 'analyst_ravi'@'%';
-- One change to the role updates ALL its users:
GRANT EXECUTE ON shop.* TO 'app_readwrite'; -- now service1 AND service2 have itA granted role is not active by default when a user logs in — they must "activate" it, or you set a default. This trips up almost everyone.
-- Set the role to activate automatically on login (the usual fix)
SET DEFAULT ROLE 'app_readwrite' TO 'service1'@'10.0.%';
-- Or make ALL granted roles active by default for a user
SET DEFAULT ROLE ALL TO 'service1'@'10.0.%';
-- Within a session, a user can switch active roles manually
SET ROLE 'report_readonly';
SET ROLE ALL; -- activate all granted roles
SET ROLE NONE; -- drop all privileges from roles for this session
-- Check the currently active role
SELECT CURRENT_ROLE(); The #1 roles gotcha: A user is granted a role but "has no permissions" — because the role was never activated. Fix it with SET DEFAULT ROLE ALL TO ... so the role applies automatically at login. Or globally set activate_all_roles_on_login = ON.
-- What privileges does a role/user have?
SHOW GRANTS FOR 'app_readwrite';
SHOW GRANTS FOR 'service1'@'10.0.%' USING 'app_readwrite';
-- Remove a role from a user
REVOKE 'app_readwrite' FROM 'service1'@'10.0.%';
-- Delete a role entirely (removes it from all users)
DROP ROLE 'db_admin';-- Compose roles: a "senior_dev" role that includes two other roles
CREATE ROLE 'senior_dev';
GRANT 'app_readwrite', 'report_readonly' TO 'senior_dev';
-- Anyone granted 'senior_dev' inherits both underlying roles Interviewer follow-up: "How do roles help with compliance and audits?" → Roles make access self-documenting — instead of scanning 40 users' raw grants, an auditor reviews a handful of well-named roles (report_readonly, app_readwrite) and who holds them. Onboarding/offboarding becomes granting/revoking one role, which is easier to control, review, and prove during a security audit.