Revoke User Privileges Using REVOKE Statement in MySQL

If you are facing the following issues, you may need to revoke the user’s permissions:

• A user was granted incorrect permissions
• Authorization expired

MySQL allows you to revoke permissions from users using the REVOKE statement.

MySQL REVOKE Statements Syntax

MySQL REVOKE statements are used to revoke one or more permissions from a user account.

Revoke all direct privileges

To revoke all global, database, table, column, and routine privileges and the grant option from an account, use:

REVOKE
    ALL [PRIVILEGES],
    GRANT OPTION
FROM user1 [, user2];

Assuming you want to revoke all direct privileges from the local account 'sqliz'@'localhost', use:

REVOKE
    ALL PRIVILEGES,
    GRANT OPTION
FROM 'sqliz'@'localhost';

or

REVOKE
    ALL,
    GRANT OPTION
FROM 'sqliz'@'localhost';

These statements do not revoke roles granted to the account. Remove each role membership separately:

REVOKE 'app_role'@'localhost' FROM 'sqliz'@'localhost';

These two all-privilege forms require the global CREATE USER privilege or UPDATE on the mysql system schema.

Revoke one or more permissions

The following syntax illustrates the basic syntax of the REVOKE statement to revoke one or more permissions from a user account:

REVOKE
   priv1 [, priv2 [, ...] ]
ON [object_type] privilege_level
FROM user1 [, user2 [, ...]];

Here:

• You should specify a comma-separated list of permissions to be revoked from the user account after the REVOKE keyword. Such as: ALL, SELECT, UPDATE, DELETE, ALTER, DROP and INSERT etc. For more details, please refer to: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_all.
• You should specify the object type and permission level for the permission after the ON keyword. Such as: *, *.*, db_name.*, db_name.table_name, table_name etc.
• In the FROM clause specify one or more user accounts whose permissions you want to revoke.

For a scoped REVOKE privilege ON ... statement, you need GRANT OPTION and the privileges being revoked. See the MySQL 8.4 REVOKE documentation.

Suppose you want to revoke the UPDATE and DELETE permissions on sqlizdb from 'sqliz'@'localhost', execute:

REVOKE UPDATE, DELETE
ON sqlizdb.*
FROM 'sqliz'@'localhost';

Conclusion

In MySQL, you can use the REVOKE statement to revoke one or more permissions from a user.

Before revoking a user’s permissions, it is a good practice to display the permissions of a user account using the following SHOW GRANTS statement:

SHOW GRANTS FOR user;