IMPDP failing with ORA-31625 and ORA-01031: insufficient privileges with Database Vault

Those days, I was having a lot of errors during my Import Datapump session in a Oracle 11g with Database Vault environment.

As the schema was protected, first I did the overall check up making sure the user I was using to import had the DBA permission and I also gave it full access to the realm.

However, I was receiving a lot of ORA-31625 and ORA-01031 errors in my impdp log file as below:

ORA-31625: Schema XXX is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

As a temporary solution, I tried to disable the realm. However, the error persisted. Then, I added the granted the user the Database Vault-specific authorization by using the DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure. However, the error still persisted.

After researching a little more, I discover that the cause of the failure was that the "BECOME USER" system privilege is not granted to DBA or IMP_FULL_DATABASE roles as it is in non Datavault environments.

In fact, when you enable Datavault, the DBA and IMP_FULL_DATABASE lose the following privileges:

DBA:
	BECOME USER
	CREATE ANY JOB
	CREATE EXTERNAL JOB
	DEQUEUE ANY QUEUE
	ENQUEUE ANY QUEUE
	EXECUTE ANY CLASS
	EXECUTE ANY PROGRAM
	MANAGE ANY QUEUE
	MANAGE SCHEDULER
	SELECT ANY TRANSACTION

IMP_FULL_DATABASE:
	BECOME USER
	MANAGE ANY QUEUE

After granting to the user running import the "BECOME USER" privilege, everything started working again =]

In the beginning, the first thing that come to my was that this would be an Oracle Bug. But it's not a bug. Why?

Because Oracle give in his Oficial Documentation the exactly steps that should be done if you want to run expdp/impdp in a DV environment:

• Users who import data into another schema must have the BECOME USER system privilege. To find the privileges a user has been granted, query the USER_SYS_PRIVS data dictionary view.
• A database administrator wants to export or import data in a schema that has no realm protection. In this case, this user only needs the standard Oracle Data Pump privileges, not the Oracle Database Vault authorization.
• A database administrator wants to export or import data in a protected schema. You must grant this user Database Vault-specific authorization by using the DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure. This authorization applies to both the EXPDP and IMPDP utilities. Later on, you can revoke this authorization by using the DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER procedure.
• A database administrator wants to export or import the contents of an entire database. In addition to the authorization granted by the DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure, you must grant this user the DV_OWNER role. For the import operation, this user must have the BECOME USER privilege.

So, the all that I needed was to execute the steps 1 and 3. The user that runs impdp doesn't need to be part of the realm, neither is necessary to disable it.

Everything is now working correctly, and one more exception in my list for future cases.

+27