[ericpauley]

> Updating the _priv tables directly is the only way to modify privileges within a transaction in MySQL.

Out of genuine curiosity, why do you want to do that? Is the transactional consistency of the applied permissions critical?

[hellcow]

Yes. When I'm applying large changes to permissions across many users and many tables/columns, which happens after every migration, then I absolutely need transaction guarantees. I cannot risk having a half-applied change leaving the database and its users in an unknown state.

[ericpauley]

Could you write your permission changes/rollback logic to be idempotent? In this case you could always push the changes to completion, and reason about whether each intermediate state is safe.

[dekhn]

An alternative to this is to snapshot the database, and test the permissions change on the snapshot. Not as good as a transaction, but often good enough to proceed with the prod operation.

[dvasdekis]

I thought Flyway did this? Have you tried it?