MySQL: How to Use the GRANT Statement

When working with MySQL, there will probably come times when you need to grant privileges to certain user accounts.  This task is executed using the appropriately named GRANT statement.  In addition to the statement’s ability to grant user privileges, the GRANT statement functions as a way to declare specific characteristics related to an account.  Among these are any limits when it comes to accessing certain server resources, as well as the use of secure connections, just to name a couple.

Before using the GRANT statement to assign privileges to user accounts, you should know that you will need the GRANT OPTION privilege to do so.  This privilege is just one of many permissible privileges that exist in MySQL, and it allows a user to grant or revoke privileges from other user accounts.  Another thing to keep in mind when using the GRANT statement is that you will need to have the privileges that you wish to GRANT to others.

Now that you have a little background on what the GRANT statement is, let’s take a look at its MySQL syntax that is used to grant privileges to user accounts:

GRANT privileges (column_list)
ON [object_type] privilege_level
TO account [IDENTIFIED BY 'password']
[REQUIRE encryption]
WITH with_options

Let’s breakdown that syntax and examine what its different components represent.

We begin with our GRANT statement, which is followed by privileges.  Privileges represent the abilities we want to grant that account.  Examples would be CREATE, DELETE, INSERT, etc.  There are many more privileges in MySQL, and we have them all listed near the end of this article.  You have the option to assign more than one privilege at a time, but just remember to separate them with a comma.

The (column_list) component is optional.  It refers to any specific columns that you want to apply the privilege to.  Any columns you list must be within the parentheses and separated by commas. 

The privilege_level component of our syntax defines the level of privilege you want to give that user account.  For example, they could have global privileges, or be restricted to certain databases, tables, and even columns.

After TO comes the account that you want to grant the privileges to. 

The [IDENTIFIED BY 'password'] component refers to the specific password that is assigned to that user account. 

[REQUIRE encryption] deals with how the user connects to the database server, and whether or not they must do it via a secure connection with SSL. 

Lastly, WITH with_options is there if you want the user account to be able to grant privileges to other user accounts.  For this to occur, you will need to follow up WITH with GRANT OPTION.  The GRANT OPTION privilege, as defined later in this article, allows users to grant or revoke privileges from other user accounts.  This WITH clause is also used to define how many maximum queries, updates, and connections an account may have per hour.

It is worth noting that if the account you name in the GRANT statement does not exist, GRANT will create that account and assign whatever privileges you have defined.  Otherwise, if the account named in the GRANT statement does already exist, the account’s privileges will be adjusted accordingly. 

If you wanted to take the conventional route of first creating an account and then defining privileges to it, you could do so as well.  Here is an example that uses the CREATE USER statement to create an account.  It then uses the GRANT statement to define the privileges:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'samplepass';
GRANT ALL ON db1.* TO 'john'@'localhost' WITH GRANT OPTION;

Here we created the john user account, whose password is samplepass.  It has been granted all privileges on db1, and has also been given the GRANT OPTION to grant and revoke privileges to other accounts.

So far, we have discussed what the GRANT statement is used for and have taken a closer look at its syntax and an example.  Since the GRANT statement is all about granting privileges to user accounts in MySQL, you are probably wondering what some of those privileges actually are.  Here is an official list of the privileges with their explanations, as listed in the MySQL manual:

ALL [PRIVILEGES] – grants all available privileges to a users at a designated access level with the exception of the GRANT OPTION privilege

ALTER – gives access to the ALTER TABLE statement

ALTER ROUTINE – allows the altering or dropping of stored routines

CREATE – lets a user create databases and tables

CREATE ROUTINE – lets a user create stored routines

CREATE TABLESPACE – allows a user to create, alter, or drop tablespaces and log file groups

CREATE TEMPORARY TABLES – gives a user the privilege to use the CREATE TEMPORARY TABLE statement

CREATE USER – grants access to the CREATE USER, DROP USER, RENAME USER, AND REVOKE ALL PRIVILEGES statements

CREATE VIEW – lets a user create or alter views

DELETE – allows for usage of the DELETE statement

DROP – lets a user drop databases, tables, and views

EVENT – grants access to events for the Event Scheduler

EXECUTE – allows for stored routine execution

FILE – lets a user cause the server to read or write files

GRANT OPTION – gives a user the power to grant or remove privileges from other accounts

INDEX – allows indexes to be created or dropped

INSERT – grants access to the INSERT statement

LOCK TABLES – gives a user access to the LOCK TABLES statement on tables where they have the SELECT privilege

PROCESS – allows a user to use the SHOW PROCESSLIST statement to see all processes

PROXY – grants access to user proxying

REFERENCES – described in the manual at this time as not implemented

RELOAD – allows for the use of FLUSH operations

REPLICATION CLIENT – gives the user the capability to ask where any master or slave servers reside

REPLICATION SLAVE – allows binary log events to be read by replication slaves from the master

SELECT – grants access to usage of the SELECT statement

SHOW DATABASES – lets a user employ the SHOW DATABASES statement to show all databases

SHOW VIEW – allows the SHOW CREATE VIEW statement to be used

SHUTDOWN – grants access to usage of the mysqladmin shutdown command

SUPER – allows for the usage of such administrative options as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, and SET GLOBAL.  Also grants access to the mysqladmin debug command

TRIGGER – allows trigger operations

UPDATE – grants access to the UPDATE statement

USAGE – listed as being synonymous with “no privileges”

Hopefully you now have a better understanding of what the MySQL GRANT statement can do.  We gave a brief overview of the statement, discussed its syntax, provided an example, and listed all of the available privileges that can be granted.

Be sure to come back for more MySQL tutorials here in the future.