Got error ORA-31625 and ORA-01031 insufficient privileges using data pump utility (expdp/impdp) under database vault environment

How authorise your database administrator using data pump utility to export and import data under database vault environment (DBV)?

Basically, DBA is not allowed to export and import data under database vault environment even you have granted EXP_FULL_DATABASE or IMP_FULL_DATABASE role. And must be got the error message with ORA-31625 and ORA-01031 when using data pump utility to export and import data under DBV environment.

Here show the example that allow the system user to using data pump utility, for details please refer to the oracle database vault administration online document.

A. To authorise the user using data pump utility to export and import all schema data.

For #1 and #2 , kindly please login as SYS

#1. Grant BECOME USER, EXP_FULL_DATABASE and IMP_FULL_DATABASE role  

    

      GRANT BECOME USER TO SYSTEM;

      GRANT EXP_FULL_DATABASE TO SYSTEM;

      GRANT IMP_FULL_DATABASE TO SYSTEM;

#2. verify the above roles are granted to the user

       

       SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SYSTEM'

       AND GRANTED_ROLE IN ('BECOME USER','EXP_FULL_DATABASE','IMP_FULL_DATABASE'); 

For #3, kindly please login as database vault owner

#3. authorise system using data pump utility

      EXEC DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM');

#4. verify the privileges

      SELECT * FROM DVSYS.DBA_DV_DATAPUMP_AUTH WHERE GRANTEE = 'SYSTEM';

B. Revoke the privileges and the role are granted to authorise user using data pump utility to export and import all schema data.

 For #1 and #2 , kindly please login as SYS

#1. Revoke BECOME USER, EXP_FULL_DATABASE and IMP_FULL_DATABASE role  

    

      REVOKE BECOME USER FROM SYSTEM;

      REVOKE EXP_FULL_DATABASE FROM SYSTEM;

      REVOKE IMP_FULL_DATABASE FROM SYSTEM;

#2. verify the above roles are granted to the user

       

       SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SYSTEM'

       AND GRANTED_ROLE IN ('BECOME USER','EXP_FULL_DATABASE','IMP_FULL_DATABASE'); 

For #3, kindly please login as database vault owner

#3. authorise system using data pump utility

      EXEC DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER('SYSTEM');

#4. verify the privileges

      SELECT * FROM DVSYS.DBA_DV_DATAPUMP_AUTH WHERE GRANTEE = 'SYSTEM';  

      

Reference:

https://docs.oracle.com/database/121/DVADM/dba.htm#DVADM70315

https://docs.oracle.com/database/121/DVADM/dba.htm#DVADM71324

How to export/import the database data

In here, I will show you the basic steps for export/import the data from oracle database 11g.

Please note that the output of the export (expdp) and import (impdp) utilities are not compatible with the original Export(exp) and Import(imp).

For details, please refer to the oracle online document:

https://docs.oracle.com/cd/E18283_01/server.112/e17222/expimp.htm#i262247

Remark: 

To ensure the data exported from the right database instance, please set the ORACLE_SID before you execute the script for export/import.

export ORACLE_SID=dummydb;

A. Sample scripts for the Original Export (exp) and Import (imp) utilities (Compatible with 9i or before).

Export(exp) Utility - export data with specified table:

EXP [USER] TABLES=[TABLE_NAME] FILE=[DUMP_FILE] LOG=[LOG_FILE] FULL=[Y/N] ROWS=[Y/N]

E.g

EXP HR TABLES=EMPLOYEES FILE=exp_hr_employees.dmp log=exp_hr_employees.log full=n rows=y

Import(imp) Utility - Import Data with specified table

IMP [USER] FILE=[EXP_DUMP_FILE_NAME] IGNORE=[Y/N] FROMUSER=[EXPORT_SOURCE_USER] TOUSER=[IMPORT_TARGET_USER] LOG=[LOG_NAME] TABLES=[TABLE_NAME] ROWS=[Y/N] COMMIT=[Y/N]

E.g

IMP SCOTT FILE=exp_hr_employees.dmp IGNORE=Y FROMUSER=HR TOUSER=SCOTT log=imp_hr_employees.log TABLES=EMPLOYEES ROWS=y COMMIT=Y

B. Sample scripts for the Data Pump Utilities (Compatible with 10g or above).

As data dump utility which required to create the directory object on database when perform export/import data process.

The directory object is only a pointer to a physical directory and it does not actually create the physical directory on the file system of the database.So, if the location is changed , we also need update the path in database side.

The directory objects may require to create by DBA if you without sys privilege “CREATE DIRECTORY” on database.

Also, it required the privileges for database user who want to export/import other user data on database.

1. Export Utility

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200

1.1 Export specify table data only

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] TABLES=[TABLE_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

EXPDP HR DIRECOTRY=DATA_EXP_IMP TABLES=TABLE_A DUMPFILE=impstestexp.dmp LOGFILE=action.log

1.2 Full Export

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

EXPDP HR DIRECOTRY=DATA_EXP_IMP DUMPFILE=impstestexp.dmp LOGFILE=action.log

1.3 Full Export with specified schema user

EXPDP [USER] DIRECTORY=[DIRECTORY_OBJECT_NAME] DUMPFILE=[DUMP_FILE] LOGFILE=[LOG_FILE_NAME] SCHEMA=[SCHEMA_NAME]

E.g

EXPDP SYSTEM DIRECOTRY=DATA_EXP_IMP DUMPFILE=impstestexp.dmp LOGFILE=action.log SCHEMA=HR

2. Import Utility

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

IMPDP [USER_NAME] DIRECTORY=[DIRETORY_OBJECT_NAME] REMAP_SCHEMA=[EXPORT_SOURCE_USER]:[IMPORT_TARGET_USER] DUMPFILE=[EXPORT_DUMP_FILE] LOGFILE=[LOG_FILE_NAME]

E.g

IMPDP SCOTT DIRECOTRY=DATA_EXP_IMP REMAP_SCHEMA=HR:SCOTT DUMPFILE=impstestexp.dmp LOGFILE=action.log