使用密码

本主题介绍管理员如何配置密码要求和重置用户密码。

密码策略

密码策略指定创建和重置密码以向 Snowflake 进行身份验证所必须满足的要求。

Snowflake 为密码策略提供两个选项：

内置的密码策略，可简化初始用户配置过程。
A schema-level password policy object that can be set at the level of the Snowflake account, an individual user, or both depending on the use cases and needs of the user administrator.

密码策略和密码的最佳实践

Snowflake 推荐以下有关密码和密码策略的最佳实践：

Create and enforce the custom password policy

在账户或用户上设置密码策略后，强制实施密码策略对象。

Set these properties to values that meet your internal security needs. For details, see 第 4 步：创建密码策略 (in this topic):

PASSWORD_HISTORY to ensure users cannot reuse passwords too frequently and to help prevent brute force attacks to determine the password for a user.
PASSWORD_MIN_AGE_DAYS to require the user to use the new password. A value of 0 is not recommended because the user can change the password to exhaust the password history and reuse the original password value too soon.

To require the user to change their password to meet the password policy on their initial or next login to Snowflake, set the MUST_CHANGE_PASSWORD property on the user to TRUE using an ALTER USER command.

For details, see 第 6 步：要求更改密码 (in this topic).

Require strong passwords

定义账户级密码策略需要强密码。

A strong password has at least 14 characters and includes a combination of uppercase and lowercase letters, special characters (e.g. ! and *), and numbers.

MFA

Use multi-factor authentication (MFA) for additional security.

Using SCIM

You can set a password for the user to access Snowflake in a SCIM API request. SCIM administrators and user administrators should choose to manage the user password to access Snowflake in either your identity provider or using a password policy in Snowflake.

Currently, users provisioned to Snowflake with SCIM are required to have their password meet the default Snowflake password policy. This requirement can be bypassed if you choose to use this password policy feature.

To bypass the default password policy requirement, follow the instructions in the Using Password Policies section (in this topic).

Monitoring passwords

要监控密码，请执行以下操作：

Query the Snowflake Account Usage USERS view to determine whether the HAS_PASSWORD column value returns TRUE for a given user.
Query the Snowflake Account Usage LOGIN_HISTORY view and evaluate the FIRST_AUTHENTICATION_FACTOR column. If a user does not require a password to access Snowflake, execute an ALTER USER command to set the password property to NULL.

为新用户设置初始密码

During the initial user creation, it is possible to set a weak password for the user that does not meet the minimum requirements of the password policy that is in effect. This gives administrators the option to use generic passwords for the user during the creation process. If this pathway is chosen, Snowflake strongly recommends setting the MUST_CHANGE_PASSWORD property to TRUE to require users to change their password on their next login, including the initial login. When a user resets their password, they must choose one that conforms to the password policy in effect, whether it is a Snowflake-provided policy or a custom one.

Additionally, Snowflake allows creating users without an initial password to support business processes in which new users are not allowed to log into the system. If this occurs, the user’s PASSWORD property value will be NULL. However, as a general rule, Snowflake expects that users are created with initial passwords.

Snowflake 提供的密码策略¶

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

In the context of resetting an existing password (e.g. change 'test12345' to 'q@-*DaC2yjZoq3Re4JYX'), Snowflake enforces the following password policy as a minimum requirement while using the ALTER USER command and the web interface:

Must be at least 14 characters long.
必须包含至少 1 位数字。
必须包含至少 1 个大写字母和 1 个小写字母。

Snowflake strongly recommends the following guidelines for creating the strongest passwords possible:

为 Snowflake 创建唯一密码（不要重复使用其他系统或账户的密码）。
包括多个随机大小写混合的字母、数字和特殊字符，包括空格。
不要使用容易猜到的常用密码、名称、数字或日期。

账户和用户的自定义密码策略

自定义密码策略是一个架构级别的对象，它指定了创建和重置密码以向 Snowflake 进行身份验证所必须满足的要求，包括成功输入密码的尝试次数，以及重试密码之前的分钟数（即“锁定”时间）。

密码的密码策略要求包括大写或小写字母、特殊字符、数字和密码长度，以满足用户和客户端向 Snowflake 进行身份验证的安全要求。需要强密码的密码策略有助于满足安全准则和法规。

Snowflake 支持为 Snowflake 账户和个人用户设置密码策略。在给定时间只能为 Snowflake 账户或用户设置一个密码策略。如果存在 Snowflake 账户的密码策略，并且为同一 Snowflake 账户中的用户设置了另一个密码策略，则用户级别的密码策略优先于账户级别的密码策略。

The password policy applies to new passwords that are set in your Snowflake account. To ensure that users with existing passwords meet the password policy requirements, require users to change their password during their next login to Snowflake as shown in 第 6 步：要求更改密码 (in this topic).

Note

Most password policy property changes take effect the next time a user changes their password. For example, if you change the PASSWORD_MAX_LENGTH property from 10 to 16 to require the user to use a longer password then the user must comply with the password policy change whenever they change their password. You can set the user property MUST_CHANGE_PASSWORD to TRUE with an ALTER USER statement to require the user to change their password on their next login to Snowflake.

但是，某些密码策略属性更改会在下次登录时生效，因为 Snowflake 不会强制用户在当前会话中更改密码：

PASSWORD_MAX_AGE_DAYS = integer
PASSWORD_MAX_RETRIES = integer
PASSWORD_LOCKOUT_TIME_MINS = integer

Any changes to these properties do not affect the current session. For example, a change to the value of the PASSWORD_MAX_AGE_DAYS property does not cause the user’s current password to expire. However, during the next login to Snowflake, the user must change their password.

注意事项

Future grants of privileges on password policies are not supported.

为解决此问题，请向自定义角色授予 APPLY PASSWORD POLICY 权限，以允许该角色对用户或 Snowflake 账户应用密码策略。

The password policy can be managed with SQL using Snowflake CLI, SnowSQL or a supported driver or connector, or using Snowsight.
When you reset or change a password, Snowflake evaluates the password policy to ensure that the newly created password matches the password policy requirements.
跟踪密码策略使用情况：
- Query the Account Usage PASSWORD_POLICIES view to return a row for each password policy in your Snowflake account.
- Use the Information Schema table function POLICY_REFERENCES to return a row for each user that is assigned to the specified password policy and a row for the password policy assigned to the Snowflake account.

当前，密码策略仅支持以下语法：

POLICY_REFERENCES( POLICY_NAME => '<password_policy_name>' )

Where password_policy_name is the fully qualified name of the password policy.

For example, execute the following query to return a row for each user that is assigned the password policy named password_policy_prod_1, which is stored in the database named my_db and the schema named my_schema:

SELECT *
FROM TABLE(
    my_db.information_schema.policy_references(
POLICY_NAME => 'my_db.my_schema.password_policy_prod_1'
  )
);

密码策略访问控制

以下访问控制权限允许用户使用密码策略：

权限	对象类型	用途
CREATE PASSWORD POLICY	架构	允许创建新密码策略。
APPLY PASSWORD POLICY	账户、用户	允许在账户或用户级别应用密码策略。
OWNERSHIP	密码策略	授予对密码策略的完全控制权。需要更改密码策略的大多数属性。

下表总结了密码策略 DDL 操作及其所需权限之间的关系。

操作	需要权限
创建密码策略	角色具有架构的 CREATE PASSWORD POLICY 权限，用于存储密码策略。
更改密码策略	角色具有密码策略的 OWNERSHIP 权限。
删除密码策略	角色具有密码策略的 OWNERSHIP 权限。
描述密码策略	A role with the OWNERSHIP privilege on the password policy or the APPLY PASSWORD POLICY privilege on the account.
显示密码策略	A role with the OWNERSHIP privilege on the password policy or the APPLY PASSWORD POLICY privilege on the account.
设置和取消设置密码策略	角色具有账户或用户的 APPLY PASSWORD POLICY 权限。

Note

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

DDL 命令¶

Snowflake provides the following DDL commands to manage password policy objects:

使用密码策略

以下步骤是 Snowflake 中定义和设置密码策略的代表性指南。

These steps assume a centralized management approach in which a custom role named policy_admin owns the password policy (i.e. has the OWNERSHIP privilege on the password policy) and is responsible for setting the password policy on an account or user (i.e. has the global APPLY PASSWORD POLICY privilege, as shown in step 2).

Note

To set a policy on an account, the policy_admin custom role must also have the USAGE privilege on the database and schema that contain the password policy.

For more information, see: Access control privileges

第 1 步：创建自定义角色¶

Create a custom role that allows creating and managing password policies. Throughout this topic, the example custom role is named policy_admin, although the role could have any appropriate name.

如果自定义角色已存在，请继续下一步。

Otherwise, create the policy_admin custom role.

USE ROLE USERADMIN;

CREATE ROLE policy_admin;

第 2 步：授予自定义角色权限¶

If the policy_admin custom role does not already have the following privileges, grant these privileges as shown below:

包含密码策略的数据库和架构的 USAGE 权限。
用于存储密码策略的架构的 CREATE PASSWORD POLICY 权限。
账户的 APPLY PASSWORD POLICY 权限。

USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE security TO ROLE policy_admin;

GRANT USAGE ON SCHEMA security.policies TO ROLE policy_admin;

GRANT CREATE PASSWORD POLICY ON SCHEMA security.policies TO ROLE policy_admin;

GRANT APPLY PASSWORD POLICY ON ACCOUNT TO ROLE policy_admin;

If you decide to set a password policy on a user, grant the APPLY PASSWORD POLICY privilege on the user. For example, if the username is JSMITH, execute the following command.

GRANT APPLY PASSWORD POLICY ON USER jsmith TO ROLE policy_admin;

For more information, see 密码策略访问控制.

第 3 步：向用户授予自定义角色¶

Grant the policy_admin custom role to the users responsible for managing password policies.

USE ROLE SECURITYADMIN;
GRANT ROLE policy_admin TO USER jsmith;

For more information, see Configuring access control

第 4 步：创建密码策略¶

Using the policy_admin custom role, create a password policy named password_policy_prod_1. For more information, see CREATE PASSWORD POLICY.

USE ROLE policy_admin;

USE SCHEMA security.policies;

CREATE PASSWORD POLICY PASSWORD_POLICY_PROD_1
    PASSWORD_MIN_LENGTH = 14
    PASSWORD_MAX_LENGTH = 24
    PASSWORD_MIN_UPPER_CASE_CHARS = 2
    PASSWORD_MIN_LOWER_CASE_CHARS = 2
    PASSWORD_MIN_NUMERIC_CHARS = 2
    PASSWORD_MIN_SPECIAL_CHARS = 2
    PASSWORD_MIN_AGE_DAYS = 1
    PASSWORD_MAX_AGE_DAYS = 999
    PASSWORD_MAX_RETRIES = 3
    PASSWORD_LOCKOUT_TIME_MINS = 30
    PASSWORD_HISTORY = 5
    COMMENT = 'production account password policy';

Note

The property PASSWORD_MAX_AGE_DAYS is set to the largest value, 999. Choose a value that aligns with your internal guidelines. For details, see CREATE PASSWORD POLICY.

第 5 步：设置账户或个人用户的密码策略¶

Set the policy on an account with the ALTER ACCOUNT command:

ALTER ACCOUNT SET PASSWORD POLICY security.policies.password_policy_prod_1;

If you decide to create an additional password policy for one or more users, set the user-level password policy on a user with an ALTER USER command:

ALTER USER jsmith SET PASSWORD POLICY security.policies.password_policy_user;

Important

要替换已为账户或用户设置的密码策略，请先取消设置密码策略，然后为账户或用户设置新的密码策略。例如：

ALTER ACCOUNT UNSET PASSWORD POLICY;

ALTER ACCOUNT SET PASSWORD POLICY security.policies.password_policy_prod_2;

第 6 步：要求更改密码¶

Set the MUST_CHANGE_PASSWORD property to TRUE for individual users using an ALTER USER statement to require the users to change their password to meet the password policy on their next login to Snowflake.

ALTER USER JSMITH SET MUST_CHANGE_PASSWORD = true;

重置用户密码

管理员可以通过以下界面更改用户的密码。

Snowsight¶

Sign in to Snowsight.
In the navigation menu, select Governance & security » Users & roles.
Locate the user whose password you want to change and select » Reset Password.
输入用户的新密码并确认密码。
Select Update.

使用 SQL¶

Use the ALTER USER command to input a user’s password. For example:

ALTER USER janesmith SET PASSWORD = 'H8MZRqa8gEe/kvHzvJ+Giq94DuCYoQXmfbb$Xnt' MUST_CHANGE_PASSWORD = TRUE;

还可以利用 ALTER USER …RESET PASSWORD 语法来生成要与用户共享的 URL。URL 将打开一个网页，用户可以在其中输入新密码。例如：

ALTER USER janesmith RESET PASSWORD;
Note

生成的 URL 仅限使用一次，4 小时后过期。

执行 ALTER USER …RESET PASSWORD 语句不会使当前密码无效。在设置新密码之前，用户可以继续使用旧密码。

使用 Python¶

The UserResource.create_or_alter method in the Snowflake Python APIs currently does not support changing the password for an existing user. You can only set the password using this method when creating a new user.

重置管理员密码

An account administrator (i.e. a user with the ACCOUNTADMIN role) can reset their own password using the procedure described in Resetting the Password for a User.

If an account administrator is locked out of their account, a different user with the ACCOUNTADMIN role can reset the password for the locked-out administrator. In the event that the administrator is locked out and there is no other administrator to change the password, contact Snowflake Support to reset the password.