MySQL Table rename and Grants

MySQLmysql-5.0permissions

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?

Best Answer

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

Related Solutions

MySQL – Show GRANTs for All Users

Nothing built-in. You have two options though:

Use common_schema's sql_show_grants view. For example, you can query:
```
SELECT sql_grants FROM common_schema.sql_show_grants;
```
Or you can query for particular users, for example:
```
SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';
```
To install common_schema, follow the instructions here.

Disclaimer: I am author of this tool.

Use Percona Toolkit's pt-show-grants, for example:

pt-show-grants --host localhost --user root --ask-pass

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

MySQL Permissions – How to Remove Useless GRANTS in MySQL

I don't see any option in pt-show-grants to do this kind of restriction. There are options for --only and --ignore specific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables.

I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason.

For what it's worth, I don't see any error when I try to grant to a non-existant table. I tested on Percona Server 5.6.16:

mysql> grant all on test.nonexist to 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

$ pt-show-grants
. . .
GRANT ALL PRIVILEGES ON `test`.`nonexist` TO 'user'@'%';

Can you tell me anything else about your version of MySQL and the specific error that it produces when you try to run that grant script?

Re your comment:

The difference between ALL PRIVILEGES and specific privileges seems to be deliberate, according to Bug #10406 Grant all command does not give error though table does not exist.

If you use GRANT CREATE, SELECT ... ON test.notexist TO 'user'@'%' then this works. As long as you include CREATE privilege, then it permits the grant to the non-existant table.

Anyway, that's interesting trivia, but it doesn't address your original question.

This has been requested before, but never implemented. It was recorded as a "blueprint" (like a feature request) here: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-show-grants-for-nonexistent-tables

Perhaps someone would like to implement a patch and contribute it to Percona Toolkit?

Best Answer

Related Solutions

MySQL – Show GRANTs for All Users

MySQL Permissions – How to Remove Useless GRANTS in MySQL

Related Question