impdp / requires unjustified privileges / to copy a table between your own schemas / different schema names

Introduction

"Regular users" (without DBA privileges) need to copy tables between databases.
Exp / Imp were good tools in the past. They are deprecated today.

Expdp / Impdp are recommended instead. As usual, new tools come with new issues.

In general, issue is that new tool/approach is not "Regular user" friendly. Also, I have not seen that any other new tool would cover missing functionality.

Expdp file is written by database server and Impdp file is read by database server (exp/imp file is written/read by client).  Therefore, "Regular user" must have access to both database server "data-pump" locations. This is quite often unacceptable from security point of view.

Another option is to use database link between database servers. But there is quite nasty issue if schema names does not match.

Issue is the following:
"Regular user" can not expdp/impdp a table between databases if schema names does not match even if "Regular user" has full access (read: uses schema name to connect) to source and target database. Error "ORA-31631: privileges are required" is reported instead of table copy.

Workaround is the following:
You need to grant the following roles to "Regular user" to make successful copy (yes, both are needed!):

1. role DATAPUMP_EXP_FULL_DATABASE in source database
2. role DATAPUMP_IMP_FULL_DATABASE in target database

I think it is enough to note that both of listed roles give "SELECT ANY TABLE" privilege to user.
In general, this is just not acceptable.

Test case

Here is test case to demonstrate the issue based on Oracle 12.1.0.2 default database installation.

Description of test case:

1. create user USER_SRC with table TABLE_SRC.
2. create user USER_TGT with privilege CREATE TABLE
3. connect USER_TGT. create DATABASE LINK to have connection to USER_SRC;
4. start impdp to copy TABLE_SRC over database link

Result:

• ORA-31631: privileges are required
• ORA-39109: Unprivileged users may not operate upon other users' schemas

Here is test case output

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 14 21:03:05 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> spool test.log
SQL>
SQL> connect / as sysdba

Connected.
SQL>
SQL> -- create user
SQL> CREATE USER user_src IDENTIFIED BY user_src DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO user_src;

Grant succeeded.

SQL>
SQL> -- create user
SQL> CREATE USER user_tgt IDENTIFIED BY user_tgt DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE DATABASE LINK TO user_tgt;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO user_tgt;

Grant succeeded.

SQL>
SQL> -- create table
SQL> CONNECT user_src/user_src
Connected.
SQL> CREATE TABLE table_src (ID NUMBER);

Table created.

SQL> INSERT INTO table_src VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> -- create db link
SQL> CONNECT user_tgt/user_tgt
Connected.
SQL> CREATE DATABASE LINK dblink_to_src CONNECT TO user_src IDENTIFIED BY user_src USING 'ORCL';

Database link created.

SQL> SELECT * FROM table_src@dblink_to_src;

    ID
----------
     1

SQL>
SQL> -- run impdp
SQL> ! impdp user_tgt/user_tgt tables=user_src.table_src CONTENT=ALL DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=dblink_to_src remap_schema=user_src:user_tgt

Import: Release 12.1.0.2.0 - Production on Sun Jun 14 21:03:05 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas



-- PS: Error message is a bit ironic. I am not operating "upon other users' schemas". I am connected to source as "me" and to target as "me". I want to copy my table from my schema to my schema.

References 

Bug 20834622 - IMPDP REQUIRES UNJUSTIFIED PRIVILEGES TO COPY A TABLE BETWEEN CLIENT SCHEMAS
Registered in April 2015

-- end --