GRANT SELECT TO ALL

[aaronsteers]

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

I'm not sure how to replicate my existing snowflake grants into terraform without access to an option for ALL TABLES IN SCHEMA/DATABASE.

Describe the solution you'd like

Just as "future" tables/views are supported in grants, the request would be to support granting access to "all" tables/views.

Describe alternatives you've considered

I considered creating a "task" resource to manually declare the grants - however, that "task" resource appears to be missing an "undo_sql_statement" to provide an "undo"/"destroy" capability, for instance when permission is changed/revoked.

Additional context

Add any other context or screenshots about the feature request here.

[tnightengale]

Would love to help implement this!

[JimFawkes]

It would be great to have this!

[a2m1]

Would be great to have GRANT < PRIVILEGE> ON ALL TABLES/VIEWS/SCHEMAS

[JavierLopezT]

Having this would be awesome

[juangesino]

+1 on the awesomeness of having something like this 👍

[gnilrets]

+1 - I can specify that I want to grant select on all future tables, but not all existing ones!

Would like the same functionality applied to snowflake_schema_grant too (e.g., grant usage on all schemas in database blah)

[aidanmelen]

Hey @tnightengale, @JimFawkes, @a2m1, @JavierLopezT, @juangesino , and @gnilrets... I just created a provider that allows us to declare GRANT ALL statements in Terraform! Here is a code snippet from the terraform-provider-snowsql/examples/simple/main.tf.

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = <<-EOT
    GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements = <<-EOT
    REVOKE ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    REVOKE ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    EOT
  }
}

Check out the terraform-provider-snowsql snowsql_exec resource documentation for more information.

[zack-klein]

+1 this would be fantastic

[ricardobf]

Any news about this one?

[vlucas-caylent]

@aidanmelen

Hey @tnightengale, @JimFawkes, @a2m1, @JavierLopezT, @juangesino , and @gnilrets... I just created a provider that allows us to declare GRANT ALL statements in Terraform! Here is a code snippet from the terraform-provider-snowsql/examples/simple/main.tf.

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = <<-EOT
    GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements = <<-EOT
    REVOKE ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    REVOKE ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    EOT
  }

  delete_on_create = true
}

Check out the terraform-provider-snowsql snowsql_exec resource documentation for more information.

There is no possible use a list ? Like in null_resources with for_each and triggers? I want to use the snowsql with more than 130 commands.

[aidanmelen]

There is no possible use a list ?

You could create snowflake tables using a list and a for_each loop. But that doesn't seem fun to manage. Also you would have to manually update the list for newly created tables.

Like in null_resources with for_each and triggers?

The local_exec null_resource would work to run Snowsql commands. However, in addition to having terraform installs, operators would also have to ensure that Snowsql is install and configured correctly. You would also not have any logic for deletion, so all the snowflake objects created with the null resource would have to be manually destroyed.

I want to use the snowsql with more than 130 commands.

The snowsql provider uses the same golang lib as the snowflake provider. I not familiar with any limits on how many statements you provide for a single transaction.

Cheers! hope that helps

[vlucas-caylent]

There is no possible use a list ?

You could create snowflake tables using a list and a for_each loop. But that doesn't seem fun to manage. Also you would have to manually update the list for newly created tables.

Like in null_resources with for_each and triggers?

The local_exec null_resource would work to run Snowsql commands. However, in addition to having terraform installs, operators would also have to ensure that Snowsql is install and configured correctly. You would also not have any logic for deletion, so all the snowflake objects created with the null resource would have to be manually destroyed.

I want to use the snowsql with more than 130 commands.

The snowsql provider uses the same golang lib as the snowflake provider. I not familiar with any limits on how many statements you provide for a single transaction.

Cheers! hope that helps

Yea, i'm using null_resource with local-exec snowsql commands, i receive a list using for_each and formatlist for db,schema,roles. My guess is about your provider, i'm trying to create a list and for_each to run in sequence in create statements. But its seems its not possible.

My idea is something like this:
[According the documentation is not possible].

I just start this discussion, because i'm trying to migrate to your provider aswell.

resource "snowsql_exec" "dcl" {
  name = local.name
  for_each = snowflake_table_grant.update_schema_future
  triggers = {
    database  = each.value.database_name
    schema    = each.value.schema_name
    roles     = join(",", each.value.roles)
    }  
  create {
    statements = <<-EOT
    GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT, REFERENCES ON ALL VIEWS IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE %s", self.triggers.roles;
    GRANT SELECT, REFERENCES ON ALL VIEWS IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE %s", self.triggers.roles;
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE %s", self.triggers.roles;
    EOT
  }
  delete {
    statements = <<-EOT
    GRANT UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA ${self.triggers.database_name} TO ROLE %s" ${self.triggers.roles};
    GRANT UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN DATABASE ${self.triggers.database_name}.${self.triggers.schema} TO ROLE %s ${self.triggers.roles};
    EOT
  }
}

[aidanmelen]

According the documentation is not possible

no it is definitely supported and encouraged. Could you please create an issue in the snowsql provider? I would be able to help more over there. Somebody else might have the same question as you and I would like scope this to the snowsql project if possible.

[vlucas-caylent]

@aidanmelen Done. Thanks bro

[iamontheinet]

Can we close this issue?

[cybdeen]

+1, currently working around this using data sources + for_each and it's quite tedious

[kanomaxb]

Can we close this issue?

No, please, we want the feature, not workarounds.

[tursid]

Hi what is the status of this issue?
Snowflake documentation supports using grant ALL privileges, it would be great if terraform worked with this aswell.

[sfc-gh-asawicki]

We are closing this issue as part of a cleanup described in announcement. If you believe that the issue is still valid in v0.89.0, please open a new ticket.