Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.4Mb
PDF (A4) - 31.5Mb
PDF (RPM) - 29.8Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Man Pages (TGZ) - 177.2Kb
Man Pages (Zip) - 287.6Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual REVOKE Syntax

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

    FROM user [, user] ...

    FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts.

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

Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

The host name part of the account 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, “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 user or users:


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

User accounts from which privileges are to be revoked must exist.

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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Paul Caskey on October 11, 2011

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.


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