[Bug]: default_secondary_roles_option incompatible with BCR-1692 empty list

[coleheflin-gt]

Terraform CLI Version

1.10.4

Terraform Provider Version

0.100.0

Company Name

Gametime

Terraform Configuration

resource "snowflake_user" "example" {
  name                      = "example_user"
  default_secondary_roles_option = "NONE"  // This becomes NULL, but we need []
}

// What we need to support:
resource "snowflake_user" "admin_user" {
  name                      = "admin_user"
  default_secondary_roles   = []  // Currently impossible with provider
}

Category

category:resource

Object type(s)

resource:user

Expected Behavior

The provider should support setting default_secondary_roles to an empty list [] to accommodate BCR-1692 requirements, particularly for admin users who need to prevent the automatic conversion of NULL to ['ALL'].

Actual Behavior

The provider only supports three string options for default_secondary_roles_option:

"DEFAULT"
"NONE" (translates to NULL in Snowflake)
"ALL"

This makes it impossible to set an empty list [], which is distinct from NULL and necessary for BCR-1692 compliance.

Steps to Reproduce

Configure a Snowflake user with admin roles
Attempt to set default_secondary_roles to an empty list
Observe that the provider only allows string options, none of which translate to an empty list in Snowflake

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

This issue ideally needs resolution before February 2025 when BCR-1692 becomes generally available and automatically enabled for all Snowflake accounts. Without this capability, admins will always have ALL roles active, making it difficult to verify individual role permissions. Current workaround requires managing this setting outside of Terraform, however, this is not ideal since it's harder to maintain over time as we add/remove users.

https://community.snowflake.com/s/article/default-secondary-roles-all-overview-and-additional-explanations?mkt_tok=MjUyLVJGTy0yMjcAAAGYD-UFmpgGrNBZ_5dYnBoTOd61JPESiXOSoa0vOewMaknYlrHL9gGMuCCON7912_Nbev_rtx-s0DKat5T-J9XwW0DWYsIZiV1RdXQ0J7dOlKJONPPtCQ#f1

https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/user#default_secondary_roles_option-1

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @coleheflin-gt 👋
Thank you for reporting the issue. Could you please double-check? I just tested the user resource and the "NONE" value and it seemed correct when migrating into the 2024_07. The general mapping is that (in configuration -> in Snowflake):

• "NONE" -> []
• ALL -> ["ALL"]
• DEFAULT -> default on Snowflake side

So, the only way to automatically change from null to all is to use the "DEFAULT" option. After enabling the BCR bundle, the default will adapt from the old default (null) to the new one (["ALL"]). Other options set explicitly will stay as they are (the bundle state disabled or enabled should not matter).

I tested it manually, but we also have automated tests that check this exact behavior:

terraform-provider-snowflake/pkg/resources/user_acceptance_test.go

Line 1254 in 61ee1bd

objectassert.User(t, userId).HasDefaultSecondaryRoles(`[]`),

terraform-provider-snowflake/pkg/sdk/testint/users_integration_test.go

Line 2246 in 61ee1bd

require.Equal(t, "[]", userDetails.DefaultSecondaryRoles.Value)

[coleheflin-gt]

I updated my user in our staging account to set default_secondary_roles_option = "NONE" and then ran SHOW USERS and the default_secondary_roles value still shows as null instead of an emtpy list. See below for a snippet of my state file.

➜ terragrunt state show 'module.staging-snowflake_account.module.snowflake_user["COLEHEFLIN"].module.user.snowflake_user.user'
Alias tip: tg state show 'module.staging-snowflake_account.module.snowflake_user["COLEHEFLIN"].module.user.snowflake_user.user'
# module.staging-snowflake_account.module.snowflake_user["COLEHEFLIN"].module.user.snowflake_user.user:
resource "snowflake_user" "user" {
    abort_detached_query                          = false
    autocommit                                    = true
    binary_input_format                           = "HEX"
    binary_output_format                          = "HEX"
    client_memory_limit                           = 1536
    client_metadata_request_use_connection_ctx    = false
    client_prefetch_threads                       = 4
    client_result_chunk_size                      = 160
    client_result_column_case_insensitive         = false
    client_session_keep_alive                     = false
    client_session_keep_alive_heartbeat_frequency = 3600
    client_timestamp_type_mapping                 = "TIMESTAMP_LTZ"
    comment                                       = "Data Engineer"
    date_input_format                             = "AUTO"
    date_output_format                            = "YYYY-MM-DD"
    default_namespace                             = "GAMETIME.PUBLIC"
    default_role                                  = "ACCOUNTADMIN"
    default_secondary_roles_option                = "NONE"

Is this because we aren't on the 2024_08 release yet? Here's the results of the bundle we are on in staging:

select SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES()
[{"name":"2024_07","isDefault":true,"isEnabled":true},{"name":"2024_08","isDefault":false,"isEnabled":false}]

[coleheflin-gt]

hm, so I ended up forcing all users to have default_secondary_roles_option = "ALL" and then I set default_secondary_roles_option = "NONE" and that worked as expected resulting in default_seconardy_roles = []!

Must've been something weird about these users being created prior to the changes you linked above and their default_secondary_roles_option were already set to NONE. Lmk if there's anything else you want me to run on this, otherwise I'm good to close this out.