How to clone a Snowflake database and own all of its child objects?

Question

I love Snowflake's zero-copy cloning feature, but it has a property that seems to seriously reduce its convenience:

The clone creator gets ownership only of the top-level cloned object. The child objects remain owned by the owner of the original child objects, and the new top-level clone owner doesn't necessarily have the right to change their ownership.

We have a developer role that has read access to our production database. I'd like to give developers a convenient way to create a clone of production that they fully own.

The only way to do this that I can think of is to create an owner's rights stored procedure, owned by the production owner and callable by the developer role, that clones the database, grants the developer ownership of the new database and all of its schemas, and then retrieves the list of schemas and loops through each schema and each schema object type, granting ownership:

snowflake.createStatement({sqlText: `create or replace transient database ${clone_db_name} clone ${prod_db_name}`}).execute();
snowflake.createStatement({sqlText: `grant ownership on database ${clone_db_name} to role ${role} revoke current grants`}).execute();

var schemas = [];
var result_set = snowflake.createStatement({sqlText: `show schemas in database ${clone_db_name}`}).execute();
while (result_set.next()) {
    var schema = result_set.getColumnValue('name');
    schemas.push(schema);
}

// All schema objects listed at:
// https://docs.snowflake.com/en/sql-reference/sql/grant-ownership.html
var object_types = [
    'TABLES', 'VIEWS', 'MATERIALIZED VIEWS', 'STAGES', 'FILE FORMATS', 'FUNCTIONS', 'PROCEDURES', 'SEQUENCES', 'STREAMS', 'TASKS'
]

for (const schema of schemas) {
    if (schema == 'INFORMATION_SCHEMA') {
        continue;
    }
    for (const object_type of object_types) {
        snowflake.createStatement({sqlText: `grant ownership on all ${object_type} in schema ${clone_db_name}.${schema} to role ${role} revoke current grants`}).execute();
    }
}

But is all this really necessary just to give the clone owner full access to their own object? I'd expect there to be an easier way.

Edit: Snowflake support says:

...this is expected behavior. For e.g. if an owner(role) who has read-only access to the database, creates a clone, will have the same access on the cloned database except that it will not be having the USAGE privilege on the cloned database but it will inherit all object-level privileges.

However, to have full control of the cloned database, the accountadmin should grant ownership on the cloned database to the role which is assigned to the user. In this way, the user can have read-only access to the source database and ownership of the cloned database.

So the short answer seems to be that it's not possible to transfer ownership by default. But I'd still like to know if there's a simple way to transfer ownership of all of the child objects, assuming that you have usage / select permission on them.

Answer 1

As mentioned in the doc: https://docs.snowflake.com/en/user-guide/object-clone.html#access-control-privileges-for-cloned-objects

For child objects, their privileges will be replicated:

However, if the source object is a database or schema, for child objects contained in the source, the clone replicates all granted privileges on the corresponding child objects:

• For databases, contained objects include schemas, tables, views, etc.
• For schemas, contained objects include tables, views, etc.

This is to prevent the cloner to have unexpected access to underlining objects by cloning.

If your developer role has access to the objects under the database or schema that is cloned, then he/she should still have access to the cloned children. He/She can't just own those objects just because he/she cloned them, as those objects might be restricted.