1
0
During this post, we are going to discuss how the object privileges works during the CLONE operation. Recently one of my colleagues encountered a scenario a scenario where they had cloned few tables from one DB to Target DB. While cloning they assumed that the object privileges would also get clone or copied to the target DB. But they were surprised to find that only the objects were cloned and there were no privileges copied on objects. Only the object OWNERSHIP was there in target DB and the rest were not moved or copied.
The challenge with object privileges during cloning becomes critical, especially when custom or authorized roles face access issues with the cloned objects. One potential solution is to query the source database, exporting a list of all privileges for the corresponding cloned objects. However, the complexity arises when dealing with numerous tables, each having multiple privileges assign to various custom roles. To address this, a dynamic process needs to be develop. This process will generate GRANT commands dynamically, facilitating the assignment of privileges in the target database.
Hence, I have developed the below procedure in my target DB which extracts all the privileges from the source and assigns them to the object that resides in the target DB.
Technical Implementation:
Source DB:
Below is the screenshot of Source DB i.e., DEMO_DB with the list of tables and respective Privileges.
We can see the objects are assigned to REP and FLM roles respectively.
Target DB:
We can verify the list of objects CLONE to Target DB and their respective privileges. We can clearly see that only OWNERSHIP privileges are there.
Below is the Procedure to implement the logic.
Execute the Procedure and verify the output.
Now check if Privileges are Clone to the Target DB or not.
Note:
Snowflake has provided COPY GRANTS command to copy the object and their respective privileges. Using COPY GRANTS with command will copies the objects and their respective privileges.
So I created new database and clone the table from DEMO_DB with COPY_GRANTS
create database DB_CLONE1;
CREATE TABLE CUST_INVC CLONE DEMO_DB.PUBLIC.CUST_INVC COPY GRANTS;
This has copied the entire privileges to target DB.
