Table: snowflake_user

A user is an identity recognized by Snowflake and can be associated with a person or program.

Note: This table can only be queried by users with a role that has the MANAGE GRANTS global privilege. This privilege is usually granted to the ACCOUNTADMIN and SECURITYADMIN roles.

Examples

Basic info

select
  name,
  login_name,
  disabled,
  default_role,
  default_warehouse,
  has_password,
  has_rsa_public_key,
  last_success_login
from
  snowflake_user;

List users that have passwords

select
  name,
  login_name,
  disabled,
  default_role,
  default_warehouse
from
  snowflake_user
where
  has_password;

List users whose passwords haven't been rotated in 90 days

select
  name,
  login_name,
  disabled,
  default_role,
  default_warehouse,
  has_password,
  password_last_set_time :: timestamp
from
  snowflake_user
where
  has_password
  and password_last_set_time :: timestamp < now() - interval '90 days';

List users using keypair authentication

select
  name,
  login_name,
  disabled,
  rsa_public_key,
  rsa_public_key_fp,
  rsa_public_key_2,
  rsa_public_key_2_fp
from
  snowflake_user
where
  has_rsa_public_key;

List users that have not logged in for 30 days

select
  name,
  email,
  disabled,
  last_success_login
from
  snowflake_user
where
  last_success_login is null
  or (last_success_login < now() - interval '30 days');

.inspect snowflake_user

A user identity recognized by Snowflake, whether associated with a person or program.

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account	text	The Snowflake account ID.
comment	text	Comment associated to user in the dictionary.
created_on	timestamp with time zone	Timestamp when the user was created.
custom_landing_page_url	text	Snowflake Support is allowed to use the user or account.
custom_landing_page_url_flush_next_ui_load	boolean	The timestamp on which the last non-null password was set for the user. Default to null if no password has been set yet.
days_to_expiry	text	User record will be treated as expired after specified number of days.
default_namespace	text	Default database namespace prefix for this user.
default_role	text	Primary principal of user session will be set to this role.
default_secondary_roles	text	The secondary roles will be set to all roles provided here.
default_warehouse	text	Default warehouse for this user.
disabled	text	Whether the user is disabled.
display_name	text	Display name of the user.
email	text	Email address of the user
expires_at_time	timestamp with time zone	The date and time when the user's status is set to EXPIRED and the user can no longer log in.
ext_authn_duo	boolean	Whether Duo Security is enabled as second factor authentication.
ext_authn_uid	text	External authentication ID of the user.
first_name	text	First name of the user.
has_password	boolean	Whether the user has password.
has_rsa_public_key	boolean	Whether the user has RSA public key.
last_name	text	Last name of the user.
last_success_login	timestamp with time zone	Date and time when the user last logged in to the Snowflake.
locked_until_time	timestamp with time zone	Specifies the number of minutes until the temporary lock on the user login is cleared.
login_name	text	Login name of the user.
mins_to_bypass_mfa	text	Temporary bypass MFA for the user for a specified number of minutes.
mins_to_bypass_network_policy	text	Temporary bypass network policy on the user for a specified number of minutes.
mins_to_unlock	text	Temporary lock on the user will be removed after specified number of minutes.
must_change_password	text	User must change the password.
name	text	Name of the snowflake user.
owner	text	Owner of the user in Snowflake.
password_last_set_time	text	The timestamp on which the last non-null password was set for the user. Default to null if no password has been set yet.
region	text	The Snowflake region in which the account is located.
rsa_public_key	text	RSA public key of the user.
rsa_public_key_2	text	Second RSA public key of the user.
rsa_public_key_2_fp	text	Fingerprint of user's second RSA public key.
rsa_public_key_fp	text	Fingerprint of user's RSA public key.
snowflake_lock	text	Whether the user or account is locked by Snowflake.
snowflake_support	text	Snowflake Support is allowed to use the user or account.

turbot/snowflake