2
0
Ownership Transfer to Custom Role: Consider a scenario, in current database we have list of tables created by ACCOUNTADMIN role. Therefore, The ownership of these tables belongs to AccountAdmin only. Say after few months some new members have been onboarded the Snowflake project. As discuss within the team we create multiple custom roles and assign them to the users respectively. Team has agreed , instead of keeping the ownership with AccountAdmin this needs to be transfer to other custom roles.
However, the main motto was to least the burden from the AccountAdmin by transferring the ownership and new users would take the control of these tables. Initially it seems very simple solution as we have to run only below statement to complete the task.
GRANT OWNERSHIP ON TABLE <<TABLE_NAME>> TO ROLE <<ROLE_NAME>>;
But our current database has huge list of tables in particular Schema and running the command for each individual tables is not feasible approach. So now the challenge is how we can transfer the existing ownership to Custom Roles automatically without any manual intervention.
Stored Procedure:
Therefore, To implement the same we have developed the below stored procedure which will get the list of tables and Grant the ownership to new Role programmatically.
We have CUSTOM_ROLE_DB having the list of tables created by ACCOUNTADMIN role. As per our use case we would be transferring the ownership of these tables to custom Role.
We have created below “CUSTOM_ROLE” and user : S_USER and grant the privileges on CUSTOM_ROLE_DB.
Login to the Snowflake with new user : S_USER.
There are no tables present inside the DB under PUBLIC schema.
We will be creating the below procedure inside the Database.
Call the Procedure with input as your Custom Role name.
call TRANSFER_privilges(‘CUSTOM_ROLE’);
Below is the output of the procedure.
Login to the Snowflake with S_USER and verify all the tables are visible under CUSTOM_ROLE with ownership has been changed from ACCOUNTADMIN.
Try to delete the table via ACCOUNTADMIN role inside the CUSTOM_ROLE_DB:
Try to delete the same table via CUSTOM_ROLE.
