Removing privileges in MySQL

To remove privileges that have been granted, one uses the REVOKE statement. One uses the same information to revoke privileges as to grant them:

• The kinds of privileges to be revoked

• The database and table involved

• The user ID

• The hostname used in granting the privilege

As with dropping and creating a user, a pattern matching hostname of % does not include localhost. That host must be revoked explicitly.

Basic syntax

The REVOKE command has the following basic syntax:

REVOKE <privileges> ON <database>.<table> FROM '<userid>'@'<hostname>';

So to revoke all access for user tempo to the City table of the world database when logged in locally, we would use the following statement:

REVOKE ALL PRIVILEGES ON world.City FROM 'tempo'@'localhost';

If we want to revoke only INSERT privileges for remote access, we would adapt the preceding statement accordingly:

REVOKE INSERT ON world.City FROM 'tempo'@'%';

Again, it is important to remember that the following...