snowflake_account_grant fails silently

[plaisted]

Provider Version

0.25.18

Terraform Version

1.0.4

Describe the bug

The resource snowflake_account_grant fails silently if not enough permissions are held.

Expected behavior

An error would be thrown if it is not successful.

Additional context

Certain account grants require the ACCOUNTADMIN role (see https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html). If terraform is run with lower roles, running GRANT EXECUTE TASK ON ACCOUNT TO ROLE "EXAMPLE"; (for example) leads to a successful SQL result with 1 row => STATUS='Grant not executed: Insufficient privileges.'. The snowflake provider reports this as a success as the command completed.

[alldoami]

Can you give an example of the terraform you're trying to create?

[plaisted]

We use TF CDK so looks like

new snow.AccountGrant(scope, `grant_${dbName}-owner-exec`, {
  roles: [db_owner.name],
  privilege: "EXECUTE TASK"
});

but on plan:

  # snowflake_account_grant.grant_TEST_DB-owner-exec will be updated in-place
  ~ resource "snowflake_account_grant" "grant_TEST_DB-owner-exec" {
        id                = "ACCOUNT|||EXECUTE TASK|false"
      ~ roles             = [
          + "OWNER_TEST_DB",
        ]
        # (2 unchanged attributes hidden)
    }

seems to happen on any of the account grants that require ACCOUNTADMIN if run without.

[markandrus]

I've also just run into this issue.

[lloydbranch]

I've also just ran into this issue while running on 0.31.0.

[stkbailey]

Also hit this

[giorgosp]

This happens when granting roles too.

Examples:

myuser#(no warehouse)@(no database).(no schema)>grant role ROLE1 to role ACCOUNTADMIN;
+----------------------------------------------+                                
| status                                       |
|----------------------------------------------|
| Grant not executed: Insufficient privileges. |
+----------------------------------------------+

myuser#(no warehouse)@(no database).(no schema)>desc role USERADMIN;
003001 (42501): SQL access control error:                                       
Insufficient privileges to operate on role 'USERADMIN'
myuser#(no warehouse)@(no database).(no schema)>grant role USERADMIN to role ROLE1;
+----------------------------------------------+                                
| status                                       |
|----------------------------------------------|
| Grant not executed: Insufficient privileges. |
+----------------------------------------------+

[alex-ld]

Using 0.63, same issue. In fact, my Snowflake UI shows the same silent failure message (successful query but failed to grant).

[frsann]

I had a short discussion about this with Snowflake support, and the behaviour of the query state is indeed "by design". They explained it as the situation that is occurring is an "exception", instead of an "error". "Errors" cause the query to be marked as "failed", but "exceptions" apparently not.

This is also somewhat problematic from an auditing point of view (who tried to change what, and was it successful), but that's beside the point in this case.

What makes this even more irritating is that there does not seem to exist a very clear list of which account grants require the escalated privileges, at least not in the first hit on Google.

[sfc-gh-kfang]

This issue is addressed permanently in the 2023_05 bundle. Going forward, an explicit error will be thrown if a grant command can't go through due to insufficient privileges.

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