Is it a good practice to separate roles with 'usage' permissions on data warehouse compute from other roles which provide access to databases?

DWGuy 0
Viewed 30

With the ability to allow secondary roles, is it a good practice to separate roles with 'usage' permissions on data warehouse compute from other roles which provide access to databases, schemas. tables? As example, I create a role called 'USE_DEV_DW". This role is granted usage to the DEV data warehouse compute. In parallel, I create a role that has usage on a specific set of databases and schemas. I then grant both roles to a user so that they can query the provided database tables using the separately provided compute (data warehouse). This way I can control what compute users have access to separate from what objects they can query. I don't see this being possible without the ability to provide for secondary roles? Looking to see if others have done something like this.

snowflake-cloud-data-platform
1 Answers

Even though there is no guidelines from Snowflake how to create the roles, the way you described makes sense.

When you have two layers of roles, let's call them database and functional, it will be easier to create role hierarchies and assign them to users.

For illustration purposes assuming you have DEVELOPERS and ANALYSTS as your groups and you have SALES and PRODUCTS databases

You can create your roles and role hierarchies like this

Database Roles

R_DB_SALES_READ_WRITE
R_DB_SALES_READ

R_DB_PRODUCTS_READ_WRITE
R_DB_PRODUCTS_READ

Functional Roles

R_DEVELOPER
R_ANALYSTS

Role Hiercharchy

R_DEVELOPER
  R_DB_SALES_READ_WRITE
  R_DB_PRODUCTS_READ_WRITE

R_ANALYSTS
  R_DB_SALES_READ
  R_DB_PRODUCTS_READ

With this you only need to assign functional roles to users

demircioglu0
Related