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.

See also:

Using password policies , Managing password policies

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>' ]
Copy

Required parameters¶

name

Identifier 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 = integer

Specifies the minimum number of characters the password must contain.

Supported range: 8 to 256, inclusive.

Default: 8

PASSWORD_MAX_LENGTH = integer

Specifies 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, and PASSWORD_MIN_LOWER_CASE_CHARS.

Supported range: 8 to 256, inclusive.

Default: 256

PASSWORD_MIN_UPPER_CASE_CHARS = integer

Specifies the minimum number of uppercase characters the password must contain.

Supported range: 0 to 256, inclusive.

Default: 1

PASSWORD_MIN_LOWER_CASE_CHARS = integer

Specifies the minimum number of lowercase characters the password must contain.

Supported range: 0 to 256, inclusive.

Default: 1

PASSWORD_MIN_NUMERIC_CHARS = integer

Specifies the minimum number of numeric characters the password must contain.

Supported range: 0 to 256, inclusive.

Default: 1

PASSWORD_MIN_SPECIAL_CHARS = integer

Specifies the minimum number of special characters the password must contain.

Supported range: 0 to 256, inclusive.

Default: 0

PASSWORD_MIN_AGE_DAYS = integer

Specifies 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 = integer

Specifies 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 = integer

Specifies 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 = integer

Specifies 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 = integer

Specifies 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';
Copy