Bug #92956 drop database won't sync up privileges information in dict tables like mysql.db
Submitted: 26 Oct 2018 8:08 Modified: 16 Nov 2020 16:24
Reporter: andy zhang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.6 and above OS:Any
Assigned to: CPU Architecture:Any
Tags: priviliges

[26 Oct 2018 8:08] andy zhang 
Description:
Drop database didn't cleanup privileges in dictionary tables like mysql.db.

We need to sync up the privilege information in the privilege tables in case of security issue.
Commercial db like Oracle/DB2 will invalidate the privileges while db/tables are dropped.

How to repeat:
Grant some privileges of db to a user
Drop the db
Granted privileges are still in the rom mysql.db.
[26 Oct 2018 8:17] MySQL Verification Team 
Hello andy zhang,

Thank you for the report.
Imho this is a documented behavior i.e In MySQL, privileges can be dropped with DROP USER or REVOKE statements. Quoting from manual "When a database is dropped, privileges granted specifically for the database are not automatically dropped. They must be dropped manually" - https://dev.mysql.com/doc/refman/8.0/en/drop-database.html

regards,
Umesh
[31 Oct 2018 16:03] andy zhang 
Not sure if the document statement is more of an excuse :( Why can't mysql kernel make the life of DBAs easier?
[13 Nov 2020 13:34] MySQL Verification Team 
https://bugs.mysql.com/bug.php?id=101593
[16 Nov 2020 16:24] andy zhang 
Glad to see that you decide to make change. It doesn't matter you mark it (design) bug or a new feature.

Please mark this bug as verified also if possible.