MySQL Bugs: #100762: Grant privileges for user in a database still exist after dropping the database

Bug #100762	Grant privileges for user in a database still exist after dropping the database
Submitted:	7 Sep 2020 20:59	Modified:	8 Sep 2020 5:21
Reporter:	Justin Levene	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop database, grant, privileges, user

Description:
Imagine an install with many databases with user(s) that can access all of them subject to restricted privileges.  

A user is given permission for various privileges in each database.

When a database is dropped, the user privileges still exist for that database, including privileges for tables, functions and procedures.

If a function in a schema is dropped, then the privileges are revoked automatically, but not the case if the schema is dropped.

This obviously has serious security implications as if a new database is created with the same name, it may have privileges already set that shouldn't be there.

How to repeat:

CREATE SCHEMA `schema` ;

CREATE TABLE `schema`.`new_table` (
  `key` INT NOT NULL,
  `test` VARCHAR(45) NULL,
  PRIMARY KEY (`key`));

GRANT DELETE,INSERT,SELECT,UPDATE ON `schema`.`new_table` TO 'user'@`%`;

DROP DATABASE `schema`;

Now run this and you will see the permissions still exist for the deleted table `schema`.`new_table`. If there were functions and procedures in the schema, the same goes for them as well.

SHOW GRANTS FOR 'user'@'%';

The same happens for procedures and functions

Suggested fix:
When a schema/database is dropped, all grants for that database be deleted.

There is no way to do this manually in bulk besides revoking each grant.

Added to the Synopsis and tags.

This is also a security issue as if an admin drops a database to wipe data, privileges, etc. due to a hack, a vulnerability still exists in that if the hacker gave himself a privilege, it still exists on the new database, even though the old one was deleted.

Hello Justin Levene,

Thank you for the report and feedback.
Imho this is documented and expected behavior,Quoting from manual "

Important

When a database is dropped, privileges granted specifically for the database are not automatically dropped. They must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.
".  Please see https://dev.mysql.com/doc/refman/8.0/en/drop-database.html

regards,
Umesh