Revoking Privileges Based on Maximums - MySQL

So far, so good. At this point, I'm sure that you're pretty familiar with the inner workings of REVOKE, especially when removing user privileges for a single database, a table and even a few columns. Don't relax yet, though, as it's possible to utilize the command when the privileges have been granted by using the WITH MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR and MAX_CONNECTIONS_PER_HOUR clauses (http://dev.mysql.com/doc/refman/5.0/en/grant.html).   

If you're wondering how to achieve this, the following set of examples should be quite helpful. Have a look at them:

(revoking privileges using the WITH MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR clauses)

GRANT ALL ON *.* TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE ALL ON *.* FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e'

GRANT SELECT, INSERT ON *.* TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE SELECT, INSERT ON *.* FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e'

GRANT ALL ON mydatabase.* TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE ALL ON mydatabase.* FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e';

GRANT ALL ON mydatabase.users TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE ALL ON mydatabase.users FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e';

GRANT SELECT, INSERT ON mydatabase.users TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE SELECT, INSERT ON mydatabase.users FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e';

GRANT SELECT (name, email), INSERT (name, email), UPDATE (name, email) ON mydatabase.users TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

REVOKE SELECT (name, email), INSERT (name, email), UPDATE (name, email) ON mydatabase.users FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e';

GRANT SELECT (name, email), INSERT (name, email), UPDATE (name, email) ON mydatabase.users TO 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e' WITH MAX_CONNECTIONS_PER_HOUR 100

REVOKE SELECT (name, email), INSERT (name, email), UPDATE (name, email) ON mydatabase.users FROM 'susan'@'locahost' IDENTIFIED BY PASSWORD '*e9fe51f94eadabf54dbf2fbbd57188b9abee436e';

That was really simple to grasp, wasn't it? Thanks to the fine-grained level of control that the REVOKE statement offers, it's dead simple to revoke user privileges that have been previously granted with the MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR and MAX_CONNECTIONS_PER_HOUR clauses.

For obvious reasons, I'm only scratching the surface of all the possible use cases where REVOKE can be employed successfully. If you're interested in getting the most out of the command, the MySQL manual is the best place to go.       

Closing Remarks

In this article, I developed a few understandable examples to demonstrate how to work with the REVOKE MySQL statement. As you saw, it can be used to remove the privileges assigned via its counterpart GRANT, whether in the global, database or table scope. In addition, you learned how to use the command when the privileges were given with the MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR clauses.

So, now that you know a bit more about the functionality of the GRANT/REVOKE tandem, just launch your preferred MySQL client program and start giving it a shot. You'll be surprised at how much you can accomplish with it by issuing only a few simple commands.

See you in the next MySQL tutorial!