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?


Have ended up Querying information_schema.Table_Privileges and scripting Grants & Revokes based on the result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.