Roles Metadata Tables in Snowflake

Question

I need to check the roles and grants given to users, but from the metadata tables. Basically, I need the metadata table, where I can query this, using multiple roles, eg. XXX, YYY,ZZZ. I need this to get the hierarchy of the roles that might have been granted.

I can do show grants OF role XXX - This'll give me all the users/ roles to which this role is granted, but I have to do for one role at a time. If I do

SELECT * 
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS 
WHERE ROLE='XXX' 
AND DELETED_ON IS null;

It gives me only the users who have been granted this role, not the roles itself. If I check on GRANTS_TO_ROLES table - it gives me the actual privileges given for that role, but not the other roles to which the particular role is granted to.

Answer 1

What you can do is use the SHOW syntax rather than select:

show roles in account;

will give you the full list of roles (still coming from metadata). Not sure what exactly you want to do with them further, but in case you'd actually want to proceed the results as a query you can follow it by

select * from table(result_scan(last_query_id()));

and use to join with other tables or just to copy into some sort of temporary table and join further from there

Answer 2

You mentioned the hierarchy - I suspect you want to see which roles are granted to other roles..

Try this:

show grants of role Your_Role

In the table returned you will see that some roles are assigned to other roles and to users..i.e. column granted_to