(mysql.info) grant
Info Catalog
(mysql.info) drop-user
(mysql.info) account-management-sql
(mysql.info) rename-user
13.5.1.3 `GRANT' Syntax
.......................
GRANT PRIV_TYPE [(COLUMN_LIST)] [, PRIV_TYPE [(COLUMN_LIST)]] ...
ON [OBJECT_TYPE] {TBL_NAME | * | *.* | DB_NAME.*}
TO USER [IDENTIFIED BY [PASSWORD] 'PASSWORD']
[, USER [IDENTIFIED BY [PASSWORD] 'PASSWORD']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'CIPHER' [AND]]
[ISSUER 'ISSUER' [AND]]
[SUBJECT 'SUBJECT']]
[WITH WITH_OPTION [WITH_OPTION] ...]
OBJECT_TYPE =
TABLE
| FUNCTION
| PROCEDURE
WITH_OPTION =
GRANT OPTION
| MAX_QUERIES_PER_HOUR COUNT
| MAX_UPDATES_PER_HOUR COUNT
| MAX_CONNECTIONS_PER_HOUR COUNT
| MAX_USER_CONNECTIONS COUNT
The `GRANT' statement enables system administrators to create MySQL
user accounts and to grant rights to from 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. See revoke.
MySQL account information is stored in the tables of the `mysql'
database. This database and the access control system are discussed
extensively in database-administration, which you should
consult for additional details.
*Important*: Some releases of MySQL introduce changes to the structure
of the grant tables to add new privileges or features. Whenever you
update to a new version of MySQL, you should update your grant tables to
make sure that they have the current structure so that you can take
advantage of any new capabilities. See mysql-upgrade.
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:
* *Global level*
Global privileges apply to all databases on a given server. These
privileges are stored in the `mysql.user' table. `GRANT ALL ON
*.*' and `REVOKE ALL ON *.*' grant and revoke only global
privileges.
* *Database level*
Database privileges apply to all objects in a given database.
These privileges are stored in the `mysql.db' and `mysql.host'
tables. `GRANT ALL ON DB_NAME.*' and `REVOKE ALL ON DB_NAME.*'
grant and revoke only database privileges.
* *Table level*
Table privileges apply to all columns in a given table. These
privileges are stored in the `mysql.tables_priv' table. `GRANT ALL
ON DB_NAME.TBL_NAME' and `REVOKE ALL ON DB_NAME.TBL_NAME' grant
and revoke only table privileges.
* *Column level*
Column privileges apply to single columns in a given table. These
privileges are stored in the `mysql.columns_priv' table. When using
`REVOKE', you must specify the same columns that were granted.
* *Routine level*
The `CREATE ROUTINE', `ALTER ROUTINE', `EXECUTE', and `GRANT'
privileges apply to stored routines (functions and procedures).
They can be granted at the global and database levels. Also,
except for `CREATE ROUTINE', these privileges can be granted at
the routine level for individual routines and are stored in the
`mysql.procs_priv' table.
The OBJECT_TYPE clause was added in MySQL 5.0.6. It should be specified
as `TABLE', `FUNCTION', or `PROCEDURE' when the following object is a
table, a stored function, or a stored procedure.
For the `GRANT' and `REVOKE' statements, PRIV_TYPE can be specified as
any of the following:
*Privilege* *Meaning*
`ALL [PRIVILEGES]' Sets all simple privileges except `GRANT OPTION'
`ALTER' Enables use of `ALTER TABLE'
`ALTER ROUTINE' Enables stored routines to be altered or dropped
`CREATE' Enables use of `CREATE TABLE'
`CREATE ROUTINE' Enables creation of stored routines
`CREATE TEMPORARY Enables use of `CREATE TEMPORARY TABLE'
TABLES'
`CREATE USER' Enables use of `CREATE USER', `DROP USER',
`RENAME USER', and `REVOKE ALL PRIVILEGES'.
`CREATE VIEW' Enables use of `CREATE VIEW'
`DELETE' Enables use of `DELETE'
`DROP' Enables use of `DROP TABLE'
`EXECUTE' Enables the user to run stored routines
`FILE' Enables use of `SELECT ... INTO OUTFILE' and
`LOAD DATA INFILE'
`INDEX' Enables use of `CREATE INDEX' and `DROP INDEX'
`INSERT' Enables use of `INSERT'
`LOCK TABLES' Enables use of `LOCK TABLES' on tables for which
you have the `SELECT' privilege
`PROCESS' Enables use of `SHOW FULL PROCESSLIST'
`REFERENCES' Not implemented
`RELOAD' Enables use of `FLUSH'
`REPLICATION CLIENT' Enables the user to ask where slave or master
servers are
`REPLICATION SLAVE' Needed for replication slaves (to read binary log
events from the master)
`SELECT' Enables use of `SELECT'
`SHOW DATABASES' `SHOW DATABASES' shows all databases
`SHOW VIEW' Enables use of `SHOW CREATE VIEW'
`SHUTDOWN' Enables use of `mysqladmin shutdown'
`SUPER' Enables use of `CHANGE MASTER', `KILL', `PURGE
MASTER LOGS', and `SET GLOBAL' statements, the
`mysqladmin debug' command; allows you to connect
(once) even if `max_connections' is reached
`UPDATE' Enables use of `UPDATE'
`USAGE' Synonym for `no privileges'
`GRANT OPTION' Enables privileges to be granted
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.
The `REFERENCES' privilege currently is unused.
`USAGE' can be specified when you want to create a user that has no
privileges.
Use `SHOW GRANTS' to determine what privileges an account has. See
show-grants.
You can assign global privileges by using `ON *.*' syntax or
database-level privileges by using `ON DB_NAME.*' syntax. If you
specify `ON *' and you have selected a default database, the privileges
are granted in that database. (*Warning:* If you specify `ON *' and you
have _not_ selected a default database, the privileges granted are
global.)
The `FILE', `PROCESS', `RELOAD', `REPLICATION CLIENT', `REPLICATION
SLAVE', `SHOW DATABASES', `SHUTDOWN', and `SUPER' privileges are
administrative privileges that can only be granted globally (using `ON
*.*' syntax).
Other privileges can be granted globally or at more specific levels.
The PRIV_TYPE values that you can specify for a table are `SELECT',
`INSERT', `UPDATE', `DELETE', `CREATE', `DROP', `GRANT OPTION',
`INDEX', `ALTER', `CREATE VIEW' and `SHOW VIEW'.
The PRIV_TYPE values that you can specify for a column (that is, when
you use a COLUMN_LIST clause) are `SELECT', `INSERT', and `UPDATE'.
The PRIV_TYPE values that you can specify 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.
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'.
MySQL allows 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.
*Important*: _MySQL does not automatically revoke any privileges when
you drop a table or database_. However, if you drop a routine, any
routine-level privileges granted for that routine are revoked.
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 ...'.
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 username and hostname separately.
You can specify wildcards in the hostname. 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 usernames_. Anonymous users are
defined by inserting entries with `User=''' into the `mysql.user' table
or by creating a user with an empty name with the `GRANT' statement:
GRANT ALL ON test.* TO ''@'localhost' ...
When specifying quoted values, quote database, table, column, and
routine names as identifiers, using backticks (‘``'’). Quote
hostnames, usernames, and passwords as strings, using single quotes
(‘`''’).
*Warning:* If you allow 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
connection-access.
You can determine whether this applies to you by executing the
following query, which lists any anonymous users:
SELECT Host, User FROM mysql.user WHERE User='';
If you want to delete the local anonymous user account to avoid the
problem just described, use these statements:
DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;
`GRANT' supports hostnames up to 60 characters long. Database, table,
column, and routine names can be up to 64 characters. Usernames can be
up to 16 characters. * _The allowable length for usernames cannot
be changed by altering the `mysql.user' table, and 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 any of the
tables in the `mysql' database in any manner whatsoever except by means
of the procedure prescribed by MySQL AB that is described in
mysql-upgrade.
The privileges for a table, column, or routine are formed additively as
the logical `OR' of the privileges at each of the privilege levels. For
example, if the `mysql.user' table specifies that a user has a global
`SELECT' privilege, the privilege cannot be denied by an entry at the
database, table, or column level.
The privileges for a column can be calculated as follows:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges
In most cases, you grant rights to a user at only one of the privilege
levels, so life is not normally this complicated. The details of the
privilege-checking procedure are presented in privilege-system.
If you grant privileges for a username/hostname combination that does
not exist in the `mysql.user' table, an entry is added and remains
there until deleted with a `DELETE' statement. In other words, `GRANT'
may create `user' table entries, but `REVOKE' does not remove them; you
must do that explicitly using `DROP USER' or `DELETE'.
*Warning*: If you create a new user but do not specify an `IDENTIFIED
BY' clause, the user has no password. This is very insecure. As of
MySQL 5.0.2, you can enable the `NO_AUTO_CREATE_USER' SQL mode to keep
`GRANT' from creating a new user if it would otherwise do so, unless
`IDENTIFIED BY' is given to provide the new user a non-empty password.
If a new user is created or if you have global grant privileges, the
user's password is set to the password specified by the `IDENTIFIED BY'
clause, if one is given. If the user already had a password, this is
replaced by the new one.
Passwords can also be set with the `SET PASSWORD' statement. See
set-password.
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';
In a C program, you can get the hashed value by using the
`make_scrambled_password()' C API function.
If you grant privileges for a database, an entry in the `mysql.db'
table is created if needed. If all privileges for the database are
removed with `REVOKE', this entry is deleted.
The `SHOW DATABASES' privilege enables the account to see database
names by issuing the `SHOW DATABASE' statement. Accounts that do not
have this privilege see only databases for which they have some
privileges, and cannot use the statement at all if the server was
started with the -skip-show-database option.
If a user has no privileges for a table, the table name is not
displayed when the user requests a list of tables (for example, with a
`SHOW TABLES' statement).
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
join 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!
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. 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.
Note: To specify any of these resource-limit options for an existing
user without affecting existing privileges, use `GRANT USAGE ON *.* ...
WITH MAX_...'.
See user-resources.
MySQL can check X509 certificate attributes in addition to the usual
authentication that is based on the username 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 secure-connections.)
There are a number of different possibilities for limiting connection
types for a given account:
* If the account has no SSL or X509 requirements, unencrypted
connections are allowed if the username 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.
* The `REQUIRE SSL' option tells the server to allow only
SSL-encrypted connections for the account. Note that this option
can be omitted if there are any access-control rows that allow
non-SSL connections.
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret' REQUIRE SSL;
* `REQUIRE X509' means that the client must have a valid certificate
but that 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.
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret' REQUIRE X509;
* `REQUIRE 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.
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret'
REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
Note that the `'ISSUER'' value should be entered as a single
string.
* `REQUIRE 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.
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';
Note that the `'SUBJECT'' value should be entered as a single
string.
* `REQUIRE CIPHER 'CIPHER'' 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. Using this
option, you can ask that a specific cipher method is used to allow
a connection.
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret'
REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
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 `AND' keyword is optional between `REQUIRE' options.
The order of the options does not matter, but no option can be
specified twice.
When `mysqld' starts, all privileges are read into memory. For details,
see privilege-changes.
Note that 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:
* In MySQL, privileges are associated with the combination of a
hostname and username and not with only a username.
* 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 `TRIGGER' or `UNDER'
privileges.
* 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 5.0.2 and up if you use `DROP
USER'. Before 5.0.2, the granted privileges are not automatically
revoked; you must revoke them yourself. See drop-user.
* 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
`REVOKE' statements or by manipulating values stored in the MySQL
grant tables.
* 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 omit those
columns for which you do not 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.)
server-sql-mode, discusses strict mode.
data-type-defaults, discusses implicit default values.
Info Catalog
(mysql.info) drop-user
(mysql.info) account-management-sql
(mysql.info) rename-user
automatically generated byinfo2html