Categories:
User & Security DDL (Users)

ALTER USER

Modifies the properties and session parameters for an existing user in the system:

  • Administrators can use this command to alter properties and session parameter defaults for any users for which they have the appropriate privileges.
  • Individual users can use this command to alter specific properties and any session parameter defaults for themselves (see Usage Notes below).

Can also be used to abort all queries (and other SQL statements) submitted by the user.

See also:
CREATE USER , DESCRIBE USER , SHOW PARAMETERS

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> REMOVE DELEGATED AUTHORIZATION FROM SECURITY INTEGRATION <integration_name>

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

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

Where:

objProperties ::=
    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
    SNOWFLAKE_SUPPORT = TRUE | FALSE
    DAYS_TO_EXPIRY = <integer>
    MINS_TO_UNLOCK = <integer>
    DEFAULT_WAREHOUSE = <string>
    DEFAULT_NAMESPACE = <string>
    DEFAULT_ROLE = <string>
    EXT_AUTHN_DUO = TRUE | FALSE
    EXT_AUTHN_UID = <string>
    MINS_TO_BYPASS_MFA = <integer>
    DISABLE_MFA = TRUE | FALSE
    RSA_PUBLIC_KEY = <string>
    RSA_PUBLIC_KEY_2 = <string>
    COMMENT = '<string_literal>'
sessionParams ::=
    ABORT_DETACHED_QUERY = TRUE | FALSE
    AUTOCOMMIT = TRUE | FALSE
    DATE_INPUT_FORMAT = <string>
    DATE_OUTPUT_FORMAT = <string>
    ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
    ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
    LOCK_TIMEOUT = <num>
    QUERY_TAG = <string>
    ROWS_PER_RESULTSET = <num>
    STATEMENT_TIMEOUT_IN_SECONDS = <num>
    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

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.
RENAME TO new_name

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

For more details, see Identifier Syntax.

RESET PASSWORD

Generates a URL to share with the user. The URL opens a web page on which the user can enter the new password. The generated URL is valid for one use only and expires after 4 hours.

Note the following:

  • 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.
  • Specifying this parameter is different than specifying SET PASSWORD = 'string', which invalidates their current password by changing it 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.

REMOVE DELEGATED AUTHORIZATION FROM SECURITY INTEGRATION integration_name

Revokes consent from a specified security integration. This has the effect of revoking any OAuth access token associated with the integration.

For more details, see:

SET ...

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

  • For more details about the properties you can set, see CREATE USER.
  • For more details about the session parameters you can set, see Parameters.
UNSET ...

Specifies one (or more) properties and/or session parameters to unset for the user, which resets them to the defaults.

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property/parameter will return an error.

Usage Notes

  • Individual users can execute the ALTER USER command on themselves (i.e. by specifying their user 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 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.

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

  • When using UNSET to reset a property/session parameter, do not specify a value; specify only the name of the property/session parameter.

Examples

Rename user1 to user2:

ALTER USER user1 RENAME TO user2;

Set the password for a user named user1 to welcome1 and require the user to change their password by logging into the Snowflake web interface:

ALTER USER user1 SET PASSWORD = 'welcome1' MUST_CHANGE_PASSWORD = TRUE;

Remove an existing comment from a user:

ALTER USER user1 UNSET COMMENT;