DBMS_DATAPUMP Schema Refreshes over DB Links [Part 1]

Problem: 

How do I allow developers to refresh their own schemas with copies of production data?

Caveat #1: developers do not have direct access to dump files on the database server

Caveat #2: developers have limited access to production

Caveat #3: Any solution needs to have the least privledges possible (i.e. no DBA or SYSDBA roles)

Solution: 

Using the DataPump API and DB Links, I was able to deploy a procedure and grant access to users in order to allow on-demand refreshes. Here's how:

Assume two databases: ORADV (development/destination) and ORAPR(production/source)

In ORAPR, create the following user and permissions:

 create user APP_ORADV identified by blahyada account unlock;  
 grant connect to ORADV;   
 grant IMP_FULL_DATABASE to ORADV;  

In ORADV, create the following user and permissions:

 create user SCHEMA_TOOLS identified by yadablah account unlock;  
 grant connect to SCHEMA_TOOLS;   
 grant resource to SCHEMA_TOOLS;  
 grant EXP_FULL_DATABASE to SCHEMA_TOOLS;  
 grant IMP_FULL_DATABASE to SCHEMA_TOOLS;  
 grant create directory to SCHEMA_TOOLS;  
 grant create database link to SCHEMA_TOOLS;  
 grant create table to SCHEMA_TOOLS;  

NOTE: create table needs to be explicitly granted, even though its also part of the RESOURCE role.

in ORADV, connect as the SCHEMA_TOOLS user and create a database link to ORAPR:

 create database link ORAPR connect to APP_ORADV identified by blahyada using 'host.domain.net:1521/ORAPR.DOMAIN.NET';  

NOTE: I like using quick-connect syntax in database links, as I find it more descriptive. TNS syntax also works fine. 


Now that we have the schemas and permissions in place, we can start working on deploying the actual procedure. That's coming in Part 2.