Permissions needed to call DBMS_DATAPUMP from within package

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a procedure I get the following error.

[Error] Execution (1: 1): ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at "UTILITY.MANAGE_SCHEMA", line 71
ORA-06512: at line 1

This error is being generated from the DBMS_DATAPUMP.OPEN call. Since it works in the anonymous block, I'm assuming this is actually a permissions issue (i.e. one or more permissions granted via a role are required). However, I can't find any documentation about what permissions are necessary to use datapump.

This problem boils down to: which permissions need to be explicitly granted to a user/schema in order for a package in that schema to be able to call DBMS_DATAPUMP?

The owner of the package has both the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles, as well as the DBA role.

The procedure code is below.

```
PROCEDURE copy_schema (p_source_schema VARCHAR2,
p_target_schema VARCHAR2,
p_asynchronous BOOLEAN := FALSE,
p_link_name VARCHAR2 := 'prddb') IS
dph NUMBER;
v_source_schema VARCHAR2 (30)
:= UPPER (DBMS_ASSERT.simple_sql_name (p_source_schema));
v_target_schema VARCHAR2 (30)
:= UPPER (DBMS_ASSERT.simple_sql_name (p_target_schema));
v_link_name VARCHAR2 (30)
:= UPPER (DBMS_ASSERT.qualified_sql_name (p_link_name));
v_job_name VARCHAR2 (30) := UPPER ('IMPORT_' || p_target_schema);
v_state VARCHAR2 (30);
BEGIN
DBMS_OUTPUT.put_line (
'Starting copy: source_schema = '
|| v_source_schema
|| '; target_schema = '
|| v_target_schema
|| '; link_name = '
|| v_

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure.

Long Answer:

In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles assigned to those two roles). Attempting to explicitly grant all 2683 privileges resulted in 2566 failures, but the package was able to call DBMS_DATAPUMP successfully.

The question then became: which of the remaining 117 privileges are actually needed for DBMS_DATAPUMP? I then grouped the remaining privileges and revoked them one group at a time (I assumed that it was a group of related permissions that would ultimately be needed). After running through nearly all of the groups, only the set of CREATE ANY privileges remained. When I revoked that group, the package stopped working. I then granted each of those permissions, calling the package after each grant. It was finally CREATE ANY TABLE that restored functionality to the package. A little further tinkering determined that CREATE TABLE is, in fact, sufficient to call DBMS_DATAPUMP from within a package.

CREATE TABLE is the only permission that I've found needs to be granted for the functionality found in the procedure shown in the question. Other procedures/datapump settings may require additional privileges. dbms_datapump.create_job_view, for instance, requires CREATE VIEW. In that case, the error message when the privilege is missing is, more reasonably, ORA-01031: insufficient privileges.

StackExchange Database Administrators Q#80154, answer score: 7