Skip navigation links
12.4.1.4 SET PASSWORD Syntax »
Section Navigation      [Toggle]

12.4.1.3. REVOKE Syntax

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. REVOKE is implemented in MySQL 3.22.11 or later. For earlier MySQL versions, it does nothing. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For details on the levels at which privileges exist, the allowable priv_type and priv_level values, and the syntax for specifying users and passwords, see Section 12.4.1.2, “GRANT Syntax”

To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking.

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 privileges for the named users: 

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the UPDATE privilege for the mysql database.

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

REVOKE removes privileges, but does not drop mysql.user table entries. To remove a user account entirely, use DELETE. As of MySQL 4.1.1, you can also use DROP USER to remove users; see Section 12.4.1.1, “DROP USER Syntax”.

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 the variable.)

To verify an account's privileges after a REVOKE operation, use SHOW GRANTS. See Section 12.4.5.12, “SHOW GRANTS Syntax”.


User Comments

Posted by sheila yao on March 4 2009 8:25pm[Delete] [Edit]

Revoke statement has to match the grants issued. If grant is issued to *.*, you can only revoke *.* as well.
This in my opinion, is very inconvenient.

For example, there are only a few tables that users shouldn't have select permission and the database has over 100 tables.

The most efficient way is to grant select on database.* to this user and then revoke select on the few tables from this user.

But this won't work. Mysql will throw out an error:
revoke select on database.suchtable from 'blabal'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'blabal' on host 'localhost' on table 'suchtable'.

So you will have to literally grant select on the 97 tables one by one in order to avoid giving the select permission on the 3 tables.



Posted by May Doušak on June 4 2009 12:46pm[Delete] [Edit]

Sheila, you can use script to grant all on * on selected user and then revoke what you don't want him to see.
PHP e.g.:

mysql_connect ('localhost', 'root', '******');
$r = mysql_query ("SHOW DATABASES");
while ($rr = mysql_fetch_row ($r)) {
mysql_query ("GRANT all ON " .$rr[0] .".* to user@host");
}

Then just revoke from the three databases you don't want him to see:
revoke all on mysql.* from user@host;

Add your own comment.