3

I am looking for your suggestions on Snowflake Role based Access control hierarchy which I created for a Snowflake data warehousing project.

Basically, we need to maintain different client data with different databases. This database creation process is automated with a script including create databases(DEV, QA and PROD), roles etc.. for a specific client. Here I have created a hierarchy with 3 different default roles for a database.

  1. ADMIN_{ENV}_{CLIENT_ID}
  2. READ_WRITE_{ENV}_{CLIENT_ID}
  3. READ_{ENV}_{CLIENT_ID}

Then I created a set of roles which have access to all databases like..

  1. ADMIN_{ENV}_ALL
  2. READ_WRITE_{ENV}_ALL
  3. READ_{ENV}_ALL

Hope below image illustrates that..

RBAC hierarchy

My questions are:

  1. Is this right approach to continue?
  2. When creating database objects, which role should I use? SYSADMIN? For example: for creating database objects in CLIENT_1_DEV_DB database, should I use ADMIN_DEV_CLIENT_1 role or SYSADMIN?
  3. ADMIN_DEV_CLIENT_1 role should be able to create new users and grant the privileges. In that case should I use USERADMIN or SECURITYADMIN? Are there any methods to restrict that to database level?
  4. There is a use case to clone a schema in a database if any issue occurred. In that case How to manage the grants? When we clone a schema, the roles are not retained to the cloned schema. In that case what is the best approach to copy the grants. having another role with MANAGE GRANTS privileges and use that?

Hope you suggestions on these. Thanks

jsrathnayake
  • 139
  • 10
  • 1
    For a start, I would use separate Snowflake accounts for each environment (DEV, TEST, PROD etc). It will make your life much simpler in the future – NickW Jan 03 '22 at 19:46
  • 2
    Separate accounts for environments isn't always the best choice, though. You limit your ability to clone data across environments. I actually prefer a good RBAC strategy on a single account. – Mike Walton Jan 03 '22 at 22:37
  • 2
    I agree that there is no correct choice, as it is dependent on the user's requirements. If they are operating in a pure Snowflake environment then a single account is probably the way to go; however, once you start looking at data pipelines using external tools, integration with data governance tools, etc. then having objects with the same name regardless of whether they are Dev/Test/Prod makes life easier. Also, if you are operating in a regulated environment, separating access to Dev and Prod is easier and the ability to clone is less of an issue, as you can't clone data between Prod & Dev – NickW Jan 04 '22 at 10:06