Hello everyone,
I’m looking for advice on the best way to handle object grants when deploying changes to Materialized Views using SQLcl projects.
When I change the query of an existing Materialized View, SQLcl correctly generates a DROP and CREATE command for the MV. However, the DROP statement automatically wipes out all existing grants on that MV.
This leads to the following problem: Because the grants themselves haven't changed (the checksum remains the same when running project export), project stage doesn't generate new changesets for them. This means the MV gets recreated, the grants are lost, and they are not automatically re-applied because they weren't part of the active changelog. If someone forgets to manually address the grants, it leads to immediate access issues post-deployment.
I can think of a few workarounds (e.g., running all grants in a post-deployment script, or changing the architecture by putting a standard view over the MV and granting access to that view instead).
However, none of these workarounds make me really happy. Is there an officially recommended approach for this within SQLcl? How do you handle this in your own deployment workflows?
Thanks in advance!