Learning MySQL

Revoking Privileges

You can selectively revoke privileges with the REVOKE statement, which essentially has the same syntax as GRANT. Consider a simple example, in which we remove the SELECT privilege from the user 'partmusic'@'localhost' for the time column in the track table in the music database. Here’s the statement, which we’ve run when logged in as 'root'@'localhost':

mysql> REVOKE SELECT (time) ON music.track FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.06 sec)

The format of REVOKE is straightforward when you understand GRANT. Following the keyword REVOKE is one or more comma-separated privileges, and these are optionally followed by column names, comma-separated in braces; this is the same as GRANT. The ON keyword has the same function as in GRANT and is followed by a database and table name, both of which can be wildcards. The FROM keyword is followed by the user and host from which the privileges are to be revoked, and the host can include wildcards.

Removing privileges using the basic syntax is laborious, since it requires that you remove the privileges in the same way they are granted. For example, to remove all privileges of 'partmusic'@'localhost', you would use the following steps:

mysql> REVOKE SELECT (track_id) ON music.track FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON music.artist FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON music.album FROM 'partmusic'@'localhost'; ...