Using Roles in MySQL 8.0: Concepts, Creation, Assignment, Activation, and Management

MySQL 8.0 introduced the concept of roles, which are collections of privileges that can be granted to users. This article explains the role concept, demonstrates how to create roles, assign them to users, activate default roles, manage role parameters, and revoke roles, with multiple practical examples.

Example 1: Granting a complete role

mysql> create role db_owner,db_datareader,db_datawriter;
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on ytt_new.* to db_owner;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on ytt_new.* to db_datareader;
Query OK, 0 rows affected (0.01 sec)

mysql> grant insert,delete,update on ytt_new.* to db_datawriter;
Query OK, 0 rows affected (0.01 sec)

mysql> create user ytt1 identified by 'ytt',ytt2 identified by 'ytt',ytt3 identified by 'ytt';
Query OK, 0 rows affected (0.01 sec)

mysql> grant db_owner to ytt1;
Query OK, 0 rows affected (0.02 sec)

mysql> set default role db_owner to ytt1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant db_datareader to ytt2;
Query OK, 0 rows affected (0.01 sec)

mysql> set default role db_datareader to ytt2;
Query OK, 0 rows affected (0.01 sec)

mysql> grant db_datawriter to ytt3;
Query OK, 0 rows affected (0.01 sec)

mysql> set default role db_datawriter to ytt3;
Query OK, 0 rows affected (0.01 sec)

Example 2: A user with multiple roles

mysql> create user ytt4 identified by 'ytt';
Query OK, 0 rows affected (0.00 sec)

mysql> grant db_owner,db_datareader,db_datawriter to ytt4;
Query OK, 0 rows affected (0.01 sec)

mysql> set default role all to ytt4;
Query OK, 0 rows affected (0.02 sec)

-- Verify current roles
mysql> select current_role();
+--------------------------------------------------------+
| current_role()                                         |
+--------------------------------------------------------+
| `db_datareader`@`%`,`db_datawriter`@`%`,`db_owner`@`%` |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Example 3: Switching roles within a session

-- Switch to db_datareader for user ytt4
mysql> set role db_datareader;
Query OK, 0 rows affected (0.00 sec)

mysql> select current_role();
+---------------------+
| current_role()      |
+---------------------+
| `db_datareader`@`%` |
+---------------------+
1 row in set (0.00 sec)

-- Attempt to create a table (fails because role lacks CREATE privilege)
mysql> create table t11(id int);
ERROR 1142 (42000): CREATE command denied to user 'ytt4'@'ytt-pc' for table 't11'

-- Switch back to db_owner (restores full privileges)
mysql> set role db_owner;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t11(id int);
Query OK, 0 rows affected (0.04 sec)

Example 4: Role activation parameters

-- Automatically activate all roles on login
mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)

-- Force a mandatory role for all users
mysql> set global mandatory_roles='db_datareader';
Query OK, 0 rows affected (0.00 sec)

-- Create a new user ytt7 (will inherit the mandatory role)
mysql> create user ytt7;
Query OK, 0 rows affected (0.01 sec)

-- Show grants for ytt7
mysql> show grants;
+-------------------------------------------+
| Grants for ytt7@%                         |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `ytt7`@`%`          |
| GRANT SELECT ON `ytt_new`.* TO `ytt7`@`%` |
| GRANT `db_datareader`@`%` TO `ytt7`@`%`   |
+-------------------------------------------+
3 rows in set (0.00 sec)

Example 5: Difference between CREATE ROLE and CREATE USER privileges

-- Create users ytt8 and ytt9
mysql> create user ytt8,ytt9;
Query OK, 0 rows affected (0.01 sec)

-- Grant CREATE ROLE to ytt8
mysql> grant create role on *.* to ytt8;
Query OK, 0 rows affected (0.02 sec)

-- Grant CREATE USER to ytt9
mysql> grant create user on *.* to ytt9;
Query OK, 0 rows affected (0.01 sec)

-- ytt8 can create a role but not a user
mysql> create role db_test;
Query OK, 0 rows affected (0.02 sec)
mysql> create user ytt10;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

-- ytt9 can create both role and user
mysql> create role db_test2;
Query OK, 0 rows affected (0.02 sec)
mysql> create user ytt10;
Query OK, 0 rows affected (0.01 sec)

Example 6: Using a MySQL user as a role

-- Create users ytt11 and ytt12
mysql> create user ytt11,ytt12;
Query OK, 0 rows affected (0.01 sec)

-- Grant SELECT to ytt11
mysql> grant select on ytt_new.* to ytt11;
Query OK, 0 rows affected (0.01 sec)

-- Grant ytt11 as a role to ytt12
mysql> grant ytt11 to ytt12;
Query OK, 0 rows affected (0.01 sec)

-- Show grants for ytt12
mysql> show grants for ytt12;
+-----------------------------------+
| Grants for ytt12@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `ytt12`@`%` |
| GRANT `ytt11`@`%` TO `ytt12`@`%`   |
+-----------------------------------+
2 rows in set (0.00 sec)

Example 7: Revoking and dropping roles

-- Revoke a role from a user
mysql> revoke db_datareader from ytt2;
Query OK, 0 rows affected (0.01 sec)

-- Drop all roles
mysql> drop role db_owner,db_datareader,db_datawriter;
Query OK, 0 rows affected (0.01 sec)

-- Verify that roles are removed from a user
mysql> show grants for ytt1;
+----------------------------------+
| Grants for ytt1@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `ytt1`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

By covering role creation, assignment, activation, parameter configuration, user‑role relationships, and revocation, this guide provides a comprehensive reference for managing MySQL role‑based access control.