Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.7.1.8 REVOKE Statement

REVOKE [IF EXISTS]priv_type [(column_list)]      [,priv_type [(column_list)]] ...    ON [object_type]priv_level    FROMuser_or_role [,user_or_role] ...    [IGNORE UNKNOWN USER]REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION    FROMuser_or_role [,user_or_role] ...    [IGNORE UNKNOWN USER]REVOKE [IF EXISTS] PROXY ONuser_or_role    FROMuser_or_role [,user_or_role] ...    [IGNORE UNKNOWN USER]REVOKE [IF EXISTS]role [,role ] ...    FROMuser_or_role [,user_or_role ] ...    [IGNORE UNKNOWN USER]user_or_role: {user (see Section 8.2.4, “Specifying Account Names”)  |role (see Section 8.2.5, “Specifying Role Names”}

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

For details on the levels at which privileges exist, the permissiblepriv_type,priv_level, andobject_type values, and the syntax for specifying users and passwords, seeSection 15.7.1.6, “GRANT Statement”.

For information about roles, seeSection 8.2.10, “Using Roles”.

When theread_only system variable is enabled,REVOKE requires theCONNECTION_ADMIN or privilege (or the deprecatedSUPER privilege), in addition to any other required privileges described in the following discussion.

Beginning with MySQL 8.0.30, all the forms shown forREVOKE support anIF EXISTS option as well as anIGNORE UNKNOWN USER option. With neither of these modifications,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. The precise effects ofIF EXISTS andIGNORE UNKNOWN USER are discussed later in this section.

Each account name uses the format described inSection 8.2.4, “Specifying Account Names”. Each role name uses the format described inSection 8.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'%'.

To use the firstREVOKE syntax, you must have theGRANT OPTION privilege, and you must have the privileges that you are revoking.

To revoke all privileges from a user, use one of the following statements; either of these statements drops all global, database, table, column, and routine privileges for the named users or roles:

REVOKE ALL PRIVILEGES, GRANT OPTION  FROMuser_or_role [,user_or_role] ...REVOKE ALL ON *.*  FROMuser_or_role [,user_or_role] ...

Neither of the two statements just shown revokes any roles.

To use theseREVOKE statements, you must have the globalCREATE USER privilege, or theUPDATE privilege for themysql system schema.

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

TheIF EXISTS andIGNORE UNKNOWN USER options (MySQL 8.0.30 and later) have the effects listed here:

  • IF EXISTS means that, if the target user or role exists but no such privilege or role is found assigned to the target for any reason, a warning is raised, instead of an error; if no privilege or role named by the statement is assigned to the target, the statement has no (other) effect. Otherwise,REVOKE executes normally; if the user does not exist, the statement raises an error.

    Example: Given tablet1 in databasetest, we execute the following statements, with the results shown.

    mysql> CREATE USER jerry@localhost;Query OK, 0 rows affected (0.01 sec)mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost;ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host'localhost' on table 't1' mysql> REVOKE IF EXISTS SELECT ON test.t1 FROM jerry@localhost;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1147Message: There is no such grant defined for user 'jerry' on host 'localhost' ontable 't1' 1 row in set (0.00 sec)

    IF EXISTS causes an error to be demoted to a warning even if the privilege or role named does not exist, or the statement attempts to assign it at the wrong level.

  • If theREVOKE statement includesIGNORE UNKNOWN USER, the statement raises a warning for any target user or role named in the statement but not found; if no target named by the statement exists,REVOKE succeeds but has no actual effect. Otherwise, the statement executes as usual, and attempting to revoke a privilege not assigned to the target for whatever reason raises an error, as expected.

    Example (continuing from the previous example):

    mysql> DROP USER IF EXISTS jerry@localhost;Query OK, 0 rows affected (0.01 sec)mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost;ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host'localhost' on table 't1' mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost IGNORE UNKNOWN USER;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 3162Message: Authorization ID jerry does not exist.1 row in set (0.00 sec)

  • The combination ofIF EXISTS andIGNORE UNKNOWN USER means thatREVOKE never raises an error for an unknown target user or role or for an unassigned or unavailable privilege, and the statement as whole in such cases succeeds; roles or privileges are removed from existing target users or roles whenever possible, and any revocation which is not possible raises a warning and executes as aNOOP.

    Example (again continuing from example in the previous item):

    # No such user, no such rolemysql> DROP ROLE IF EXISTS Bogus;Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> SHOW WARNINGS;+-------+------+----------------------------------------------+| Level | Code | Message                                      |+-------+------+----------------------------------------------+| Note  | 3162 | Authorization ID 'Bogus'@'%' does not exist. |+-------+------+----------------------------------------------+1 row in set (0.00 sec)# This statement attempts to revoke a nonexistent role from a nonexistent usermysql> REVOKE Bogus ON test FROM jerry@localhost;ERROR 3619 (HY000): Illegal privilege level specified for test# The same, with IF EXISTSmysql> REVOKE IF EXISTS Bogus ON test FROM jerry@localhost;ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host'localhost' on table 'test' # The same, with IGNORE UNKNOWN USERmysql> REVOKE Bogus ON test FROM jerry@localhost IGNORE UNKNOWN USER;ERROR 3619 (HY000): Illegal privilege level specified for test# The same, with both optionsmysql> REVOKE IF EXISTS Bogus ON test FROM jerry@localhost IGNORE UNKNOWN USER;Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> SHOW WARNINGS;+---------+------+--------------------------------------------+| Level   | Code | Message                                    |+---------+------+--------------------------------------------+| Warning | 3619 | Illegal privilege level specified for test || Warning | 3162 | Authorization ID jerry does not exist.     |+---------+------+--------------------------------------------+2 rows in set (0.00 sec)

Roles named in themandatory_roles system variable value cannot be revoked. WhenIF EXISTS andIGNORE UNKNOWN USER are used together in a statement that tries to remove a mandatory privilege, the error normally raised by attempting to do this is demoted to a warning; the statement executes successfully, but does not make any changes.

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. Suppose that 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. In this case, the account still possesses that privilege if the first role is revoked. For example, if an account is granted two roles that each includeSELECT, 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.

A revoked privilege that is granted but not known to the server is revoked with a warning. This situation can occur for dynamic privileges. For example, a dynamic privilege can be granted while the component that registers it is installed, but if that component is subsequently uninstalled, the privilege becomes unregistered, although accounts that possess the privilege still possess it and it can be revoked from them.

REVOKE removes privileges, but does not remove rows from themysql.user system table. To remove a user account entirely, useDROP USER. SeeSection 15.7.1.5, “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 in such cases 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. Thelower_case_table_names setting can only be configured when initializing the server.)

When successfully executed from themysql program,REVOKE responds withQuery OK, 0 rows affected. To determine what privileges remain after the operation, useSHOW GRANTS. SeeSection 15.7.7.21, “SHOW GRANTS Statement”.