Any attempt to grant roles EXP_FULL_DATABASE or IMP_FULL_DATABASE to a user for an Amazon RDS Oracle DB instance fails with errors similar to the following:

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20997: "EXP_FULL_DATABASE" grants not allowed

ORA-06512: at "RDSADMIN.RDSADMIN", line 63

ORA-06512: at line 2

These error messages do not occur when granting roles to Oracle users outside of Amazon RDS.

The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles cannot be granted in RDS and are not a recommended practice because they contain privileges that should be reserved for DBAs. To see how powerful these roles are, run the following query:

SQL> select substr(grantee,1,15) grantee, privilege from dba_sys_privs where grantee in ('IMP_FULL_DATABASE','EXP_FULL_DATABASE') order by grantee;

A common use case for granting these roles is to permit the export and import of objects between schemas.

Workaround 1

Have a DBA user such as the master user perform the export and import operations. This is appropriate for manual exports and imports, but it is not desirable for automated jobs because of the powerful permissions of the DBA role.

Workaround 2

Have each schema owner export or import its own schema objects. This is the superior solution because it avoids granting user permissions on "ANY" table. The following examples show three methods for exporting the objects as the original schema owner and importing them into the new schema as the new schema owner.

Database Permissions for Workaround 2

Grant old and new schema owners permissions as shown. These permissions will work for all of the workaround implementations that follow.

SOURCEDB Setup:

Create old_owner in SOURCEDB:

SOURCEDB> create user old_owner identified by pwd default tablespace users;

SOURCEDB> grant connect, resource to old_owner;

SOURCEDB> grant read,write on directory DATA_PUMP_DIR to old_owner;

SOURCEDB> grant execute on dbms_datapump to old_owner; -- on-prem you have to connect as SYS

SOURCEDB> grant execute on dbms_file_transfer to old_owner; -- on-prem you have to connect as SYS

SOURCEDB> alter user old_owner quota unlimited on users;

Create a demo table owned by old_owner to practice with:

SOURCEDB> create table old_owner.mytab (x number) tablespace users;

TARGETDB setup:

TARGETDB> create user old_owner identified by pwd default tablespace users;

TARGETDB> grant connect, resource to old_owner;

TARGETDB> grant read,write on directory DATA_PUMP_DIR to old_owner;

TARGETDB> create user new_owner identified by pwd default tablespace users;

TARGETDB> grant connect, resource to new_owner;

TARGETDB> grant create table to new_owner;

TARGETDB> grant read,write on directory DATA_PUMP_DIR to new_owner;

TARGETDB> grant execute on dbms_datapump to new_owner;

TARGETDB> alter user new_owner quota unlimited on users;

Workaround 2 Implementation 1: using Oracle's original exp/imp:

$ exp old_owner/pwd@sourcedb file=mytab.dmp tables=mytab

$ imp new_owner/pwd@targetdb file=mytab.dmp fromuser=old_owner touser=new_owner

Workaround 2 Implementation 2: using datapump with o/s interface expdp/impdp:

$ expdp old_owner/pwd@sourcedb directory='DATA_PUMP_DIR' dumpfile=mytab.dmp tables=mytab

dbms_file_transfer Instructions

If sourcedb and targetdb are the same, you can skip all dbms_file_transfer instructions.

Refer to Oracle documentation for information about how to create a public or private database link named "targetdblink".

SOURCEDB> show user -- should be OLD_OWNER

SOURCEDB> select * from dual@targetdblink ; -- this test must be successful before proceeding

SOURCEDB> alter session set global_names=false; -- in case you need to get past error "ORA-02085: database link X connects to Y"

SOURCEDB>

BEGIN

    DBMS_FILE_TRANSFER.PUT_FILE(

    source_directory_object                        => 'DATA_PUMP_DIR',

    source_file_name                               => 'mytab.dmp',

    destination_directory_object                   => 'DATA_PUMP_DIR',

    destination_file_name                          => 'mytab.dmp',

    destination_database                           => 'targetdblink'

    );

END;

/

End of dbms_file_transfer instructions

$ impdp new_owner/pwd@targetdb directory='DATA_PUMP_DIR' dumpfile=mytab.dmp full=y remap_schema=old_owner:new_owner

Workaround 2 Implementation 3: using the in-database procedure DBMS_DATAPUMP:

SOURCEDB> show user    -- should be OLD_OWNER

DECLARE

    h1 NUMBER;

BEGIN

    h1 := DBMS_DATAPUMP.OPEN(operation=>'EXPORT', job_mode=>'SCHEMA',     job_name=>'OLD_OWNER_EXP2');

    DBMS_DATAPUMP.ADD_FILE (

        handle=>h1,

        filename=>'mytab.dmp',

        directory=>'DATA_PUMP_DIR',

        filetype=>dbms_datapump.ku$_file_type_dump_file,

        reusefile=>1);

    DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR', 'IN(''OLD_OWNER'')');

    DBMS_DATAPUMP.start_job(h1)

END;

/

Insert dbms_file_transfer instructions here

TARGETDB> show user    -- should be NEW_OWNER

DECLARE

    h1 NUMBER;

BEGIN

    h1:= DBMS_DATAPUMP.OPEN(operation=>'IMPORT', job_mode=>'SCHEMA',     job_name=>'NEW_OWNER_IMP2');

        DBMS_DATAPUMP.ADD_FILE (

        handle=>h1,

        filename=>'mytab.dmp',

        directory=>'DATA_PUMP_DIR',

        filetype=>dbms_datapump.ku$_file_type_dump_file

);

    DBMS_DATAPUMP.METADATA_REMAP(handle=>h1, name=>'REMAP_SCHEMA',

    old_value=>'OLD_OWNER',value=>'NEW_OWNER');

    DBMS_DATAPUMP.start_job(h1);

END;

/

Amazon RDS, Oracle, ORA-20997, dbms_file_transfer, expdp/impdp, exp/imp, EXP_FULL_DATABASE, IMP_FULL_DATABASE, ORA-00604, ORA-06512, grants not allowed, DBMS_DATAPUMP


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-08-25