If you know your SnowPro factoids, you’d know that when you CLONE an object, you can only replicate the grants on that object if that object is a table. But wouldn’t life be easier if that wasn’t the case? Well…make it so!
Your challenge is to create a stored procedure that not only creates a clone of a schema, but replicates all the grants on that schema. This should be able to accept a custom ‘AT’ or ‘BEFORE’ statement written by the user.
Start-Up Code
create or replace role frosty_role_one;
create or replace role frosty_role_two;
create or replace role frosty_role_three;
create or replace schema cold_lonely_schema;
create or replace table cold_lonely_schema.table_one (key int, value varchar);
grant all on schema cold_lonely_schema to frosty_role_one;
grant all on schema cold_lonely_schema to frosty_role_two;
grant all on schema cold_lonely_schema to frosty_role_three;
grant all on table cold_lonely_schema.table_one to frosty_role_one;
grant all on table cold_lonely_schema.table_one to frosty_role_two;
grant all on table cold_lonely_schema.table_one to frosty_role_three;
create or replace procedure schema_clone_with_copy_grants(database_name string,
<code_here>
call schema_clone_with_copy_grants('frosty_friday',
'cold_lonely_schema',
'frosty_friday',
'cold_lonely_clone',
NULL);
select *
from table(information_schema.query_history_by_session())
order by start_time desc;
Pay attention to the parameters being passed into the function:
- database_name = this should be the name of the database of the original schema
- schema_name = this should be the name of the original schema
- target_database = this should be the database of the cloned schema
- cloned_schema_name = this should be the cloned schema’s name
- at_or_before_statement = your user should be able to provide a custom AT/BEFORE statement which will be appended to the CREATE …. CLONE statement. E.g:
- ‘at (timestamp => to_timestamp_tz(’04/05/2013 01:02:03’, ‘mm/dd/yyyy hh24:mi:ss’));’
- ‘before (statement => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726′);’
With the last parameter, the code above passes a NULL. Bonus points for those who test that this part of their code is working!
RESULT
The first query the ‘call’ statement should produce the following:

The second should produce a result like this:

Remember, if you want to participate:
That was a nice way to wrap up this week
I solved it using Snowflake Java Script procedure.
https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week20_Hard.sql
My approach using sql sp
https://github.com/dsmdavid/frostyfridays-sf/blob/main/macros/init_for_challenge_20.sql
The model
https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_20.sql


My solution using sql stored procedure.
Interestingly, when using object_privileges there are some grants e.g. CREATE ICEBERG TABLE, which currently cannot be granted (region AWS_EU_WEST_1). I guess this is preparation for what is coming.
Snowflake scripting (SQL) solution