Home arrow MySQL arrow The Grant Statement in MySQL

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.

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

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



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