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!