MySQL Bugs: #17613: Change privileges after object is changed

Bug #17613	Change privileges after object is changed
Submitted:	21 Feb 2006 13:19	Modified:	21 Feb 2006 13:24
Reporter:	Markus Popp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.0/5.1	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
After a database or table (or even columns?) are dropped or renamed, the privileges currently remain unchanged.

How to repeat:
For example:

Create a database
Create a user
Grant the user privileges for the database
Delete the database again or rename it
The privilege is still the same

Suggested fix:
I think it would be more intuitive if the privileges would also change (or be dropped), if an object is changed (or dropped). If a user isn't aware that the privileges remain, this could even result in a security risk, if another user creates an object with the same name without knowing that there are still existing privileges.

At least, I would speak for making this the default behaviour. Eventually, it could make sense to create an option for preserving the privileges, if a user wants to - maybe something like this:

DROP DATABASE xyz PRESERVE PRIVILEGES;

Thank you for the feature request bug report.

This is working as it should. All is fine with your request when everything is working normally. But what happens when there's a problem and you need to drop the table and reload it from another copy or a backup?

Lose the privileges (and perhaps foreign keys and cascaded deletes from other tables) and what you're asking just made a problem which is easy to fix into one which gets you a miniature disaster with massive data loss you need to recover from.