GRANT General Overview

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

Normally, a database administrator first uses
CREATE USER to create an
account and define its nonprivilege characteristics such as
its password, whether it uses secure connections, and limits
on access to server resources, then uses
GRANT to define its privileges.
ALTER USER may be used to
change the nonprivilege characteristics of existing accounts.
For example:

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.

Note

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. It is also possible to use
GRANT to specify nonprivilege
account characteristics such as whether it uses secure
connections and limits on access to server resources.

However, use of GRANT to
create accounts or define nonprivilege characteristics is
deprecated as of MySQL 5.7.6. Instead, perform these tasks
using CREATE USER or
ALTER USER.

Under some circumstances,
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 the conditions
under which this occurs for the server logs and how to
control it, see Section 6.1.2.3, “Passwords and Logging”. For
similar information about client-side logging, see
Section 4.5.1.3, “mysql Logging”.

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

Warning

Do not attempt to change the permissible length
for user names by altering the mysql.user
table. Doing so results in unpredictable behavior which may
even make it impossible for users to log in to the MySQL
server. Never alter the structure of tables in
the mysql database in any manner except
by means of the procedure described in
Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

Object Quoting Guidelines

Several objects within GRANT
statements are subject to quoting, although quoting is
optional in many cases: Account, 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.

The _ and % wildcards
are permitted when specifying database names in
GRANT statements that grant
privileges at the database level. This means, for example,
that to use a _ character as part of a
database name, 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 ....

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 each privilege, see
Section 6.2.1, “Privileges Provided by MySQL”.

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.

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

Important

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.

Account Names and Passwords

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'@'198.51.100.%'
applies to user_name for any host
in the 198.51.100 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.

If you permit local 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 system table 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 this issue 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';

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 (for example, a 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.2, “CREATE USER Syntax”.

Note

Use of GRANT to define
account authentication characteristics is deprecated as of
MySQL 5.7.6. Instead, establish or change authentication
characteristics using CREATE
USER or ALTER USER.
This GRANT capability will be
removed in a future MySQL release.

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.

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

GRANT OPTION granted at the
global level for any global privilege applies to all global
privileges.

MySQL stores global privileges in the
mysql.user system 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 *.*), 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 system 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 routine level are ALTER
ROUTINE, EXECUTE, and
GRANT OPTION.
CREATE ROUTINE is not a
routine-level privilege because you must have the privilege at
the global or database level to create a routine in the first
place.

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

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

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.10, “Proxy Users”.

Implicit Account Creation

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.

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

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 Encrypted Connections”.

Use of GRANT to define
account SSL characteristics is deprecated as of MySQL 5.7.6.
Instead, establish or change SSL characteristics using
CREATE USER or
ALTER USER. This
GRANT capability will be
removed in a future MySQL release.

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!

Use of GRANT to define
account resource limits is deprecated as of MySQL 5.7.6.
Instead, establish or change resource limits using
CREATE USER or
ALTER USER. This
GRANT capability will be
removed in a future MySQL release.

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 13.7.1.3, “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 with DROP USER or
REVOKE statements.

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.10, “Server SQL Modes”,
and Section 11.7, “Data Type Default Values”.

It would be helpful to link the paragraph on the 'WITH GRANT OPTION' to the '--safe-user-create' start option for mysqld.

It may be obvious to experienced users that the GRANT option not only allows to give privileges to existing users, but also to create new users this way. However, it is not intuitive how to restrict this.

For grant options MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR there isn't a way to get the current status (as opposed to the current setting!) e.g. how close is current queries/hour to MAX_QUERIES_PER_HOUR, say for use in a dashboard, or just as a means of determining how close to capacity the current settings are.

To implement a dashboard or equivalent you basically have to redo all the logic on your own. This limits the usefulness of the current grant options.