OWNERSHIP is a special privilege in Snowflake. You should read & fully understand the Access Control Snowflake documentation first.

SnowDDL implementation of OWNERSHIP is following:

  • Account-level object types are typically owned by SnowDDL admin role

  • Schema-level objects are typically owned by SCHEMA ROLE (OWNER) or DATABASE ROLE (OWNER) which are created automatically.

For schema-level objects OWNERSHIP is assigned using FUTURE GRANTS. There is no "race condition" between creation of object and change of OWNERSHIP, which is common for other object management tools.

SnowDDL typically creates schemas with MANAGED ACCESS. It means that users having SCHEMA ROLE (OWNER) can create / alter / drop objects in such schema, but they cannot grant access to objects in this schema to some other role (e.g. to PUBLIC).

This is very important for security.

Practical example

MY_DB                    (owned by SNOWDDL_ADMIN)
|--  MY_SCHEMA           (owned by SNOWDDL_ADMIN)
     |-- MY_TABLE        (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE)
     |-- MY_VIEW         (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE)
     |-- MY_FUNCTION     (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE)
MY_WAREHOUSE             (owned by SNOWDDL_ADMIN)
MY_USER                  (owned by SNOWDDL_ADMIN)

Last updated