✅ MySQL GRANT / ❌ REVOKE Privileges – Manage Database Access Controls

---

🧲 Introduction – Why Use GRANT and REVOKE?

MySQL’s GRANT and REVOKE statements allow fine-grained control over what users can do inside a database. These commands ensure:

• ✅ Proper access separation between users
• 🔒 Protection against unauthorized data changes
• 🎯 Role-based permission assignment

Using these commands correctly ensures security, maintainability, and least-privilege access across your MySQL systems.

🎯 In this guide, you’ll learn:

• How to use GRANT to assign privileges
• How to use REVOKE to remove them
• Common privileges and their scopes
• Best practices and real-world examples

---

✅ GRANT – Assign Privileges to Users

🔹 Syntax

GRANT privileges ON database.table TO 'username'@'host';

• privileges: One or more permissions (e.g., SELECT, INSERT, ALL PRIVILEGES)
• database.table: Use *.* for all databases/tables or db.* for one database
• 'username'@'host': Account receiving the permissions

---

🔹 Grant Specific Privileges

GRANT SELECT, INSERT ON sales.* TO 'report_user'@'localhost';

Explanation:
Allows report_user to only read and insert into all tables of the sales database.

---

🔹 Grant All Privileges

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

Explanation:
Grants full access to everything, including the ability to grant privileges to others.

---

🔹 Grant Privileges to a Table

GRANT UPDATE, DELETE ON hr.employees TO 'hr_editor'@'localhost';

✅ Limits access to just one table within one database.

---

🔹 View Granted Privileges

SHOW GRANTS FOR 'username'@'host';

---

❌ REVOKE – Remove Privileges

🔹 Syntax

REVOKE privileges ON database.table FROM 'username'@'host';

---

🔹 Revoke Specific Privileges

REVOKE INSERT ON sales.* FROM 'report_user'@'localhost';

Explanation:
Removes INSERT access but leaves SELECT intact.

---

🔹 Revoke All Privileges

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'%';

✅ Fully strips the user of all permissions and the ability to delegate access.

---

🔐 Common MySQL Privileges

Privilege	Description
SELECT	Read rows from tables or views
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create new databases or tables
DROP	Delete databases or tables
INDEX	Create or remove indexes
ALTER	Change table structures
GRANT OPTION	Grant permissions to other users
ALL PRIVILEGES	Shortcut for all available privileges

---

📋 Scope Control

Target	Syntax Example	Effect
All DBs/tables	GRANT SELECT ON *.*	Access across entire server
Specific DB	GRANT INSERT ON app_db.*	Access within a database
One Table	GRANT DELETE ON crm.users	Access to only one table
One Column	GRANT SELECT(name) ON crm.users	Grants on a column (MySQL 8.0+)

---

🧱 Example: Role-Based Privileges

Role	Grant Command Example
Reader	GRANT SELECT ON analytics.* TO 'reader'@'%';
Editor	GRANT SELECT, UPDATE ON crm.customers TO 'editor'@'localhost';
Admin	GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;
Limited API	GRANT SELECT ON api.products TO 'api_user'@'%';

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Always assign minimum required privileges	Prevents misuse or accidental changes
Use GRANT OPTION sparingly	Prevents privilege escalation
Review privileges regularly	Audits security holes and user bloat
Grant at database or table level	Improves precision of access control
Use separate users per app/module	Enables scoped revocation and auditing

---

🚀 Real-World Use Cases

Scenario	GRANT/REVOKE Command
Give read-only access to analyst	GRANT SELECT ON sales.* TO 'analyst'@'%';
Revoke drop privileges from developer	REVOKE DROP ON dev.* FROM 'dev_user'@'localhost';
Give insert/update to form handler	GRANT INSERT, UPDATE ON web.contacts TO 'form_user'@'%';
Remove all access for former intern	REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'intern'@'%';

---

📌 Summary – Recap & Next Steps

MySQL’s GRANT and REVOKE statements give you full control over user access. Use them to define precise roles, limit exposure, and enforce best practices in user and permission management.

🔍 Key Takeaways

• Use GRANT to assign one or more privileges to users
• Use REVOKE to remove or restrict access
• Always limit privileges to only what is necessary
• Use SHOW GRANTS to audit access
• Use GRANT OPTION carefully to avoid privilege escalation

⚙️ Real-World Relevance

You’ll use GRANT and REVOKE in DevOps pipelines, production database setups, application configuration, compliance enforcement, and more.

---

❓ FAQ – GRANT / REVOKE in MySQL

---

❓ How do I see what a user can do?

SHOW GRANTS FOR 'username'@'host';

---

❓ Can I grant privileges on a single table?

✅ Yes:

GRANT SELECT ON db_name.table_name TO 'user'@'host';

---

❓ What’s the difference between ALL PRIVILEGES and GRANT OPTION?

• ALL PRIVILEGES: Grants all abilities on specified objects
• GRANT OPTION: Allows the user to delegate their privileges to others

---

❓ How do I revoke all access from a user?

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';

---

❓ Does REVOKE delete the user?

❌ No. It only removes access. Use DROP USER to delete the account.

---

Share Now :