turbot/snowflake

GitHub
steampipe plugin install snowflakesteampipe plugin install snowflake

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.

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accounttextThe Snowflake account ID.
commenttextComment associated to user in the dictionary.
created_ontimestamp with time zoneTimestamp when the user was created.
custom_landing_page_urltextSnowflake Support is allowed to use the user or account.
custom_landing_page_url_flush_next_ui_loadbooleanThe 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_expirytextUser record will be treated as expired after specified number of days.
default_namespacetextDefault database namespace prefix for this user.
default_roletextPrimary principal of user session will be set to this role.
default_secondary_rolestextThe secondary roles will be set to all roles provided here.
default_warehousetextDefault warehouse for this user.
disabledtextWhether the user is disabled.
display_nametextDisplay name of the user.
emailtextEmail address of the user
expires_at_timetimestamp with time zoneThe date and time when the user's status is set to EXPIRED and the user can no longer log in.
ext_authn_duobooleanWhether Duo Security is enabled as second factor authentication.
ext_authn_uidtextExternal authentication ID of the user.
first_nametextFirst name of the user.
has_passwordbooleanWhether the user has password.
has_rsa_public_keybooleanWhether the user has RSA public key.
last_nametextLast name of the user.
last_success_logintimestamp with time zoneDate and time when the user last logged in to the Snowflake.
locked_until_timetimestamp with time zoneSpecifies the number of minutes until the temporary lock on the user login is cleared.
login_nametextLogin name of the user.
mins_to_bypass_mfatextTemporary bypass MFA for the user for a specified number of minutes.
mins_to_bypass_network_policytextTemporary bypass network policy on the user for a specified number of minutes.
mins_to_unlocktextTemporary lock on the user will be removed after specified number of minutes.
must_change_passwordtextUser must change the password.
nametextName of the snowflake user.
ownertextOwner of the user in Snowflake.
password_last_set_timetextThe timestamp on which the last non-null password was set for the user. Default to null if no password has been set yet.
regiontextThe Snowflake region in which the account is located.
rsa_public_keytextRSA public key of the user.
rsa_public_key_2textSecond RSA public key of the user.
rsa_public_key_2_fptextFingerprint of user's second RSA public key.
rsa_public_key_fptextFingerprint of user's RSA public key.
snowflake_locktextWhether the user or account is locked by Snowflake.
snowflake_supporttextSnowflake Support is allowed to use the user or account.