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 permissions

To revoke all permissions from a user, use the following syntax of the REVOKE statement:

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

Assuming you want to revoke all permissions from sqliz user, use the following statement:

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

or

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

To execute the REVOKE statement, you must have global CREATE USER permission or the UPDATE permission on the mysql database.

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.

Note that to execute REVOKE statements, you must have GRANT OPTION permission.

Suppose you want to revoke the UPDATE and DELETE permissions on sqlizdb from the sqliz user, please execute the following statement:

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

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;