Several objects within GRANT statements are subject to quoting, although quoting is optional in many cases: Account, role, database, table, column, and routine names. For example, if a user_name or host_name value in an account name 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.

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 database level. 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 ....

The following tables summarize the permissible static and dynamic 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 each privilege, see Section 6.2.1, “Privileges Provided by MySQL”. For information about the differences between static and dynamic privileges, see Section 6.2.2, “Static Versus Dynamic Privileges”.

A trigger is associated with a table. To create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger.

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

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. (However, use of GRANT to define nonprivilege characteristics is deprecated.

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 or PROXY 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.7, “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.

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

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

Dynamic privileges are all global and can only be granted globally.

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

The effect of GRANT OPTION granted at the global level differs for static and dynamic privileges:

GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the GRANT statement is not granted retroactively to any account.

MySQL stores global privileges in the mysql.user table.

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, LOCK TABLES, and REFERENCES 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 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, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

Table-level privileges apply to base tables and views. They do not apply to tables created with CREATE TEMPORARY TABLE, even if the table names match. For information about TEMPORARY table privileges, see Section 13.1.15.3, “CREATE TEMPORARY TABLE Syntax”.

MySQL stores table privileges in the mysql.tables_priv table.

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, REFERENCES, SELECT, and UPDATE.

MySQL stores column privileges in the mysql.columns_priv table.

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.

The PROXY privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user.

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

When PROXY is granted, it must be the only privilege named in the GRANT statement, the REQUIRE clause cannot be given, and the only permitted WITH option is WITH GRANT OPTION.

Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have the PROXY privilege for the proxied user. For details and examples, see Section 6.3.11, “Proxy Users”.

MySQL stores proxy privileges in the mysql.proxies_priv table.

GRANT syntax without an ON clause grants roles rather than individual privileges. A role is a named collection of privileges; see Section 6.3.4, “Using Roles”. For example:

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

Each role to be granted must exist, as well as each user account or role to which it is to be granted.

If the GRANT statement includes the WITH ADMIN OPTION clause, each named user becomes able to grant the named roles to other users or roles, or revoke them from other users or roles. This includes the ability to use WITH ADMIN OPTION itself.

It is possible to create circular references with GRANT. For example:

CREATE USER 'u1', 'u2';
CREATE ROLE 'r1', 'r2';

GRANT 'u1' TO 'u1';   -- simple loop: u1 -> u1
GRANT 'r1' TO 'r1';   -- simple loop: r1 -> r1

GRANT 'r2' TO 'u2';
GRANT 'u2' TO 'r2';   -- mixed user/role loop: u2 -> r2 -> u2

Circular grant references are permitted but add no new privileges or roles to the grantee because a user or role already has its privileges and roles.

A user value in a GRANT statement indicates a MySQL account to which the statement applies. To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form 'user_name'@'host_name'.

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.4, “Specifying Account Names”.

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

DROP USER ''@'localhost';

For GRANT syntaxes that permit an auth_option value to follow a user value, auth_option begins with IDENTIFIED and indicates how the account authenticates by specifying an account authentication plugin, credentials (password), or both. Syntax of the auth_option clause is the same as for the CREATE USER statement. For details, see Section 13.7.1.3, “CREATE USER Syntax”.

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

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

If the account already exists, IDENTIFIED WITH is prohibited because it is intended only for use when creating new accounts.

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. The syntax is the same as for the CREATE USER statement. For details, see Section 13.7.1.3, “CREATE USER Syntax”.

The optional WITH clause is used for these purposes:

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.6, “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. The syntax is the same as for the CREATE USER statement. For details, see Section 13.7.1.3, “CREATE USER Syntax”.

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