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 “SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_ROLES" object.
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.
This is a good programming exercise in tail call recursion (sort of) in JavaScript. Here is the code:
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:
IMPORTANT
This query must be the next statement run immediately after the CALL statement and cannot be run again until you run another CALL statement.
More adventures with Snowflake soon!
if you have enjoyed this post, please consider buying me a coffee ☕ to help me keep writing!