CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Will
Will

Reputation: 2227

Show grants on all schemas in a Snowflake database

I am trying to list all grants of all schemas in a specific database.

I found that only SHOW GRANTS ON SCHEMA "TEST_DB"."TEST_SCHEMA" do the trick for one schema, but unfortunately I cannot be done for all schemas.

I believe I would need to do a combination of SHOW and result_scan, but I wanted to know if there is a more straightforward solution

I came up with :

show grants on schema "DB"."SCHEMA_A";
show grants on schema "DB"."SCHEMA_B";
select * from table(result_scan(last_query_id())) union all select * from table(result_scan(last_query_id(-2)));

But it quite dirty, and very cumbersome

Upvotes: 3

Views: 8342

Answers (1)

Simon D
Simon D

Reputation: 6279

Each database you create in Snowflake has an information_schema schema which you can use to get metadata about objects. You can see what grants have been assigned to a schema in your database with:

select * 
from your_db_name.information_schema.object_privileges 
where object_type = 'SCHEMA';

This will show you which roles (the "grantee") have been granted access to the schemas in that database but keep in mind that it won't show how the permissions are pushed down the role hierarchy to child roles.

Information schema documentation: https://docs.snowflake.com/en/sql-reference/info-schema.html

Upvotes: 6

Related Questions