Additional control over generic ownership grants on destroy

[hexDoor]

Is your feature request related to a problem? Please describe.

Since 2020, revoking ownership grants on snowflake resources has been deprecated in favour of only being able to transfer ownership.

At the current time, terraform destroy of certain ownership grant resources (anything that still uses the generic grant.go code) does not support the ability to determine/control which role the ownership of the resource to transfer to.
As such, this excludes resources such as RoleOwnershipGrant and UserOwnershipGrant

Instead, this is determined at runtime as the role on which the snowflake terraform provider is executing SQL on behalf of:
Relevant Code Here
The behaviour is fine for the vast majority of use-cases but with more complex RBAC hierachies, this lack of control can present an issue in the long term.

In particular, we are running into failing snowflake queries which do not have any functional issues (failing ownership transfer does not hinder the destroy in terms of permissions with our use case) but are caused as a result of the aforementioned runtime determinate behaviour.
I have not been able to find a long-term viable way of bypassing without contributing a fix to the terraform provider itself.

Describe the solution you'd like

I am currently working on a fix that is ready once documentation has been fixed with a draft PR: #1743
(PR information will be further updated once fix is complete)
PR now ready for review: #1743

I've thought of multiple solutions but I could only think of one in the last week that won't introduce lots of headache to existing users except for those who must have a specific interface for the generic grant code which I find to be highly unlikely.

Effectively, it introduces a new variable to the resource schema for generic grants but requires a slight change in the generic grant.go code to get this variable to the function which generates the SQL code to be executed.
The reason why it's most likely the best way forward is that it shouldn't affect existing users of the provider but will support our needs. Initial testing has shown that this is to be the case.

Describe alternatives you've considered

Other solutions include changing the GrantBuilder interface to make additional information available during the Revoke function.
In my experience, I don't believe in this approach for this particular issue since Revoke ownership should be considered a "special" case rather than still remaining generic due to necessity of transferring ownership.

We could also create additional *OwnershipGrant resources which were considered but were dropped due to possible redundancies it would cause:
#917 (comment)

Additional context

The lack of control over how Ownership Grants are destroyed has led to another issue in the past:

• Unable to destroy resources created with terraform when changing ownership #1060