Monday, February 20, 2006

To separate or to not separate

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.


Chris said...

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.

Lukas said...

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.

Markus Popp said...

I have now filed a feature request about this:

Feel free to also add your suggestions to this feature request.

Sheeri said...

I kind of feel like it's a referential integrity issue, and like being able to have ON CASCADE DELETE with foreign keys, there should be a similar ON DROP TABLE REMOVE PRIVILEGES type function. That way it can be user-specified, like it is with referential integrity *inside* the database.

Anonymous said...

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