(mysql.info.gz) GRANT
Info Catalog
(mysql.info.gz) DROP USER
(mysql.info.gz) Account management SQL
(mysql.info.gz) RENAME USER
13.5.1.3 `GRANT' and `REVOKE' Syntax
....................................
GRANT PRIV_TYPE [(COLUMN_LIST)] [, PRIV_TYPE [(COLUMN_LIST)]] ...
ON {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 [GRANT OPTION | MAX_QUERIES_PER_HOUR COUNT |
MAX_UPDATES_PER_HOUR COUNT |
MAX_CONNECTIONS_PER_HOUR COUNT |
MAX_USER_CONNECTIONS COUNT]]
REVOKE PRIV_TYPE [(COLUMN_LIST)] [, PRIV_TYPE [(COLUMN_LIST)]] ...
ON {TBL_NAME | * | *.* | DB_NAME.*}
FROM USER [, USER] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER [, USER] ...
The `GRANT' and `REVOKE' statements allow system administrators to
create MySQL user accounts and to grant rights to and revoke them from
accounts. `GRANT' and `REVOKE' are implemented in MySQL 3.22.11 or
later. For earlier MySQL versions, these statements do nothing.
MySQL account information is stored in the tables of the `mysql'
database. This database and the access control system are discussed
extensively in MySQL Database Administration, which you should
consult for additional details.
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. 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.
To make it easy to revoke all privileges, MySQL 4.1.2 has added the
following syntax, which drops all global, database-, table-, and
column-level privileges for the named users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER [, USER] ...
Before MySQL 4.1.2, all privileges cannot be dropped at once. Two
statements are necessary:
REVOKE ALL PRIVILEGES ON *.* FROM USER [, USER] ...
REVOKE GRANT OPTION ON *.* FROM USER [, USER] ...
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' Allows use of `ALTER TABLE'
`ALTER ROUTINE' Alter or drop stored routines
`CREATE' Allows use of `CREATE TABLE'
`CREATE ROUTINE' Create stored routines
`CREATE TEMPORARY Allows use of `CREATE TEMPORARY TABLE'
TABLES'
`CREATE VIEW' Allows use of `CREATE VIEW'
`DELETE' Allows use of `DELETE'
`DROP' Allows use of `DROP TABLE'
`EXECUTE' Allows the user to run stored routines
`FILE' Allows use of `SELECT ... INTO OUTFILE' and `LOAD
DATA INFILE'
`INDEX' Allows use of `CREATE INDEX' and `DROP INDEX'
`INSERT' Allows use of `INSERT'
`LOCK TABLES' Allows use of `LOCK TABLES' on tables for which
you have the `SELECT' privilege
`PROCESS' Allows use of `SHOW FULL PROCESSLIST'
`REFERENCES' Not implemented
`RELOAD' Allows use of `FLUSH'
`REPLICATION CLIENT' Allows 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' Allows use of `SELECT'
`SHOW DATABASES' `SHOW DATABASES' shows all databases
`SHOW VIEW' Allows use of `SHOW CREATE VIEW'
`SHUTDOWN' Allows use of `mysqladmin shutdown'
`SUPER' Allows 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' Allows use of `UPDATE'
`USAGE' Synonym for "no privileges"
`GRANT OPTION' Allows privileges to be granted
The `CREATE TEMPORARY TABLES', `EXECUTE', `LOCK TABLES', `REPLICATION
CLIENT', `REPLICATION SLAVE', `SHOW DATABASES', and `SUPER' privileges
were added in MySQL 4.0.2. (`EXECUTE' is not operational until MySQL
5.0.3.) `CREATE VIEW' and `SHOW VIEW' were added in MySQL 5.0.1.
`CREATE ROUTINE' and `ALTER ROUTINE' were added in MySQL 5.0.3. To use
these privileges when upgrading from an earlier version of MySQL that
does not have them, , you must upgrade your grant tables.
Upgrading-grant-tables.
The `REFERENCES' privilege currently is unused.
In older MySQL versions that do not have the `SUPER' privilege, specify
the `PROCESS' privilege instead.
`USAGE' can be specified when you want to create a user that has no
privileges.
Use `SHOW GRANTS' to determine what privileges the account has.
`SHOW GRANTS' SHOW GRANTS.
You can assign global privileges by using `ON *.*' syntax or database
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 `EXECUTION', `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 only `priv_type' values you can specify for a table are `SELECT',
`INSERT', `UPDATE', `DELETE', `CREATE', `DROP', `GRANT OPTION',
`INDEX', and `ALTER'.
The only `priv_type' values you can specify for a column (that is, when
you use a `column_list' clause) are `SELECT', `INSERT', and `UPDATE'.
The only `priv_type' values you can specify at the routine level are
`ALTER ROUTINE', `EXECUTE', and `GRANT'. `CREATE ROUTINE' is not a
routine-level privilege because you must have this privilege to be able
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, if you use `GRANT ALL ON DB_NAME.*', that is a
database-level statement, so none of the global-only privileges such as
`FILE' will be granted.
For column-level privileges (that is, when you specify COLUMN_LIST),
you must explicitly name the privileges to be granted. You cannot use
`ALL' as a privilege specifier.
MySQL allows you to grant database-level privileges even if the database
doesn't exist, to make it easy to prepare for database use. However,
MySQL currently does not allow you to grant table-level or column-level
privileges if the table doesn't exist. Similarly, you cannot grant
routine-level privileges to a non-existent routine.
MySQL does not automatically revoke any privileges even if you drop a
table or drop a database. If you drop a routine, and routine-level
privileges granted for the routine are revoked.
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 ...'.
In order 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 doesn't support wildcards in usernames. Anonymous users are
defined by inserting entries with `User=''' into the `mysql.user' table
or creating a user with an empty name with the `GRANT' statement:
mysql> 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 apostrophes (`'').
*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 the
local host in the `mysql.user' table will be used when named users try
to log in to the MySQL server from the local machine! (This
anonymous-user account is created during MySQL installation.)
You can determine whether this applies to you by executing the
following query:
mysql> 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:
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
mysql> 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 privileges for a table or column are formed additively from the
logical OR of the privileges at each of the four 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
In most cases, you grant rights to a user at only one of the privilege
levels, so life isn't 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' will not remove them; you
must do that explicitly using `DROP USER' or `DELETE'.
In MySQL 3.22.12 or later, 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
had a password, it is replaced by the new one.
*Warning:* If you create a new user but do not specify an `IDENTIFIED
BY' clause, the user has no password. This is insecure. As of MySQL
5.0.2, you can enable the `NO_AUTO_CREATE_USER' SQL mode to prevent
`GRANT' from creating new users if it would otherwise do so, unless
`IDENTIFIED BY' is given to provide a password.
Passwords can also be set with the `SET PASSWORD' statement.
`SET PASSWORD' 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 don't 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.
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 `SHOW DATABASES' privilege allows 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.
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 you don't have yourself; the
`GRANT OPTION' privilege allows you to give away only those privileges
you possess.
Be aware that when you grant a user the `GRANT OPTION' privilege at a
particular privilege level, any privileges the user possesses (or is
given in the future!) at that level are also grantable by that user.
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', the user can give away not only the `SELECT' privilege,
but also `INSERT'. If you then grant the `UPDATE' privilege to the
user on the database, the user can give away `INSERT', `SELECT', and
`UPDATE'.
You should not grant `ALTER' privileges to a normal user. 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 are new in MySQL 4.0.2. They
limit the number of queries, updates, and logins a user can perform
during one hour. If COUNT is 0 (the default), this means there is no
limitation for that user.
The `MAX_USER_CONNECTIONS COUNT' option is new in MySQL 5.0.3. It
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_...'.
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 on the use of SSL with MySQL,
see Secure connections.)
There are different possibilities for limiting connection types for an
account:
* If an account has no SSL or X509 requirements, unencrypted
connections are allowed if the username and password are valid.
However, encrypted connections also can 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 records that allow
non-SSL connections.
mysql> 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.
mysql> 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.
mysql> 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
with subject `'subject'' in it. If the client presents a
certificate that is valid but has a different subject, the server
rejects the connection.
mysql> 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 strong enough
ciphers and key lengths will be used. SSL itself can be weak if
old algorithms with short encryption keys are used. Using this
option, you can ask for some exact cipher method to allow a
connection.
mysql> 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:
mysql> 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';
Note that the `SUBJECT' and `ISSUER' values each should be entered as a
single string.
Starting from MySQL 4.0.4, 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. Database,
table, and column privileges take effect at once, and user-level
privileges take effect the next time the user connects. Modifications
to the grant tables that you perform using `GRANT' or `REVOKE' are
noticed by the server immediately. If you modify the grant tables
manually (using `INSERT', `UPDATE', and so on), you should execute a
`FLUSH PRIVILEGES' statement or run `mysqladmin flush-privileges' to
tell the server to reload the grant tables. Privilege changes.
Note that if you are using table or column privileges for even one
user, the server examines table and column 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 a username/hostname
combination and not with only a username.
* Standard SQL doesn't have global or database-level privileges, nor
does it support all the privilege types that MySQL supports.
* MySQL doesn't 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. `DROP USER' DROP
USER.
* With standard SQL, when you drop a table, all privileges for the
table are revoked. With standard SQL, when you revoke a
privilege, all privileges that were granted based on the privilege
are also revoked. In MySQL, privileges can be dropped only with
explicit `REVOKE' statements or by manipulating the MySQL grant
tables.
* In MySQL, if you have the `INSERT' privilege on only some of the
columns in a table, you can execute `INSERT' statements on the
table; the columns for which you don't have the `INSERT' privilege
will be set to their default values. Standard SQL requires you to
have the `INSERT' privilege on all columns.
Info Catalog
(mysql.info.gz) DROP USER
(mysql.info.gz) Account management SQL
(mysql.info.gz) RENAME USER
automatically generated byinfo2html