Support for grant ownership on role

[ericmjalbert]

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

I'm setting up my Snowflake account with a Vault Database Engine to manage the user passwords for applications. As part of this solution I need to grant ownership on role ROLENAME to role VAULTROLE.

I don't think there is a way to currently do this with the terraform snowflake provider.

Describe the solution you'd like

I'd like to see a new resource that lets you define role ownership to a specific user. Maybe something like a new resource named snowflake_ownership_grant that covers the functionality of https://docs.snowflake.com/en/sql-reference/sql/grant-ownership.html.

It might looks something like:

resource snowflake_role_ownership_grant grant {
  on_role = "ROLENAME"
  to_role = "VAULTROLE"

  current_grants = "COPY|REVOKE"
}

Describe alternatives you've considered

I haven't thought of any alternatives yet. I think I'll be able to solve this using unrelated automation scripts, but those exist outside the terraform ecosystem.

Additional context

NA

[yohei1126]

Have you tried role_grants? https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/role_grants

[ericmjalbert]

I don't think that snowflake_role_grants can do this since it only does the GRANT ROLE ... type sql (see source code) . This feature request is specifically for the granting of OWNERSHIP.

[malonsocasas]

I have a similar issue here.

An alternative proposal, although I don't know if this completely fixes ericmjalbert's problem: would it be possible to specify parent role when creating a new role with snowflake_role

Something like :

resource snowflake_role role {
  name    = "role1"
  comment = "A role."
  parent_role = "parentrole1"
}

[PChambino]

My use case for this is that I want role A (created by terraform) to be owned by role B so that role B can grant role A to users without having to give MANAGE GRANTS at the account level to role B.

The lack of support for the GRANT OWNERSHIP ON ROLE A TO ROLE B statement prevents to do this with terraform.

[KulkarniAbhishek]

I have a similar request to give the ownership of one role to another in Snowflake.

The query looks like this:
grant ownership on role ROLE_1 to role ROLE_2;

Is there a way to do this in Terraform?

[jesusenlanet]

Same issue here, as @PChambino explain, I'm dealing with the same scenario.

[aidanmelen]

As @chrisluedtke pointed out, the snowsql provider can be used as a workaround solution for granting role ownership.

terraform {
  required_version = ">= 0.13.0"

  required_providers {
    snowflake = {
      source  = "chanzuckerberg/snowflake"
      version = ">= 0.25.18"
    }

    snowsql = {
      source  = "aidanmelen/snowsql"
      version = ">= 0.3.0"
    }
  }
}

provider "snowflake" {}
provider "snowsql" {}

resource "snowflake_role" "role" {
  name = "ROLENAME"
}

resource "snowflake_role" "vault" {
  name = "VAULTROLE"
}

resource "snowsql_exec" "grant_ownership_on_vault_role_to_role" {
  name = "grant_ownership_on_vault_role_to_role"

  create {
    statements = <<-EOT
    GRANT OWNERSHIP ON ROLE ${snowflake_role.vault.name} TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements = <<-EOT
    GRANT OWNERSHIP ON ROLE ${snowflake_role.vault.name} TO ROLE ACCOUNTADMIN;
    EOT
  }
}

[aidanmelen]

I think @ericmjalbert has the right idea. A new snowflake_role_ownership_grant resource would make the most sense.

[aidanmelen]

new PR: #917

[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.