During this post we will discuss how Schema object PRIVILEGES behaves during Cloning the Database and Schema. Consider the Scenario when entire source Database needs to be CLONE at target end. In addition to it we want all the GRANTS applied to Source DB should be readily available in CLONED Database. At first glance it seems pretty straightforward. We can run the below command which will CLONE the entire Schema in few mins aways.
CREATE SCHEMA IF NOT EXISTS ” + TARGET_DB + “.” + TARGET_SCHEMA + ” CLONE ” + SRC_DB + “.” + SRC_SCHEMA
But the Question is, Will the GRANTS/PRIVILEGES would be copied at the CLONE Schema. Does the Target DB or Schema possess the same PRIVILEGES as Source Schema have. The answer is No. Except OWNERSHIP , any other privileges does not copy. Now assume the scenario where you have huge number of GRANTS are available in SOURCE Schema and creating the same in TARGET Schema manually is not a feasible solution. Moreover this may lead to the error or missing required grants if do so manually.
Our task is to create a stored procedure that not only creates a clone of a schema, but replicates all the grants on that schema.
use Database DEMO_DB;
use Schema PUBLIC;
Create The Clone Schema:
CREATE SCHEMA DEMO_DB.CLONE_SCHEMA CLONE DEMO_DB.PUBLIC;
Verifies the GRANTS on CLONE Schema and we will see only OWNERSHIP Privileges are there.
Now Execute the below Stored procedure which will create the schema and applied all the Source privileges on target schema automatically.
Verifies the Privileges on the new Clone Schema.