snowflake_grant_privileges_to_account_role - revokes

[tabu69]

Hi Community,

Hopefully this isn't silly question....

I have migrated to use new resource snowflake_grant_privileges_to_account_role for table and dynamic table grants. The RBAC structure is environmental roles for example I have Dev, System test, UAT and Production environments. Within Dev databases, roles would be lead_developer_dev, developer_dev and in production roles would be called lead_developer_prod, developer_prod.

This is the use case:

• Refreshing databases within snowflake using pipeline process (code is python and terraform), each database will have it's own statefile, upon apply of RBAC process.
• A common scenario is to refresh Dev, system test and UAT databases using production database as source
  -Prior to migrating to snowflake_grant_privileges_to_account_role, the behaviour of our RBAC process, revoked roles derived from source database, on target database and added the appropriate environmental roles associated with target environment, to avoid mixing environmental roles.

I am wondering if there is a specific parameter in snowflake_grant_privileges_to_account_role resource, when plural is used, to revoke outbound_privileges, similar to what's available in resource for snowflake_grant_ownership? And is there a flag to enforce re-apply of grants, even when nothing has changed?

Snippet below of code for table grants from our RBAC code.

`{% if kind == "TABLES" %}
{% if priv != "OWNERSHIP" %}
## Set Tables object permissions for {{ db.lower() }}.{{ sch.lower() }}, {{ priv.lower() }}
resource "snowflake_grant_privileges_to_account_role" "{{ db.lower() }}{{ sch.lower() }}table{{ priv.lower().replace(' ', '') }}" {
for_each = toset([{% for role in roles %}{% if not loop.first %}, {% endif %}"{{ role }}"{% endfor %}])
account_role_name = each.key
privileges = ["{{ priv }}"]

        on_schema_object {
        all {
            object_type_plural = "TABLES"
            in_schema          = "\\\"{{ db|safe }}\\\".\\\"{{ sch|safe }}\\\""
            }
        }
    }`

As part of testing, deleting statefile so it creates new, the grants for correct environment roles will get applied successfully on table objects. Of course deleting statefile is never the solution! :)

[sfc-gh-jcieslak]

Hey @tabu69 👋
Sorry, you waited so long. To answer both questions:

1. No, but we plan to add this flag in the future. It will make privileges coming from the snowflake_grant_privileges_to_account_role the only privileges granted on the object; other privileges will be revoked. Right now, the snowflake_grant_privileges_to_account_role only cares about specified privileges and whether they're granted or not.
2. Yes, there is a flag. As you can see here, the always_apply flag grants privileges on every terraform apply. Is that something you were looking for?

[tabu69]

Hi @sfc-gh-jcieslak, thanks for the reply on this 🙂. Good to hear the snowflake_grant_privileges_to_account_role resource will have functionality that handles revokes; is there an estimated eta for when this functionality will be available?

The always_apply flag is possibly something that could be used for one of our use cases. Thanks for sharing the details for this flag, I’ll do some testing on this.

[sfc-gh-jcieslak]

Unfortunately, there's no eta for this. Currently, we're mostly focused on making the essential objects stable to prepare them for V1. This and other grant improvements are already in our backlog, so we'll get there eventually, but I'm not sure when. At the moment, you can try to setup unsafe_execute to revoke all privileges on every apply (we don't have always_apply flag there, but I'm guessing there are some tricks in Terraform that would allow that) and apply them with snowflake_grant_privileges_to_account_role and always_apply flag (remember about correct dependency), but that creates a window where roles are not granted with any privilege causing some outage which is not ideal.

[tabu69]

unsafe_execute, fantastic! I wasn’t aware of this functionality. This is similar to original behaviour I had prior to migrating to the new resource. I will certainly test this, thank you!

Also thank you for sharing the links for essential objects and path to V1, super exciting to see the steps towards the first major release 🙂.