DESCRIBE USER

Describes a user, including the current and default values of the properties of the user.

DESCRIBE can be abbreviated to DESC.

See also:

DROP USER , ALTER USER , CREATE USER , SHOW USERS

Syntax

{ DESC | DESCRIBE } USER <name>
Copy

Parameters

name

Specifies the identifier for the user to describe.

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.

For more information, see Identifier requirements.

Output

The output of the command includes the following columns, which describe the properties and metadata of the object:

Column

Description

property

The name of the property (see Properties of users).

property_type

The data type of the property (for example, Boolean or String).

property_value

The value assigned to the property.

property_default

The default value of the property.

The property column can include the following properties of the notification integration:

Properties of users

Property

Description

NAME

Name of the user.

COMMENT

Comment about the user.

DISPLAY_NAME

Name displayed for the user in Snowsight.

TYPE

Type of the user. For a list of possible values, see Types of users.

LOGIN_NAME

Name that the user enters to log into the system.

FIRST_NAME

First name of the user.

MIDDLE_NAME

Middle name of the user.

LAST_NAME

Last name of the user.

EMAIL

Email addresss for the user.

PASSWORD

Obfuscated password of the user.

MUST_CHANGE_PASSWORD

If true, the user is forced to change their password on next login (including their first/initial login) into the system.

DISABLED

If true, the user is locked out of Snowflake and cannot log back in.

SNOWFLAKE_LOCK

If true, the user is locked by Snowflake. When a user is locked, they are unable to log in until the lock is removed.

SNOWFLAKE_SUPPORT

If true, Snowflake Support is allowed to use the user or account.

DAYS_TO_EXPIRY

Number of days after which the user status is set to “Expired” and the user is no longer allowed to log in.

MINS_TO_UNLOCK

Number of minutes until the temporary lock on the user login is cleared.

DEFAULT_WAREHOUSE

Virtual warehouse that is active by default for the user’s session upon logging in.

DEFAULT_NAMESPACE

Namespace (database only or database and schema) that is active by default for the user’s session upon logging in.

DEFAULT_ROLE

Primary role that is active by default for the user’s session upon logging in.

DEFAULT_SECONDARY_ROLES

Set of secondary roles that are active for the user’s session upon logging in.

EXT_AUTHN_DUO

If true, Duo is enabled for the user, which requires the user to use MFA (multi-factor authentication) when logging in.

EXT_AUTHN_UID

Authorization ID used for Duo.

DEFAULT_MFA_METHOD

Default MFA method for the user.

HAS_MFA

If true, the user is enrolled in multi-factor authentication (MFA).

HAS_PAT

If true, the user has one or more programmatic access tokens.

HAS_FEDERATED_WORKLOAD_AUTHENTICATION

Reserved for future use.

MINS_TO_BYPASS_MFA

Number of minutes to temporarily bypass MFA requirement for the user.

MINS_TO_BYPASS_NETWORK_POLICY

Number of minutes to temporarily bypass the requirement of having a network policy for programmatic access tokens.

RSA_PUBLIC_KEY

RSA public key of the user for key-pair authentication.

RSA_PUBLIC_KEY_FP

Fingerprint of the user’s RSA public key.

RSA_PUBLIC_KEY_LAST_SET_TIME

Date and time when the RSA public key was last set for the user.

RSA_PUBLIC_KEY_2

Second RSA public key of the user for use during key-pair rotation.

RSA_PUBLIC_KEY_2_FP

Fingerprint of the user’s second RSA public key.

RSA_PUBLIC_KEY_2_LAST_SET_TIME

Date and time when the second RSA public key was last set for the user.

PASSWORD_LAST_SET_TIME

Date and time when the last non-NULL password was set for the user. If no password was set, the value of this property is NULL.

CUSTOM_LANDING_PAGE_URL

Reserved for future use.

CUSTOM_LANDING_PAGE_URL_FLUSH_NEXT_UI_LOAD

Reserved for future use.

Access control requirements

Individual users can see their own properties by executing this command and specifying their own name.

To view the properties of another user, you must use a role that has the following privilege:

Privilege

Object

Notes

OWNERSHIP

User

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.

Usage notes

  • The user object property MINS_TO_BYPASS_NETWORK_POLICY defines the number of minutes in which a user can access Snowflake without conforming to an existing network policy. The number of minutes can only be set by Snowflake (Default: NULL) and is intended as a temporary workaround to allow user access to Snowflake. To set a value for this property, please contact Snowflake Support.

  • This command does not show the session parameter defaults for a user. Instead, use SHOW PARAMETERS IN USER.

  • The user object property PASSWORD_LAST_SET_TIME defaults to Null if no password has been set yet. Values of 292278994-08-17 07:12:55.807 or 1969-12-31 23:59:59.999 indicate the password was set before the inclusion of this row. A value of 1969-12-31 23:59:59.999 can also indicate an expired password and the user needs to change their password.

  • To post-process the output of this command, you can use the pipe operator or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.

Examples

The following example describes the user named my_user:

DESCRIBE USER my_user;
Copy
+--------------------------------------------+-------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| property                                   | value                   | default | description                                                                                                                                |
|--------------------------------------------+-------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------|
| NAME                                       | JSMITH                  | null    | Name                                                                                                                                       |
| COMMENT                                    | null                    | null    | user comment associated to an object in the dictionary                                                                                     |
| DISPLAY_NAME                               | Jane Smith              | null    | Display name of the associated object                                                                                                      |
| TYPE                                       | PERSON                  | null    | Type of the account, application package, data exchange, data exchange listing, replication group, secret, network rule, or user.          |
| LOGIN_NAME                                 | JSMITH                  | null    | Login name of the user                                                                                                                     |
| FIRST_NAME                                 | Jane                    | null    | First name of the user                                                                                                                     |
| MIDDLE_NAME                                | null                    | null    | Middle name of the user                                                                                                                    |
| LAST_NAME                                  | Smith                   | null    | Last name of the user                                                                                                                      |
| EMAIL                                      | jane.smith@example.com  | null    | Email address of the user                                                                                                                  |
| PASSWORD                                   | ********                | null    | Password of the user                                                                                                                       |
| MUST_CHANGE_PASSWORD                       | false                   | false   | User must change the password                                                                                                              |
| DISABLED                                   | false                   | false   | Whether the entity is disabled                                                                                                             |
| SNOWFLAKE_LOCK                             | false                   | false   | Whether the user, account, or organization is locked by Snowflake                                                                          |
| SNOWFLAKE_SUPPORT                          | false                   | false   | Snowflake Support is allowed to use the user or account                                                                                    |
| DAYS_TO_EXPIRY                             | null                    | null    | User record will be treated as expired after specified number of days                                                                      |
| MINS_TO_UNLOCK                             | null                    | null    | Temporary lock on the user will be removed after specified number of minutes                                                               |
| DEFAULT_WAREHOUSE                          | MY_WAREHOUSE            | null    | Default warehouse for this user                                                                                                            |
| DEFAULT_NAMESPACE                          | MY_DB.MY_SCHEMA         | null    | Default database namespace prefix for this user                                                                                            |
| DEFAULT_ROLE                               | MY_ROLE                 | null    | Primary principal of user session will be set to this role                                                                                 |
| DEFAULT_SECONDARY_ROLES                    | []                      | [ALL]   | The secondary roles will be set to all roles provided here.                                                                                |
| EXT_AUTHN_DUO                              | false                   | false   | Whether Duo Security is enabled as second factor authentication                                                                            |
| EXT_AUTHN_UID                              | null                    | null    | External authentication ID of the user                                                                                                     |
| DEFAULT_MFA_METHOD                         | null                    | null    | Default MFA method for the user                                                                                                            |
| HAS_MFA                                    | true                    | false   | Whether the user is enrolled in multi-factor authentication                                                                                |
| HAS_PAT                                    | true                    | false   | Whether the user has a programmatic access token                                                                                           |
| HAS_FEDERATED_WORKLOAD_AUTHENTICATION      | false                   | false   | Reserved for future use                                                                                                                    |
| MINS_TO_BYPASS_MFA                         | null                    | null    | Temporary bypass MFA for the user for a specified number of minutes                                                                        |
| MINS_TO_BYPASS_NETWORK_POLICY              | null                    | null    | Temporary bypass network policy on the user for a specified number of minutes                                                              |
| RSA_PUBLIC_KEY                             | ...                     | null    | RSA public key of the user                                                                                                                 |
| RSA_PUBLIC_KEY_FP                          | SHA256:...=             | null    | Fingerprint of user's RSA public key.                                                                                                      |
| RSA_PUBLIC_KEY_LAST_SET_TIME               | null                    | null    | The timestamp at which the RSA public key was last set for the user. Defaults to null if no RSA public key has been set yet.               |
| RSA_PUBLIC_KEY_2                           | ...                     | null    | Second RSA public key of the user                                                                                                          |
| RSA_PUBLIC_KEY_2_FP                        | SHA256:...=             | null    | Fingerprint of user's second RSA public key.                                                                                               |
| RSA_PUBLIC_KEY_2_LAST_SET_TIME             | null                    | null    | The timestamp at which the second RSA public key was last set for the user. Defaults to null if no second RSA public key has been set yet. |
| PASSWORD_LAST_SET_TIME                     | 2020-10-08 01:33:13.43  | null    | The timestamp on which the last non-null password was set for the user. Default to null if no password has been set yet.                   |
| CUSTOM_LANDING_PAGE_URL                    | null                    | null    | Reserved for future use                                                                                                                    |
| CUSTOM_LANDING_PAGE_URL_FLUSH_NEXT_UI_LOAD | false                   | false   | Reserved for future use                                                                                                                    |
+--------------------------------------------+-------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
Language: English