אבטחת / הקשחת סביבת סנופלייק

-- USERS WITH RISKY AUTHENTICATION METHOD
--*****************************************
use role accountadmin;

With admins as (select GRANTEE_NAME
 from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
where ROLE in ('ACCOUNTADMIN','SECURITYADMIN')
  and DELETED_ON is null
),
sessions as (
select current_account() company_slug, parse_json(CLIENT_ENVIRONMENT):APPLICATION::string application_full_name
        , ifnull(regexp_substr(application_full_name,'(VSCODE)\s* (TableauServer)\d*|(TableauDesktop)\d*')
        , application_full_name) application_name, *
From SNOWFLAKE.ACCOUNT_USAGE.SESSIONS
Where created_on >= dateadd('d',-90, current_date()) )

Select user_name, application_name, authentication_method, case when admins.GRANTEE_NAME is not null then 'YES' else null end HAS_ACCOUNTADMIN, CASE WHEN HAS_ACCOUNTADMIN = 'YES' and AUTHENTICATION_METHOD = 'Password' Then 'CRITICAL'    WHEN  AUTHENTICATION_METHOD = 'Password' Then 'HIGH'    End Risk    , count(*) login_events    , datediff('d', max(created_on), current_date()) last_used_in_days
from sessions 
    left outer join admins
        on sessions.user_name = admins.GRANTEE_NAME
Where  risk is not null
group by all
order by 1

-- USERS LAST LOGIN IN DAYS
--*************************
use role accountadmin;

WITH admins as (
    select GRANTEE_NAME user_name
     from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
    where ROLE in ('ACCOUNTADMIN','SECURITYADMIN')
      and DELETED_ON is null
    ),
-- Get the last login date for each user
last_login_dates AS (
  SELECT     user_name,    MAX(event_timestamp) AS last_login_date
  FROM    snowflake.account_usage.login_history
  GROUP BY     user_name),
-- Get all users
all_users AS (
  SELECT     name AS user_name, *
  FROM     snowflake.account_usage.users
  where not disabled and has_password and deleted_on is null
)
-- Find users who have not logged in in the last 3 months
SELECT    u.user_name, datediff('d',max(ifnull( lld.last_login_date, '2022-01-01')), current_date()) last_login_in_days
,case when admins.user_name is not null then 'YES' else null end HAS_ACCOUNTADMIN
,case when last_login_in_days >= 90 then concat('ALTER USER ', u.user_name, ' SET DISABLED=TRUE;') else null End Action
FROM   all_users u
LEFT JOIN   last_login_dates lld 
ON   u.user_name = lld.user_name
LEFT JOIN   admins  
ON   u.user_name = admins.user_name
group by all
order by last_login_in_days desc;