Understanding the REVOKE statement in MySQL

The REVOKE command is used to rescind privileges previously granted to a user. Its syntax is:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
       ON {tbl_name | * | *.* | db_name.*}
       FROM user_name [, user_name ...]

As is the case with the GRANT command, perhaps the best way to really understand how it operates is to experiment with several examples. Assume that the administrator wants to repeal the DELETE privilege from the user widgetAdmin. This is accomplished using the following command:

mysql>REVOKE DELETE ON widgets.*
    ->FROM widgetAdmin@localhost;

One point to keep in mind is that while REVOKE can remove all privileges (including connection privileges) from a user, it does not explicitly remove that user from the privilege tables. To illustrate this, consider the following command:

mysql>REVOKE ALL PRIVILEGES ON widgets.*
    ->FROM widgetAdmin@localhost;

While this would result in all privileges being revoked from the user widgetAdmin, it would not delete the relevant rows from the privilege tables! If completely removing the user from the database is the intention, the rows would have to be removed using the delete command, as follows:

mysql>DELETE FROM user WHERE user = 'widgetAdmin';
Query OK, 1 row affected (0.00 sec)
mysql>flush privileges;

This will effectively deny that user from connecting to the MySQL server. Note that rows from the user table will have to be explicitly removed using DELETE should the administrator wish to entirely remove the user from the privilege tables.