Granular access control for shared databases in Snowflake

Question

When using shared databases in Snowflake (as a data consumer), there seems to be no other way to control the access than granting IMPORTED PRIVILEGES on the DB, which effectively grants read-only access to the entire DB and all its schemas & objects.

Is there no way to grant more granular access to the schemas & objects inside a shared database? My goal would be to create roles that have access to only limited portions of the share (specific schemas or objects, like I can with a regular database).

For example, let's say I want to share a database with 10 schemas in it (as a data provider), and would like to restrict/control the access to each of these schemas on the consumer side... would there be no other option than to create 10 shares (one for each schema) on the data provider side? And so on to restrict/control access for specific tables & views?

This is assuming I have access to both accounts (the provider and the consumer), or that I can negotiate the implementation details with the provider/consumer.

Answer 1

Is there no way to grant more granular access to the schemas & objects inside a shared database?

Currently, there is not.

For example, let's say I want to share a database with 10 schemas in it (as a data provider), and would like to restrict/control the access to each of these schemas on the consumer side... would there be no other option than to create 10 shares (one for each schema) on the data provider side? And so on to restrict/control access for specific tables & views?

That's how you would have to do it currently.

Edit: Actually, following up on Robert's answer, if the inbound side of the share is a full account rather than a reader account, you could sort of restrict access by creating a view that filters out rows based on current_role(). It wouldn't be like a privilege, more like row level security based on role.

Answer 2

One approach to this problem is to block access to certain objects in the share for those roles which you don't want to allow access. If your role owns the share then you can block access by revoking privileges on the objects from the share. If your role does not own the share, but owns the objects in the share, you can block access by revoking the USAGE or SELECT privileges with CASCADE on the objects from the share owner.

Alternatively, depending on all the details, it may be easier just to create as many seperate shares as are needed.