SnowDDL Custom role with read only and read write permission on account level

[raginc]

I am trying to create read only and read write permission custom role on all databases, schemas, objects i.e. on account level

business_role.yml

admin role

dev_admin:
global_roles:
- create database
- create role
- create schema

Tech role

read_role:

read_role:
grants:
DATABASE:USAGE:
- test_db
SCHEMA:USAGE:
- test_db.*

do I need to specify all database one by one to have read only role

But I see I need to specify each grant as above, I have not find particular usage of global privs that can be granted all at once, how do I define the custom role on account level which has access to create / read / write on all dbs, schemas, roles etc along with FUTURE GRANTS. can you please share specific usage

I want to create one read only role on all dbs,schemas and objects underneath those schemas and other full admin role with full access.

also how do I assign SYSADMIN or SECURITYADMIN to this custom roles?

[littleK0i]

Normally you're not supposed to create admin roles with tools, since it will cause a circular dependency. ACCOUNTADMIN should be at the top of hierarchy and have USAGE on downstream roles. Snowflake should not let you create a loop like this: ACCOUNTADMIN -> (SnowDDL Admin role) -> (User role) -> (Business role) -> ACCOUNTADMIN.

---

Reading / writing to all schemas created by SnowDDL should be possible by specifying * for schema_read and schema_write in business role config https://docs.snowddl.com/basic/yaml-configs/business-role

read_write_everything:
  schema_read:
    - *
  schema_write:
    - *

But it kind of defeats the purpose of having role hierarchy and even having separate schemas. I would recommend to use at least a basic prefix to split permissions, e.g.

read_write_aaa:
  schema_read:
    - aaa.*
  schema_write:
    - aaa.*

read_write_bbb:
  schema_read:
    - bbb.*
  schema_write:
    - bbb.*

[raginc]

this means that if I need role that need to assign SYSADMIN or SECURITYADMIN to newly created role, I need to follow below steps?

1. I need to create one role and assign SYSADMIN or SECURITYADMIN to this new role outside snowDDL
2. Assign this new role via global role in snowDDL using business role

This way new role will have SYS or SECURITY ADMIN correct?

[littleK0i]

You may try putting SYSADMIN / SECURITYADMIN in global_roles directly, but circular dependcy check will most likely not let you do that.

If you want to add genuine admins for human users, the best way is to create these manually. The amount of such admins should be very low.

If you want admin users for other tools, just follow their instructions and create them separately. You may have objects managed by SnowDDL and objects managed by other tools co-existing on the same account with no issues. Just make sure you do not mix.

If you want users to be able to create some tables in schemas managed by SnowDDL, you do not need admin privileges. You may use schema_owner instead.