Skip to content

User

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with user:

  • Manage the lifecycle of new and existing users
  • Manage the grants of a user

Usage

- user:
    name: <user-name>
    <configuration-key>: <value>
users:
  <user-name>:
    <configuration-key>: <value>

Supported parameters

The engine supports the parameters listed below.

Configuration Key Required/Optional Data Types and Values Description
abort_detached_query Optional Boolean Specifies whether to abort detached queries when they fail
autocommit Optional Boolean Specifies whether autocommit is enabled for the user session
binary_input_format Optional String: HEX, BASE64, UTF8 Specifies the input format for binary string values
binary_output_format Optional String: HEX, BASE64 Specifies the output format for binary string values
client_memory_limit Optional Integer Specifies the memory limit (in MB) for client connections
client_metadata_request_use_connection_ctx Optional Boolean Specifies whether to use connection context for metadata requests
client_prefetch_threads Optional Integer Specifies the number of threads used for prefetching query results
client_result_chunk_size Optional Integer Specifies the chunk size for client result sets
client_result_column_case_insensitive Optional Boolean Specifies whether client result column names are case-insensitive
client_timestamp_type_mapping Optional String: TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ Specifies how timestamps are mapped for client connections
comment Optional String Specifies a comment for the user
date_input_format Optional String Specifies the input format for date values (e.g., YYYY-MM-DD)
date_output_format Optional String Specifies the output format for date values (e.g., YYYY-MM-DD)
default_namespace Optional String/Object: See here for a definition of default_namespace Specifies the namespace, database only or database and schema, that is active by default for the user's session upon login
default_role Optional String Specifies the role that is active by default for the user's session upon login
default_secondary_roles_option Optional String: DEFAULT, ALL, NONE Specifies which secondary roles are activated by default when the user logs in
default_warehouse Optional String Specifies the virtual warehouse that is active by default for the user's session upon login
deleted Optional Boolean: True enables deletion prevention, False does nothing Specifies what objects are allowed to be deleted
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this user depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
disable_mfa Optional Boolean Specifies whether multi-factor authentication is disabled for the user
disabled Optional Boolean Specifies whether the user is disabled
display_name Optional String Name displayed for the user in the Snowflake web interface
email Optional String Email address for the user
enable_unload_physical_type_optimization Optional Boolean Specifies whether to enable physical type optimization for unload operations
enable_unredacted_query_syntax_error Optional Boolean Specifies whether to show unredacted query syntax errors
environment Optional String Specifies the environment in which the user is managed. Regex can be provided as well.
error_on_nondeterministic_merge Optional Boolean Specifies whether to raise an error when nondeterministic merge operations are detected
error_on_nondeterministic_update Optional Boolean Specifies whether to raise an error when nondeterministic update operations are detected
first_name Optional String First name of the user
geography_output_format Optional String: WKB, WKT, EWKB, EWKT, GeoJSON Specifies the output format for geography values
geometry_output_format Optional String: WKB, WKT, EWKB, EWKT, GeoJSON Specifies the output format for geometry values
jdbc_treat_decimal_as_int Optional Boolean Specifies whether JDBC treats DECIMAL values as integers when scale is 0
jdbc_treat_timestamp_ntz_as_utc Optional Boolean Specifies whether JDBC treats TIMESTAMP_NTZ as UTC
jdbc_use_session_timezone Optional Boolean Specifies whether JDBC uses session timezone for timestamp operations
json_indent Optional Integer Specifies the number of spaces for JSON indentation
last_name Optional String Last name of the user
lock_timeout Optional Integer Specifies the lock timeout in seconds
log_level Optional String: TRACE, DEBUG, INFO, WARN, ERROR, FATAL, OFF Specifies the log level for the user session
login_name Optional String Name that the user enters to log into the system. Login names for users must be unique across your entire account.
manage_mode Optional String: all (default), none Configures what properties to manage for the user.
See Changing Manage Mode before changing the value.
middle_name Optional String Middle name of the user
multi_statement_count Optional Integer Specifies the number of statements to execute in a multi-statement transaction
must_change_password Optional Boolean Specifies whether the user is forced to change their password on the next login (including their first/initial login) into the system
namespacing Optional String: both (default), none, prefix, suffix Specifies whether prefix or suffix or both are to be added to user name
network_policy Optional String: SOLE managed and preexisting non-managed network_policy names Attaches a network policy to the user
noorder_sequence_as_default Optional Boolean Specifies whether to use NOORDER as the default for sequences
odbc_treat_decimal_as_int Optional Boolean Specifies whether ODBC treats DECIMAL values as integers when scale is 0
password Optional String The password for the user must be enclosed in single or double quotes. If no password is specified, the user cannot log into Snowflake until a password has been explicitly specified for them.
prevent_unload_to_internal_stages Optional Boolean Specifies whether to prevent unload operations to internal stages
query_tag Optional String Specifies a tag to be added to queries executed by the user
quoted_identifiers_ignore_case Optional Boolean Specifies whether quoted identifiers should ignore case
rows_per_resultset Optional Integer Specifies the maximum number of rows returned in a result set
rsa_public_key Optional String Specifies the user's RSA public key, used for key-pair authentication
rsa_public_key_2 Optional String Specifies the user's second RSA public key, used to rotate the public and private keys for key-pair authentication based on an expiration schedule set by your organization
s3_stage_vpce_dns_name Optional String Specifies the DNS name for S3 VPC endpoint used for accessing S3 stages
search_path Optional String Specifies the search path for resolving unqualified object names
simulated_data_sharing_consumer Optional String Specifies the simulated data sharing consumer account identifier
statement_queued_timeout_in_seconds Optional Integer Specifies the timeout (in seconds) for queued statements
statement_timeout_in_seconds Optional Integer Specifies the timeout (in seconds) for statement execution
strict_json_output Optional Boolean Specifies whether to enforce strict JSON output format
time_input_format Optional String Specifies the input format for time values (e.g., HH24:MI)
time_output_format Optional String Specifies the output format for time values (e.g., HH24:MI)
timestamp_day_is_always_24h Optional Boolean Specifies whether timestamp day is always treated as 24 hours
timestamp_input_format Optional String Specifies the input format for timestamp values (e.g., YYYY-MM-DD HH24:MI:SS)
timestamp_ltz_output_format Optional String Specifies the output format for TIMESTAMP_LTZ values
timestamp_ntz_output_format Optional String Specifies the output format for TIMESTAMP_NTZ values
timestamp_output_format Optional String Specifies the output format for timestamp values (e.g., YYYY-MM-DD HH24:MI:SS)
timestamp_type_mapping Optional String: TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ Specifies the default timestamp type mapping
timestamp_tz_output_format Optional String Specifies the output format for TIMESTAMP_TZ values
timezone Optional String Specifies the timezone for the user (e.g., Europe/Warsaw, America/New_York)
trace_level Optional String: ALWAYS, ON_EVENT, OFF Specifies the trace level for debugging
transaction_abort_on_error Optional Boolean Specifies whether transactions abort on error
unsupported_ddl_action Optional String: IGNORE, FAIL Specifies the action to take when encountering unsupported DDL
use_cached_result Optional Boolean Specifies whether to use cached query results when available
user_parameter_client_session_keep_alive Optional Boolean Specifies whether to keep client sessions alive
user_parameter_client_session_keep_alive_heartbeat_frequency Optional Integer Specifies the frequency (in seconds) of client heartbeats to keep the session alive. Must be set with user_client_session_keep_alive: true.
user_type Optional String: USER (default), LEGACY_SERVICE, SERVICE Specifies the user type in Snowflake
week_of_year_policy Optional Integer: 0, 1 Specifies the week of year policy (0 = Legacy, 1 = ISO)
week_start Optional Integer: 0-7 Specifies which day is considered the start of the week (0 = Sunday, 1 = Monday, etc.)

User Types

Snowflake now supports the concept of a user type. The type determines if the user can log in without using MFA, and with a password. Users can now specify the type of a user in SOLE using the user_type parameter.

- user:
    name: DATAOPS_USER_1
    user_type: USER
    login_name: DATAOPS_USER_1

- user:
    name: DATAOPS_USER_2
    user_type: LEGACY_SERVICE
    login_name: DATAOPS_USER_2

- user:
    name: DATAOPS_USER_3
    user_type: SERVICE
    login_name: DATAOPS_USER_3

- user:
    name: DATAOPS_USER_4
    login_name: DATAOPS_USER_4 #user_type: USER is the default value

If user_type is SERVICE

Behaviour changes:

  • They cannot log in using a password.
  • They cannot log in using SAML SSO.
  • They cannot enroll in MFA.
  • They are not subject to authentication policy MFA enforcement.

They cannot have the following parameters:

  • first_name
  • middle_name
  • last_name
  • password
  • must_change_password
  • disable_mfa

default_namespace parameter

You can specify the name of default_namespace if it belongs to the same schema and database as the user or the name of the schema and database.

This parameter supports the following parameters if explicitly provided:

Configuration Key Required/Optional Data Types and Values Description
database Optional String Name of the database
schema Optional String Name of the schema

Examples

default_namespace:
  database: "<database-name>"
  schema: "<schema-name>"

Examples

- user:
    name: SAM
    comment: "management"
    login_name: "user_login"
    password: "user_login"
    disabled: false
    display_name: "manager"
    email: "user@example.com"
    first_name: "user"
    last_name: "login"
    must_change_password: true
    default_namespace:
      schema: rel(schema.<schema-name>)
    default_warehouse: rel(warehouse.<warehouse-name>)
    default_role: "role"
    rsa_public_key: "..."
    rsa_public_key_2: "..."
    network_policy: rel(network_policy.<network-policy-name>)
users:
  SAM:
    comment: "management"
    login_name: "user_login"
    password: "user_login"
    disabled: false
    display_name: "manager"
    email: "user@example.com"
    first_name: "user"
    last_name: "login"
    must_change_password: true
    default_namespace:
      database: "<database-name>"
      schema: "<schema-name>"
    default_warehouse: "<warehouse-name>"
    default_role: "role"
    rsa_public_key: "..."
    rsa_public_key_2: "..."
    network_policy: "<network-policy-name>"

Advanced Configuration Examples

The following examples demonstrate comprehensive user configurations with advanced parameters:

- user:
    name: DATAOPS_Us_2
    comment: "User Login for Dataops Test User1"
    login_name: "User_2_6884"
    email: "test1@gmail.com"
    disabled: false
    must_change_password: false
    display_name: "Test User 1"
    first_name: "Test1"
    middle_name: "middel_name1"
    last_name: "User1"
    default_warehouse: rel(warehouse.COMPUTE_WH)
    default_namespace: "Temp1"
    default_role: "ACCOUNTADMIN"
    default_secondary_roles_option: "ALL"
    disable_mfa: true
    abort_detached_query: true
    autocommit: false
    multi_statement_count: 1
    query_tag: "eueueueu"
    statement_queued_timeout_in_seconds: 150
    statement_timeout_in_seconds: 230
    transaction_abort_on_error: false
    date_input_format: "YYYY-MM-DD"
    date_output_format: "YYYY-MM-DD"
    time_input_format: "HH24:MI"
    time_output_format: "HH24:MI"
    timestamp_input_format: "YYYY-MM-DD"
    timestamp_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_ltz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_ntz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_tz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_type_mapping: "TIMESTAMP_LTZ"
    timestamp_day_is_always_24h: true
    timezone: "Europe/Warsaw"
    binary_input_format: "UTF8"
    binary_output_format: "BASE64"
    geography_output_format: "WKB"
    geometry_output_format: "WKB"
    json_indent: 4
    strict_json_output: true
    quoted_identifiers_ignore_case: true
    week_start: 1
    week_of_year_policy: 1
    client_memory_limit: 1024
    client_metadata_request_use_connection_ctx: true
    client_prefetch_threads: 2
    client_result_chunk_size: 48
    client_result_column_case_insensitive: true
    client_timestamp_type_mapping: "TIMESTAMP_NTZ"
    jdbc_treat_decimal_as_int: false
    jdbc_treat_timestamp_ntz_as_utc: true
    jdbc_use_session_timezone: false
    odbc_treat_decimal_as_int: true
    enable_unload_physical_type_optimization: false
    lock_timeout: 21222
    rows_per_resultset: 2
    s3_stage_vpce_dns_name: "vpce-id.s3.region.vpce.amazonaws.com"
    search_path: "$public, $current"
    use_cached_result: false
    prevent_unload_to_internal_stages: true
    noorder_sequence_as_default: false
    error_on_nondeterministic_merge: false
    error_on_nondeterministic_update: true
    enable_unredacted_query_syntax_error: true
    unsupported_ddl_action: "FAIL"
    log_level: "ERROR"
    trace_level: "ON_EVENT"
    simulated_data_sharing_consumer: "some_consumer"
    user_parameter_client_session_keep_alive: true
    user_parameter_client_session_keep_alive_heartbeat_frequency: 2400
users:
  DATAOPS_Us_2:
    comment: "User Login for Dataops Test User1"
    login_name: "User_2_6884"
    email: "test1@gmail.com"
    disabled: false
    must_change_password: false
    display_name: "Test User 1"
    first_name: "Test1"
    middle_name: "middel_name1"
    last_name: "User1"
    default_warehouse: "COMPUTE_WH"
    default_namespace:
      database: "Temp1"
    default_role: "ACCOUNTADMIN"
    default_secondary_roles_option: "ALL"
    disable_mfa: true
    abort_detached_query: true
    autocommit: false
    multi_statement_count: 1
    query_tag: "eueueueu"
    statement_queued_timeout_in_seconds: 150
    statement_timeout_in_seconds: 230
    transaction_abort_on_error: false
    date_input_format: "YYYY-MM-DD"
    date_output_format: "YYYY-MM-DD"
    time_input_format: "HH24:MI"
    time_output_format: "HH24:MI"
    timestamp_input_format: "YYYY-MM-DD"
    timestamp_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_ltz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_ntz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_tz_output_format: "YYYY-MM-DD HH24:MI:SS"
    timestamp_type_mapping: "TIMESTAMP_LTZ"
    timestamp_day_is_always_24h: true
    timezone: "Europe/Warsaw"
    binary_input_format: "UTF8"
    binary_output_format: "BASE64"
    geography_output_format: "WKB"
    geometry_output_format: "WKB"
    json_indent: 4
    strict_json_output: true
    quoted_identifiers_ignore_case: true
    week_start: 1
    week_of_year_policy: 1
    client_memory_limit: 1024
    client_metadata_request_use_connection_ctx: true
    client_prefetch_threads: 2
    client_result_chunk_size: 48
    client_result_column_case_insensitive: true
    client_timestamp_type_mapping: "TIMESTAMP_NTZ"
    jdbc_treat_decimal_as_int: false
    jdbc_treat_timestamp_ntz_as_utc: true
    jdbc_use_session_timezone: false
    odbc_treat_decimal_as_int: true
    enable_unload_physical_type_optimization: false
    lock_timeout: 21222
    rows_per_resultset: 2
    s3_stage_vpce_dns_name: "vpce-id.s3.region.vpce.amazonaws.com"
    search_path: "$public, $current"
    use_cached_result: false
    prevent_unload_to_internal_stages: true
    noorder_sequence_as_default: false
    error_on_nondeterministic_merge: false
    error_on_nondeterministic_update: true
    enable_unredacted_query_syntax_error: true
    unsupported_ddl_action: "FAIL"
    log_level: "ERROR"
    trace_level: "ON_EVENT"
    simulated_data_sharing_consumer: "some_consumer"
    user_parameter_client_session_keep_alive: true
    user_parameter_client_session_keep_alive_heartbeat_frequency: 2400