Roles

Roles

Roles, Default Roles, and SET ROLE

A role is a locked, named account that holds privileges. Grant privileges to the role and the role to accounts, and one change reaches every holder:

A read role, a write role and a reporting accountSQL

CREATE ROLE 'shop_read', 'shop_write';
GRANT SELECT ON shop.* TO 'shop_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';
CREATE USER 'report'@'localhost' IDENTIFIED BY 'Report#Only-2026';
GRANT 'shop_read' TO 'report'@'localhost';

A granted role is inactive until SET ROLE switches it on for the session, or SET DEFAULT ROLE makes it active at every login:

A granted role does nothing until it is activeSQL

q() { mysql -ureport -p'Report#Only-2026' -Nse "$1" 2>&1 | grep -v Warning; }
q "SELECT CURRENT_ROLE(); SELECT COUNT(*) FROM shop.orders"
q "SET ROLE 'shop_read'; SELECT CURRENT_ROLE(); SELECT COUNT(*) FROM shop.orders"
sudo mysql -e "SET DEFAULT ROLE 'shop_read' TO 'report'@'localhost'"
q "SELECT COUNT(*) FROM shop.orders; UPDATE shop.products SET price = 0"

Output

NONE
ERROR 1142 (42000) at line 1: SELECT command denied to user 'report'@'localhost' for table
  'orders'
`shop_read`@`%`
9
9
ERROR 1142 (42000) at line 1: UPDATE command denied to user 'report'@'localhost' for table
  'products'

The reporting login is read-only by construction. SHOW GRANTS FOR ... USING 'shop_read' previews a role's effect, activate_all_roles_on_login activates every granted role, and mandatory_roles grants one to everybody (off and empty by default). Roles nest: granting shop_read to shop_write makes the writer a superset.

MENU