How to Revoke Privileges in MySQL: Syntax and Examples

To revoke privileges in MySQL, use the REVOKE statement followed by the specific privileges and the user account. For example, REVOKE SELECT ON database.table FROM 'user'@'host'; removes the SELECT privilege from that user on the specified table.

Syntax

The REVOKE statement removes one or more privileges from a MySQL user. The general syntax is:

• REVOKE privilege_type ON database.table FROM 'user'@'host';
• privilege_type can be SELECT, INSERT, UPDATE, DELETE, etc.
• database.table specifies where the privilege applies; use *.* for all databases and tables.
• 'user'@'host' identifies the user account.

sql

REVOKE privilege_type ON database.table FROM 'user'@'host';

Example

This example revokes the SELECT privilege on the employees table in the company database from the user 'alice'@'localhost'. It shows how to remove a specific privilege from a user.

sql

REVOKE SELECT ON company.employees FROM 'alice'@'localhost';

Output

Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when revoking privileges include:

• Using incorrect user or host names, which causes no privileges to be revoked.
• Forgetting to specify the correct database and table, so privileges remain unchanged.
• Not running FLUSH PRIVILEGES; if you manually modify privilege tables (though this is not needed with REVOKE).

Always double-check the user and host, and verify privileges after revoking.

sql

/* Wrong: misspelled user name, no effect */
REVOKE SELECT ON company.employees FROM 'alic'@'localhost';

/* Correct: exact user name */
REVOKE SELECT ON company.employees FROM 'alice'@'localhost';

Quick Reference

Command	Description
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';	Remove all privileges from the user on all databases and tables
REVOKE INSERT, UPDATE ON db.table FROM 'user'@'host';	Remove INSERT and UPDATE privileges on a specific table
REVOKE GRANT OPTION ON *.* FROM 'user'@'host';	Remove the ability to grant privileges to others
REVOKE SELECT ON db.* FROM 'user'@'host';	Remove SELECT privilege on all tables in a database

Key Takeaways

Use the REVOKE statement to remove specific privileges from a MySQL user.

Always specify the exact user and host to target the correct account.

Check the database and table scope to revoke privileges accurately.

Common errors include misspelling user names or forgetting the host part.

No need to run FLUSH PRIVILEGES after REVOKE unless you edit privilege tables manually.