Home arrow MySQL arrow Page 2 - Revoke Statement in MySQL

Revoking Privileges Based on Maximums - 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
 

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!



 
 
>>> 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: