snowflake show Roles does not show the user name

user_11366870 0
Viewed 187

Show ROLES provide me the info about Roles and user to whom the roles are assigned to. However instead of providing the username it gives the user number like below in assigned_to_users

show roles;

---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+
           created_on            |     name      | is_default | is_current | is_inherited | assigned_to_users | granted_to_roles | granted_roles |     owner     |         comment          |
---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+
 Fri, 05 Dec 2014 16:25:06 -0800 | ACCOUNTADMIN  | Y          | Y          | N            | 1                 | 0                | 2             |               |                          |
 Mon, 15 Dec 2014 17:58:33 -0800 | ANALYST       | N          | N          | N            | 0                 | 6                | 0             | SECURITYADMIN | Data analyst             |
 Fri, 05 Dec 2014 16:25:06 -0800 | PUBLIC        | N          | N          | Y            | 0                 | 0                | 0             |               |                          |
 Fri, 05 Dec 2014 16:25:06 -0800 | SECURITYADMIN | N          | N          | Y            | 0                 | 1                | 0             |               |                          |
 Fri, 05 Dec 2014 16:25:06 -0800 | SYSADMIN      | N          | N          | Y            | 5                 | 1                | 2             |               |                          |
---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+

is there a way I can join the number to actual user.

snowflake-cloud-data-platform
2 Answers

SHOW USERS is show you the count of users it is assigned to:

SHOW USERS;

created_on name is_default is_current is_inherited assigned_to_users granted_to_roles granted_roles
2022-03-06 USERADMIN N N Y 0 1 0 
create user testo;

grant role useradmin to user testo;

SHOW USERS;
created_on name is_default is_current is_inherited assigned_to_users granted_to_roles granted_roles
2022-03-06 USERADMIN N N Y 1 1 0

You might want the SHOW GRANTS command, in the show grants of role <role_name>; form? But that will require multiple requests to see all GRANTS of ALL roles to all users.

OR on a per database level information_schema.APPLICABLE_ROLES:

SELECT * FROM information_schema.APPLICABLE_ROLES;
GRANTEE ROLE_NAME ROLE_OWNER IS_GRANTABLE
SIMEON ACCOUNTADMIN NO
SIMEON ORGADMIN NO
SIMEON PUBLIC NO
ACCOUNTADMIN SECURITYADMIN NO
ACCOUNTADMIN SYSADMIN NO
SECURITYADMIN USERADMIN NO
user_439920

GRANTS_TO_ROLES View holds the privileges granted to roles.

GRANTS_TO_USERS View holds the roles granted to the users.

You can join them as follows to get what you're looking for:

select u.grantee_name
    , u.role
    , r.privilege
    , r.granted_on
    , r.name
from snowflake.account_usage.grants_to_users u
join snowflake.account_usage.grants_to_roles r
on u.role = r.grantee_name
where u.grantee_name = '<user_name>'
order by 1,2,3,4,5;
user_81464960
Related