Giving 20 developers the same ten privileges one by one is tedious and error-prone. A role is a named set of privileges. You grant privileges to the role once, and grant the role to users. MySQL 8.0 added roles.
CREATE DATABASE shop;
CREATE TABLE shop.customers (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO shop.customers VALUES (1, 'Maya');
CREATE ROLE 'r_read', 'r_write';
GRANT SELECT ON shop.* TO 'r_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'r_write';
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'D3v!one#Pass';
GRANT 'r_read', 'r_write' TO 'dev1'@'localhost';
SHOW GRANTS FOR 'dev1'@'localhost';+--------------------------------------------------------+
| Grants for dev1@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT `r_read`@`%`,`r_write`@`%` TO `dev1`@`localhost` |
+--------------------------------------------------------+
2 rows in setGranting the roles is not the same as having them active. The user’s grants list shows the role grants, and the privileges of the roles show only with USING:
SHOW GRANTS FOR 'dev1'@'localhost' USING 'r_read';+--------------------------------------------------------+
| Grants for dev1@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT SELECT ON `shop`.* TO `dev1`@`localhost` |
| GRANT `r_read`@`%`,`r_write`@`%` TO `dev1`@`localhost` |
+--------------------------------------------------------+
3 rows in setA role is itself an account (a locked one, with host %), and GRANT role TO user records an edge in mysql.role_edges.
Roles must be activated
A role grant does nothing until the role is active in the session. By default, no role is active at login. This is what a real connection as dev1 shows (on MySQL 8.4):
SELECT CURRENT_ROLE() AS active_roles; -> NONE
SELECT * FROM shop.customers; -> ERROR 1142 (42000): SELECT command denied to user 'dev1'@'localhost' for table 'customers'The user activates roles with SET ROLE:
SET ROLE 'r_read';
SELECT CURRENT_ROLE(); -> `r_read`@`%`
SELECT * FROM shop.customers; -> 1 row
INSERT INTO shop.customers VALUES (2,'Ben');-> ERROR 1142 (42000): INSERT command denied to user 'dev1'@'localhost' for table 'customers'
SET ROLE ALL;
SELECT CURRENT_ROLE(); -> `r_read`@`%`,`r_write`@`%`
INSERT INTO shop.customers VALUES (2,'Ben');-> worksForms of SET ROLE:
| Statement | Effect |
|---|---|
SET ROLE 'r_read' | Only this role |
SET ROLE ALL | All granted roles |
SET ROLE ALL EXCEPT 'r_write' | All but some |
SET ROLE DEFAULT | The user’s default roles |
SET ROLE NONE | No roles |
Default roles
To have roles active automatically at login, set default roles:
SET DEFAULT ROLE 'r_read' TO 'dev1'@'localhost';
SELECT default_role_user FROM mysql.default_roles WHERE user = 'dev1';+-------------------+
| default_role_user |
+-------------------+
| r_read |
+-------------------+
1 row in setNow dev1 logs in with r_read already active (confirmed by a real session: CURRENT_ROLE() shows ` r_read@% `, and SELECT COUNT(*) works). SET DEFAULT ROLE ALL TO user` makes all granted roles default.
Two server settings change the behaviour:
SELECT @@activate_all_roles_on_login AS activate_all_on_login, @@mandatory_roles AS mandatory_roles;+-----------------------+-----------------+
| activate_all_on_login | mandatory_roles |
+-----------------------+-----------------+
| 0 | |
+-----------------------+-----------------+
1 row in setactivate_all_roles_on_login = ONactivates every granted role at login (convenient, less explicit). Default:OFF.mandatory_roleslists roles that every account receives automatically (for example a read-only baseline).
Role design
A small, clear set of roles beats many one-off grants:
| Role | Privileges |
|---|---|
app_rw | SELECT, INSERT, UPDATE, DELETE on the application database |
app_ro | SELECT on the application database |
app_migrate | CREATE, ALTER, DROP, INDEX, REFERENCES on the application database |
dba_readonly | SELECT on all, plus PROCESS and SHOW VIEW |
Roles can also be granted to other roles (a role hierarchy):
CREATE ROLE 'r_support';
GRANT 'r_read' TO 'r_support';
GRANT SELECT ON mysql.help_topic TO 'r_support';
SHOW GRANTS FOR 'r_support' USING 'r_read';+---------------------------------------------------------+
| Grants for r_support@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `r_support`@`%` |
| GRANT SELECT ON `shop`.* TO `r_support`@`%` |
| GRANT SELECT ON `mysql`.`help_topic` TO `r_support`@`%` |
| GRANT `r_read`@`%` TO `r_support`@`%` |
+---------------------------------------------------------+
4 rows in setChanging a role
When you GRANT or REVOKE privileges on the role, every user who has the role gets the change at their next role activation (new session or SET ROLE). That is the main benefit: update the role once, and everybody follows.
Drop
DROP ROLE 'r_support';
REVOKE 'r_write' FROM 'dev1'@'localhost';
SHOW GRANTS FOR 'dev1'@'localhost';+------------------------------------------+
| Grants for dev1@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT `r_read`@`%` TO `dev1`@`localhost` |
+------------------------------------------+
2 rows in setRoles and applications
For application accounts, privileges granted directly are simpler, because applications do not issue SET ROLE. If you use a role for an application account, set it as the default role (or enable activate_all_roles_on_login), or the application connects with no privileges and every query fails.
Roles and stored programs
A stored program with SQL SECURITY INVOKER runs with the caller’s active roles. One with DEFINER uses the definer’s roles at the time of execution. Test both.