MySQL GRANT

Summary: in this tutorial, you will learn how to use the MySQL GRANT statement to assign privileges to user accounts.

Introduction to the MySQL GRANT statement

The CREATE USER statement creates a user account with no privileges. It means that the user account can log in to the MySQL Server, but cannot do anything such as selecting a database and querying data from tables.

To enable the user account to work with database objects, you need to grant it privileges. You use the GRANT statement to assign one or more privileges to the user account.

Here’s the basic syntax of the GRANT statement:

GRANT privilege [,privilege],.. 
ON privilege_level 
TO account_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, specify one or more privileges after the GRANT keyword. If you grant multiple privileges, you need to separate privileges by commas.
  • Second, specify the privilege_level, which determines the extent to which the privileges apply. More information on privilege levels will be provided shortly.
  • Third, specify the account name of the user you want to grant privileges after the TO keyword.

Notice that to use the GRANT statement, you must have the GRANT OPTION privilege and the privileges that you are granting. If the system variable read_only is enabled, you need to have the SUPER privilege to execute the GRANT statement.

MySQL privilege levels

MySQL supports the following privilege levels:

MySQL Grant - Privilege Levels

Global Privileges

Global privileges apply to all databases in a MySQL Server. To assign all global privileges, you use the *.* syntax, for example:

GRANT SELECT 
ON *.* 
TO bob@localhost;Code language: SQL (Structured Query Language) (sql)

The account user bob@localhost can manage all databases of the current MySQL Server.

Database privileges

Database privileges apply to all objects in a particular database. To assign database-level privileges, you use the ON database_name.* syntax, for example:

GRANT INSERT 
ON classicmodels.* 
TO bob@localhost;Code language: SQL (Structured Query Language) (sql)

In this example, bob@localhost can manage all objects in the classicmodels database.

Table privileges

Table privileges apply to all columns in a table. To assign table-level privileges, you use the ON database_name.table_name syntax. For example:

GRANT DELETE 
ON classicmodels.employees 
TO bob@localhsot;Code language: SQL (Structured Query Language) (sql)

In this example, bob@localhost can manage rows from the employees table in the classicmodels database.

If you skip the database name, MySQL uses the default database or issues an error if there is no default database.

Column privileges

Column privileges apply to individual columns within a table. You must specify the column or columns for each privilege. For example:

GRANT 
   SELECT (employeeNumner,lastName, firstName,email), 
   UPDATE(lastName) 
ON employees 
TO bob@localhost;Code language: SQL (Structured Query Language) (sql)

In this example, bob@localhost can select data from four columns:

  • employeeNumber
  • lastName
  • firstName
  • email

And updates only the lastName column in the employees table.

Stored routine privileges

Stored routine privileges apply to stored procedures and stored functions. For example:

GRANT EXECUTE 
ON PROCEDURE CheckCredit 
TO bob@localhost;Code language: SQL (Structured Query Language) (sql)

In this example, bob@localhost can execute the stored procedure CheckCredit in the current database.

Proxy user privileges

Proxy user privileges allow one user to be a proxy for another. The proxy user gets all the privileges of the proxied user. For example:

GRANT PROXY 
ON root 
TO alice@localhost;Code language: SQL (Structured Query Language) (sql)

In this example, alice@localhost assumes all privileges of the user root.

MySQL GRANT statement examples

Typically, you use the CREATE USER statement to create a new user account first and then use the GRANT statement to grant privileges to the user.

First, create a new user named super@localhost by using the following CREATE USER statement:

CREATE USER super@localhost 
IDENTIFIED BY 'Secure1Pass!';Code language: SQL (Structured Query Language) (sql)

Second, show the privileges assigned to super@localhost user by using the SHOW GRANTS statement:

SHOW GRANTS FOR super@localhost;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------------+
| Grants for super@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `super`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The USAGE means that the super@localhost can log in to the database but has no privilege.

Third, grant all privileges in all databases in the current database server to super@localhost:

GRANT ALL 
ON classicmodels.* 
TO super@localhost;Code language: SQL (Structured Query Language) (sql)

Fourth, use the SHOW GRANTS statement again:

SHOW GRANTS FOR super@localhost;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------------------------------------+
| Grants for super@localhost                                       |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `super`@`localhost`                        |
| GRANT ALL PRIVILEGES ON `classicmodels`.* TO `super`@`localhost` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Permissible privileges for the GRANT statement

The following table illustrates all permissible privileges that you can use for the GRANT and REVOKE statement:

PrivilegeMeaningLevel
GlobalDatabaseTableColumnStored RoutineProxy
ALL [PRIVILEGES]Allow the user to alter and drop stored procedures or stored functions.
ALTERAllow the user to use the ALTER TABLE statementXXX
ALTER ROUTINEAllow the user to create databases and tablesXXX
CREATEAllow the user to create stored procedures and stored functionsXXX
CREATE ROUTINEAllow the user to create a temporary table by using CREATE TEMPORARY TABLE statementXX
CREATE TABLESPACEAllow the user to create, alter, or drop tablespaces and log file groupsX
CREATE TEMPORARY TABLESAllow the user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.XX
CREATE USERAllow the user to create or modify the view.X
CREATE VIEWAllow the user to use DELETE statementXXX
DELETEAllow the user to execute stored routinesXXX
DROPAllow the user to drop database, table, and viewXXX
EVENTAllow the user to use events for the Event Scheduler.XX
EXECUTEAllow the user to drop the database, table, and viewXXX
FILEAllow the user to have privileges to grant or revoke privileges from other accounts.X
GRANT OPTIONAllow the user to create or drop indexes.XXXXX
INDEXAllow the user to create or drop indexes.XXX
INSERTAllow the user to use the INSERT statementXXXX
LOCK TABLESAllow the user to query to see where the master or slave servers areXX
PROCESSAllow the user to see all processes with SHOW PROCESSLIST statement.X
PROXYEnable user proxying.
REFERENCESAllow user to create a foreign keyXXXX
RELOADAllow the user to use FLUSH statementX
REPLICATION CLIENTAllow the user to query to see where master or slave servers areX
REPLICATION SLAVEAllow the user to use replicate slaves to read binary log events from the master.X
SELECTAllow the user to use SELECT statementXXXX
SHOW DATABASESAllow user to show all databasesX
SHOW VIEWAllow the user to use SHOW CREATE VIEW statementXXX
SHUTDOWNAllow user to use mysqladmin shutdown commandX
SUPERAllow the user to use other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin commandX
TRIGGERAllow the user to use TRIGGER operations.XXX
UPDATEAllow the user to use the UPDATE statementXXXX
USAGEEquivalent to “no privileges”

Summary

  • Use the MySQL GRANT statement to grant privileges to a user.
Was this tutorial helpful?