Revoke Statement in MySQL

In this article I show you how to work with the REVOKE MySQL statement. It can be used for removing the privileges assigned via its counterpart GRANT, in either the global, database or table scope.

Being by far the most popular open source RDBMS available to date, MySQL offers a wealth of engaging facilities that make it stand out from its closest competitors. A stable, reliable performance, solid support for ACID transactions and multiple storage engines, foreign keys handling and cascade operations are only a small part of the tremendous menu of features that the database server offers for free. These have, not surprisingly, contributed to extending its existing popularity even further over time.

Among other features, MySQL includes a powerful privilege administration module. It lets you easily assign and remove privileges from users via a fine-grained control schema, based on the conditions and scope in which those users inter-operate with the server. The good news is that putting the module to work is much simpler than you might think. Its workhorses are the complementary GRANT and REVOKE statements.

If you’ve already read the article that I wrote on the GRANT command, you understand how to assign privileges to a user, either at the database, table and/or column level, or by taking into account a few additional conditions, such as the number of queries, updates and connections allowed for that user in particular.      

Of course, there’s no need to say that granting privileges would be pretty pointless if there wasn’t a way to reverse the process. As I explained before, the REVOKE statement shoulders this responsibility. Thus, if you’re interested in learning how to get it working for you, in the lines to come I’ll be analyzing it in depth through some approachable hands-on examples.

Removing Granted User Privileges with MySQL Revoke

Before I start demonstrating how to get the things rolling with the REVOKE statement, let’s take a quick look at its syntax, so you can familiarize yourself with it and see in general the set of arguments and options that it supports.

Check the following code fragment, which outlines the generic usage of REVOKE:

REVOKE
    privilege_type [(column_list)]
      [, privilege_type [(column_list)]] …
    ON [object_type] privileg_level
    FROM user [, user] …

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] …

It’s fairly simple to understand he syntax of the REVOKE command. As you can see above, all that you need to do to get it working is specify what type of privilege(s) will be removed from the given user, and in which scope (global, database, table and so forth).

Of course, the best way to grasp the statement’s underlying logic is with some concrete examples. In line with this concept, the first step is to create an imaginary user, so that we can start granting and removing privileges from them. That’s exactly what the bit of code below does: 

CREATE USER ‘susan’@'localhost’ IDENTIFIED BY ‘mysecret’;

Mission accomplished. Now there is a fictional user ready to be put to the test. It’s time to make things more interesting and begin granting and revoking privileges from her (don’t feel concerned about Susan;  she won’t be upset at all).

Check the following set of examples, which first show how to give Susan some global rights and then how to take them away from her via the REVOKE statement:
  
(revoking global privileges)

GRANT ALL ON *.* TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

 

GRANT SELECT, INSERT ON *.* TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

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

If you ever thought that revoking privileges from a specific user was a painful, annoying process, the above code samples should make you change your mind quickly. In this case, once the pertinent privileges have been assigned with the GRANT command (again, if you need more information on it, feel free to stop by the article I wrote here at Developer Shed), they’re removed by using its counterpart REVOKE.

Furthermore, the use of the *.* wild card means that the entire granting/revoking process takes place in the global scope, or in other words, that it’s valid in all of the current databases. Note that it is possible to use the REVOKE command with a single database, a table and even with specific columns. 

If you want to see how to accomplish all of these additional tasks with our loyal friend Susan, the following examples should be pretty illustrative. Check them out:
 
(revoking database privileges)

GRANT ALL ON mydatabase.* TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

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

 


(revoking table privileges)

GRANT ALL ON mydatabase.users TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

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’;

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

 


(revoking column privileges)

GRANT SELECT (name) ON mydatabase.users TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

REVOKE SELECT (name) ON mydatabase.users FROM ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;


GRANT SELECT (name, email), INSERT (name, email) ON mydatabase.users TO ‘susan’@'locahost’ IDENTIFIED BY PASSWORD ‘*e9fe51f94eadabf54dbf2fbbd57188b9abee436e’;

REVOKE SELECT (name, email), INSERT (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’;

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

As shown above, revoking privileges in the database, table and column scopes doesn’t differ too much from doing the same in the global scope. Quite possibly, the only detail worth noting here is the use of Susan’s sha1 hashed password, which might look somewhat weird at first glance.

We did it this way because MySQL doesn’t like you to include passwords as plain text. So, make sure to hash your passwords if you want to try out the GRANT and REVOKE commands using your own MySQL client application.

{mospagebreak title=Revoking Privileges Based on Maximums} 

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!

Google+ Comments

Google+ Comments