How to clone a database object along with the existing grants of the underlying table in Snowflake ?

I have a requirement where i need to clone tables for backup purposes. Once i create a clone i need to manually give grant to the cloned table. Is there a way or command to automatically create a clone along with the existing grants of the underline table ?

AzharuddinK

posted on 01 Nov 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Post Comment

nVector01-Nov-19

There isn't a straight forward way to copy the roles / grants that are in your source table to the cloned table. You may need a custom unix script to check for all the roles and then reapply them to the cloned table post the clone operation.

However, if you are looking for an easy way to grant access to the backup schema automatically, you can try future grants option:

grant select,insert on future tables in schema 
mydb.mybackupschema to role role1;

This will automatically grant access to your users to the backup schema and all the tables that you create in them

ReplyMore..ReportDelete

AzharuddinK04-Nov-19

So my requirement was to copy the same grants as that of the original table which is cloned. Giving future access to the entire database/schema gives access to a few tables which I don't the user to access it.

ReplyMore..ReportDelete

nVector05-Nov-19

Then you may try option #1, a custom unix script to check for all the roles and then reapply them to the cloned table post the clone operation.

Also, can you update your solution here, once you get through this. It might be helpful for others

ReplyMore..ReportDelete