LearnContact
Lesson 7118 min read

Roles & Privileges

Learn how to grant and revoke specific privileges, apply the principle of least privilege, and group permissions using MySQL roles.

Introduction

Creating a user, as you saw in the last lesson, is only half the story — a fresh account has no meaningful access until you explicitly grant it privileges. Deciding exactly what each user is allowed to do is where real database security happens.

This lesson covers granting specific privileges with GRANT, removing them with REVOKE, the principle of least privilege that should guide every decision you make, and MySQL 8.0's roles feature for managing privileges at scale.

What You Will Learn
  • How to grant specific privileges with GRANT.
  • The principle of least privilege, and why it matters.
  • How to remove privileges with REVOKE.
  • How MySQL roles group privileges for reuse across multiple users.

Granting Specific Privileges

The GRANT statement gives a user permission to perform specific actions on a specific database, table, or column. You list exactly which privileges (like SELECT, INSERT, UPDATE, DELETE) and exactly which object they apply to.

GRANT SELECT, INSERT ON shop.products TO 'app_user'@'localhost';

This allows app_user, connecting from localhost, to read from and insert into the products table in the shop database — nothing more. It cannot UPDATE or DELETE rows, and it cannot touch any other table, unless separately granted.

SHOW GRANTS FOR 'app_user'@'localhost';
Result
+----------------------------------------------------------------+
| Grants for app_user@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                    |
| GRANT SELECT, INSERT ON `shop`.`products` TO `app_user`@`localhost` |
+----------------------------------------------------------------+

You can grant privileges across an entire database using a wildcard for the table, or across the entire server using a wildcard for both.

-- All privileges on every table within the shop database
GRANT ALL PRIVILEGES ON shop.* TO 'admin_user'@'localhost';

-- Read-only access to a single table
GRANT SELECT ON shop.orders TO 'reporting_user'@'localhost';

The Principle of Least Privilege

The principle of least privilege means granting a user only the exact permissions it needs to do its job — nothing more. A reporting tool that only ever reads data should be granted SELECT and nothing else. It should never be granted DELETE just because it might be convenient someday.

Why This Matters

If an account with minimal privileges is ever compromised — through a leaked password or an application bug — the attacker is limited to exactly what that account could do. A reporting account restricted to SELECT cannot be used to delete your data, even if it is fully compromised.

UserActual NeedPrivileges to Grant
reporting_userReads data for dashboardsSELECT only
app_userEveryday application reads/writesSELECT, INSERT, UPDATE, DELETE
migration_userOccasionally changes table structureALTER, CREATE, DROP (used temporarily)
backup_userRuns mysqldump backupsSELECT, LOCK TABLES

Revoking Privileges

Just as GRANT adds a privilege, REVOKE removes one that was previously granted. This is useful when a user's role changes, or when you discover a privilege was granted too broadly.

REVOKE INSERT ON shop.products FROM 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
Result
+----------------------------------------------------------------+
| Grants for app_user@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                    |
| GRANT SELECT ON `shop`.`products` TO `app_user`@`localhost`     |
+----------------------------------------------------------------+

After this REVOKE, app_user can still SELECT from products, but INSERT has been removed — exactly mirroring how it was granted, just in reverse.

Grouping Privileges with Roles

Manually granting the same set of privileges to many different users, one by one, is repetitive and easy to get inconsistent. MySQL 8.0 introduced roles: named, reusable collections of privileges that can be assigned to multiple users at once.

First, create a role and grant it the privileges it should represent, exactly like you would grant them to a user.

CREATE ROLE 'read_only_role';
GRANT SELECT ON shop.* TO 'read_only_role';

Then, assign that role to as many users as needed, and activate it for each of them.

CREATE USER 'analyst_1'@'localhost' IDENTIFIED BY 'AnalystPass1!';
CREATE USER 'analyst_2'@'localhost' IDENTIFIED BY 'AnalystPass2!';

GRANT 'read_only_role' TO 'analyst_1'@'localhost';
GRANT 'read_only_role' TO 'analyst_2'@'localhost';

SET DEFAULT ROLE 'read_only_role' TO 'analyst_1'@'localhost', 'analyst_2'@'localhost';

Now, if the reporting team needs an additional table added to their read access, you update the role in one place — GRANT SELECT ON shop.new_table TO 'read_only_role'; — and every user holding that role gets the update automatically.

Roles vs Granting Directly

Think of a role as a labeled folder of permissions. Instead of copying the same set of GRANT statements to every new employee's account, you create the folder once and hand it to whoever needs it — updating the folder updates everyone holding it.

Applying FLUSH PRIVILEGES

Statements like GRANT, REVOKE, CREATE USER, and DROP USER take effect immediately, so FLUSH PRIVILEGES is rarely required after them. It exists mainly for the rarer case where privilege tables were changed directly (for example, with a manual UPDATE on the mysql.user table), which needs an explicit reload to take effect.

FLUSH PRIVILEGES;
When You Actually Need It

If you always manage users and privileges through CREATE USER, GRANT, and REVOKE, you will rarely need FLUSH PRIVILEGES. It becomes relevant mainly after direct, manual edits to MySQL's internal grant tables.

Common Mistakes

Avoid These Mistakes
  • Granting ALL PRIVILEGES out of convenience instead of listing exactly what is needed.
  • Forgetting to REVOKE a privilege after a user's responsibilities change.
  • Recreating the same set of GRANT statements for every new user instead of using a role.
  • Assuming FLUSH PRIVILEGES is required after every GRANT or REVOKE — it usually is not.

Best Practices

  • Always apply the principle of least privilege — grant exactly what is needed, nothing more.
  • Use roles to manage privileges consistently across groups of similar users.
  • Periodically audit privileges with SHOW GRANTS and REVOKE anything no longer necessary.
  • Document why each privilege was granted, especially broad ones like ALL PRIVILEGES.

Frequently Asked Questions

What is the difference between a role and a user?

A user is an account that can log in and connect to MySQL. A role cannot log in on its own — it is purely a named bundle of privileges that gets assigned to one or more actual users.

Do I need MySQL 8.0 to use roles?

Yes, roles were introduced in MySQL 8.0. Earlier versions require granting the same privileges individually to each user.

Can a user have more than one role?

Yes, a user can be granted multiple roles, and can even have several active at once, combining all their privileges.

What happens if I REVOKE a privilege that was never granted?

MySQL will typically report a warning or error indicating the privilege did not exist for that user, though the exact behavior can depend on server settings.

Is GRANT ALL PRIVILEGES ever appropriate?

It can be appropriate for a genuine database administrator account, but it violates least privilege for ordinary application or reporting accounts and should be avoided for those.

Key Takeaways

  • GRANT assigns specific privileges, like SELECT or INSERT, on a specific database or table.
  • The principle of least privilege means granting only what a user actually needs.
  • REVOKE removes a privilege that was previously granted.
  • MySQL 8.0 roles group privileges together so they can be assigned to many users at once.
  • FLUSH PRIVILEGES is rarely needed after GRANT/REVOKE, but matters after direct edits to grant tables.

Summary

Precisely controlling who can do what — through targeted GRANT statements, careful REVOKE cleanup, and reusable roles — is what turns a database from a security liability into a properly guarded system.

In this lesson, you learned how to grant and revoke specific privileges, why the principle of least privilege matters, and how MySQL roles let you manage privileges for groups of users efficiently. Next, you will pull all of these security ideas together into a complete set of security best practices.

Lesson 71 Completed
  • You can grant and revoke specific privileges with GRANT and REVOKE.
  • You understand and can apply the principle of least privilege.
  • You can create and assign MySQL roles to group privileges.
  • You are ready to learn MySQL security best practices.
Next Lesson →

Security Best Practices