Week 20 - Stored Procedures & UDFs

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.

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:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don't know how to here)
3. Post the URL in the comments of the challenge