MySQL Manual | 4.3.1 GRANT and REVOKE Syntax

4 Database Administration
- 4.3 MySQL User Account Management

Previous / Next / Up / Table of Contents

4.3.1 `GRANT` and `REVOKE` Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:

Global level: Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. REVOKE ALL ON *.* will revoke only global privileges.
Database level: Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. REVOKE ALL ON db.* will 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. REVOKE ALL ON db.table will 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.

For the GRANT and REVOKE statements, priv_type may be specified as any of the following:

`ALL [PRIVILEGES]`	Sets all simple privileges except `WITH GRANT OPTION`
`ALTER`	Allows usage of `ALTER TABLE`
`CREATE`	Allows usage of `CREATE TABLE`
`CREATE TEMPORARY TABLES`	Allows usage of `CREATE TEMPORARY TABLE`
`DELETE`	Allows usage of `DELETE`
`DROP`	Allows usage of `DROP TABLE`.
`EXECUTE`	Allows the user to run stored procedures (MySQL 5.0)
`FILE`	Allows usage of `SELECT ... INTO OUTFILE` and `LOAD DATA INFILE`.
`INDEX`	Allows usage of `CREATE INDEX` and `DROP INDEX`
`INSERT`	Allows usage of `INSERT`
`LOCK TABLES`	Allows usage of `LOCK TABLES` on tables for which one has the `SELECT` privilege.
`PROCESS`	Allows usage of `SHOW FULL PROCESSLIST`
`REFERENCES`	For the future
`RELOAD`	Allows usage of `FLUSH`
`REPLICATION CLIENT`	Gives the right to the user to ask where the slaves/masters are.
`REPLICATION SLAVE`	Needed for the replication slaves (to read binlogs from master).
`SELECT`	Allows usage of `SELECT`
`SHOW DATABASES`	`SHOW DATABASES` shows all databases.
`SHUTDOWN`	Allows usage of `mysqladmin shutdown`
`SUPER`	Allows one connect (once) even if max_connections is reached and execute commands `CHANGE MASTER`, `KILL thread`, `mysqladmin debug`, `PURGE MASTER LOGS` and `SET GLOBAL`
`UPDATE`	Allows usage of `UPDATE`
`USAGE`	Synonym for ``no privileges.''
`GRANT OPTION`	Synonym for `WITH GRANT OPTION`

USAGE can be used when you want to create a user that has no privileges.

The privileges CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER are new for in version 4.0.2. To use these new privileges after upgrading to 4.0.2, you have to run the mysql_fix_privilege_tables script.

In older MySQL versions, the PROCESS privilege gives the same rights as the new SUPER privilege.

To revoke the GRANT privilege from a user, use a priv_type value of GRANT OPTION:

mysql> REVOKE GRANT OPTION ON ... FROM ...;

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.

MySQL allows you to create database level privileges even if the database doesn't exist, to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exist. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.

You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don't have a current database, you will affect the global privileges!)

Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT commands. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, 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_name value in the form user@host. If you want to specify a user string containing special characters (such as `-'), or a host string containing special characters or wildcard characters (such as `%'), you can quote the user or host name (for example, 'test-user'@'test-hostname').

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

The simple form user is a synonym for user@"%".

MySQL doesn't support wildcards in user names. Anonymous users are defined by inserting entries with User='' into the mysql.user table or creating an user with an empty name with the GRANT command.

Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!

You can verify if this applies to you by executing this query:

mysql> SELECT Host,User FROM mysql.user WHERE User='';

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A user name can be up to 16 characters.

The privileges for a table or column are formed 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, this can't 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 as complicated as above. The details of the privilege-checking procedure are presented in section 4.2 General Security Issues and the MySQL Access Privilege System.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.

In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

If you don't want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD() or the C API function make_scrambled_password(char *to, const char *password).

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.

Passwords can also be set with the SET PASSWORD command. See section 5.5.6 SET Syntax.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). The same is true for SHOW DATABASES.

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 privilege, as two users with different privileges may be able to join privileges!

MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2. These options limit the number of queries/updates and logins the user can do during one hour. If # is 0 (default), then this means that there are no limitations for that user. See section 4.3.6 Limiting user resources. Note: to specify any of these options for an existing user without adding other additional privileges, use GRANT USAGE ON *.* ... WITH MAX_....

You cannot grant another user a privilege you don't have yourself; the GRANT privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the GRANT privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose 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 the 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!

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 will slow down MySQL a bit.

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, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See section 4.3.3 When Privilege Changes Take Effect.

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

In MySQL privileges are given for an username + hostname combination and not only for an username.
SQL-99 doesn't have global or database-level privileges, nor does it support all the privilege types that MySQL supports. MySQL doesn't support the SQL-99 TRIGGER or UNDER privileges.
SQL-99 privileges are structured in a hierarchal manner. If you remove an user, all privileges the user has granted are revoked. In MySQL the granted privileges are not automatically revoked, but you have to revoke these yourself if needed.
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. SQL-99 requires you to have the INSERT privilege on all columns.
With SQL99, when you drop a table, all privileges for the table are revoked. With SQL-99, 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 commands or by manipulating the MySQL grant tables.

For a description of using REQUIRE, see section 4.3.9 Using Secure Connections.

User Comments

Posted by Frank Wortner on Saturday February 16 2002, @10:21pm