Home arrow MySQL arrow Page 2 - The Grant Statement in MySQL

The Grant Option Clause - MySQL

In this article, I'll introduce you to what the MySQL GRANT statement is, and how it can be used to assign all sorts of privileges to a given user, based on different scopes and conditions.

TABLE OF CONTENTS:
  1. The Grant Statement in MySQL
  2. The Grant Option Clause
By: Alejandro Gervasio
Rating: starstarstarstarstar / 0
December 23, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As the previous examples show, there’s no shortage of options for leveraging the functionality of the GRANT statement, either in the database, table or column scope. Moreover, while in all of the use cases covered so far, the assignment process was carried by the “root” user (yes, that’s me), it’s possible to achieve similar results with a user other than “root” via the optional GRANT OPTION clause.

The following batch of statements show how to achieve this with minor effort:

GRANT ALL ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT SELECT, INSERT ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT USAGE ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT ALL ON mydatabase.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT SELECT (name, email) ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT SELECT, INSERT ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

GRANT SELECT (name, email), INSERT(name, email), UPDATE(name, email) ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH GRANT OPTION;

There you have it. In this case, the privileges assigned to our fictional user are similar to the ones that you saw in previous examples. However, the WITH GRANT OPTION clause specifies that they’ve been given by a user owning the same or higher privileges. This form of delegation makes it really easy to transfer rights from one user to another, even with a fine-grained level of control.  

All in all, at this point you should be aware of the handy abilities that the GRANT statement provides right out of the box. But wait, there's more! The command will permit you to assign privileges to a user not only according to a certain scope, be it a database, a table or one or more columns, but based on a set of predefined conditions.

If this explanation sounds somewhat vague and confusing to you, be sure to check the following code snippet. It grants some privileges to your loyal friend Jenny, taking into account the maximum number of queries and updates per hour she can issue to the server:

(granting privileges using the WITH MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR clauses)

GRANT ALL ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000;

GRANT ALL ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200;

GRANT SELECT, INSERT ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000;

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

GRANT ALL ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000;

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

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

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

GRANT USAGE ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000;

GRANT SELECT (name) ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000;

Pretty good, isn’t? As shown above, it’s possible to blend the core functionality of the GRANT statement with those provided by the WITH MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR clauses to restrict the use of certain privileges, based on the number of queries/updates allowed per hour. Moreover, it’s even possible to achieve a similar level of privilege control by specifying the maximum number of connections allowed, via the additional MAX_CONNECTIONS_PER_HOUR clause.  

The following example depicts how to achieve this:

(granting privileges using the MAX_CONNECTIONS_PER_HOUR clause)

GRANT ALL ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200 MAX_CONNECTIONS_PER_HOUR 100;

GRANT SELECT, INSERT ON *.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 100;

GRANT ALL ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 100;

GRANT SELECT, INSERT ON mydatabase.* TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 100;

GRANT SELECT (name) ON mydatabase.users TO 'jenny'@'locahost' IDENTIFIED BY PASSWORD '*91dfd9ddb4198affc5c194cd8ce6d338fde470e2' WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 100;

I don't want to sound like I’m overreacting here, but at this point it’s clear to see that the GRANT command packs a remarkable amount of functionality and flexibility into an intuitive and readable syntax. Of course, the command will enable you to do a lot more clever things with MySQL users. These include assigning privileges to them even if they’re accessing the server through a proxy. Additional options, though, will be left as homework for you, which hopefully will keep you entertained for long hours. 

Final Thoughts

In this tutorial, I provided you with a quick introduction to what the MySQL GRANT statement is, and how it can be used to assign all sorts of privileges to a given user, based on different scopes and conditions. While in most cases you’ll be using the command through a GUI application, it’s really helpful to learn how to manipulate it directly, thus making easier to get the most out of it.

You might be wondering if MySQL includes a counterpart of the GRANT statement, which permits you to remove privileges as easily as they were given in the first place. In fact, it does. This complementary contender is called, unsurprisingly, REVOKE. It will be covered in a separate article, so feel free to stop by the Developer Shed network later to learn how to put it to work for you.

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: