Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Snowflake Security Dashboards - ACCOUNTADMIN grant query won't return all grants #1500

Open
SnowflakeJim opened this issue Aug 22, 2024 · 0 comments

Comments

@SnowflakeJim
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant