CREATE USER¶

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

Note

Only user administrators (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:: DROP USER , ALTER USER , DESCRIBE USER , SHOW PARAMETERS

Syntax¶

CREATE [ OR REPLACE ] USER [ IF NOT EXISTS ] <name>
  [ objectProperties ]
  [ objectParams ]
  [ sessionParams ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

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
  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_FP = <string>
  RSA_PUBLIC_KEY_2 = <string>
  RSA_PUBLIC_KEY_2_FP = <string>
  TYPE = PERSON | SERVICE | LEGACY_SERVICE | NULL
  COMMENT = '<string_literal>'

Copy

objectParams ::=
  ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR = TRUE | FALSE
  NETWORK_POLICY = <string>

Copy

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>

Copy

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.

If the password uses the backslash (i.e. \) character, escape the character with a backslash or use double dollar sign (i.e. $$) delimiters when specifying the password in a SQL command. For details, refer to String & binary data types.

For more information about passwords in Snowflake, refer to Password policies.

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

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). In general, you should not set this property for account administrators (i.e. users with the ACCOUNTADMIN role) because Snowflake locks them out when they become “Expired”.

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.

()
No roles.

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_FP = string

Specifies the fingerprint of 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.

RSA_PUBLIC_KEY_2_FP = string

Specifies the fingerprint of 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.

TYPE = PERSON | SERVICE | LEGACY_SERVICE | NULL

Specifies the type of user. You can set this property to differentiate between human, service, and legacy service users.

Default: NULL

PERSON:

Users do not have restrictions when the TYPE property is set to PERSON, NULL, or when TYPE is unset.

NULL:

See PERSON.

SERVICE:

To improve the security posture of non-interactive use cases, users with the TYPE property set to SERVICE have the following characteristics:

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 properties:
- FIRST_NAME
- MIDDLE_NAME
- LAST_NAME
- PASSWORD
- MUST_CHANGE_PASSWORD
- MINS_TO_BYPASS_MFA
The following commands cannot be used:
- ALTER USER RESET PASSWORD
- ALTER USER SET DISABLE_MFA = TRUE

If a user has their TYPE property set to SERVICE using the ALTER USER command, then the incompatible properties remain stored, but are not returned in user views returned by commands such as DESCRIBE USER. The incompatible properties cannot be set using the ALTER USER command.

If a user, with their TYPE property set to SERVICE, is changed to a user with their TYPE property set to PERSON or NULL, the incompatible properties are restored and can be changed, including their PASSWORD property.

LEGACY_SERVICE:

A user with their TYPE property set to LEGACY_SERVICE represents a non-interactive integration. LEGACY_SERVICE users let services or applications that cannot easily use more secure authentication methods authenticate using password or SAML authentication.

LEGACY_SERVICE users have the following characteristics:

They can log in using password or SAML authentication, unlike SERVICE: users.

They do not have the properties of a SERVICE user, but have the PASSWORD and MUST_CHANGE_PASSWORD properties available.

They have the command restrictions of a SERVICE user, but can use the ALTER USER RESET PASSWORD command.

They are not affected by authentication policy multi-factor authentication (MFA) enforcement. This property lets administrators set account-level authentication policies that require MFA for password authentication without needing to set a user-level authentication policy to prevent LEGACY_SERVICE users from requiring MFA for password authentication.

COMMENT = 'string_literal'

Specifies a comment for the user.

Default: NULL

Optional object parameters (`objectParams`)¶

ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR = { TRUE | FALSE }

Controls how queries that fail due to syntax or parsing errors show up in a query history. If FALSE, the contents of a failed query is redacted from the views, pages, and functions that provide a query history.

This parameter controls behavior for the user viewing the query history, not the user who executed the query.

Only users with a role that is granted or inherits the AUDIT privilege can set the ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR parameter.

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.

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

Optional parameters¶

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

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE USER	Account	Only the USERADMIN role, or a higher role, has this privilege by default. The privilege can be granted to additional roles as needed.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

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.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

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;

Copy

CREATE USER¶

Syntax¶

Required parameters¶

Optional object properties (objectProperties)¶

Optional object parameters (objectParams)¶

Optional session parameters (sessionParams)¶

Optional parameters¶

Access control requirements¶

Usage notes¶

Examples¶

Optional object properties (`objectProperties`)¶

Optional object parameters (`objectParams`)¶

Optional session parameters (`sessionParams`)¶