Snowflake: How to Show Grants available to a given User or Role

Show Grants: What does it do?

Used to find:

Which Users have access to a given Role?
Which Users or Roles were granted to a given Role (confusing - but it makes sense below)
Which Roles have access to a given Table or View
Which Privileges (eg. Create Database) have been granted to Roles

SHOW GRANTS TO USER <User Name>

Lists the Roles accessible to a given User. This could be used (for example), to list the roles available to a given User.

show grants to user JOHN;

💡

Note: This doesn't show which Tables, Views or Warehouses the User. You may need to tree-walk to find the accessible tables. See Show Grants: Reports below, which includes the code required.

The screenshot shows that user TEST has been granted several roles including ACCOUNTADMIN and SECURITYADMIN.

SHOW GRANTS OF ROLE <Role Name>

For a given Role, this lists the Users and Roles to which a given Role has been granted. For example, you could identify the Users who have been granted the role ACCOUNTADMIN.

show grants of role accountadmin;

💡

Note: A given Role can, in turn, be granted to another role. For example, the system-defined role ACCOUNTADMIN can be granted to the user-defined role DBA_MASTER, which in turn can be granted to other users. You may, therefore, need to tree-walk to find exactly who has been granted a given role.

Want to learn more from a Snowflake Expert? Click on the image below for training from Analytics Today.

SHOW GRANTS TO ROLE <Role>

Lists the Privileges granted to a given Role. This could be used (for example) to check whether a given role can CREATE TABLEs.

show grants to role TEST_ROLE;

The screenshot below shows the Privileges granted to role TEST_ROLE:

show grants to role TEST_ROLE;

SHOW GRANTS ON TABLE <Table>

Shows the Privileges (for example, SELECT, INSERT, UPDATE, DELETE) granted on Objects. This could be used (for example), to check which roles have access to a given table, view or even virtual warehouse.

show grants on table clustered_tables;

The above screenshot shows which roles have access to the table CLUSTERED_TABLES.

SHOW GRANTS ON WAREHOUSE <Warehouse>

This example shows how to list the roles with access to the warehouse named BIGGER_WH.

show grants on warehouse bigger_wh;

Show Grants: Reports

List All Objects accessible to a <User>

As indicated above, users are granted roles, which may be given other roles, and eventually are granted privileges on objects. Because of the potential hierarchy, it's necessary to tree-walk from a User to the Roles and, finally, Tables, Views, and Warehouses.

The code below lists all the Objects (Tables, Views, etc.) accessible to the current user.

💡

Querying the ACCOUNT_USAGE views are incredibly slow, so this query builds a temporary local copy first.

-- Create local TEMP table purely to speed the next query
create or replace temp table temp_grants_to_roles as
select *
from snowflake.account_usage.grants_to_roles;

-- Treewalk the RBAC hierarchy 
with ROLE_HIER as (
     -- anchor query
     select gu.GRANTEE_NAME as USER_NAME
          , gu.ROLE::varchar(1000) as ROLE_NAME
     from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gu
     where DELETED_ON is null
     union all
     -- recursive query
     select rh.USER_NAME
          , g2u.NAME as ROLE_NAME
     from role_hier rh
       join temp_grants_to_roles g2u on rh.ROLE_NAME = g2u.GRANTEE_NAME
     where g2u.PRIVILEGE = 'USAGE'
       and g2u.GRANTED_TO = 'ROLE'
       and g2u.GRANTED_ON = 'ROLE'
       and g2u.DELETED_ON is null)
 , DIST_USER_ROLE as (
       select distinct USER_NAME, ROLE_NAME
       from ROLE_HIER
       order by  USER_NAME, ROLE_NAME)

select distinct
     dur.USER_NAME      as "User"
   , dur.ROLE_NAME      as "User Role"
--    , g2r.CREATED_ON
   , g2r.TABLE_CATALOG  as "Database"
   , g2r.TABLE_SCHEMA   as "Schema"
   , g2r.GRANTED_ON     as "On"
   , g2r.NAME           as "Object Name"
   , g2r.PRIVILEGE      as "Privilege Granted"   
--   , g2r.GRANT_OPTION
--   , g2r.GRANTED_BY
from DIST_USER_ROLE dur
 join temp_grants_to_roles g2r on dur.ROLE_NAME = g2r.GRANTEE_NAME
where g2r.GRANTED_TO =  'ROLE'
 and  g2r.GRANTED_ON <> 'ROLE'
 and  g2r.DELETED_ON is null
 and  dur.USER_NAME = current_user()
 -- and  g2r.PRIVILEGE = 'OWNERSHIP'
order by dur.USER_NAME
      , dur.ROLE_NAME
      , g2r.TABLE_CATALOG
      , g2r.TABLE_SCHEMA
      , g2r.NAME
--      , g2r.CREATED_ON    
;

The screenshot above shows the tables owned by the current User - JOHN. It also shows the Roles that have resulted in access and (if relevant) the Database and Schema names.

Snowflake Training by an Expert.

Click in the image below for more detail

List Roles Accessible to a User

Whereas the code above shows all the accessible Objects (Tables, Views, Warehouses), this query returns only the Roles accessible to a given User.

Unlike SHOW GRANTS TO USER, which only shows the roles directly granted, this code performs a tree-walk to list all accessible Roles for a given User.

set user_name = 'JOHN';

-- For a NAMED user - list the roles they have been granted

with ROLE_HIER as (
      -- anchor query
      select gu.GRANTEE_NAME as USER_NAME
           , gu.ROLE::varchar(1000) as ROLE_NAME
      from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gu
      where DELETED_ON is null
      union all
      -- recursive query
     select rh.USER_NAME
          , g2u.NAME as ROLE_NAME
     from role_hier rh
       join monitor_grant_to_roles g2u on rh.ROLE_NAME = g2u.GRANTEE_NAME
     where g2u.PRIVILEGE = 'USAGE'
       and g2u.GRANTED_TO = 'ROLE'
       and g2u.GRANTED_ON = 'ROLE'
       and g2u.DELETED_ON is null)
select distinct USER_NAME, ROLE_NAME
from ROLE_HIER
where USER_NAME = $user_name
order by  USER_NAME, ROLE_NAME
;

The screenshot above shows the Roles accessible to user JOHN.

Snowflake Training from an Snowflake Superhero.

Click on the image below for more information.

Snowflake Syntax: Show Grants

Table of contents