MySQL Roles (MySQL 8.0+)

MySQL 8.0 introduced roles, which are named collections of privileges. Instead of granting the same set of privileges to multiple users individually, you define a role once and assign it to users. This simplifies privilege management and reduces errors.

Creating and Using Roles

Create a role with CREATE ROLE, grant privileges to the role, then grant the role to users. Users must activate roles (or have them set as default) to benefit from them.

Benefits of Roles

Centralized privilege management — change a role and all users inherit the update
Easier auditing — review role permissions in one place
Supports role hierarchies (granting roles to roles)
Separation of duties — DBA, developer, and read-only roles

Example

-- Create roles
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON myapp.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_read' TO 'report_user'@'%';
GRANT 'app_write' TO 'app_user'@'localhost';

-- Set default roles
SET DEFAULT ROLE 'app_write' TO 'app_user'@'localhost';

-- View active roles
SELECT CURRENT_ROLE();

SQL

Full Editor

Pro Tip

Use roles instead of granting identical privilege sets to multiple users — it's much easier to manage.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned