turbot/snowflake
steampipe plugin install snowflake

Table: snowflake_user - Query OCI Snowflake Users using SQL

Snowflake is a cloud-based data warehousing platform that enables data storage, processing, and analytic solutions. It is designed to support and manage all aspects of data, analytics, and application integration. Snowflake is built on top of the Amazon Web Services (AWS) cloud infrastructure and is a scalable and elastic solution that can handle high volumes of data and concurrent workloads.

Table Usage Guide

The snowflake_user table provides insights into users within the OCI Snowflake service. As a data analyst or database administrator, explore user-specific details through this table, including user properties, roles, and status. Utilize it to manage user access, track user activity, and ensure compliance with your organization's data usage policies.

Examples

Basic info

Explore user profiles on your Snowflake platform to understand their access level and recent activity. This aids in maintaining security by identifying unusual behavior or inactive accounts.

select
name,
login_name,
disabled,
default_role,
default_warehouse,
has_password,
has_rsa_public_key,
last_success_login
from
snowflake_user;
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

Discover the segments that have passwords in the Snowflake user base to assess the elements within the user configuration. This can help in identifying instances where users may have potential security risks.

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

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

Assess the elements within your user base to identify those who haven't updated their passwords in the past 90 days. This can be useful for enforcing security standards and ensuring regular password rotation.

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';
select
name,
login_name,
disabled,
default_role,
default_warehouse,
has_password,
datetime(password_last_set_time)
from
snowflake_user
where
has_password
and datetime(password_last_set_time) < datetime('now', '-90 days');

List users using keypair authentication

Discover the segments that use keypair authentication to gain insights into the security measures in place for user access. This can be useful in assessing the strength and variety of authentication methods employed within your system.

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;
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 = 1;

List users that have not logged in for 30 days

Identify users who haven't engaged with your platform in the last month. This can help in tailoring re-engagement strategies and understanding user activity patterns.

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

Control examples

Schema for snowflake_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accounttext=, !=, ~~, ~~*, !~~, !~~*The 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.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- snowflake

You can pass the configuration to the command with the --config argument:

steampipe_export_snowflake --config '<your_config>' snowflake_user