snow_exec gives error on multiple statement

[almekpoh]

Hi,

I keep having this error from time to time, even though I have privilege on this schema and only have 2 statements. What's is weird is that when I retry without changing anything, it works.

Statements: GRANT INSERT ON ALL TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE; GRANT INSERT ON FUTURE TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE;

100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "GRANT INSERT ON FUTURE TABLES ..." (at line 2, position 0). SQL access control error: Insufficient privileges to operate on schema 'TEST' in SYSTEM$MULTISTMT at ' throw Execution of multiple statements failed on statement {0} (at line {1}, position {2})..replace('{1}', LINES[i])' position 4 stackstrace: SYSTEM$MULTISTMT line: 10

Do you have any clue of what is going on ? i didn't have this type of error on version 1.1.0, I have just migrated !

[aidanmelen]

The error seems to suggest that the snowsql provider does not have the correct privileges:

Insufficient privileges to operate on schema 'TEST' in SYSTEM

Please ensure the user or role passed to the snowsql provider has the correct privileges or ownership on the TEST schema.

[almekpoh]

The role does have privileges on every resources to manage grants, so i don't think it comes from there

[aidanmelen]

Three things to investigate:

1. ensure this command works in a snowflake worksheet with the same privileges. It is possible the schema does not exist or you do not have the correct access.
2. try breaking this up into multiple statements with a single command to see if it still fails. This will help determine if this is a problem running ExecContext with multiple statements or not.
3. Fully qualify the statements if you haven't already.

[aidanmelen]

This example works:

terraform {
  required_version = ">= 0.13.0"

  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = ">= 0.56.5"
    }
    snowsql = {
      source  = "aidanmelen/snowsql"
      version = ">= 1.3.3"
    }
    random = ">= 2.1"
  }
}

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

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

resource "snowflake_schema" "schema" {
  name = "TEST"
  database = snowflake_database.database.name
}

resource "snowflake_table" "table" {
  database            = snowflake_schema.schema.database
  schema              = snowflake_schema.schema.name
  name                = "MY_TABLE"

  column {
    name     = "id"
    type     = "int"
  }
}

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

resource "snowsql_exec" "role_grant_all" {
  name = "TERRAFORM_PROVIDER_SNOWSQL_ISSUE_92"

  create {
    statements           = <<-EOT
      GRANT INSERT ON ALL TABLES IN SCHEMA ${snowflake_schema.schema.database}.${snowflake_schema.schema.name} TO ROLE ${snowflake_role.role.name};
      GRANT INSERT ON FUTURE TABLES IN SCHEMA ${snowflake_schema.schema.database}.${snowflake_schema.schema.name} TO ROLE ${snowflake_role.role.name};
    EOT
  }

  read {
    statements           = <<-EOT
      SHOW GRANTS TO ROLE ${snowflake_role.role.name};
      SHOW FUTURE GRANTS TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements           = <<-EOT
      REVOKE INSERT ON ALL TABLES IN SCHEMA ${snowflake_schema.schema.database}.${snowflake_schema.schema.name} FROM ROLE ${snowflake_role.role.name};
      REVOKE INSERT ON FUTURE TABLES IN SCHEMA ${snowflake_schema.schema.database}.${snowflake_schema.schema.name} FROM ROLE ${snowflake_role.role.name};
    EOT
  }
}

output "show_role_grant_all_results" {
  description = "The SnowSQL query results from the read statements."
  value       = jsondecode(nonsensitive(snowsql_exec.role_grant_all.read_results))
}

with output:

Apply complete! Resources: 1 added, 1 changed, 0 destroyed.

Outputs:

show_role_grant_all_results = [
  {
    "created_on" = "2023-04-05T07:11:24.953-07:00"
    "grant_option" = "false"
    "granted_by" = "ACCOUNTADMIN"
    "granted_on" = "TABLE"
    "granted_to" = "ROLE"
    "grantee_name" = "USER_ROLE"
    "name" = "DATABASE.TEST.MY_TABLE"
    "privilege" = "INSERT"
  },
  {
    "created_on" = "2023-04-05T07:11:25.013-07:00"
    "grant_on" = "TABLE"
    "grant_option" = "false"
    "grant_to" = "ROLE"
    "grantee_name" = "USER_ROLE"
    "name" = "DATABASE.TEST.<TABLE>"
    "privilege" = "INSERT"
  },
]

[aidanmelen]

I was able to reproduce the error:

╷
│ Error: failed to execute create statements.
│ 
│ Statements:
│ 
│   GRANT INSERT ON ALL TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE;
│ GRANT INSERT ON FUTURE TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE;
│ 
│ 
│ 100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "GRANT INSERT ON ALL TABLES IN ..." (at line 1, position 0).
│ SQL compilation error:
│ Database 'DATABASE' does not exist or not authorized. in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
│ stackstrace: 
│ SYSTEM$MULTISTMT line: 10
│ 
│   with snowsql_exec.role_grant_all,
│   on main.tf line 44, in resource "snowsql_exec" "role_grant_all":
│   44: resource "snowsql_exec" "role_grant_all" {
│ 
╵

when I provided a statement that referenced a database that either does not exist or I do not have access to (in this case, the database did not exist). Please see the terraform code below that was used to reproduced the error for further insights:

terraform {
  required_version = ">= 0.13.0"

  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = ">= 0.56.5"
    }
    snowsql = {
      source  = "aidanmelen/snowsql"
      version = ">= 1.3.3"
    }
    random = ">= 2.1"
  }
}

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

# resource "snowflake_database" "database" {
#   name = "DATABASE"
# }

# resource "snowflake_schema" "schema" {
#   name = "TEST"
#   database = snowflake_database.database.name
# }

# resource "snowflake_table" "table" {
#   database            = snowflake_schema.schema.database
#   schema              = snowflake_schema.schema.name
#   name                = "MY_TABLE"

#   column {
#     name     = "id"
#     type     = "int"
#   }
# }

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

resource "snowsql_exec" "role_grant_all" {
  name = "TERRAFORM_PROVIDER_SNOWSQL_ISSUE_92"

  create {
    statements           = <<-EOT
      GRANT INSERT ON ALL TABLES IN SCHEMA DATABASE.TEST TO ROLE ${snowflake_role.role.name};
      GRANT INSERT ON FUTURE TABLES IN SCHEMA DATABASE.TEST TO ROLE ${snowflake_role.role.name};
    EOT
  }

  read {
    statements           = <<-EOT
      SHOW GRANTS TO ROLE ${snowflake_role.role.name};
      SHOW FUTURE GRANTS TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements           = <<-EOT
      REVOKE INSERT ON ALL TABLES IN SCHEMA DATABASE.TEST FROM ROLE ${snowflake_role.role.name};
      REVOKE INSERT ON FUTURE TABLES IN SCHEMA DATABASE.TEST FROM ROLE ${snowflake_role.role.name};
    EOT
  }
}

output "show_role_grant_all_results" {
  description = "The SnowSQL query results from the read statements."
  value       = jsondecode(nonsensitive(snowsql_exec.role_grant_all.read_results))
}

[aidanmelen]

Please reach out if you have any other questions.

[almekpoh]

Oh okay, I will check one more time database access privileges in this case, it might be in my dependencies. thank you

[aidanmelen]

Please let me know if that resolved your issue. Thanks

[almekpoh]

Hi @aidanmelen , it seems that it worked, I haven't encounter this error again for now.

[aidanmelen]

Also, it should be noted that the snowflake provider is working to support grant all on schema. In the future, you may want to consider migrating management of supported snowflake objects to the snowflake provider.

Otherwise, continue using snowsql if you prefer more control and the raw sql syntax.

[almekpoh]

thanks for letting me now this. I will keep an eye on this as well.