Role for managing all tables in a schema

[jimmyzzxhlh]

I'm looking into creating roles to be able to create/modify/drop any tables in a schema. The roles can be either used by automated data pipelines to create and modify tables, or by data analysts to create and modify tables for testing purposes. In this case, ideally I'd like to define databases and schemas in SnowDDL but preferably not tables, so that it's flexible for the roles to create whatever tables they like, without pre-defining the tables in SnowDDL.

Any recommendations how I should create SnowDDL? Or maybe there is a better practice? I might be missing something as I can't find a way to grant usage/create/drop/alter on all and future tables in a schema.

Thanks!

[littleK0i]

Try configuring a business role like this:

my_fancy_loader:
  schema_owner:
    - my_db.my_schema

And while defining a schema, create nested sub-directories for database and for schema. Put file params.yaml in schema directory with the following content:

is_sandbox: true

Full path: /my_db/my_schema/params.yaml.

So.. now your schema is a "sandbox" where tables can be created by external tools.

And your business role is an "owner" of this schema, so it can create / drop / alter objects in it.

[jimmyzzxhlh]

Ah this is brilliant. Thanks!

[jimmyzzxhlh]

@littleK0i Follow-up on a similar question -- What if I want to create a database that allows ad-hoc schema to be created, but also need a business role to own all schemas/tables in the db?

Right now I have something like this as an owner business role:

production_writer:
  database_owner:
    - tenant_production
  schema_owner:
    - tenant_production.*
  warehouse_usage:
    - production_wh

And then I defined a tenant_production db with is_sandbox: true in params.yaml. What happens is that the tenant_production database is owned by the production_writer business role, but any new schema being created is not owned by production_writer. There is also no future grant specified on the database level.

In addition, I have to specify at least one schema in tenant_production in SnowDDL as a folder. If there is no schema folder presented, I'll get No SCHEMAS matched wildcard grant with pattern [TENANT_PRODUCTION.*]

2025-01-25 09:26:16.681 - WARNING - [/Users/zhongxia.zhou/REMHealth/infra/snowflake/snowddl/business_role.yaml]: Traceback (most recent call last):
  File "/Users/zhongxia.zhou/.asdf/installs/python/3.11.7/lib/python3.11/site-packages/snowddl/parser/abc_parser.py", line 28, in parse_single_file
    return callback(file)
           ^^^^^^^^^^^^^^
  File "/Users/zhongxia.zhou/.asdf/installs/python/3.11.7/lib/python3.11/site-packages/snowddl/parser/business_role.py", line 110, in process_business_role
    grants.extend(self.build_database_role_grants(full_schema_name, "READ"))
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/zhongxia.zhou/.asdf/installs/python/3.11.7/lib/python3.11/site-packages/snowddl/parser/business_role.py", line 180, in build_database_role_grants
    grants.extend(self.build_schema_role_grants(f"{database_bp.full_name.database}.*", grant_type))
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/zhongxia.zhou/.asdf/installs/python/3.11.7/lib/python3.11/site-packages/snowddl/parser/business_role.py", line 212, in build_schema_role_grants
    raise ValueError(f"No {ObjectType.SCHEMA.plural} matched wildcard grant with pattern [{full_schema_name}]")
ValueError: No SCHEMAS matched wildcard grant with pattern [TENANT_PRODUCTION.*]

[littleK0i]

Try to define permission model with DATABASE_OWNER ruleset and assign it via parama.yaml for database: https://docs.snowddl.com/basic/yaml-configs/permission-model

It should create future grants on database level and handle other issues.

Business roles should point to this database via database_owner.

[jimmyzzxhlh]

The permission model example for FiveTran works well, thank you!

One nit is that I still need to create a placeholder schema to avoid the above error.

[littleK0i]

Remove schema_owner, keep database_owner only. It automatically includes all existing and future schemas.

schema_owner emits errors because it can only take into account schemas which exist in config. No schemas in config = instruction which does not work. This error prevents you from having instructions which actually do nothing.

[jimmyzzxhlh]

Got it, that works! Thank you!