CODEWITHSUNDEEP
snowflake-cloud-data-platform
Chris B
Chris B

Reputation: 33

How can I change the owner of all objects in a Snowflake database clone?

As part of our development lifecycle we clone our prod databases to replace our dev databases, the next step is to apply the correct privileges to the newly cloned databases as our devs need full access to the dev environment whilst they shouldn't have write access to the prod environment. So I have a requirement to change the owner of all objects in the dev databases to allow the devs to replace and update existing tables, views, procs etc. I have so far been unable to find how to achieve this.

Upvotes: 3

Views: 16170

Answers (4)

Frédéric Leroy
Frédéric Leroy

Reputation: 1

I agree with @the_rusteiner. Statement "GRANT OWNERSHIP ON ALL TABLES IN DATABASE db" does'nt work with schema with MANAGEED ACCESS, even if it's run with the database or schema owner, or even with SYSADMIN.

When the schema has managed access, the grant must be passed at the schema level

Upvotes: 0

augrs
augrs

Reputation: 1

@the_rusteiner

That is probably because the role you are running the GRANT as is not the owner of the schema. You can either do:

USE ROLE <owner_role>;

and try again or execute the GRANT OWNERSHIP as SECURITYADMIN role.

Upvotes: -1

the_rusteiner
the_rusteiner

Reputation: 1

To me the statement

GRANT OWNERSHIP ON ALL SCHEMAS IN DATABASE my_clone_db TO ROLE developer COPY CURRENT GRANTS;

executes but returns

Statement executed successfully. 0 objects affected.

even though the database has many schemas in it.

Only GRANT OWNERSHIP ON SCHEMA my_clone_db.schema_name TO ROLE developer COPY CURRENT GRANTS; seems to work but then I am back to the problem of having to execute multiple statements, one per schema.

Upvotes: 0

Michael Golos
Michael Golos

Reputation: 2069

After cloning the database, transfer ownership to another role using the GRANT OWNERSHIP (see also example) function using COPY CURRENT GRANTS clause, for example:

GRANT OWNERSHIP ON DATABASE mydb TO ROLE developer COPY CURRENT GRANTS;    
GRANT OWNERSHIP ON ALL SCHEMAS IN DATABASE mydb TO ROLE developer COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL TABLES IN DATABASE mydb TO ROLE developer COPY CURRENT GRANTS; 
GRANT OWNERSHIP ON ALL VIEWS IN DATABASE mydb TO ROLE developer COPY CURRENT GRANTS;

Upvotes: 5

Related Questions