Role-based access in MySQL

Introduction

Role-based access helps control who can do what in a database. It keeps data safe by giving permissions based on roles, not individuals.

When you want to give different people different levels of access, like read-only or full control.

When managing a team where some members only need to view data and others need to change it.

When you want to easily add or remove permissions by changing roles instead of each user.

When you want to keep your database secure by limiting access to sensitive information.

When you want to organize permissions clearly for auditing and management.

Syntax

MySQL

CREATE ROLE role_name;
GRANT privilege ON database.table TO role_name;
GRANT role_name TO user_name;
REVOKE privilege ON database.table FROM role_name;
REVOKE role_name FROM user_name;

Roles group permissions so you can assign many permissions at once.

You grant roles to users, and users get all permissions of that role.

Examples

This creates a role named read_only, gives it permission to read the customers table, and assigns the role to user Alice.

MySQL

CREATE ROLE read_only;
GRANT SELECT ON mydb.customers TO read_only;
GRANT read_only TO 'alice'@'localhost';

This creates an admin role with full access to the database and assigns it to user Bob.

MySQL

CREATE ROLE admin;
GRANT ALL PRIVILEGES ON mydb.* TO admin;
GRANT admin TO 'bob'@'localhost';

This removes the read permission from the read_only role and removes the role from Alice.

MySQL

REVOKE SELECT ON mydb.customers FROM read_only;
REVOKE read_only FROM 'alice'@'localhost';

Sample Program

This example creates a manager role with permission to view and add orders. Then it assigns this role to user Carol. Finally, it shows Carol's permissions.

MySQL

CREATE ROLE manager;
GRANT SELECT, INSERT ON mydb.orders TO manager;
GRANT manager TO 'carol'@'localhost';

-- Check roles granted to Carol
SHOW GRANTS FOR 'carol'@'localhost';

Important Notes

Roles make permission management easier and cleaner.

Remember to create roles before granting permissions to them.

Use SHOW GRANTS FOR user; to see what permissions a user has.

Summary

Role-based access controls permissions by grouping them into roles.

Assign roles to users to give them the permissions they need.

This method improves security and simplifies permission management.