Restrict snowflake warehouse to be used by database

user_12965658 0
Viewed 103

I have one snowflake instance and I want to use it for 2 different environments.

To segregate the cost, I will create 2 warehouse for dev and qa.

Also, I will create two databases. Is it possible that dev warehouse can be restricted to be used by one dev db and qa database can only be used by qa warehouse.

I know we can restrict it be roles. But it is possible on database level in snowflake?

Or if not, what is the best way - is it creating two roles and then granting the access of db and warehouse by roles?

The issue with roles is that- I can impose restrictions with application level roles but not with individual user roles

snowflake-schema snowflake-cloud-data-platform
2 Answers

It's not possible to assign warehouses to databases. You need to set up (at least) 2 roles and grant required permissions to use the specific warehouse and the database.

I couldn't understand the issue with the users. You will assign these roles to users, so the users will have access to only the specific warehouse and the database.

user_125509650

As Gokhan notes.

WAREHOUSES and DATABASES are just resources. They are only acted on by USER, users do stuff via ROLES.

So you have to have your QA users, have QA_x roles, and those ROLES have access to the WAREHOUSES and the DATABASES. And thus the DEV roles have access to the DEV DB & WAREHOUSE.

The only problem with this is if a USER has access to both ROLES, but then they will have to select between ROLES to access the resources, which they can use both ROLES at the same time via USE SECONDARY ROLES unless you have that blocked.

user_439920
Related