He'Story

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option [with_option] ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

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

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS. See Section 12.5.1.5, “REVOKE Syntax”, and Section 12.5.5.17, “SHOW GRANTS Syntax”.

MySQL Enterprise.  For automated notification of users with inappropriate privileges, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

The following table summarizes the allowable priv_type privilege types that can be specified for the GRANT and REVOKE statements. For additional information about these privileges, see Section 5.4.1, “Privileges Provided by MySQL”.

The EXECUTE privilege is not operational until MySQL 5.0.3. CREATE VIEW and SHOW VIEW were added in MySQL 5.0.1. CREATE USER, CREATE ROUTINE, and ALTER ROUTINE were added in MySQL 5.0.3.

USAGE can be specified when you want to create a user that has no privileges, or to modify 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. This database and the access control system are discussed extensively in Chapter 5, MySQL Server Administration, which you should consult for additional details.

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 non-zero 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 the 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 take away. The examples shown here include no IDENTIFIED BY 'password' clause for brevity, but you should include one if the account does not already exist to avoid creating an account with no password.

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

Privileges also are assigned at the global level if you use ON * syntax and you have not selected a default database.

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.

Global privileges are stored 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';

Privileges also are assigned at the database level if you use ON * syntax and you have selected a default database.

The CREATE, DROP, and GRANT OPTION 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.

Database privileges are stored in the mysql.db and mysql.host tables. GRANT and REVOKE affect the db table, but not the host table, which is rarely used.

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 allowable priv_type values for a table are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, and UPDATE.

Table privileges are stored 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 allowable priv_type values for a column (that is, when you use a column_list clause) are INSERT, SELECT, and UPDATE.

Column privileges are stored in the mysql.columns_priv table.

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 allowable 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.

Routine-level privileges are stored in the mysql.procs_priv table.

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.

The object_type clause was added in MySQL 5.0.6. If present, it 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 5.4.4, “Access Control, Stage 2: Request Verification”.

MySQL enables you to grant privileges even on database objects that do not exist. In such cases, 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 database objects that are to be created at a later time.

The user value indicates which MySQL account to grant the privileges to. 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, quotes 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'@'test-hostname'. Quote the user name and host name separately.

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

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

MySQL does not support wildcards in user names. Anonymous users are created by specifying a user with an empty 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 allowed access, with the privileges associated with the anonymous-user account.

To specify quoted values, quote database, table, column, and routine names as identifiers, using backticks (“`”). Quote host names and user names as identifiers or as strings, using either backticks or single quotes (“'”). Quote passwords as strings, using single quotes.

The “_” and “%” wildcards are allowed 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 ....

You can determine whether the preceding warning applies to you by executing 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';

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.

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 non-empty password.

When the IDENTIFIED BY clause is present and you have global grant privileges, the password becomes the new password for the account, even if the account exists and already has a password.

MySQL Enterprise.  The MySQL Enterprise Monitor specifically guards against user accounts with no passwords. To find out more, see http://www.mysql.com/products/enterprise/advisors.html.

REVOKE does not remove mysql.user table entries; you must do that using DROP USER or DELETE.

Passwords can also be set with the SET PASSWORD statement. See Section 12.5.1.6, “SET PASSWORD Syntax”.

In the IDENTIFIED BY clause, the password should be given as the literal password value. It is unnecessary to use the PASSWORD() function as it is for the SET PASSWORD statement. For example:

GRANT ... IDENTIFIED BY 'mypass';

If you do not want to send the password in clear text and you know the hashed value that PASSWORD() would return for the password, you can specify the hashed value preceded by the keyword PASSWORD:

GRANT ...
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

The WITH clause is used for several 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. You should 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 non-administrative 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 5.4.1, “Privileges Provided by MySQL”.

The MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR count options limit the number of queries, updates, and logins a user can perform 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 that user.

The MAX_USER_CONNECTIONS count option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the account can make. If count is 0 (the default), the max_user_connections system variable determines the number of simultaneous connections for the account.

To specify any of these resource-limit options for an existing user without affecting existing privileges, use GRANT USAGE ON *.* ... WITH MAX_....

For more information on restricting resources, see Section 5.5.4, “Limiting Account Resources”.

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and password. To specify SSL-related options for a MySQL account, use the REQUIRE clause of the GRANT statement. (For background information on the use of SSL with MySQL, see Section 5.5.7, “Using SSL for Secure Connections”.)

There are a number of different possibilities for limiting connection types for a given account:

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

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

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

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.

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