-- 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;
Comentarios