Effective way to grant limited access to 3rd party service account in Snowflake?

Question

I'm green as a dba somewhat. Not really a DBA actually.

So we have a bunch of databases/ datatables ... we need to grant a small subsection of access (say 3 tables, show half the fields for PPI reasons).

For some reason the project lead initially thought we should create an entirely different database (DB_service_account) and either replicate or ETL-filter-write the limited data necessary to this.

The thinking would be this simplifies management -- the service account has access to everything in that database.

Is this a common design pattern? I mean --- an alternative would be create a limited/ secure views on these tables ... maybe with a 'schema' name (more of a logical than physical separation) .. right? That would avoid write jobs, additional storage? Then grant access to this schema ... right?

Project lead also wants a separate warehouse (compute) for cost monitoring -- again probably not the most efficient but that can be done at the User level anyway --- is it possible or necessary to use "Shares" or "Replication" or are these completely different use cases and over-complicating matters? Just wondering thoughts -- thanks!

Answer 1

Having a separate database is an option. It gives the strong separation you describe. But then you have the whole ETL thing to manage and maintain, and timeliness and consistency to worry about.

If you really want a separate database Snowflake can clone databases. That will side-step the ETL work. Then you can drop the tables and columns that must be hidden. There may still be a problem around undelete and time travel, though. Not a great solution, I'd say.

Since the basic problem is around permission and access rights I think using Snowflake's role based access control (RBAC) would be good. Create views referencing only the restricted data this account can see. Create a database role and grant it read access to those views. Create an account role and grant the database role to it. Make the service account user a member of this account-level role only and no other. We had a Snowflake consultant set our RBAC this way during our migration and it has worked well.

Absolutely create a virtual warehouse for this purpose. Make it the only one assigned to the role created above. Then you can track (and limit) cost for that account. Further it prevents them interfering with other work. No further replication is needed for this.