How do I show all grants to all roles in Snowflake?

Question

In Snowflake, I know I can show roles:

show roles

I know I can show grants on specific roles and access those via query:

show grants to role dev_role;
select * from table(RESULT_SCAN (LAST_QUERY_ID()));

I'd like to show ALL grants for ALL roles in one table. My best guess would be to write a procedure that iterates through all the role names, executes the above code, and outputs the result to a table.

Is there a better way of doing this?

Answer 1

Do you have access to the shared database called snowflake? If not then you may need to ask an accountadmin to provide you the IMPORTED PRIVILEGES grant as explained here.

Anyway if you have access to this database then I think you should be able to get the info you need from the GRANTS_TO_ROLES view:

select * 
from snowflake.account_usage.grants_to_roles;

Answer 2

Unfortunately there is not currently a better way to accumulate the grant information. My current solution is exactly what you've described.