Bug #106400 Table-level REVOKE fails for a re-created user.
Submitted: 8 Feb 2022 2:52 Modified: 8 Feb 2022 7:17
Reporter: Christopher McGowan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any (Fails on all 5.7 environments tested)
Assigned to: CPU Architecture:Any (Only tested amd64/i386)
Tags: create user, revoke

[8 Feb 2022 2:52] Christopher McGowan 
Description:
In attempting to revoke privileges from a user that has previously been dropped and then recreated, the REVOKE statement will fail with "ERROR 1141 (42000): There is no such grant defined for user...".

This is contrary to the manual, which states: "User accounts from which privileges are to be revoked must exist, but the privileges to be revoked need not be currently granted to them."

https://dev.mysql.com/doc/refman/5.7/en/revoke.html

Per the manual, it is confirmed that lower_case_table_names is set to 0 in the environments we tested on. This behavior can also be confirmed on db-fiddle.

How to repeat:
CREATE SCHEMA `fun`;

CREATE USER IF NOT EXISTS 'bob';

DROP USER IF EXISTS 'bob';

CREATE USER IF NOT EXISTS 'bob';

REVOKE INSERT, UPDATE ON `fun`.* FROM 'bob'; #--Should succeed

Suggested fix:
Respond with "Query OK, 0 rows affected." As stated in the manual.
[8 Feb 2022 7:17] MySQL Verification Team 
Hello Christopher McGowan,

Thank you for the report and feedback.

regards,
Umesh