Using Snowsight Tiles to Create a Security Sentry Dashboard

Turning your Snowflake Account into a Fortress!

Table of Contents:

• Introduction
• Creating the Sentry Dashboard
• Creating a Tile
• Building the Sentry Dashboard
• Wrapping Up

Introduction

Snowflake has maintained that security is a fundamental aspect of an enterprise data platform. New enhancements (like the Trust Center) are adding to the suite of tools already available: multi-factor, key-pair and OAuth authentication as well as networking policies to name a few.

Monitoring your Snowflake account is a key component of the continuous cybersecurity battle. Today, I’d like to share how you can create your very own Security Sentry Dashboard within your Snowflake account.

This article is based on a Quickstart Guide written by the Snowflake Security Field CTO team. You can find the link to the guide and the code here.

I have updated the queries to reflect new platform features including network rules, external access, user types and more. I have also modified the string wildcard expressions for deeper filtering of the query_history view.

Dashboards in Snowflake have been around for some time. Recently Streamlit-in-Snowflake (SiS) for application views has become popular. However, SiS is not generally available for Private Link or government regions at this time of writing. Dashboards will enable you to easily create a shareable monitoring hub in your account.

This is meant to a be a starting point for your organization’s Snowflake security strategy. If you’d like to learn more, visit the official security documentation and contact your Snowflake account team.

Creating the Sentry Dashboard

We can quickly set up a new role for the Sentry project by running the code below in a SQL Worksheet in the Snowsight UI. Please modify depending on your organization’s security strategy:

use role accountadmin; 
create role SENTRY; --feel free to replace this with another role name
grant imported privileges on database snowflake to role SENTRY;
create or replace warehouse SENTRY with warehouse_size='SMALL';
grant usage on warehouse SENTRY to role SENTRY;
grant role SENTRY to user <YOURUSER>; --replace with your own username

To create a Dashboard, navigate to the Projects tab on the left side of the UI, click on Dashboards. Make sure you are using your newly created role:

Once you’re on the Dashboards page, click + Dashboard on the top right:

Then after checking that the new role and warehouse are selected as the context, we’ll click the + sign on the left to create a new tile:

Creating a Tile

Now let’s create the first tile by clicking on the + sign and selecting “New Tile from SQL Worksheet”. We’ll need to run the following for each tile to set the context. Just add it to the start of every code block going forward:

use role SENTRY; 
use warehouse SENTRY;
use database snowflake;
use schema account_usage;

The first tile is going to find the number of failed logins to the account, grouped by username and the associated error message:

-- find the number of failed logins by user, by reason
select
    user_name,
    error_message,
    count(*) num_of_failures
from
    snowflake.account_usage.login_history
where
    is_success = 'NO'
group by
    user_name,
    error_message
order by
    num_of_failures desc;

After running this in the Dashboard tile, click on Chart:

Let’s rename the chart to Authentication: Failures, by User and Reason by editing the timestamped name above the chart.

We can modify the chart to create a stacked bar chart by modifying some of the Data and Appearance toggles. First set the User_Name to the X or Y Axis, then set Error_Message to be a Series. Lastly add Num_of_Failures (Sum) as the value. It should look like the below image:

To stack the bar chart, click on the stacked picture under Grouping:

Feel free to customize the chart even further by adding Axis Labels, changing the orientation, etc. Once you’re done, this chart should show you the reason for login failure by every user in the account:

When finished, click “Return to <your dashboard name>” in the top left:

Building the Sentry Dashboard

We’ll speed it up a bit now, providing just the SQL for the tabular results, and a sample of what charts are available to use. Feel free to add components and changes to the code for your business needs. With multiple tiles you can drag them to other locations in the same Dashboard

Now you’re now a Tile expert and can create them on your own!

Once these Tiles are completed and your Dashboard is finished, you can share this asset to other users that have the same Role or associated privileges.

You can learn more about that in the documentation.

Authentication

Authentication: Breakdown by Method

-- find out how users are authenticating to the platform 
select
   first_authentication_factor || ' ' ||nvl(second_authentication_factor, '') as authentication_method
   , count(*)
    from snowflake.account_usage.login_history
    where is_success = 'YES'
    and user_name != 'WORKSHEETS_APP_USER'
    group by authentication_method
    order by count(*) desc;

Understand which methods users are employing to access Snowflake.

Authentication: Service identities bypassing keypair auth with a password

-- find service accounts denoted by "%svc" convention, not using key-pair
SELECT
 l.user_name,
 first_authentication_factor,
 second_authentication_factor,
 count(*) as Num_of_events
FROM snowflake.account_usage.login_history as l
JOIN snowflake.account_usage.users u on l.user_name = u.name and l.user_name ilike '%svc%' and has_rsa_public_key = 'true'
WHERE is_success = 'YES'
AND first_authentication_factor != 'RSA_KEYPAIR'
GROUP BY l.user_name, first_authentication_factor, second_authentication_factor
ORDER BY count(*) desc;

This assumes the service accounts (connected applications or tools) are assuming a user like “%svc%”. This may change depending on the organizational naming convention. You could filter on the new TYPE column in the USERS view to search for ‘SERVICE’ or ‘LEGACY_SERVICE’.

Authentication: Connections by Client Type

-- find the types of clients being used to access the Snowflake account
select client_ip, user_name, reported_client_type, first_authentication_factor, count(*)
from snowflake.account_usage.login_history
group by client_ip, user_name, reported_client_type, first_authentication_factor
order by count(*) desc;

Investigate unknown clients/drivers connected to the account here.

Privileged Access

Privileged Access: ACCOUNTADMIN Grants

-- create an audit trail of accountadmin grants to other roles
select
    user_name || ' granted the ' || role_name || ' role on ' || end_time as Description, query_text as Statement
from
    snowflake.account_usage.query_history
where
    execution_status = 'SUCCESS'
    and query_type = 'GRANT'
    and query_text ilike '%grant%accountadmin%to%'
order by
    end_time desc;

Find the number of accountadmins and who’s granted this to other roles.

Privileged Access: ACCOUNTADMINs that do not use MFA

-- investigate accountadmins who do not have MFA enabled on their user 
select u.name, 
timediff(days, last_success_login, current_timestamp()) || ' days ago' last_login ,
timediff(days, password_last_set_time,current_timestamp(6)) || ' days ago' password_age
from snowflake.account_usage.users u
join snowflake.account_usage.grants_to_users g on grantee_name = name and role = 'ACCOUNTADMIN' and g.deleted_on is null
where ext_authn_duo = false and u.deleted_on is null and has_password = true
order by last_success_login desc;

You could also extend this to the other roles in your Snowflake account.

Privileged Access: All users who are not using MFA

-- investigate all users who do not have MFA enabled on their user 
select u.name, 
timediff(days, last_success_login, current_timestamp()) || ' days ago' last_login ,
timediff(days, password_last_set_time,current_timestamp(6)) || ' days ago' password_age
from snowflake.account_usage.users u
join snowflake.account_usage.grants_to_users g on grantee_name = name and g.deleted_on is null
where ext_authn_duo = false and u.deleted_on is null and has_password = true
order by last_success_login desc;

This query extends the previous one to find all users who aren’t using MFA.

Privileged Access: All Person users who are not using MFA

-- all users who aren't MFA enabled and are PERSON, not service accounts. 
select u.name, 
timediff(days, last_success_login, current_timestamp()) || ' days ago' last_login ,
timediff(days, password_last_set_time,current_timestamp(6)) || ' days ago' password_age
from snowflake.account_usage.users u
join snowflake.account_usage.grants_to_users g on grantee_name = name and g.deleted_on is null
where ext_authn_duo = false and u.deleted_on is null and has_password = true and type='PERSON'
order by last_success_login desc; 

This query leverages the new TYPE parameter for the from the USERS table. You can learn more about in our documentation here. If there is an invalid identifier error, the change bundle may not have been enabled yet.

Privileged Access: Users not using AccountAdmin Role, by query type

-- find the users that are not using AccountAdmin Role as much
select q.query_type, q.user_name as "User", count(*) as queries_ran
from snowflake.account_usage.query_history q
left join snowflake.account_usage.grants_to_users g on g.grantee_name = q.user_name
where g.role = 'ACCOUNTADMIN'
group by q.user_name, q.query_type order by queries_ran desc;

This query will help you understand which users are not using their AccountAdmin role very often. It’s possible they no longer require it.

Identity Management

Identity Management: Users by Oldest Passwords

-- find out which users are not rotating passwords effectively 
select name, datediff('day', password_last_set_time, current_timestamp()) || ' days ago' as password_last_changed 
from snowflake.account_usage.users where deleted_on is null and 
password_last_set_time is not null
order by password_last_set_time;

Use this tile to find users that haven’t rotated their stale password.

Identity Management: Stale Users

-- find users who haven't logged into Snowflake in a long time based on history
select name, datediff("day", nvl(last_success_login, created_on), current_timestamp()) || ' days ago' Last_Login 
from snowflake.account_usage.users where deleted_on is null
order by datediff("day", nvl(last_success_login, created_on), current_timestamp()) desc;

Find the users that haven’t logged into Snowflake for a while.

Identity Management: Popular Default Roles

-- find the most popular default roles and secondary roles
select default_role, default_secondary_role, count(*) as number_of_defaults
from snowflake.account_usage.users where deleted_on is null
group by default_role, default_secondary_role
order by number_of_defaults desc;

Use the above query to find the most popular default roles for users. Check this query to see if users should not default to highly privileged roles.

Identity Management: SCIM Token Lifecycle

-- check on the health of the SCIM token in terms of upcoming expiry 
select
    user_name as by_whom,
    datediff('day', start_time, current_timestamp()) || ' days ago' as created_on,
    ADD_MONTHS(start_time, 6) as expires_on,
    datediff(
        'day',
        current_timestamp(),
        ADD_MONTHS(end_time, 6)
    ) as expires_in_days
from
    snowflake.account_usage.query_history
where
    execution_status = 'SUCCESS'
    and query_text ilike 'select%SYSTEM$GENERATE_SCIM_ACCESS_TOKEN%'
    and query_text not ilike 'select%where%SYSTEM$GENERATE_SCIM_ACCESS_TOKEN%'
order by
    expires_in_days;

Use this to check on the token provided by the SCIM provider system.

Least Privileged Access

Least Privileged Access: Most Dangerous Person

-- extract the user with the most granted privileges in the account 
with role_hier as (
    --Extract all Roles
    select
        grantee_name,
        name
    from
        snowflake.account_usage.grants_to_roles
    where
        granted_on = 'ROLE'
        and privilege = 'USAGE'
        and deleted_on is null
    union all
        --Adding in dummy records for "root" roles
    select
        'root',
        r.name
    from
        snowflake.account_usage.roles r
    where
        deleted_on is null
        and not exists (
            select
                1
            from
                snowflake.account_usage.grants_to_roles gtr
            where
                gtr.granted_on = 'ROLE'
                and gtr.privilege = 'USAGE'
                and gtr.name = r.name
                and deleted_on is null
        )
) --CONNECT BY to create the polyarchy and SYS_CONNECT_BY_PATH to flatten it
,
role_path_pre as(
    select
        name,
        level,
        sys_connect_by_path(name, ' -> ') as path
    from
        role_hier connect by grantee_name = prior name start with grantee_name = 'root'
    order by
        path
) --Removing leading delimiter separately since there is some issue with how it interacted with sys_connect_by_path
,
role_path as (
    select
        name,
        level,
        substr(path, len(' -> ')) as path
    from
        role_path_pre
) --Joining in privileges from GRANT_TO_ROLES
,
role_path_privs as (
    select
        path,
        rp.name as role_name,
        privs.privilege,
        granted_on,
        privs.name as priv_name,
        'Role ' || path || ' has ' || privilege || ' on ' || granted_on || ' ' || privs.name as Description
    from
        role_path rp
        left join snowflake.account_usage.grants_to_roles privs on rp.name = privs.grantee_name
        and privs.granted_on != 'ROLE'
        and deleted_on is null
    order by
        path
) --Aggregate total number of priv's per role, including hierarchy
,
role_path_privs_agg as (
    select
        trim(split(path, ' -> ') [0]) role,
        count(*) num_of_privs
    from
        role_path_privs
    group by
        trim(split(path, ' -> ') [0])
    order by
        count(*) desc
) --Most Dangerous Person - final query
select
    grantee_name as user,
    count(a.role) num_of_roles,
    sum(num_of_privs) num_of_privs
from
    snowflake.account_usage.grants_to_users u
    join role_path_privs_agg a on a.role = u.role
where
    u.deleted_on is null
group by
    user
order by
    num_of_privs desc;

This query will leverage the grants and roles Account Usage views to find the users that have the most privileges in your account. You could consider this the most “dangerous person”, who should have MFA enabled!

Least Privileged Access: Most Bloated Roles

-- find the roles with the most privileges in the account 
--Role Hierarchy
with role_hier as (
    --Extract all Roles
    select
        grantee_name,
        name
    from
        snowflake.account_usage.grants_to_roles
    where
        granted_on = 'ROLE'
        and privilege = 'USAGE'
        and deleted_on is null
    union all
        --Adding in dummy records for "root" roles
    select
        'root',
        r.name
    from
        snowflake.account_usage.roles r
    where
        deleted_on is null
        and not exists (
            select
                1
            from
                snowflake.account_usage.grants_to_roles gtr
            where
                gtr.granted_on = 'ROLE'
                and gtr.privilege = 'USAGE'
                and gtr.name = r.name
                and deleted_on is null
        )
) --CONNECT BY to create the polyarchy and SYS_CONNECT_BY_PATH to flatten it
,
role_path_pre as(
    select
        name,
        level,
        sys_connect_by_path(name, ' -> ') as path
    from
        role_hier connect by grantee_name = prior name start with grantee_name = 'root'
    order by
        path
) --Removing leading delimiter separately since there is some issue with how it interacted with sys_connect_by_path
,
role_path as (
    select
        name,
        level,
        substr(path, len(' -> ')) as path
    from
        role_path_pre
) --Joining in privileges from GRANT_TO_ROLES
,
role_path_privs as (
    select
        path,
        rp.name as role_name,
        privs.privilege,
        granted_on,
        privs.name as priv_name,
        'Role ' || path || ' has ' || privilege || ' on ' || granted_on || ' ' || privs.name as Description
    from
        role_path rp
        left join snowflake.account_usage.grants_to_roles privs on rp.name = privs.grantee_name
        and privs.granted_on != 'ROLE'
        and deleted_on is null
    order by
        path
) --Aggregate total number of priv's per role, including hierarchy
,
role_path_privs_agg as (
    select
        trim(split(path, ' -> ') [0]) role,
        count(*) num_of_privs
    from
        role_path_privs
    group by
        trim(split(path, ' -> ') [0])
    order by
        count(*) desc
) 
select * from role_path_privs_agg order by num_of_privs desc 
--limit 1 to find the top 1

This tile finds the roles that have the most privileges in the account. The ideal state, similar for users, is that roles only have the entitlements needed to complete their business goals and functions. You can use a Scorecard to show how many privileges the most bloated role has been granted.

Least Privileged Access: Infrequently Used Roles

-- find the roles and the associated user that hasn't been used in a while
with least_used_roles (user_name, role_name, last_used, times_used) as
(select user_name, role_name, max(end_time), count(*) from snowflake.account_usage.query_history group by user_name, role_name order by user_name, role_name)
select grantee_name,
role,
nvl(last_used, (select min(start_time) from SNOWFLAKE.ACCOUNT_USAGE.query_history)) last_used,
nvl(times_used, 0) times_used, datediff(day, created_on, current_timestamp()) || ' days ago' age
from snowflake.account_usage.grants_to_users
left join least_used_roles on user_name = grantee_name and role = role_name
where deleted_on is null order by last_used, times_used, age desc;

This can be used to find potential revokable roles from specific users.

Configuration Management

Configuration Management: Privileged Object Changes by User

-- track all of the major changes occuring in your account at a time 
-- modify the query_text parameter in the query as needed 
select
  query_text,
  user_name,
  role_name,
  end_time
from snowflake.account_usage.query_history
  where execution_status = 'SUCCESS'
    and query_type not in ('SELECT')
    --AND user_name= '<USER>'
    and (query_text ILIKE '%create% %role%'
        or query_text ILIKE '%manage %grants%'
        or query_text ILIKE '%create% %integration%'
        or query_text ILIKE '%alter %integration%'
        or query_text ILIKE '%create% %share%'
        or query_text ILIKE '%create% %account%'
        or query_text ILIKE '%monitor% %usage%'
        or query_text ILIKE '%ownership%'
        or query_text ILIKE '%drop% %table%'
        or query_text ILIKE '%drop% %database%'
        or query_text ILIKE '%create% %stage%'
        or query_text ILIKE '%drop% %stage%'
        or query_text ILIKE '%alter% %stage%'
        or query_text ILIKE '%create% %user%'
        or query_text ILIKE '%alter% %user%'
        or query_text ILIKE '%drop% %user%'
        or query_text ILIKE '%create% %network% %policy%'
        or query_text ILIKE '%alter% %network% %policy%'
        or query_text ILIKE '%drop% %network% %policy%'
        or query_text ILIKE '%create% %network% %rule%'
        or query_text ILIKE '%alter% %network% %rule%'
        or query_text ILIKE '%drop% %network% %rule%'
        or query_text ILIKE '%copy%'
        )
order byend_time desc;

This can be used to understand the critical changes happening in a Snowflake account and which users executed them. The “%” in the query above represents any number of characters, including zero characters. As always here, this may include commands that do have the SQL wording sequentially. So this query may be overzealous in capturing information!

Configuration Management: Network Policy Changes

-- monitor any changes made to which network origination points can access 
select user_name || ' made the following Network Policy change on ' || end_time || ' [' ||  query_text || ']' as Events
   from snowflake.account_usage.query_history where execution_status = 'SUCCESS'
   and query_type in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY')
   or (query_text ilike '%set% %network_policy%' or
       query_text ilike '%unset% %network_policy%')
       and query_type != 'SELECT' and query_type != 'UNKNOWN'
   order by end_time desc;

Network policies are a powerful tool for security a Snowflake account, use this tile to check which users have made any policy changes.

Configuration Management: Network Policies without Network Rules

--find networking policies that do not have a network rule attached. 
select * from network_policies where name not in 
(select container_name from network_rule_references);

This query will search to find networking policies that do not have an assigned network rules. It is a new best practice to set a network rule on the networking policy, in place of using ALLOW and BLOCK lists directly.

Configuration Management: External Network Access Policies

-- find usage patterns of snowpark external access history 
select * from snowflake.account_usage.external_access_history;

This will help you understand where users are leveraging external access integrations from within the account.

Configuration Management: External Network Access Setup

-- track all of the necessary changes occuring in your account to set up external access integrations
-- modify the query_text parameter in the query as needed 
select 
  query_text,
  user_name,
  role_name,
  end_time
from snowflake.account_usage.query_history
  where execution_status = 'SUCCESS'
    and query_type not in ('SELECT')
    --AND user_name= '<USER>'
    and (query_text ILIKE '%create% %network% rule%'
        or query_text ILIKE '%create% %security% %integration%'
        or query_text ILIKE '%create% %secret%'
        or query_text ILIKE '%create% %external% %access% %integration%'
        or query_text ILIKE '%grant% %read% %on% %secret%'
        or query_text ILIKE '%grant% %usage% %on% %integration%'
        or query_text ILIKE '%alter% %network% %rule%'
        or query_text ILIKE '%alter% %security% %integration%'
        or query_text ILIKE '%alter% %secret%'
        or query_text ILIKE '%alter% %external% %access% %integration%'
        )
order by end_time desc;

This query will find the relevant queries that are used in general to set up external access integrations including network rules, security integrations, external access integrations and secrets. Again, this query may capture more queries than accurate due to the query_history filtering with “%”.

Session History

Session History: Client Application and OS interactions

-- displays the client applications and OS that are interacting with Snowflake
--replace the sessions.created_on filter as necessary
select
    count(*) as client_application_count,
    parse_json(client_environment):APPLICATION::STRING asclient_application,
    parse_json(client_environment):OS::STRING as client_os,
    parse_json(client_environment):OS_VERSION::STRING asclient_os_version
from snowflake.account_usage.sessions
where sessions.created_on >= '2024-01-01' --change the data here as neccesary 
group by all order by client_application_count asc;

You can use this query to investigate anomalous client OS and applications.

Wrapping Up!

Once all of these Tiles are complete, you should be left with a new Security Sentry Dashboard for your Snowflake account. These findings can now be shared and used to monitor the environment. Refresh the page for the latest updates by hitting Run in the top right corner. That’s it!

Please remember to update these queries as new features are added to the platform; data security is continuous exercise and won’t be solved with just these queries. Continue to follow and build upon best practices.

I hope you all enjoyed this post and please comment if you have any additional thoughts on this project. Thank you to the Snowflake Security FCTO team for the Quickstart Guide.

Disclaimer: The opinions expressed in this post are my own and not necessarily those of my employer (Snowflake).