0
0
During this post we will discuss an interesting use case about SHARES. With Data Sharing, the customer doesn’t create a copy of a dataset and moves it across organizational boundaries. Consider the scenario when you have multiple data SHARES object in your Snowflake Account. Share is a securable object which encapsulates all the information and consist of:
- Privileges that grant access to the database and schema containing the objects to share.
- Privileges that grant access to specific objects.
- The consumer account with which the objects are share.
As part of the Audit, Client wants to know how many OUTBOUND Shares are exists in Snowflake Account. Moreover, they want to know which object has been encapsulated within each share. There is no straight DDL or function provided by Snowflake to fetch this information in one step. We need to follow below two steps to extract this data:
SHOW SHARES: Details about all the SHARES in account.
DESC SHARE<<SHARE_NAME>> : Returns the list of object encapsulated with in Share Object.
In addition to it ,there is full possibility that a VIEW has been shared with the SHARE object. So Client wants if possible ,extract the underlying base table information on which VIEW does lies. So in short they want output which depict the complete details like:
SHARE NAME,SHARED OBJECT Details (Database,Schema,Table),SHARED VIEW Details (View Name,Base Table Name)
To cater the scenario, I tried to develop the below Stored procedure which looks for OUTBOUND shares and build the all information in an ARRAY.
Execute the procedure and it will produce the below output:
