ALTER USER¶

Syntax¶

ALTER USER [ IF EXISTS ] [ <name> ] RENAME TO <new_name>

ALTER USER [ IF EXISTS ] [ <name> ] RESET PASSWORD

ALTER USER [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES

ALTER USER [ IF EXISTS ] [ <name> ] ADD DELEGATED AUTHORIZATION OF ROLE <role_name> TO SECURITY INTEGRATION <integration_name>

ALTER USER [ IF EXISTS ] [ <name> ] REMOVE DELEGATED { AUTHORIZATION OF ROLE <role_name> | AUTHORIZATIONS } FROM SECURITY INTEGRATION <integration_name>

ALTER USER [ IF EXISTS ] [ <name> ] mfaActions

ALTER USER [ IF EXISTS ] [ <name> ] SET { AUTHENTICATION | PASSWORD | SESSION } POLICY <policy_name>

ALTER USER [ IF EXISTS ] [ <name> ] UNSET { AUTHENTICATION | PASSWORD | SESSION } POLICY

ALTER USER [ IF EXISTS ] [ <name> ] SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER USER [ IF EXISTS ] [ <name> ] UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER USER [ IF EXISTS ] [ <name> ] SET { [ objectProperties ] [ objectParams ] [ sessionParams ] }

ALTER USER [ IF EXISTS ] [ <name> ] UNSET { <object_property_name> | <object_param_name> | <session_param_name> } [ , ... ]

Where:

mfaActions ::=
  {
    ENROLL MFA
    SET DEFAULT_MFA_METHOD = { PASSKEY | TOTP | DUO }
    REMOVE MFA METHOD <mfa_method>
    MODIFY MFA METHOD <mfa_method> SET COMMENT = '<string>'
  }

Copy

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>
    DISABLE_MFA = TRUE | FALSE
    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>'

Copy

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

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>
    DEFAULT_NULL_ORDERING = <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>
    S3_STAGE_VPCE_DNS_NAME = <string>
    SEARCH_PATH = <string>
    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

Parameters¶

name

Specifies the identifier for the user to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

If the identifier is omitted, the statement modifies the active (i.e. logged in) user. The restrictions described in Usage Notes (in this topic) apply.

RENAME TO new_name

Specifies the new identifier for the user; must be unique for your account.

For more information, see Identifier requirements.

RESET PASSWORD

Generates a URL, which you can share with the user, that opens a web page from which the user can enter a new password. The generated URL is valid for a single use and expires after 4 hours.

Note that specifying this parameter does not invalidate the user’s current password. The user can continue to use their current password until they reset it through the URL.

If you wish to invalidate their current password, use SET PASSWORD = 'string' instead, which changes their password to a new value.

ABORT ALL QUERIES

Aborts all the queries and other SQL statements currently running or scheduled by the user, regardless of the warehouse on which the queries are running/scheduled.

Note that the user can still log into Snowflake and initiate new queries.

If you want to abort all running/scheduled queries and prevent the user from logging into Snowflake or initiating new queries, specify SET DISABLED = TRUE instead.

ADD DELEGATED AUTHORIZATION OF ROLE role_name TO SECURITY INTEGRATION integration_name;

Adds user consent to initiate a session using a specified role for a particular integration.

For more details, see Adding Delegated Authorizations for OAuth User Consent.

REMOVE DELEGATED AUTHORIZATION OF ROLE role_name FROM SECURITY INTEGRATION integration_name , . REMOVE DELEGATED AUTHORIZATIONS FROM SECURITY INTEGRATION integration_name

Revokes consent for the user:

• The first syntax revokes consent for a specified security integration for a specified role. This has the effect of revoking any OAuth access token associated with the integration and specific role.

• The second syntax revokes all consent from a specified security integration. This has the effect of revoking any OAuth access token associated with the integration.

For more details, see:

• Configure Snowflake OAuth for partner applications

• Configure Snowflake OAuth for custom clients

{ AUTHENTICATION | PASSWORD | SESSION } POLICY policy_name

Specifies one of the following policies for the user:

• Authentication policy

• Password policy

• Session policy

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.

Object properties (objectProperties)¶

SET property_name = property_value [ ... ] , . UNSET property_name [ , ... ]

Specifies one (or more) object properties to set or unset for the use. Unsetting an object property resets it back to the default.

TYPE = { PERSON | SERVICE | LEGACY_SERVICE | NULL }

Alters 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

A user who is a human user that interacts with Snowflake.

SERVICE

A user that is a service or application that interacts with Snowflake without human intervention.

If a user has their TYPE property set to SERVICE using the ALTER USER command, then incompatible properties remain stored, but are not 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. It is similar to SERVICE, but allows password and SAML authentication.

NULL

Functions the same as PERSON.

DISABLE_MFA = { TRUE | FALSE }

The effect of this parameter depends on whether the user voluntarily enrolled in MFA or was required to enroll.

• If the user is subject to an authentication policy that requires them to use MFA, setting this parameter to TRUE clears the MFA methods for the user. The next time the user signs in, they are prompted to add a new MFA method that they can use as a second factor of authentication.

• If the user voluntarily enrolled in MFA, setting this parameter to TRUE allows the password user to authenticate without a second factor of authentication.

For information about the other object properties you can set (for example, PASSWORD, LOGIN_NAME, DEFAULT_ROLE), see CREATE USER.

Refer to Usage Notes (in this topic) for more general details about setting and unsetting properties.

Object parameters (objectParams)¶

SET ...

Specifies one (or more) parameters to set for the user (separated by blank spaces, commas, or new lines):

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.

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.

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

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 the network policy that is active for the user.

Also, see Usage Notes (in this topic) for more general details about setting and unsetting parameters.

UNSET ...

Specifies the properties to unset for the user, which resets them to the defaults.

• NETWORK_POLICY

• SESSION POLICY

• TAG tag_name [ , tag_name ... ]

Session parameters (sessionParams)¶

SET session_param_name = param_value [ ... ] , . UNSET session_param_name [ , ... ]

Specifies one (or more) session parameters to set or unset for the user. Unsetting a session parameter resets it back to the default.

For more details about the session parameters you can set (ABORT_DETACHED_SESSION, AUTOCOMMIT, etc.), see Parameters.

Also, see Usage Notes (in this topic) for more general details about setting and unsetting parameters.

Multi-factor authentication (MFA) actions (mfaActions)¶

user ENROLL MFA

Enrolls the specified user in multi-factor authentication (MFA) and prompts them to add a second factor of authentication.

• If the user has a verified email, Snowflake sends an email prompting them to add an MFA authentication method.

• If the user does not have a verified email, Snowflake returns the URL of a page that prompts the user to add an MFA authentication method.

SET DEFAULT_MFA_METHOD = { PASSKEY | TOTP | DUO }

If the current user has more than one MFA method, specifies which one will be used as the second factor of authentication.

user REMOVE MFA METHOD mfa_method

Removes an MFA method that the specified user previously set up. The user can no longer use the MFA method as a second factor of authentication.

To obtain the identifier for mfa_method, execute the SHOW MFA METHODS command and find the value in the name column.

[ user ] MODIFY MFA METHOD mfa_method SET COMMENT = 'string'

Sets a descriptive name for the specified MFA method.

To obtain the identifier for mfa_method, execute the SHOW MFA METHODS command and find the value in the name column.

Users can omit user to set a descriptive name for their own MFA methods.

Usage notes¶

• Only the role with the OWNERSHIP privilege on the user, or a higher role, can execute this command to modify most user properties.

  Tip

  When changing a user’s password using SET PASSWORD = 'string', we recommend also specifying MUST_CHANGE_PASSWORD = TRUE to force the user to log into the web interface and change their password before they can log into Snowflake through any other interface (e.g. SnowSQL or another client application).

  Alternatively, use RESET PASSWORD to generate a URL to a web page that the user can access to change their password.

• Only users with the ACCOUNTADMIN role can set the following parameters:

  • PREVENT_UNLOAD_TO_INLINE_URL

  • PREVENT_UNLOAD_TO_INTERNAL_STAGES

• Individual users can execute the ALTER USER command on themselves (i.e. by specifying their user name/identifier in the command) and change the following:

  • DEFAULT_WAREHOUSE

  • DEFAULT_NAMESPACE

  • DEFAULT_ROLE

  • Any of their session parameter defaults

  Note that users can not use this command to change their password. For security reasons, Snowflake only allows users to change their passwords from within the web interface.

  However, an administrator with the appropriate privileges can use this command with SET PASSWORD = 'string' to change the password for a user.

  Tip

  When changing a user’s password, we recommend also specifying MUST_CHANGE_PASSWORD = TRUE to force the user to log into the web interface and change their password before they can log into Snowflake through any other interface (e.g. SnowSQL or another client application).

  Alternatively, use RESET PASSWORD to generate a URL to a web page that the user can access to change their password.

• An ALTER USER statement does not verify that default objects (DEFAULT_WAREHOUSE, DEFAULT_NAMESPACE, and DEFAULT_ROLE) exist. Note that DEFAULT_SECONDARY_ROLES does not accept an object name as the value, but an ALTER USER statement does verify that a supported value is specified.

• You can set and unset multiple object properties and object/session parameters with a single ALTER statement:

  • When setting multiple properties/parameters, separate them with blank spaces, commas, or new lines.

  • When unsetting multiple properties/parameters, they must be separated by a comma. Also, when unsetting a property/parameter, specify only the name; specifying a value for the property/parameter will return an error.

• If you specify SET DISABLED = TRUE for a user:

  • All queries and other SQL statements currently running or scheduled by the user are aborted and the user cannot initiate additional queries.

  • The user is locked out of Snowflake and cannot log in again.

  If you only want to abort all running and scheduled queries/statements for a user, use ABORT ALL QUERIES instead.

• If the user’s TYPE property is SERVICE, the following commands cannot be used:

  • ALTER USER RESET PASSWORD

  • ALTER USER SET DISABLE_MFA = TRUE

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

Examples¶

Rename user1 to user2:

ALTER USER user1 RENAME TO user2;

Copy

Set the password for a user named user1 to H8MZRqa8gEe/kvHzvJ+Giq94DuCYoQXmfbb$Xnt and require the user to change their password by logging into the Snowflake web interface:

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

Copy

Change the type of user to an application that interacts with Snowflake programmatically:

ALTER USER user1 SET TYPE = SERVICE;

Copy

Remove an existing comment from a user:

ALTER USER user1 UNSET COMMENT;

Copy

Activate no secondary roles by default:

ALTER USER user1 SET DEFAULT_SECONDARY_ROLES = ();

Copy

Activate all secondary roles by default:

ALTER USER user1 UNSET DEFAULT_SECONDARY_ROLES;

Copy

OR

ALTER USER user1 SET DEFAULT_SECONDARY_ROLES = ('ALL');

Copy