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 user adminstrators (i.e. users with the USERADMIN role or higher), or another role with the CREATE USER privilege on the account, can create users.

See also:

ALTER USER , DESCRIBE USER , SHOW PARAMETERS

Syntax

CREATE [ OR REPLACE ] USER [ IF NOT EXISTS ] <name>
  [ objectProperties ]
  [ objectParams ]
  [ sessionParams ]

Where:

objectProperties ::=
  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>
  DEFAULT_SECONDARY_ROLES = ( 'ALL' )
  MINS_TO_BYPASS_MFA = <integer>
  RSA_PUBLIC_KEY = <string>
  RSA_PUBLIC_KEY_2 = <string>
  COMMENT = '<string_literal>'
objectParams ::=
  NETWORK_POLICY = <string>
  [ WITH ] TAG ( <tag_key> = 'tag_value' [ , <tag_key> = 'tag_value' , ... ] )
sessionParams ::=
  ABORT_DETACHED_QUERY = TRUE | FALSE
  AUTOCOMMIT = TRUE | FALSE
  BINARY_INPUT_FORMAT = <string>
  BINARY_OUTPUT_FORMAT = <string>
  DATE_INPUT_FORMAT = <string>
  DATE_OUTPUT_FORMAT = <string>
  ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
  ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
  JSON_INDENT = <num>
  LOCK_TIMEOUT = <num>
  QUERY_TAG = <string>
  ROWS_PER_RESULTSET = <num>
  SIMULATED_DATA_SHARING_CONSUMER = <string>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>
  STRICT_JSON_OUTPUT = TRUE | FALSE
  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
  WEEK_OF_YEAR_POLICY = <num>
  WEEK_START = <num>

Note

For readability, the complete list of session parameters that can be set for a user is not included here. For a complete list of all session parameters, with their descriptions, as well as account and object parameters, see Parameters.

Required Parameters

name

Identifier for the user; must be unique for your account.

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

Note

The user does not use this value to log into Snowflake; instead, the user uses the value specified for the LOGIN_NAME property to log in. However, if no login name is explicitly specified for the user, the user name/identifier serves as the default login name.

Optional Object Properties (objectProperties)

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.

For more information about passwords in Snowflake, see Snowflake Password Policy.

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: User’s name/identifier (i.e. if no value is specified, the value specified for name is used as the login name)

DISPLAY_NAME = string

Name displayed for the user in the Snowflake web interface.

Default: User’s name/identifier (i.e. if no value is specified, the value specified for name 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.

We recommend specifying a business email address rather than a personal email address. User email addresses are visible to all other users in your Snowflake account.

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.

Note that the CREATE USER operation does not verify that the warehouse exists.

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.

Note that the CREATE USER operation does not verify that the namespace exists.

Default: NULL

DEFAULT_ROLE = string

Specifies the primary role that is active by default for the user’s session upon login. The primary role is a single role that authorizes the execution of CREATE <object> statements or any other SQL action. The permissions to perform these actions can be granted to the primary role or any lower role in the role hierarchy.

Note that specifying a default role for a user does not grant the role to the user. The role must be granted explicitly to the user using the GRANT ROLE command. In addition, the CREATE USER operation does not verify that the role exists.

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

DEFAULT_SECONDARY_ROLES = ( 'ALL' )

Specifies the set of secondary roles that are active for the user’s session upon login. Secondary roles are a set of roles that authorize any SQL action other than the execution of CREATE <object> statements. The permissions to perform these actions can be granted to the primary role, secondary roles, or any lower roles in the role hierarchies.

Note that specifying a default secondary 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.

The following values are supported:

ALL:

All roles that have been granted to the user.

Note that the set of roles is reevaluated when each SQL statement executes. If additional roles are granted to the user, and that user executes a new SQL statement, the newly granted roles are active secondary roles for the new SQL statement. The same logic applies to roles that are revoked from a user.

Default: NULL

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.

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

Optional Object Parameters (objectParams)

NETWORK_POLICY = string

Specifies an existing network policy is active for the user. The network policy restricts the list of user IP addresses when exchanging an authorization code for an access or refresh token and when using a refresh token to obtain a new access token.

If this parameter is not set, the network policy for the account (if any) is used instead.

TAG ( tag_key = 'tag_value' [ , tag_key = 'tag_value' , ... ] ) ]

Specifies the tag name (i.e. the key) and the tag value.

The tag value is always a string and the maximum number of characters for the tag value is 256. The maximum number of tags that can be set on an object is 20.

Optional Session Parameters (sessionParams)

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.

Usage Notes

Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

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 DEFAULT_SECONDARY_ROLES = ('ALL') MUST_CHANGE_PASSWORD = TRUE;