[Bug]: Provider produced inconsistent result after apply (snowflake_grant_ownership)

[jonathanneo]

Terraform CLI Version

1.9.0

Terraform Provider Version

0.99.0

Company Name

No response

Terraform Configuration

resource "snowflake_database" "database" {
  name = "MY_PROJECT_DB"
}

resource "snowflake_schema" "my_schema" {
  name = "MY_SCHEMA"
  database = snowflake_database.database.fully_qualified_name
}

resource "snowflake_database_role" "my_schema_reader" {
  name = "MY_SCHEMA_READER"
  database = snowflake_database.database.fully_qualified_name
}

resource "snowflake_grant_ownership" "change_schema_ownership" {
  database_role_name = snowflake_database_role.my_schema_reader.fully_qualified_name
  on {
    object_type = "SCHEMA"
    object_name = snowflake_schema.my_schema.fully_qualified_name
  }
  outbound_privileges = "REVOKE"
}

# <--- FAILS HERE, CANNOT MOVE ON .... 

resource "snowflake_grant_database_role" "grant_usage_on_database_role" {
  depends_on = [snowflake_grant_ownership.change_schema_ownership]
  database_role_name = snowflake_database_role.my_schema_reader.fully_qualified_name
  parent_role_name = "MY_DEPLOYMENT_ROLE"
}

Category

category:grants

Object type(s)

No response

Expected Behavior

I expect the role ownership transfer to execute successfully to transfer ownership from the deployment role to the database role (MY_DATABASE.MY_SCHEMA_READER).

I have tested this in SQL, and it works:

create database MY_PROJECT_DB;

create schema MY_PROJECT_DB.MY_SCHEMA;

create database role MY_PROJECT_DB.MY_SCHEMA_READER;

grant ownership on schema MY_PROJECT_DB.MY_SCHEMA to database role MY_PROJECT_DB.MY_SCHEMA_READER revoke current grants; 

show grants on schema my_project_db.my_schema; -- FAILED, AS EXPECTED. 

grant database role MY_PROJECT_DB.MY_SCHEMA_READER to role MY_DEPLOYMENT_ROLE;

show grants on schema my_project_db.my_schema; -- SUCCESS! 

Actual Behavior

│ Error: Provider produced inconsistent result after apply
│ 
│ When applying changes to snowflake_grant_ownership.change_schema_ownership, provider "provider[\"registry.terraform.io/snowflake-labs/snowflake\"]"
│ produced an unexpected new value: Root object was present, but now absent.
│ 
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.

In Snowflake Query History, the failure is caused by this query:

SHOW GRANTS ON SCHEMA "MY_PROJECT_DB"."MY_SCHEMA" --terraform_provider_usage_tracking {"json_schema_version":"1","version":"v0.99.0","resource":"snowflake_grant_ownership","operation":"create"}

Since I have just changed the ownership of the schema to a different role, the deployment role doesn't have permissions to execute SHOW GRANTS on the schema.

I plan to do GRANT DATABASE ROLE to the deployment role later on, so that the deployment role can USE the database role. But the SHOW GRANTS from resource "snowflake_grant_ownership" is causing the error and preventing me from doing so.

Steps to Reproduce

Use the terraform code provided above.

Run terraform apply, and the bug will reproduced.

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[jonathanneo]

If I change outbound_privileges = 'COPY', I get a different error.

Code:

resource "snowflake_grant_ownership" "change_schema_ownership" {
  database_role_name = snowflake_database_role.my_schema_reader.fully_qualified_name
  on {
    object_type = "SCHEMA"
    object_name = snowflake_schema.my_schema.fully_qualified_name
  }
  outbound_privileges = "COPY"
}

Error:

│ Error: An error occurred during grant ownership
│ 
│   with snowflake_grant_ownership.change_schema_ownership,
│   on main.tf line 39, in resource "snowflake_grant_ownership" "change_schema_ownership":
│   39: resource "snowflake_grant_ownership" "change_schema_ownership" {
│ 
│ Id: ToDatabaseRole|"MY_PROJECT_DB"."MY_SCHEMA_READER"|COPY|OnObject|SCHEMA|"MY_PROJECT_DB"."MY_SCHEMA"
│ Error: 003001 (42501): SQL access control error:
│ Insufficient privileges to operate on grant ownership 'with copy current grants'

I am getting the same error even if I execute the SQL code directly:

GRANT OWNERSHIP ON SCHEMA "MY_PROJECT_DB"."MY_SCHEMA" TO DATABASE ROLE "MY_PROJECT_DB"."MY_SCHEMA_READER" COPY CURRENT GRANTS

This appears to be because the deployment role needs to have the MANAGE GRANTS privilege on the Account in order to use with copy current grants according to Snowflake docs: "This parameter requires that the role that executes the GRANT OWNERSHIP command have the MANAGE GRANTS privilege on the account." https://docs.snowflake.com/en/sql-reference/sql/grant-ownership#optional-parameters

[sfc-gh-jcieslak]

Hey @jonathanneo
This seems more like a general usage error rather than a bug. You are right about MANAGE GRANTS. I ran the example config, and it worked fine with the ACCOUNTADMIN role that has implicit MANAGE GRANTS granted. One fix for this issue is to simply use the ACCOUNTADMIN role. Another one would be to adjust the order in which the configuration is run by adjusting the dependencies between the resources. The order should be to first, grant the role to the current role (so the access won't be lost after losing ownership), then grant the ownership. I guess the third one would be the grant currently running role with MANAGE GRANTS, but adjusting the order could also be needed in this case. Please, let me know if that helps.

[jonathanneo]

The order should be to first, grant the role to the current role (so the access won't be lost after losing ownership), then grant the ownership.

Ah yes, of course! Why didn't I think of that 😓 I've swapped the code to below, and it works 👏

# Grant usage first! 
resource "snowflake_grant_database_role" "grant_usage_on_database_role" {
  database_role_name = snowflake_database_role.my_schema_reader.fully_qualified_name
  parent_role_name = "MY_DEPLOYMENT_ROLE"
}

# Then change ownership
resource "snowflake_grant_ownership" "change_schema_ownership" {
  database_role_name = snowflake_database_role.my_schema_reader.fully_qualified_name
  on {
    object_type = "SCHEMA"
    object_name = snowflake_schema.my_schema.fully_qualified_name
  }
  outbound_privileges = "REVOKE"
}

[sfc-gh-jcieslak]

Great to hear :)
As the issue was solved, I'm closing the thread.