We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables.
We are using the following syntax for the rename
ALTER TABLE `mydb`.`table_a` RENAME TO `mydb`.`table_b`
The expected behaviour would be that the GRANTS for
table_a would be shifted across to
table_b. This does not seem to be the case.
Assuming that what we see is correct is there a simple solution we can add to our scripting?
It looks like we should be able to update the
TABLE_PRIVILEGES.TABLE_NAME column in the information schema, but we are assuming that whilst reading the schema is OK, we shouldn't be manually updating it.
Which leads to reading
TABLE_PRIVILEGES and Granting / Revoking privileges line by line...
Any other options?