Snowflake allows roles to be assigned to other roles, so when a user is assigned to a role, they may inherit the ability to use countless other roles.
Challenge: recursively enumerate all roles for a given user
One solution would be to create a complex query on the
An easier solution is to use a stored procedure to recurse through grants for a given user and return an
ARRAY of roles for that user.
To call the stored proc, execute:
One drawback of stored procedures in Snowflake is that they can only have scalar or array return types and cannot be used directly in a SQL query, however you can use the
table(result_scan(last_query_id())) trick to get around this, as shown below where we will pivot the
ARRAY into a record set with the array elements as rows:
This query must be the next statement run immediately after the
CALL statement and cannot be run again until you run another
More adventures with Snowflake soon!
if you have enjoyed this post, please consider buying me a coffee ☕ to help me keep writing!