The Grant Statement in 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.

Let’s be honest: unless you’re a multifaceted, good-at-everything web developer, with skills that go beyond the boundaries of (X)HTML, CSS, JavaScript and the server-side language of your choice, most of the time you’ll be reluctant to deal with the oddities of MySQL. With lots of frameworks and libraries that will do the leg work for you and handle the interaction with the database server, you’d rarely need to spend your valuable time diving into its lower-level features. After all, that’s what systems administrators are for, right?

While the actual tasks you’ll be performing with MySQL will most likely be limited to picking the right storage engine for your next killer web application, and designing your database schema(s), you might find times when you’ll have to act like an administrator. For example, you may have to decide the set of privileges that will be assigned to each user accessing the server. In such cases, you should have at least a basic knowledge of how to tackle the assignment process without breaking into a cold sweat.

The good news is that MySQL includes a full-featured privilege mechanism, which makes fairly easy to control which users will have access to what things on the server. Moreover, in most cases the entire process can be conducted via a batch of graphical client programs, such as the MySQL GUI Tools Bundle, or the newer, revamped MySQL Workbench. Nevertheless, the workhorse that carries out the real assignment tasks under the surface is the GRANT statement. Does this term ring any bells for you? I bet it does.

Since the GRANT command is in charge when assigning user privileges in MySQL, in the lines to come I’ll be taking a closer look at it. This way you can familiarize yourself with its syntax, and painlessly learn how to get the most out of it. So let’s get started. 
   
Using the GRANT Statement: Basics

In reality, taking the first steps with the GRANT statement is a straightforward process. But before I show you some examples of how to use the command, it’d be helpful to see its generic syntax. Please take a look at the code snippet below:

GRANT
    privilege_type [(column_list)]
      [, privilege_type [(column_list)]] …
    ON [object_type] privilege_level
    TO user_specification [, user_specification] …
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
    [WITH with_option ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] …
    [WITH GRANT OPTION]

privilege_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
  
object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] ‘password’
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

ssl_option:
    SSL
  | X509
  | CIPHER ‘cipher’
  | ISSUER ‘issuer’
  | SUBJECT ‘subject’

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

Although the syntax of the GRANT statement is rather lengthy, don’t feel intimidated; grasping its driving logic is much simpler than you might think. In short, the use of the statement is reduced to specifying the type of privilege(s) that will be granted to a given user, along with the scope and the conditions in which those privileges will be valid.

As usual, the most effective way to understand the real functionality of GRANT is with a few hands-on examples. That said, the first thing we must do is create a fictional MySQL user and start playing around with their privileges. Since in this case I’ll be accessing the server as “root,” creating the user is as simple as opening up the MySQL monitor (or any of the client programs mentioned at the start) and issuing the following CREATE USER clause:       

CREATE USER ‘jenny’@'localhost’ IDENTIFIED BY ‘mypassword’;

Done. At this moment, our good friend Jenny has become a happy new MySQL user, eager to start doing all sorts of clever things with the server. But before she has the chance to massage databases, tables, and columns, she must first have the appropriate privileges. This is exactly where the GRANT command comes into play. The example below shows how to assign to her three different types of privileges, which are valid across all of the existing databases. Check it out:  

GRANT ALL ON *.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

GRANT SELECT, INSERT ON *.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

GRANT USAGE ON *.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

As you can see, putting the GRANT statement into action is a no-brainer process, indeed. While the previous examples are somewhat simplistic, they do show how to grant Jenny all of the privileges, then how to give her only SELECT and INSERT rights, and finally how to take all of these privileges away from her (yes, the USAGE clause means no privileges at all).

Also, there are a couple of details worth nothing here. First, notice the use of the *.* wild card; it means that all of the assignments are performed in all of the databases. And second, note the utilization of Jenny’s sha1 hashed password, as MySQL will complain loudly and clearly if you treat passwords as plain text.

So far, so good. At this point, I’m sure you have a clear idea of how to use the functionality of GRANT in order to give/remove privileges in the global server scope. But, if you’re anything like me, you’ll be wondering if the same can be accomplished at database, table and even column level, right? In fact, this can be achieved in a snap, as you can see from the following snippet:

(granting database privileges)

GRANT ALL ON mydatabase.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

GRANT SELECT, INSERT ON mydatabase.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

GRANT USAGE ON mydatabase.* TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

 

(granting table privileges)

GRANT ALL ON mydatabase.users TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

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

GRANT USAGE ON mydatabase.users TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

 

(granting column privileges)

GRANT SELECT (name) ON mydatabase.users TO ‘jenny’@'locahost’ IDENTIFIED BY PASSWORD ‘*91dfd9ddb4198affc5c194cd8ce6d338fde470e2′;

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

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

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

{mospagebreak title=The Grant Option Clause}

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!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan