Snowflake warehouse: get all roles (including inherited ones) assigned to a user

Question

I am trying to get all the roles a user is part of. In my case, the user is part of an admin role which inherits another role ingestor, this inherits another role analyst. If I query from snowflake like as follows:

show grants to user <userid>

This lists only the admin role but not other two roles (ingestor, analyst). If the same user logs into snowflake, he could see all three roles available for him in the role dropdown.

Need help to get all explicit roles irrespective of role inheritance.

Answer 1

As a start, the views "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_USERS" and "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_ROLES" in combination have the information you need,
but are only accessible to ACCOUNTADMIN

You also have:

SELECT * FROM "MY_DATABASE"."INFORMATION_SCHEMA"."ENABLED_ROLES";
SELECT * FROM "MY_DATABASE"."INFORMATION_SCHEMA"."APPLICABLE_ROLES";

The latter looks like a good place to start.

Answer 2

i found the best way to find all roles with inherited roles.

just run below SQL.

SELECT CURRENT_AVAILABLE_ROLES()