Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.0Mb
PDF (A4) - 32.9Mb
PDF (RPM) - 30.9Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.7Kb
Man Pages (Zip) - 203.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


Pre-General Availability Draft: 2017-08-19

13.7.1.8 REVOKE Syntax

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

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

REVOKE PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user
  | role
}

user:
    (see Section 6.2.4, “Specifying Account Names”)

role:
    (see Section 6.2.5, “Specifying Role Names”.

The REVOKE statement enables system administrators to revoke privileges and roles, which can be revoked from user accounts and roles.

For information about roles, see Section 6.3.4, “Using Roles”.

When the read_only system variable is enabled, REVOKE requires the CONNECTION_ADMIN or SUPER privilege in addition to any other required privileges described in the following discussion.

REVOKE either succeeds for all named users and roles or rolls back and has no effect if any error occurs. The statement is written to the binary log only if it succeeds for all named users and roles.

Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. Each role name uses the format described in Section 6.2.5, “Specifying Role Names”. For example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';

The host name part of the account or role name, if omitted, defaults to '%'.

For details on the levels at which privileges exist, the permissible priv_type, priv_level, and object_type values, and the syntax for specifying users and passwords, see Section 13.7.1.6, “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 revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named users or roles:

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

REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke any roles.

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

The syntax for which the REVOKE keyword is followed by one or more role names takes a FROM clause indicating one or more users or roles from which to revoke the roles.

Roles named in the mandatory_roles system variable value cannot be revoked.

A revoked role immediately affects any user account from which it was revoked, such that within any current session for the account, its privileges are adjusted for the next statement executed.

Revoking a role revokes the role itself, not the privileges that it represents. If an account is granted a role that includes a given privilege, and is also granted the privilege explicitly or another role that includes the privilege, the account still is granted that privilege after the first role is revoked. For example, if an account is granted two roles that each include SELECT, the account still can select after either role is revoked.

REVOKE ALL ON *.* (at the global level) revokes all granted static global privileges and all granted dynamic privileges.

User accounts and roles from which privileges and roles are to be revoked must exist, but the roles to be revoked need not be currently granted to them.

REVOKE removes privileges, but does not drop mysql.user table entries. To remove a user account entirely, use DROP USER. See Section 13.7.1.5, “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.)

When successfully executed from the mysql program, REVOKE responds with Query OK, 0 rows affected. To determine what privileges remain after the operation, use SHOW GRANTS. See Section 13.7.5.21, “SHOW GRANTS Syntax”.


User Comments
  Posted by sheila yao on March 4, 2009
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
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;

  Posted by Paul Caskey on October 11, 2011
Sheila,

RE: REVOKE on 3 tables out of 100
I solved this problem by creating a separate database to hold the sensitive tables, and joining those back to the master tables on unique IDs for 1-1 row joins. Yes permission changes could be scripted, but now I have a cleaner security design that is far easier to maintain as I add/remove tables from the master. There are usage inconveniences now, but I'm giving this a shot. It's easy to join across databases.

Paul

Sign Up Login You must be logged in to post a comment.