MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.6 REVOKE Statement (original) (raw)

13.7.1.6 REVOKE Statement

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

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

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

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

For details on the levels at which privileges exist, the permissible privtype,privlevel, and_objecttype_ values, and the syntax for specifying users and passwords, see Section 13.7.1.4, “GRANT Statement”.

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 inSection 6.2.4, “Specifying Account Names”. For example:

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

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

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:

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

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, but the privileges to be revoked need not be currently granted to them.

REVOKE removes privileges, but does not remove rows from the mysql.user system table. To remove a user account entirely, useDROP USER. SeeSection 13.7.1.3, “DROP USER Statement”.

If the grant tables hold privilege rows that contain mixed-case database or table names and thelower_case_table_names system variable is set to a nonzero value,REVOKE cannot be used to revoke these privileges. It is necessary to manipulate the grant tables directly. (GRANT does not create such rows whenlower_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 withQuery OK, 0 rows affected. To determine what privileges remain after the operation, useSHOW GRANTS. SeeSection 13.7.5.21, “SHOW GRANTS Statement”.