Schema roles are created with Database Onwer ruleset

[kokorin]

Describe the bug
We have DEV DB where every developer may create personal schemas to work on DBT-based project. Schema names start with developer name. So we used Database Owner ruleset for it with is_sandbox: true flag for that Database.

The same database contains schemas and tables created by DEV CI runs, some of those tables (not all) should be granted to specific business roles. So, we added schema and tables in it to SnowDDL-based project (otherwise Tech role validation fails). Additionally granted Tech role privileges on mentioned tables and schema.

Despite Database Owner ruleset, SnowDDL creates schema-specific owner/read/write roles (like ...__READ__S_ROLE).

Expected behavior
Only database read/write/owner roles are created without creating schema roles.

Attach log
N/A

Attach YAML config (if applicable)
N/A

[littleK0i]

It is by design. Currently it is the only way to make such schemas work with schema_read and schema_write parameters.

It is even more interesting internally. Since future grants on schema level override future grants on database level, such schemas require two sets of future grants: one set for schema-level and another set for database-level.

Naturally, "unknown" schemas are managed on database-level only and cannot be granted via schema_read / schema_write.

In general, it should not cause any issues or noticeable overhead.

[kokorin]

I think it can be converted to discussion then, thank you!