How to Securely Copy Database Environments Using Snowflake Cloning

				
					CREATE OR REPLACE PROCEDURE UTIL_DB.ROUTINES.CLONE_DB_GRANT_FUTURE_PRIVS(DBNAME VARCHAR, SOURCE_ENV VARCHAR, DEST_ENV VARCHAR)
returns varchar
language javascript
execute as caller
as
$$
/*
Definition: Procedure grants future privileges required by the new database clone.
Params:
   DBNAME (STRING): Name of the cloned database.
   SOURCE_ENV (STRING): Name of the source environment (PROD)
   DEST_ENV (STRING): Name of the destination environment (TEST,DEV)

*/
// We need to get all roles that have potential future grants.
var SQLGETROLES = "SELECT DISTINCT GRANTEE \
FROM $.INFORMATION_SCHEMA.OBJECT_PRIVILEGES \
WHERE OBJECT_CATALOG = '$' \
   AND PRIVILEGE_TYPE IN ('SELECT','INSERT','UPDATE','DELETE','REFERENCES') \
UNION \
SELECT DISTINCT GRANTEE \--
FROM $.INFORMATION_SCHEMA.TABLE_PRIVILEGES \
WHERE TABLE_CATALOG = '$'";

SQLGETROLES = SQLGETROLES.replaceAll("$", DBNAME);

var stmtRoles = snowflake.createStatement( {sqlText: SQLGETROLES} );
var resultRoles = stmtRoles.execute();

while(resultRoles.next()){
   // We have to first execute SHOW FUTURE GRANTS before
   // we can return a result set.
   var SQLFUTUREPRIVS = "SHOW FUTURE GRANTS TO ROLE " + resultRoles.getColumnValue(1) + ";";
   var stmtShowPrivs = snowflake.createStatement( {sqlText: SQLFUTUREPRIVS } );
   var resultsFuturePrivs = stmtShowPrivs.execute();
  
   // Now we can execute a statement that will return a results set.
   var SQLGETPRIVS = "SELECT 'GRANT ' || $2 || ' ON FUTURE ' || $3 || \
   'S IN SCHEMA ' || \
   REPLACE(REPLACE(REPLACE($4,'.<TABLE>',''),'.<VIEW>',''),'@','#') \
   || ' TO ' || REPLACE($6,'@','#') || ';' \
   FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) \
   WHERE SUBSTRING($4,1,CHARINDEX('.',$4)-1)='^';"
  
   SQLGETPRIVS = SQLGETPRIVS.replaceAll("^", DBNAME);
   SQLGETPRIVS = SQLGETPRIVS.replaceAll("@", SOURCE_ENV);
   SQLGETPRIVS = SQLGETPRIVS.replaceAll("#", DEST_ENV);
  
   var stmtGetPrivs = snowflake.createStatement( {sqlText: SQLGETPRIVS } );
   var resultsFutureGrants = stmtGetPrivs.execute();

   // Loop results and execute grant statements.
   while(resultsFutureGrants.next()){
       var SQLPERM = resultsFutureGrants.getColumnValue(1)
       var stmtGrant = snowflake.createStatement( {sqlText: SQLPERM } );
       var resultsOfGrant = stmtGrant.execute();
   }

}

return "DONE!";
$$;