13.7.1.4 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 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| IDENTIFIED BY PASSWORD '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
}
The GRANT
statement grants privileges to MySQL user accounts. There are several aspects to the GRANT
statement, described under the following topics:
The GRANT
statement grants privileges to MySQL user accounts.
To grant a privilege with GRANT
, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE
privilege for the grant tables in the mysql
system database, you can grant any account any privilege.) 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 13.7.1.6, “REVOKE Statement”.
Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
The host name part of the account, if omitted, defaults to '%'
.
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:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER '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. 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
.
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 13.7.5.21, “SHOW GRANTS Statement”.
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 Client 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.
Do not attempt to change the permissible length for user names by altering the mysql.user
system 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
system database in any manner except by means of the procedure described in Section 2.11, “Upgrading MySQL”.
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.
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 (GRANT ... ON
). This means, for example, that to use a db_name
.*_
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 ...
).
When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON
), wildcard characters are treated as normal characters. db_name
.tbl_name
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.2, “Privileges Provided by MySQL”.
Table 13.8 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 source or replica servers are. Level: Global. |
REPLICATION SLAVE |
Enable replicas to read binary log events from the source. 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” |
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
system database. For additional details, consult Section 6.2, “Access Control and Account Management”, which discusses the mysql
system 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
is a database-level statement, so it does not grant any global-only privileges such as db_name
.*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, 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;
The globally granted privileges apply to all databases, tables, and columns, even though not granted at any of those lower levels.
Details of the privilege-checking procedure are presented in Section 6.2.6, “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.
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, '
applies to user_name
'@'%.example.com'user_name
for any host in the example.com
domain, and '
applies to user_name
'@'198.51.100.%'user_name
for any host in the 198.51.100
class C subnet.
The simple form '
is a synonym for user_name
''
. 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”.
If you permit local anonymous users to connect to the MySQL server, you should also grant privileges to all local users as '
. Otherwise, the anonymous user account for user_name
'@'localhost'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.5, “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
syntax that permits 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 Statement”.
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 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
privileges are administrative and can only be granted globally.
Other privileges can be granted globally or at more specific levels.
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 apply to all objects in a given database. To assign database-level privileges, use ON
syntax: db_name
.*
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 apply to all columns in a given table. To assign table-level privileges, use ON
syntax: db_name.tbl_name
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.18.2, “CREATE TEMPORARY TABLE Statement”.
MySQL stores table privileges in the mysql.tables_priv
system 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
system 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 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.
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.2.14, “Proxy Users”.
MySQL stores proxy privileges in the mysql.proxies_priv
system table.
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 usingIDENTIFIED BY
.If
NO_AUTO_CREATE_USER
is enabled,GRANT
fails and does not create the account, unless you specify a nonempty password usingIDENTIFIED BY
or name an authentication plugin usingIDENTIFIED WITH
.
If the account already exists, IDENTIFIED WITH
is prohibited because it is intended only for use when creating new accounts.
MySQL can check X.509 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.3, “Using Encrypted 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.2, “CREATE USER Statement”.
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
system 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.2, “Privileges Provided by MySQL”.
It is possible to place limits on use of server resources by an account, as discussed in Section 6.2.16, “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.2, “CREATE USER Statement”.
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.
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 Statement”.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
orREVOKE
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 executeINSERT
statements on the table, provided that you insert values only for those columns for which you have theINSERT
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 theINSERT
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.6, “Data Type Default Values”.