Permissions needed to call DBMS_DATAPUMP from within package

datapumporacleoracle-11g-r2

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_link_name
         || '; job_name = '
         || v_job_name);
      dph :=
         DBMS_DATAPUMP.open ('IMPORT',
                             'SCHEMA',
                             v_link_name,
                             v_job_name);
      DBMS_OUTPUT.put_line ('dph = ' || dph);
      DBMS_DATAPUMP.metadata_filter (dph,
                                     'SCHEMA_LIST',
                                     '''' || v_source_schema || '''');
      DBMS_DATAPUMP.metadata_remap (dph,
                                    'REMAP_SCHEMA',
                                    v_source_schema,
                                    v_target_schema);
      DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
      DBMS_DATAPUMP.create_job_view (dph, UPPER (v_job_name || '_vw'));
      DBMS_DATAPUMP.start_job (dph);

      IF NOT p_asynchronous THEN
         DBMS_DATAPUMP.wait_for_job (dph, v_state);
      END IF;
   END copy_schema;

I have found one solution to creating a procedure that calls datapump, but it solves the problem by putting the procedure in the SYSTEM schema, which I would prefer not to do.

Best Answer

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.

Related Question