First I have to correct myself. In my last blog article I wrote about a bug (17564) that MySQL remains database privileges after a database has been renamed. This behaviour is described in the manual, so it's no bug.
However, this raises an interesting question. Should data definition statements also affect the privilege settings? If you for example drop a database or a table, the privileges still remain for this object. Or if you rename a database or a table, the privileges will not be set to the new name. Should the definition statements and the privilege settings be separated, or handled together?
I would be interested, what other people think about this, what SQL standard suggests and how other RDBMS handle this. Is this the default behaviour among the RDBMS, or is it a Gotcha?
Could it be a good idea to add an option to the data definition statements, something like (just an idea) PRESERVE PRIVILEGES or MODIFY PRIVILEGES?
I would really appreciate your thoughts.
Subscribe to:
Post Comments (Atom)
4 comments:
Just because it's "in the manual" doesn't mean it's not a crappy bug :) More like it was too hard for some reason for the MySQL coders to implement. You and I both know that if you drop a damn table, the privileges should disappear, and if you rename it the privileges should move with it.
Well for a drop statement it should be possible to write a little stored proc that cleans things up whenever you feel like cleaning up. No need to add this to all the drop statemements.
For rename things it gets alot trickier of course. I also think that rename is special in that its purely organizational and not really about modeling your data and so I could see rename also "move" the priviledges.
I have now filed a feature request about this:
http://bugs.mysql.com/17613
Feel free to also add your suggestions to this feature request.
You're thinking too much of normal condiutions and not enough about problem situations. :)
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.
Hopefully we all know that we're less likely to consider all possible interactions when dealing with an emergency than normaly and also know that it's easier to delete permissions later than it is to work out what they used to be and give them back.
James Day
Post a Comment