Which Role has which tables access in Snowflake

2.5k views Asked by At

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

1

There are 1 answers

0
Marcel On BEST ANSWER

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.