? MySQL Roles & Privileges

? Quick Overview

In MySQL, privileges control what actions a user can perform, while roles are collections of privileges that can be assigned to users. Roles simplify permission management and improve database security.

? Key Concepts

Privilege – Permission to perform a specific database action
Role – Named group of privileges
GRANT – Assign privileges or roles
REVOKE – Remove privileges or roles
DEFAULT ROLE – Role enabled automatically on login

? Syntax & Theory

MySQL supports different privilege levels:

Global level (*.*)
Database level (db_name.*)
Table level (db_name.table)
Column level

? Code Examples

? View Code Example

-- Create a new role for read-only access
CREATE ROLE 'reporting_role';

-- Grant SELECT privilege on a database
GRANT SELECT ON company_db.* TO 'reporting_role';

? View Code Example

-- Assign role to a user
GRANT 'reporting_role' TO 'rahul'@'localhost';

-- Set role as default for the user
SET DEFAULT ROLE 'reporting_role' TO 'rahul'@'localhost';

? View Code Example

-- Grant multiple privileges directly to a user
GRANT INSERT, UPDATE ON company_db.employees TO 'admin1'@'localhost';

? Live Output / Explanation

After executing these commands:

The role reporting_role can only read data
User rahul inherits all permissions of the role
Permissions are applied automatically at login

✅ Tips & Best Practices

Use roles instead of granting privileges directly to users
Follow the principle of least privilege
Regularly audit user permissions
Revoke unused roles and privileges

? Try It Yourself

Create a role for backup operations
Assign it to multiple users
Revoke a privilege and observe the effect
List privileges using SHOW GRANTS