Revoking privileges in MySQL

Introduction

Revoking privileges means taking away permissions from a user. This helps keep the database safe by controlling who can do what.

When a user no longer needs access to certain data or actions.

If a user's role changes and they should have fewer permissions.

To fix mistakes when too many privileges were given by accident.

When cleaning up old users who should not have any access.

To improve security by limiting access to sensitive parts of the database.

Syntax

MySQL

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

You can revoke one or more privileges at the same time by listing them separated by commas.

The 'host' part usually is '%' for any host or 'localhost' if the user connects from the same machine.

Examples

This removes the SELECT permission on the customers table in mydb from user alice connecting from localhost.

MySQL

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

This removes both INSERT and UPDATE permissions on the orders table in mydb from user bob connecting from any host.

MySQL

REVOKE INSERT, UPDATE ON mydb.orders FROM 'bob'@'%';

This removes all privileges from user charlie connecting from IP 192.168.1.10 on all databases and tables.

MySQL

REVOKE ALL PRIVILEGES ON *.* FROM 'charlie'@'192.168.1.10';

Sample Program

This example creates a user named dave, gives him SELECT and INSERT permissions on the products table, then removes the INSERT permission. Finally, it shows the current permissions for dave.

MySQL

CREATE USER 'dave'@'localhost' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT ON testdb.products TO 'dave'@'localhost';
REVOKE INSERT ON testdb.products FROM 'dave'@'localhost';
SHOW GRANTS FOR 'dave'@'localhost';

Important Notes

Revoking a privilege that the user does not have will not cause an error.

Always check current privileges with SHOW GRANTS before and after revoking.

Revoking privileges does not delete the user account itself.

Summary

Revoking privileges removes specific permissions from a user.

Use the REVOKE statement with the privilege, database, table, and user details.

Check privileges before and after to confirm changes.