Home arrow MySQL arrow Revoke Statement in MySQL

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.

TABLE OF CONTENTS:
  1. Revoke Statement in MySQL
  2. Revoking Privileges Based on Maximums
By: Alejandro Gervasio
Rating: starstarstarstarstar / 0
December 30, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: