Categories:
User & Security DDL (Users)

CREATE USER

Creates a new user or replaces an existing user in the system. For more details, see User Management.

Note

Only security and account administrators (users with the SECURITYADMIN or ACCOUNTADMIN role) can create users.

See also:
ALTER USER , DESCRIBE USER , SHOW PARAMETERS

Syntax

CREATE [ OR REPLACE ] USER [ IF NOT EXISTS ] <name>
  [ userProperties ]
  [ sessionParams ]
  [ COMMENT = '<string_literal>' ]

Where:

userProperties ::=
  PASSWORD = '<string>'
  LOGIN_NAME = <string>
  DISPLAY_NAME = <string>
  FIRST_NAME = <string>
  MIDDLE_NAME = <string>
  LAST_NAME = <string>
  EMAIL = <string>
  MUST_CHANGE_PASSWORD = TRUE | FALSE
  DISABLED = TRUE | FALSE
  SNOWFLAKE_SUPPORT = TRUE | FALSE
  DAYS_TO_EXPIRY = <integer>
  MINS_TO_UNLOCK = <integer>
  DEFAULT_WAREHOUSE = <string>
  DEFAULT_NAMESPACE = <string>
  DEFAULT_ROLE = <string>
  EXT_AUTHN_DUO = TRUE | FALSE
  EXT_AUTHN_UID = <string>
  MINS_TO_BYPASS_MFA = <integer>
  DISABLE_MFA = TRUE | FALSE
  RSA_PUBLIC_KEY = <string>
  RSA_PUBLIC_KEY_2 = <string>
sessionParams ::=
  ABORT_DETACHED_QUERY = TRUE | FALSE
  AUTOCOMMIT = TRUE | FALSE
  DATE_INPUT_FORMAT = <string>
  DATE_OUTPUT_FORMAT = <string>
  ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
  ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
  LOCK_TIMEOUT = <num>
  QUERY_TAG = <string>
  ROWS_PER_RESULTSET = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>
  TIMESTAMP_DAY_IS_ALWAYS_24H = TRUE | FALSE
  TIMESTAMP_INPUT_FORMAT = <string>
  TIMESTAMP_LTZ_OUTPUT_FORMAT = <string>
  TIMESTAMP_NTZ_OUTPUT_FORMAT = <string>
  TIMESTAMP_OUTPUT_FORMAT = <string>
  TIMESTAMP_TYPE_MAPPING = <string>
  TIMESTAMP_TZ_OUTPUT_FORMAT = <string>
  TIMEZONE = <string>
  TIME_INPUT_FORMAT = <string>
  TIME_OUTPUT_FORMAT = <string>
  TRANSACTION_DEFAULT_ISOLATION_LEVEL = <string>
  TWO_DIGIT_CENTURY_START = <num>
  UNSUPPORTED_DDL_ACTION = <string>
  USE_CACHED_RESULT = TRUE | FALSE

Required Parameters

name

Identifier for the user; must be unique for your account. Note that the user does not use name to log into Snowflake; instead, the LOGIN_NAME property specifies the login name for the user.

The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Syntax.

Optional Parameters

PASSWORD = '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.

A password can be any case-sensitive string up to 256 characters, including blank spaces and special (i.e. non-alphanumeric) characters, such as exclamation points (!), percent signs (%), and asterisks (*).

Snowflake enforces the following password policy when creating a user, setting a new/initial password for a user, or resetting the existing password for a user:

  • Must be at least 8 characters long.
  • Must contain at least 1 digit.
  • Must contain at least 1 uppercase letter and 1 lowercase letter.

However, this policy only covers the minimum requirements for user passwords. We strongly recommend the following guidelines for creating the strongest passwords possible:

  • Create a unique password for Snowflake (i.e. do not reuse passwords from other systems or accounts).
  • Use more than 8 characters.
  • Include multiple, random mixed-case letters, numbers, and special characters, including blank spaces.
  • Do not use easily-guessed common passwords, names, numbers, or dates.

In addition, to configure the highest level of security for user login, we recommend that users enroll in MFA.

Note that Snowflake does not enforce this policy when a password is initially set for a user using this command or the ALTER USER command. This enables security administrators to decide whether to assign generic, less-secure passwords or specific, stronger passwords to new users. If generic initial passwords are used, we strongly recommend also setting the MUST_CHANGE_PASSWORD property to TRUE to require users to change their password on their next login (including initial login) into Snowflake.

In addition, Snowflake allows creating users without an initial password to support business processes in which new users are not allowed to log into the system. However, as a general rule, Snowflake expects that users are created with initial passwords. In fact, the Snowflake web interface doesn’t allow users to be created without a password.

An empty string ('') or NULL specifies that the user does not have a password.

Default: NULL

LOGIN_NAME = string

Name that the user enters to log into the system. Login names for users must be unique across your entire account.

A login name can be any string, including spaces and non-alphanumeric characters, such as exclamation points (!), percent signs (%), and asterisks (*); however, if the string contains spaces or non-alphanumeric characters, it must be enclosed in single or double quotes. Login names are always case-insensitive.

Snowflake allows specifying different user and login names to enable using common identifiers (e.g. email addresses) for login.

Default: name (i.e. if no value is provided, the user’s identifier is used as the login name)

DISPLAY_NAME = string

Name displayed for the user in the Snowflake web interface.

Default: name (i.e. if no value is provided, user’s identifier is used as the display name)

FIRST_NAME = string , . MIDDLE_NAME = string , . LAST_NAME = string

First, middle, and last name of the user.

Default: NULL

EMAIL = string

Email address for the user.

An email address is not required to use Snowflake; however, to access the Snowflake Community to open support tickets or contribute to the community forums, a valid email address must be specified for the user.

Default: NULL

MUST_CHANGE_PASSWORD = TRUE | FALSE

Specifies whether the user is forced to change their password on next login (including their first/initial login) into the system.

Default: FALSE

DISABLED = TRUE | FALSE

Specifies whether the user is disabled, which prevents the following actions:

  • For a new user, the user is locked out of Snowflake and cannot log in.
  • For an existing user, setting the property aborts all their currently-running queries and does not allow the user to issue any new queries; the user is also immediately locked out of Snowflake and cannot log back in.

Default: FALSE

SNOWFLAKE_SUPPORT = TRUE | FALSE

Specifies whether Snowflake can log in as this user. This property can be used to grant a Snowflake representative or support engineer access to your account to debug or troubleshoot issues.

Default: FALSE

DAYS_TO_EXPIRY = integer

Specifies the number of days after which the user status is set to “Expired” and the user is no longer allowed to log in. This is useful for defining temporary users (i.e. users who should only have access to Snowflake for a limited time period).

Once set, the value counts down to 0, but doesn’t stop. A negative value indicates the status for the user is “Expired”. To reset the value, use ALTER USER to set the following values:

  • To re-enable the user as a temporary user, set the value to a value greater than 0.
  • To specify the user as a permanent user, set the value to NULL or 0.

Default: NULL

MINS_TO_UNLOCK = integer

Specifies the number of minutes until the temporary lock on the user login is cleared. To protect against unauthorized user login, Snowflake places a temporary lock on a user after five consecutive unsuccessful login attempts:

  • A positive value indicates the status for the user is “Locked”.
  • Once the value counts down to 0 (or a negative value), the lock is cleared and the user is allowed to log in again.
  • When the user successfully logs into Snowflake, the value resets to NULL.

When creating a user, this property can be set to prevent them from logging in until the specified amount of time passes.

To remove a lock immediately for a user, use ALTER USER and specify a value of 0 for this parameter.

Default: NULL

DEFAULT_WAREHOUSE = string

Specifies the virtual warehouse that is active by default for the user’s session upon login.

A user can specify or change their current default virtual warehouse using ALTER USER. In addition, after starting a session (i.e. logging in), a user can change the virtual warehouse for the session using USE WAREHOUSE.

Default: NULL

DEFAULT_NAMESPACE = string

Specifies the namespace (database only or database and schema) that is active by default for the user’s session upon login:

  • To specify a database only, enter the database name.
  • To specify a schema, enter the fully-qualified schema name in the form of db_name.schema_name.

A user can specify or change their current default namespace using ALTER USER. In addition, after starting a session (i.e. logging in), a user can change the namespace for their session using USE DATABASE or USE SCHEMA.

Default: NULL

DEFAULT_ROLE = string

Specifies the role that is active by default for the user’s session upon login.

Note that specifying a default role for a user does not grant the role to the user. The role must also be granted explicitly to the user using the GRANT ROLE command.

A user can specify or change their current default role using ALTER USER. In addition, after starting a session (i.e. logging in), a user can change the role for the session using USE ROLE. In either case, they can only choose from roles that have been explicitly granted to them.

Default: NULL

EXT_AUTHN_DUO = TRUE | FALSE

This property cannot be set directly.

Specifies whether Duo Security is enabled for the user, which requires the user to use MFA (multi-factor authorization) for login:

  • This property gets set when the user enrolls in MFA.
  • This property gets unset when MFA is disabled for the user (e.g. DISABLE_MFA = TRUE or user cancels their enrollment in MFA).

For more information, see Multi-Factor Authentication (MFA).

EXT_AUTHN_ID = string

This property cannot be set directly.

Specifies the authorization ID used for Duo Security:

  • This property gets set when the user enrolls in MFA.
  • This property gets unset when MFA is disabled for the user (e.g. DISABLE_MFA = TRUE or user cancels their enrollment in MFA).
MINS_TO_BYPASS_MFA = integer

Specifies the number of minutes to temporarily bypass MFA for the user.

This property can be used to allow a MFA-enrolled user to temporarily bypass MFA during login in the event that their MFA device is not available.

DISABLE_MFA = TRUE | FALSE

Disables MFA for the user (if they are currently enrolled). If the user wants to use MFA again, they must re-enroll.

This property has no effect if the user is not currently enrolled in MFA (i.e. EXT_AUTHN_DUO = FALSE). As such, it is typically only set when using ALTER USER to disable MFA for a user.

RSA_PUBLIC_KEY = string
Specifies the user’s RSA public key; used for key-pair authentication.
RSA_PUBLIC_KEY_2 = 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.
COMMENT = 'string_literal'

Specifies a comment for the user.

Default: NULL

Session Parameters

Specifies one (or more) session parameter defaults to set for the user (separated by blank spaces, commas, or new lines). These defaults are set each time the user logs into Snowflake and initiates a session. The user can always change these defaults themselves within the session using ALTER SESSION.

For the complete list of session parameters, including their default values, that can be specified for a user, see Parameters.

Examples

Create a user with all default properties, a default role, and a basic password that must be changed by the user after their first login:

CREATE USER user1 PASSWORD='abc123' DEFAULT_ROLE = myrole MUST_CHANGE_PASSWORD = TRUE;