MySQL Roles: Simplify Database Privilege Management

Managing database permissions for dozens of users individually is highly inefficient and prone to human error. MySQL roles, introduced in version 8.0, solve this problem by allowing administrators to group privileges into a single container (a role) and assign that container to multiple users. This article provides a direct, step-by-step guide on how to create, assign, and manage MySQL roles to streamline your database security and access control.

What is a Database Role?

A MySQL role is a named collection of privileges. Instead of granting specific table, schema, or global privileges to every individual user account, you grant those privileges to a role. You then assign the role to users. When you need to modify permissions for a group of users, you only need to update the role, and the changes automatically apply to all users assigned to that role.

Step 1: Create a Role

In MySQL, roles are treated similarly to user accounts but are designed to hold privileges rather than log in directly.

To create a role, use the CREATE ROLE statement:

CREATE ROLE 'developer_role', 'analyst_role', 'admin_role';

In this example, three distinct roles are created in a single command.

Step 2: Grant Privileges to the Role

Once the roles are created, you define what they can do by assigning privileges to them using the standard GRANT statement.

-- Grant read-only access to the analyst role
GRANT SELECT ON company_db.* TO 'analyst_role';

-- Grant read and write access to the developer role
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'developer_role';

-- Grant all privileges on the database to the admin role
GRANT ALL PRIVILEGES ON company_db.* TO 'admin_role';

Step 3: Assign Roles to Users

With the roles configured, you can now assign them to existing database users. If the user accounts do not exist yet, you must create them first.

-- Create a new user
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'secure_password';

-- Assign the developer role to the user
GRANT 'developer_role' TO 'alice'@'localhost';

You can assign multiple roles to a single user if their job requires overlapping responsibilities:

GRANT 'analyst_role', 'developer_role' TO 'bob'@'localhost';

Step 4: Activate the Roles

By default, when a role is granted to a MySQL user, it is not active when the user logs in. The user or the administrator must activate the role.

Manual Activation

A user can manually activate their assigned roles during their session:

SET ROLE 'developer_role';

To activate all assigned roles for the current session:

SET ROLE ALL;

Automatic Activation (Recommended)

To prevent users from having to activate their roles manually every time they connect, you can define default roles.

To set a default role for a specific user:

SET DEFAULT ROLE 'developer_role' TO 'alice'@'localhost';

To make all granted roles active by default for all future logins, configure the global system variable:

SET GLOBAL activate_all_roles_on_login = ON;

Step 5: Revoking Roles and Privileges

If a user’s job description changes, you can easily strip them of their role:

REVOKE 'developer_role' FROM 'alice'@'localhost';

If you need to change what a role can do globally, modify the role itself. The change will instantly propagate to all assigned users:

REVOKE DELETE ON company_db.* FROM 'developer_role';

To delete a role entirely from the system:

DROP ROLE 'analyst_role';

Verifying Role Assignments

To audit your security setup and see which roles are assigned to which users, use the SHOW GRANTS command:

-- View roles and privileges assigned to a user
SHOW GRANTS FOR 'alice'@'localhost';

-- View the active roles in the current session
SELECT CURRENT_ROLE();