You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Describe the bug
In the Snowflake Security Dashboards, Step 5 (Privileged Access Tiles), the Privileged Access: ACCOUNTADMIN Grants SQL is as follows:
select
user_name || ' granted the ' || role_name || ' role on ' || end_time as Description, query_text as Statement
from
query_history
where
execution_status = 'SUCCESS'
and query_type = 'GRANT'
and query_text ilike '%grant%accountadmin%to%'
order by
end_time desc;
However, if ACCOUNTADMIN is granted via Snowsight by clicking on the Grant and adding users, or clicking on the User and adding the grants then the grant won't be captured in QUERY_HISTORY.
A better query would be:
select gtu.created_on, gtu.grantee_name || ' was granted ACCOUNTADMIN.' as DESCRIPTION
from snowflake.account_usage.grants_to_users gtu
join snowflake.account_usage.users u
on gtu.grantee_name = u.name
and u.disabled = false
where gtu.deleted_on is null
and gtu.granted_to = 'USER'
and gtu.role = 'ACCOUNTADMIN'
order by gtu.created_on desc
;
Describe the bug
In the Snowflake Security Dashboards, Step 5 (Privileged Access Tiles), the Privileged Access: ACCOUNTADMIN Grants SQL is as follows:
select
user_name || ' granted the ' || role_name || ' role on ' || end_time as Description, query_text as Statement
from
query_history
where
execution_status = 'SUCCESS'
and query_type = 'GRANT'
and query_text ilike '%grant%accountadmin%to%'
order by
end_time desc;
However, if ACCOUNTADMIN is granted via Snowsight by clicking on the Grant and adding users, or clicking on the User and adding the grants then the grant won't be captured in QUERY_HISTORY.
A better query would be:
select gtu.created_on, gtu.grantee_name || ' was granted ACCOUNTADMIN.' as DESCRIPTION
from snowflake.account_usage.grants_to_users gtu
join snowflake.account_usage.users u
on gtu.grantee_name = u.name
and u.disabled = false
where gtu.deleted_on is null
and gtu.granted_to = 'USER'
and gtu.role = 'ACCOUNTADMIN'
order by gtu.created_on desc
;
URL of where you see the bug
https://quickstarts.snowflake.com/guide/security_dashboards_for_snowflake/index.html?index=..%2F..index#4
To Reproduce
N/A
Expected behavior
All active users with ACCOUNTADMIN grants should be returned by the query
Screenshots
N/A
Desktop (please complete the following information):
N/A
Smartphone (please complete the following information):
N/A
Additional context
Observed in account https://spb60398.snowflakecomputing.com
The text was updated successfully, but these errors were encountered: