CREATE USER¶

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

See also:

DROP USER , ALTER USER , DESCRIBE USER , SHOW PARAMETERS

语法

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

其中：

Copy codeExpand code block

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 }
  ALLOWED_INTERFACES = ( <list_of_interfaces> )
  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 }
  WORKLOAD_IDENTITY = ( <list_of_properties> )
  COMMENT = '<string_literal>'

Show lessSee more

Scroll to top

Copy codeExpand code block

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

Show lessSee more

Scroll to top

Copy codeExpand code block

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>
  DEFAULT_NULL_ORDERING = <string>
  ENABLE_GET_DDL_USE_DATA_TYPE_ALIAS = TRUE | FALSE
  ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
  ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
  JSON_INDENT = <num>
  LOCK_TIMEOUT = <num>
  OPT_OUT_ERROR_LOGGING = TRUE | FALSE
  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>

Show lessSee more

Scroll to top

必填参数

name

用户的标识符；对于您的账户必须是唯一的。

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.

Optional object properties (objectProperties)¶

PASSWORD = 'string'

用户的密码必须放在单引号或双引号中。如果未指定密码，则在为用户明确指定密码之前，用户无法登录 Snowflake。

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

用户在登录系统时输入的名称。用户的登录名在整个账户中必须是唯一的。

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 允许指定不同的用户名和登录名，以便使用通用标识符（例如电子邮件地址）进行登录。

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

在 Snowflake Web 界面中为用户显示的名称。

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

用户的名字、中间名和姓氏。

Default: NULL

EMAIL = string

用户的电子邮件地址。

使用 Snowflake 不需要电子邮件地址；但是，要访问 Snowflake 社区以打开支持部门工单或为社区论坛做出贡献，则必须为用户指定一个有效的电子邮件地址。

我们建议指定企业电子邮件地址，而不是个人电子邮件地址。用户的电子邮件地址对 Snowflake 账户中的所有其他用户可见。

Default: NULL

MUST_CHANGE_PASSWORD = TRUE | FALSE

指定是否强制用户在下次登录（包括首次/初始登录）时更改其密码。

Default: FALSE

DISABLED = TRUE | FALSE

指定用户是否被禁用，禁用后将无法执行以下操作：

• 对于新用户，该用户将被锁定在 Snowflake 之外，无法登录。
• 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

ALLOWED_INTERFACES = ( { 'ALL' | 'interface' [ , ... ] } )

Specifies which Snowflake interfaces the user can access.

If you specify ('ALL'), the user can access Snowsight and all other interfaces that can be specified for this property. If you specify one or more interfaces, the user can only access the interfaces specified and can’t interact with any Snowflake data outside of the interfaces specified.

For interface, you can specify one or more of the following values in a comma-delimited list:

SNOWFLAKE_INTELLIGENCE

The user can access Snowflake Intelligence.

STREAMLIT

The user can access Streamlit apps through the app-viewer URLs.

Default: ('ALL')

DAYS_TO_EXPIRY = integer

指定用户状态设置为“已过期”且不再允许用户登录的天数。这有助于定义临时用户（即应仅在有限时间内访问 Snowflake 的用户）。

Setting DAYS_TO_EXPIRY for account administrators (that is, users with the ACCOUNTADMIN role) is not allowed. If you set DAYS_TO_EXPIRY for account administrators, Snowflake ignores the setting.

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

指定在清除用户登录的临时锁定之前的分钟数。为了防止未经授权的用户登录，Snowflake 会在用户连续五次尝试登录失败后对其进行临时锁定：

• 正值表示用户的状态为“已锁定”。
• 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.

创建用户时，可以设置此属性来阻止用户登录，直到指定的时间过去。

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

指定登录时默认为用户会话激活的虚拟仓库。

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.

请注意，CREATE USER 操作不会验证仓库是否存在。

Default: NULL

DEFAULT_NAMESPACE = string

指定登录时默认为用户会话激活的命名空间（仅数据库或数据库和架构）。

• 要仅指定数据库，请输入数据库名称。
• 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.

请注意，CREATE USER 操作不会验证命名空间是否存在。

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.

支持以下值：

('ALL')

已授予用户的所有角色。

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: ALL

MINS_TO_BYPASS_MFA = integer

指定用户临时绕过 MFA 的分钟数。

此属性可用于允许已注册 MFA 的用户在其 MFA 设备不可用时在登录事件期间暂时绕过 MFA。

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 }

Specifies the type of user. You can set this property to differentiate between human, service, and legacy service users. For information about the characteristics of these types of users, see Types of users.

PERSON

用户是可以与 Snowflake 交互的人类用户。

SERVICE

用户是指无需人类干预即可与 Snowflake 进行交互的服务或应用程序。

LEGACY_SERVICE

A user with their TYPE property set to LEGACY_SERVICE represents a non-interactive integration. It is similar to SERVICE, but allows password and SAML authentication.

Note

The LEGACY_SERVICE type is being deprecated. Use the SERVICE type for services and applications. For a timeline of the deprecation of LEGACY_SERVICE, see Planning for the deprecation of single-factor password sign-ins.

Default: PERSON

COMMENT = 'string_literal'

指定用户的注释。

Default: NULL

Optional object parameters (objectParams)¶

ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR = { TRUE | FALSE }

控制由于语法或解析错误而失败的查询在查询历史记录中的显示方式。如果值为 FALSE，则在提供查询历史记录的视图、页面和函数中编辑失败查询的内容。

此参数控制查看查询历史记录的用户的行为，而不是执行查询的用户的行为。

用户只有具有被授予或继承 AUDIT 权限的角色才能设置 ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR 参数。

ENABLE_UNREDACTED_SECURE_OBJECT_ERROR = { TRUE | FALSE }

Controls whether error messages related to secure objects are redacted in metadata. For more information about error message redaction for secure objects, see Secure objects: Redaction of information in error messages.

用户只有具有被授予或继承 AUDIT 权限的角色才能设置 ENABLE_UNREDACTED_SECURE_OBJECT_ERROR 参数。

When using the ALTER USER command to set the parameter to TRUE for a particular user, modify the user that you want to see the redacted error messages in metadata, not the user who caused the error.

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.

如果未设置此参数，则使用账户的网络策略（如果有）。

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.

可选参数

访问控制要求

使用说明

• The TYPE property of a new user object can’t be NULL. You can’t set the TYPE property of an existing user to NULL. Running a CREATE USER command without setting the TYPE property sets the TYPE property for that user to PERSON.

• 关于元数据：

  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.

示例

创建一个具有所有默认属性、默认角色和基本密码的用户，用户在首次登录后必须更改密码：

Copy codeExpand code block

CREATE USER user1 PASSWORD='abc123' DEFAULT_ROLE = myrole DEFAULT_SECONDARY_ROLES = ('ALL') MUST_CHANGE_PASSWORD = TRUE;

Show lessSee more

Scroll to top