CREATE PASSWORD POLICY¶
Creates a new password policy or replaces an existing password policy.
After creating a password policy, apply the password policy to an account using an ALTER ACCOUNT statement or a user using an ALTER USER statement.
Syntax¶
CREATE [ OR REPLACE ] PASSWORD POLICY [ IF NOT EXISTS ] <name>
[ PASSWORD_MIN_LENGTH = <integer> ]
[ PASSWORD_MAX_LENGTH = <integer> ]
[ PASSWORD_MIN_UPPER_CASE_CHARS = <integer> ]
[ PASSWORD_MIN_LOWER_CASE_CHARS = <integer> ]
[ PASSWORD_MIN_NUMERIC_CHARS = <integer> ]
[ PASSWORD_MIN_SPECIAL_CHARS = <integer> ]
[ PASSWORD_MIN_AGE_DAYS = <integer> ]
[ PASSWORD_MAX_AGE_DAYS = <integer> ]
[ PASSWORD_MAX_RETRIES = <integer> ]
[ PASSWORD_LOCKOUT_TIME_MINS = <integer> ]
[ PASSWORD_HISTORY = <integer> ]
[ COMMENT = '<string_literal>' ]
Required parameters¶
nameIdentifier for the password policy; must be unique for your account.
The identifier value 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 parameters¶
PASSWORD_MIN_LENGTH = integerSpecifies the minimum number of characters the password must contain.
Supported range: 8 to 256, inclusive.
Default: 8
PASSWORD_MAX_LENGTH = integerSpecifies the maximum number of characters the password must contain. This number must be greater than or equal to the sum of
PASSWORD_MIN_LENGTH,PASSWORD_MIN_UPPER_CASE_CHARS, andPASSWORD_MIN_LOWER_CASE_CHARS.Supported range: 8 to 256, inclusive.
Default: 256
PASSWORD_MIN_UPPER_CASE_CHARS = integerSpecifies the minimum number of uppercase characters the password must contain.
Supported range: 0 to 256, inclusive.
Default: 1
PASSWORD_MIN_LOWER_CASE_CHARS = integerSpecifies the minimum number of lowercase characters the password must contain.
Supported range: 0 to 256, inclusive.
Default: 1
PASSWORD_MIN_NUMERIC_CHARS = integerSpecifies the minimum number of numeric characters the password must contain.
Supported range: 0 to 256, inclusive.
Default: 1
PASSWORD_MIN_SPECIAL_CHARS = integerSpecifies the minimum number of special characters the password must contain.
Supported range: 0 to 256, inclusive.
Default: 0
PASSWORD_MIN_AGE_DAYS = integerSpecifies the number of days the user must wait before a recently changed password can be changed again.
Supported range: 0 to 999, inclusive.
Default: 0
PASSWORD_MAX_AGE_DAYS = integerSpecifies the maximum number of days before the password must be changed.
Supported range: 0 to 999, inclusive.
A value of zero (i.e.
0) indicates that the password does not need to be changed. Snowflake does not recommend choosing this value for a default account-level password policy or for any user-level policy. Instead, choose a value that meets your internal security guidelines.Default: 90, which means the password must be changed every 90 days.
Important
This parameter is stateful. For details, see the note in Custom password policy for the account and users.
PASSWORD_MAX_RETRIES = integerSpecifies the maximum number of attempts to enter a password before being locked out.
Supported range: 1 to 10, inclusive.
Default: 5
Important
This parameter is stateful. For details, see the note in Custom password policy for the account and users.
PASSWORD_LOCKOUT_TIME_MINS = integerSpecifies the number of minutes the user account will be locked after exhausting the designated number of password retries (i.e.
PASSWORD_MAX_RETRIES).Supported range: 1 to 999, inclusive.
Default: 15
Important
This parameter is stateful. For details, see the note in Custom password policy for the account and users.
PASSWORD_HISTORY = integerSpecifies the number of the most recent passwords that Snowflake stores. These stored passwords cannot be repeated when a user updates their password value.
The current password value does not count towards the history.
When you increase the history value, Snowflake saves the previous values.
When you decrease the value, Snowflake saves the stored values up to that value that is set. For example, if the history value is 8 and you change the history value to 3, Snowflake stores the most recent 3 passwords and deletes the 5 older password values from the history.
Default: 0
Max: 24
COMMENT = 'string_literal'Adds a comment or overwrites an existing comment for the password policy.
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
|---|---|---|
CREATE PASSWORD POLICY |
Schema |
Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.
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.
For additional details on password policy DDL and privileges, see Managing password policies.
Usage notes¶
If you want to replace an existing password policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE PASSWORD POLICY command.
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 password policy named password_policy_prod_1 for your current account:
CREATE PASSWORD POLICY PASSWORD_POLICY_PROD_1 PASSWORD_MIN_LENGTH = 12 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 = 30 PASSWORD_MAX_RETRIES = 3 PASSWORD_LOCKOUT_TIME_MINS = 30 PASSWORD_HISTORY = 5 COMMENT = 'production account password policy';