Search code examples
snowflake-cloud-data-platformshowrolessql-grantsnowflake-schema

Which Role has which tables access in Snowflake

Could you please help me query, which can tell me which role has which tables access. E.g. Role Sales has access to t1, t2 and Role Analyst has access to t2. Thank you

Solution

  • You can check which roles have access to a certain table by running SHOW GRANTS.

    Examples:

    1. List all privileges of a role: SHOW GRANTS TO yourRole;
    2. List all privileges on an object: SHOW GRANTS ON TABLE myTable;

    More info and other variations can be found here: https://docs.snowflake.com/en/sql-reference/sql/show-grants.html

    On top of that you may query the information schema view TABLE_PRIVILEGES to see more information: https://docs.snowflake.com/en/sql-reference/info-schema/table_privileges.html

    Note here: You only see objects here for which the current role of your session has access privileges.