GRANT Statement

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY [PASSWORD] 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

GRANT General Overview

For example:

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

The host name part of the account name, if omitted, defaults to '%'.

Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

Object Quoting Guidelines

To specify quoted values:

  • Quote database, table, column, and routine names as identifiers.
  • Quote user names and host names as identifiers or as strings.
  • Quote passwords as strings.

Account Names and Passwords

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

GRANT ALL ON test.* TO ''@'localhost' ...;

Privileges Supported by MySQL

Permissible Privileges for GRANT and REVOKE

Privilege Meaning and Grantable Levels
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION and PROXY.
ALTER Enable use of ALTER TABLE. Levels: Global, database, table.
ALTER ROUTINE Enable stored routines to be altered or dropped. Levels: Global, database, routine.
CREATE Enable database and table creation. Levels: Global, database, table.
CREATE ROUTINE Enable stored routine creation. Levels: Global, database.
CREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE. Levels: Global, database.
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEW Enable views to be created or altered. Levels: Global, database, table.
DELETE Enable use of DELETE. Level: Global, database, table.
DROP Enable databases, tables, and views to be dropped. Levels: Global, database, table.
EVENT Enable use of events for the Event Scheduler. Levels: Global, database.
EXECUTE Enable the user to execute stored routines. Levels: Global, database, routine.
FILE Enable the user to cause the server to read or write files. Level: Global.
GRANT OPTION Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy.
INDEX Enable indexes to be created or dropped. Levels: Global, database, table.
INSERT Enable use of INSERT. Levels: Global, database, table, column.
LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESS Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.
PROXY Enable user proxying. Level: From user to user.
REFERENCES Enable foreign key creation. Levels: Global, database, table, column.
RELOAD Enable use of FLUSH operations. Level: Global.
REPLICATION CLIENT Enable the user to ask where master or slave servers are. Level: Global.
REPLICATION SLAVE Enable replication slaves to read binary log events from the master. Level: Global.
SELECT Enable use of SELECT. Levels: Global, database, table, column.
SHOW DATABASES Enable SHOW DATABASES to show all databases. Level: Global.
SHOW VIEW Enable use of SHOW CREATE VIEW. Levels: Global, database, table.
SHUTDOWN Enable use of mysqladmin shutdown. Level: Global.
SUPER Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
TRIGGER Enable trigger operations. Levels: Global, database, table.
UPDATE Enable use of UPDATE. Levels: Global, database, table, column.
USAGE Synonym for “no privileges”

The privileges that a user holds for a database, table, column, or routine are formed additively as the logical OR of the account privileges at each of the privilege levels, including the global level. It is not possible to deny a privilege granted at a higher level by absence of that privilege at a lower level. For example, this statement grants the SELECT and INSERT privileges globally:

GRANT SELECT, INSERT ON *.* TO u1;

Global Privileges

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax:

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Database Privileges

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.* syntax:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Table Privileges

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

Column Privileges

Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

Stored Routine Privileges

The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

Proxy User Privileges

The PROXY privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user; that is, it assumes the privileges of the proxied user.

GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

Implicit Account Creation

If an account named in a GRANT statement does not exist, the action taken depends on the NO_AUTO_CREATE_USER SQL mode:

  • If NO_AUTO_CREATE_USER is not enabled, GRANT creates the account. This is very insecure unless you specify a nonempty password using IDENTIFIED BY.
  • If NO_AUTO_CREATE_USER is enabled, GRANT fails and does not create the account, unless you specify a nonempty password using IDENTIFIED BY or name an authentication plugin using IDENTIFIED WITH.

As of MySQL 5.6.12, if the account already exists, IDENTIFIED WITH is prohibited because it is intended only for use when creating new accounts.

Other Account Characteristics

GRANT permits these tls_option values:

  • NONE

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE NONE;
    
  • SSL

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE SSL;
    
  • X509

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE X509;
    
  • ISSUER 'issuer'

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL/CN=CA/emailAddress=ca@example.com';
    
  • SUBJECT 'subject'

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL demo client certificate/
        CN=client/emailAddress=client@example.com';
    
  • CIPHER 'cipher'

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
    

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL demo client certificate/
    CN=client/emailAddress=client@example.com'
  AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL/CN=CA/emailAddress=ca@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

To grant the GRANT OPTION privilege to an account without otherwise changing its privileges, do this:

GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

GRANT permits these resource_option values:

  • MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count
  • MAX_USER_CONNECTIONS count

To specify resource limits for an existing user without affecting existing privileges, use GRANT USAGE at the global level (ON *.*) and name the limits to be changed. For example:

GRANT USAGE ON *.* TO ...
WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;