CODEWITHSUNDEEP
snowflake-cloud-data-platform
Marty_C137
Marty_C137

Reputation: 439

Snowflake - query to find roles that haven't been used recently

I am trying to delete roles in Snowflake that haven't been used recently.

Is there a way to query the last date a role was used to execute a query? query_history seems promising, but according to the docs, it only allows query_history_by_user.

Upvotes: 1

Views: 1176

Answers (3)

user22298910
user22298910

Reputation: 1

If your query uses permissions from multiple roles, I believe it only logs primary role used. If inherited/nested roles or 'secondary roles all' are used, they do not appear in query_history.

Upvotes: 0

Marty_C137
Marty_C137

Reputation: 439

Here's a solution that returns the role name and the last date that it was used. Note that the query_history view only has data for the last 365 days, so you cannot return a date for roles that haven't been used in > 1 year:

with RECENTLY_USED as (
    select ROLE_NAME, max(END_TIME) as last_query_date  
    from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    where END_TIME >= dateadd(day, -30, current_timestamp)
    group by 1
), 

LAST_YEAR as (
    select ROLE_NAME, max(END_TIME) as last_query_date  
    from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    where END_TIME >= dateadd(year, -1, current_timestamp)
    group by 1
)

select ROLE_NAME, LAST_QUERY_DATE from LAST_YEAR  
minus 
select ROLE_NAME, LAST_QUERY_DATE from RECENTLY_USED
order by LAST_QUERY_DATE;

Output:

ROLE_NAME LAST_QUERY_DATE
My_old_role 2022-04-21T11:44:38.384-05:00
My_other_old_role 2022-06-07T15:14:45.245-05:00

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11046

You can get that from the SNOWFLAKE database. Just keep in mind that the data in the Snowflake database is up to 3 hours delayed, but most views are about 15-20 minutes or so delayed:

-- This shows all roles used in the last 30 days
select  ROLE_NAME 
from    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where   END_TIME >= dateadd(day, -30, current_timestamp)
group by 1
;

-- This shows all roles:
select NAME as ROLE_NAME from SNOWFLAKE.ACCOUNT_USAGE.ROLES where DELETED_ON is null
;

-- Combine the two and join to get unused roles in the last 30 days:
with USED_ROLES as
(
select  ROLE_NAME 
from    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where   END_TIME >= dateadd(day, -30, current_timestamp)
group by 1
), ROLES as
(
select NAME as ROLE_NAME from SNOWFLAKE.ACCOUNT_USAGE.ROLES where DELETED_ON is null
)
select ROLE_NAME from ROLES where ROLE_NAME not in (select ROLE_NAME from USED_ROLES)

Upvotes: 1

Related Questions