How do I grant CREATE TABLE without granting OWNERSHIP in a schema

[y-tee]

I need the ability to create table, but seems like the write role only writes, and it does not allow table creation. How do i implement this in snowddl without granting OWNERSHIP? since ownership will allow the role to grant future objects of the schema to other people as well

[y-tee]

What is a good access grant to allow for dbt run locally for development but not with ownership access to the schema where they can also grant schema object access to the others

[littleK0i]

@y-tee , all schemas created by SnowDDL have flag "WITH MANAGED ACCESS". It means SnowDDL admin role controls access. You may read more about it here: https://docs.snowflake.com/en/sql-reference/sql/create-schema#optional-parameters

So if DBT creates an object in such schema, it won't be able to change its permissions.

Make sure to set flag "is_sandbox: true" for schema with objects created by external forces. Otherwise SnowDDL will try to delete it, since new objects do not exist in config.

[y-tee]

@littleK0i any idea on the least privileges that can be given to a user who needs to allow for run dbt locally (drop view creates views describe table) without granting ownership? or this is not possible at all in snowflake?

[littleK0i]

In my view, ownership on created schema objects (tables, views) is required. Otherwise you won't be able to do anything with object after creating it. Dropping object requires ownership as well.

Users created by SnowDDL normally do not get ownership on databases and schemas. There is no risk of dbt user dropping an entire schema or messing with permissions.