To allow other users without object’s OWNERSHIP privileges to query, you must grant some privileges using a role. System roles are no exception, so even ACCOUNTADMIN cannot do anything without privileges, so it is recommended to grant the top-level custom role in the hierarchy to SYSADMIN.
# CREATE ROLE ...
resource "snowflake_account_role" "test" {
name = "test"
}
# GRANT USAGE ON SCHEMA ... TO ROLE ...
resource "snowflake_grant_privileges_to_account_role" "grant_testschema_role" {
privileges = ["USAGE", "MONITOR"]
account_role_name = snowflake_account_role.test.name
on_schema {
schema_name = snowflake_schema.test.fully_qualified_name
}
}
# GRANT ROLE ... TO ROLE ...
resource "snowflake_grant_account_role" "test" {
role_name = snowflake_account_role.test.name
parent_role_name = "SYSADMIN"
}
If you grant a role to a user, they can call USE ROLE to execute queries with that privilege. GRANT is required even for default_role.
locals {
users = {
"user1" = { email = "[email protected]", default_role = "ROLE1" }
"user2" = { email = "[email protected]", default_role = "ROLE2", active = false }
}
}
resource "snowflake_user" "users" {
for_each = { for key, user in local.users : key => user if lookup(user, "active", true) }
name = each.key
email = each.value.email
default_role = each.value.default_role
}
# GRANT ROLE ... TO USER ...
resource "snowflake_grant_account_role" "default_role" {
for_each = snowflake_user.users
user_name = each.value.name
role_name = each.value.default_role
}
By the way, there is a issue about how to set the password for a new user. There is a method to pass the initial password in the password field and have the user change it at first login by setting must_change_password = true, but I am concerned that the password that has not been changed will remain in tfstate for longer than expected. Therefore, I tried to automatically send the password reset link that can be issued by ALTER USER <user_name> RESET PASSWORD; using SYSTEM$SEND_EMAIL(), but email address verification for new users was not performed, and it could not be sent to an address that has not been verified, so I gave up. At present, there is no way even to check whether the email has been confirmed or not, so I am looking forward to future improvements. Besides, SAML authentication is supported.
provider "snowflake" {
...
preview_features_enabled = ["snowflake_email_notification_integration_resource"]
}
resource "snowflake_email_notification_integration" "email_integration" {
name = "EMAIL_INTEGRATION"
enabled = true
}
resource "null_resource" "run_procedure_once" {
for_each = snowflake_user.users
triggers = {
email = each.value.email
}
provisioner "local-exec" {
command = <<EOF
snow sql -q "CALL SYSTEM\$SEND_EMAIL(
'EMAIL_INTEGRATION',
'${each.value.email}',
'title'
'content'
);"
EOF
}
}
For ease of management, there are also database roles that are associated with databases. There is no difference from normal roles except that they can be granted only to database roles that must be in the same database.
# CREATE DATABASE ROLE ...
resource "snowflake_database_role" "testdb" {
database = snowflake_database.test.name
name = "TESTDB_ROLE"
}
# GRANT DATABASE ROLE ... TO ROLE ...
resource "snowflake_grant_database_role" "grant_testdb_role" {
database_role_name = snowflake_database_role.testdb.fully_qualified_name
parent_role_name = snowflake_account_role.test.name
}
SCHEMA privileges alone do not allow access to tables, and table privileges must be granted individually. With FUTURE, privileges can be automatically granted for objects that will be created in the future. Besides, object_type_plural receives DYNAMIC, EVENT, EXTERNAL, HYBRID, and ICEBERG TABLES in addition to regular TABLES, and note that privileges do not apply to tables of different types.
# GRANT SELECT ON FUTURE TABLES IN DATABASE ... TO DATABASE ROLE ...
resource "snowflake_grant_privileges_to_database_role" "grant_select_testdb_future_tables" {
privileges = ["SELECT"]
database_role_name = snowflake_database_role.testdb.fully_qualified_name
on_schema_object {
future {
object_type_plural = "TABLES"
in_database = snowflake_database_role.testdb.database
}
}
}