GRANT Syntax

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

object_type: {

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

    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'

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

resource_option: {

The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources.

To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. In MySQL 5.1 and later, when the read_only system variable is enabled, GRANT additionally requires the SUPER privilege.

The REVOKE statement is related to GRANT and enables administrators to remove account privileges. See Section, “REVOKE Syntax”.

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

Examples shown here include no IDENTIFIED clause. It is assumed that you establish passwords with CREATE USER at account-creation time to avoid creating insecure accounts.

If an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode.

From the mysql program, GRANT responds with Query OK, 0 rows affected when executed successfully. To determine what privileges result from the operation, use SHOW GRANTS. See Section, “SHOW GRANTS Syntax”.

There are several aspects to the GRANT statement, described under the following topics in this section:

GRANT supports host names up to 60 characters long. Database, table, column, and routine names can be up to 64 characters. User names can be up to 16 characters.


The permissible length for user names cannot be changed by altering the mysql.user table. Attempting to do so results in unpredictable behavior which may even make it impossible for users to log in to the MySQL server. You should never alter the structure of tables in the mysql database in any manner whatsoever except by means of the procedure described in Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.


GRANT may be recorded in server logs or on the client side in a history file such as ~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. For information about password logging in the server logs, see Section, “Passwords and Logging”. For similar information about client-side logging, see Section, “mysql Logging”.

Privileges Supported by MySQL

The following table summarizes the permissible priv_type privilege types that can be specified for the GRANT and REVOKE statements, and the levels at which each privilege can be granted. For additional information about these privileges, see Section 6.2.1, “Privileges Provided by MySQL”.

Table 13.2 Permissible Privileges for GRANT and REVOKE

PrivilegeMeaning and Grantable Levels
ALL [PRIVILEGES]Grant all privileges at specified access level except GRANT OPTION
ALTEREnable use of ALTER TABLE. Levels: Global, database, table.
ALTER ROUTINEEnable stored routines to be altered or dropped. Levels: Global, database, procedure.
CREATEEnable database and table creation. Levels: Global, database, table.
CREATE ROUTINEEnable stored routine creation. Levels: Global, database.
CREATE VIEWEnable views to be created or altered. Levels: Global, database, table.
DELETEEnable use of DELETE. Level: Global, database, table.
DROPEnable databases, tables, and views to be dropped. Levels: Global, database, table.
EVENTEnable use of events for the Event Scheduler. Levels: Global, database.
EXECUTEEnable the user to execute stored routines. Levels: Global, database, table.
FILEEnable the user to cause the server to read or write files. Level: Global.
GRANT OPTIONEnable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure.
INDEXEnable indexes to be created or dropped. Levels: Global, database, table.
INSERTEnable use of INSERT. Levels: Global, database, table, column.
LOCK TABLESEnable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESSEnable the user to see all processes with SHOW PROCESSLIST. Level: Global.
REFERENCESNot implemented
RELOADEnable use of FLUSH operations. Level: Global.
REPLICATION CLIENTEnable the user to ask where master or slave servers are. Level: Global.
REPLICATION SLAVEEnable replication slaves to read binary log events from the master. Level: Global.
SELECTEnable use of SELECT. Levels: Global, database, table, column.
SHOW DATABASESEnable SHOW DATABASES to show all databases. Level: Global.
SHOW VIEWEnable use of SHOW CREATE VIEW. Levels: Global, database, table.
SHUTDOWNEnable use of mysqladmin shutdown. Level: Global.
SUPEREnable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
TRIGGEREnable trigger operations. Levels: Global, database, table.
UPDATEEnable use of UPDATE. Levels: Global, database, table, column.
USAGESynonym for no privileges

The EVENT and TRIGGER privileges were added in MySQL 5.1.6. A trigger is associated with a table, so to create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger. (Before MySQL 5.1.6, the SUPER privilege was required to create or drop triggers.)

In GRANT statements, the ALL [PRIVILEGES] privilege is named by itself and cannot be specified along with other privileges. It stands for all privileges available for the level at which privileges are to be granted except for the GRANT OPTION privilege.

USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.

MySQL account information is stored in the tables of the mysql database. For additional details, consult Section 6.2, “The MySQL Access Privilege System”, which discusses the mysql database and the access control system extensively.

If the grant tables hold privilege rows that contain mixed-case database or table names and the lower_case_table_names system variable is set to a nonzero value, REVOKE cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such rows when lower_case_table_names is set, but such rows might have been created prior to setting that variable.)

Privileges can be granted at several levels, depending on the syntax used for the ON clause. For REVOKE, the same ON syntax specifies which privileges to remove.

For the global, database, table, and routine levels, GRANT ALL assigns only the privileges that exist at the level you are granting. For example, GRANT ALL ON db_name.* is a database-level statement, so it does not grant any global-only privileges such as FILE. Granting ALL does not assign the GRANT OPTION privilege.

The object_type clause, if present, should be specified as TABLE, FUNCTION, or PROCEDURE when the following object is a table, a stored function, or a stored procedure.

The privileges for a database, table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels. For example, if a user has a global SELECT privilege, the privilege cannot be denied by an absence of the privilege at the database, table, or column level. Details of the privilege-checking procedure are presented in Section 6.2.5, “Access Control, Stage 2: Request Verification”.

If you are using table, column, or routine privileges for even one user, the server examines table, column, and routine privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time.


MySQL does not automatically revoke any privileges when you drop a database or table. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

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

Before MySQL 5.1.12, privileges also are assigned at the global level if you use ON * syntax and you have not selected a default database. As of 5.1.12, ON * requires a default database and produces an error if there is none.

The CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative and can only be granted globally.

Other privileges can be granted globally or at more specific levels.

MySQL stores global privileges in the mysql.user table.

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

If you use ON * syntax (rather than ON *.*) and you have selected a default database, privileges are assigned at the database level for the default database. An error occurs if there is no default database.

The CREATE, DROP, EVENT, GRANT OPTION, and LOCK TABLES privileges can be specified at the database level. Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.

MySQL stores database privileges in the mysql.db table.

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

If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database. An error occurs if there is no default database.

The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

MySQL stores table privileges in the mysql.tables_priv table.

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

The permissible priv_type values for a column (that is, when you use a column_list clause) are INSERT, SELECT, and UPDATE.

MySQL stores column privileges in the mysql.columns_priv table.

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

The permissible priv_type values at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.

MySQL stores routine-level privileges in the mysql.procs_priv table.

Account Names and Passwords

The user_specification clause names a user and optionally provides authentication information such as a password.

The user value indicates the MySQL account to which the GRANT statement applies. To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form user_name@host_name. If a user_name or host_name value is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify a user_name string containing special characters (such as -), or a host_name string containing special characters or wildcard characters (such as %); for example, 'test-user'@'%.com'. Quote the user name and host name separately.

You can specify wildcards in the host name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.

The simple form user_name is a synonym for user_name@'%'.

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

In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.

For additional information about user name and host name values in account names, see Section 6.2.3, “Specifying Account Names”.

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. For string-quoting and identifier-quoting guidelines, see Section 9.1.1, “String Literals”, and Section 9.2, “Schema Object Names”.

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a _ character as part of a database name, you should specify it as \_ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....


If you permit anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user_name@localhost. Otherwise, the anonymous user account for localhost in the mysql.user table (created during MySQL installation) is used when named users try to log in to the MySQL server from the local machine. For details, see Section 6.2.4, “Access Control, Stage 1: Connection Verification”.

To determine whether the preceding warning applies to you, execute the following query, which lists any anonymous users:

SELECT Host, User FROM mysql.user WHERE User='';

To avoid the problem just described, delete the local anonymous user account using this statement:

DROP USER ''@'localhost';

To indicate how a user should authenticate when connecting to the server, the user_specification value may include an IDENTIFIED clause to specify a password. Syntax of the user specification is the same as for the CREATE USER statement. For details, see Section, “CREATE USER Syntax”.

When IDENTIFIED BY is present and you have the global grant privilege (GRANT OPTION), the password becomes the new password for the account, even if the account exists and already has a password. Without IDENTIFIED BY, the account password remains unchanged.

If the NO_AUTO_CREATE_USER SQL mode is not enabled and the account named in a GRANT statement does not exist in the mysql.user table, GRANT creates it. If you specify no IDENTIFIED BY clause or provide an empty password, the user has no password. This is very insecure.

If NO_AUTO_CREATE_USER is enabled and the account does not exist, GRANT fails and does not create the account unless the IDENTIFIED BY clause is given to provide a nonempty password.

Other Account Characteristics

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. For background information on the use of SSL with MySQL, see Section 6.4, “Using Secure Connections”.

The optional REQUIRE clause specifies SSL-related options for a MySQL account, using one or more tls_option values.

Implicit Account Creation

GRANT permits these tls_option values:

  • NONE

    Indicates that the account has no SSL or X509 requirements. Unencrypted connections are permitted if the user name and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files. That is, the client need not specify any SSL command options, in which case the connection will be unencrypted. To use an encrypted connection, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.

    NONE is the default if no SSL-related REQUIRE options are specified.

  • SSL

    Tells the server to permit only encrypted connections for the account.

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'

    To connect, the client must specify the --ssl-ca option to authenticate the server certificate, and may additionally specify the --ssl-key and --ssl-cert options. If neither the --ssl-ca option nor --ssl-capath option is specified, the client does not authenticate the server certificate.

  • X509

    Requires that the client must have a valid certificate but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

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

    The client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.) This is true for ISSUER and SUBJECT as well because those REQUIRE options imply the requirements of X509.

  • ISSUER 'issuer'

    Places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because ISSUER implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/

    If MySQL is linked against a version of OpenSSL older than 0.9.6h, use Email rather than emailAddress in the 'issuer' value.

  • SUBJECT 'subject'

    Places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because SUBJECT implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL demo client certificate/

    MySQL does a simple string comparison of the 'subject' value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.


    Regarding emailAddress, see the note in the description of REQUIRE ISSUER.

  • CIPHER 'cipher'

    Requests a specific cipher method for encrypting connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used.

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'

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/
  AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/

The order of the options does not matter, but no option can be specified twice. The AND keyword is optional between REQUIRE options.

The optional WITH clause is used for these purposes:

  • To enable a user to grant privileges to other users

  • To specify resource limits for a user

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level.

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;

Be careful to whom you give the GRANT OPTION privilege because two users with different privileges may be able to combine privileges!

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

Be aware that when you grant a user the GRANT OPTION privilege at a particular privilege level, any privileges the user possesses (or may be given in the future) at that level can also be granted by that user to other users. Suppose that you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, that user can give to other users not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can grant INSERT, SELECT, and UPDATE.

For a nonadministrative user, you should not grant the ALTER privilege globally or for the mysql database. If you do that, the user can try to subvert the privilege system by renaming tables!

For additional information about security risks associated with particular privileges, see Section 6.2.1, “Privileges Provided by MySQL”.

It is possible to place limits on use of server resources by an account, as discussed in Section 6.3.4, “Setting Account Resource Limits”. To do so, use a WITH clause that specifies one or more resource_option values. Limits not specified retain their current values.

GRANT permits these resource_option values:


    These options restrict the number of queries, updates, and connections to the server permitted to this account during any given one-hour period. (Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.) If count is 0 (the default), this means that there is no limitation for the account.


    Restricts the maximum number of simultaneous connections to the server by the account. A nonzero count specifies the limit for the account explicitly. If count is 0 (the default), the server determines the number of simultaneous connections for the account from the global value of the max_user_connections system variable. If max_user_connections is also zero, there is no limit for the account.

If a given resource limit is specified multiple times, the last instance takes precedence.

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:

MySQL and Standard SQL Versions of GRANT

The biggest differences between the MySQL and standard SQL versions of GRANT are:

  • MySQL associates privileges with the combination of a host name and user name and not with only a user name.

  • Standard SQL does not have global or database-level privileges, nor does it support all the privilege types that MySQL supports.

  • MySQL does not support the standard SQL UNDER privilege.

  • Standard SQL privileges are structured in a hierarchical manner. If you remove a user, all privileges the user has been granted are revoked. This is also true in MySQL if you use DROP USER. See Section, “DROP USER Syntax”.

  • In standard SQL, when you drop a table, all privileges for the table are revoked. In standard SQL, when you revoke a privilege, all privileges that were granted based on that privilege are also revoked. In MySQL, privileges can be dropped only with explicit DROP USER or REVOKE statements or by manipulating the MySQL grant tables directly.

  • In MySQL, it is possible to have the INSERT privilege for only some of the columns in a table. In this case, you can still execute INSERT statements on the table, provided that you insert values only for those columns for which you have the INSERT privilege. The omitted columns are set to their implicit default values if strict SQL mode is not enabled. In strict mode, the statement is rejected if any of the omitted columns have no default value. (Standard SQL requires you to have the INSERT privilege on all columns.) For information about strict SQL mode and implicit default values, see Section 5.1.7, “Server SQL Modes”, and Section 11.6, “Data Type Default Values”.