reference manuals search engine

MySQL Reference Manual - 4.0.20

14.5.1.2 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]]

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 Database Administration, which you should consult for additional details.

Privileges can be granted at four 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 tables 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.

To make it easy to revoke all privileges, MySQL 4.1.2 has added the following syntax, which drops all 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 FROM user [, user] ...
REVOKE GRANT OPTION FROM user [, user] ...

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

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 MySQL 4.0.2. To use these new privileges after upgrading to 4.0.2, you must run the mysql_fix_privilege_tables script. See section Upgrading the Grant Tables.

In older MySQL versions that do not have the SUPER privilege, the PROCESS privilege can be used instead.

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 a current database, the privileges will be granted in that database. (Warning: If you specify ON * and you don't have a current database, the privileges granted will be 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.

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.

MySQL allows you to create 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 create table-level privileges if the table doesn't exist.

MySQL does not automatically revoke any privileges even if you drop a table or drop a database.

Note: the `_' and `%' wildcards are allowed when 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 ....

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 you want to specify a user_name string containing special characters (such as `-'), or a host_name string containing special characters or wildcard characters (such as `%'), you can quote the username or hostname (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' ...

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 users try to log in to the MySQL server from the local machine! (This 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;

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A username 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 The MySQL Access 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 already 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.

Passwords can also be set with the SET PASSWORD statement. See section SET PASSWORD.

If you don't want to send the password in clear text, you can use the PASSWORD keyword followed by a scrambled password from the PASSWORD() SQL function or 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). If a user has no privileges for a database, the database name is not displayed by SHOW DATABASES unless the user has the SHOW DATABASES privilege.

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 already 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. See section Limiting Account Resources. Note: To specify any of these options for an existing user without affecting existing privileges, use GRANT USAGE ON *.* ... WITH MAX_....

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

• REQUIRE SSL option limits the server to allow only SSL encrypted connections for the account. Note that this option can be omitted if there are any ACL 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 unneccessary.

  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' on 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, we 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.

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.

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. See section When Privilege Changes Take Effect.

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

• In MySQL, privileges are given for a username/hostname combination and not only for 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. In MySQL, the granted privileges are not automatically revoked; you must revoke them yourself.

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

This document was generated on July, 26 2004 using texi2html 1.70.