Unable to export realm protected table using data pump

A database user cannot export with expdp if an object is protected by a realm due. This post is intended to explain the fix for this bug and the steps to implement before doing an export or an import using data pump (expdp / impdp) if Database Vault is enabled.

A user can use data pump to export in one of the following cases (in all the four cases the user should have standard Data Pump privileges) :

1. When user want to export or import objects from a schema that is not protected by a realm then the user can do that with standard data pump privileges (if in 11.2, see also point #5).

2. If the user wants to export or import objects from a schema that is protected by a realm and the user is the owner of the same schema then the user should be either participant or owner of the realm which is protecting the schema or the objects of the exported schema.

3. If the user wants to export or import objects protected by a realm from other schemas than his own, then the user must be granted Database Vault specific authorization by using the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure. Revoking these privileges can be done using the DVSYS.DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER procedure.

4. If the user wants to do a full database export or import then, in addition to the authorization granted by the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure, the user must also have DV_OWNER role.

5. If the user is in release 11.2 and exporting data from another schema, protected or not by a database vault realm, then in addition to the conditions above, the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure should be used to grant privileges to the exporting user on the realm protecting the SYSMAN schema.

Steps to grant Database Vault specific authorizations to users for import and export

1. Check whether the user has appropriate data pump privileges like EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. If not grant the same privilege to user. These are only needed if exporting a different schema than his own.

2. Grant Database Vault specific authorizations

SQL> conn DV_Owner

//Authorize the user "" to export and import objects for an entire database:
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('[username]');

//Authorize the user "" to export and import a specific schema (HR) or object (HR.EMPLOYEES):
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('[username]', 'HR');
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('[username]', 'HR', 'EMPLOYEES');

3. If the user wants to do a full database export then after step 2, grant DV_OWNER to the user.

4. Ensure that rule set “Allow Data Pump Operation”/”Allow Oracle Data Pump Operation” has been enabled by querying the DVSYS.DBA_DV_RULE_SET data dictionary view as follows:

SQL> SELECT ENABLED FROM DVSYS.DBA_DV_RULE_SET WHERE RULE_SET_NAME = 'Allow Data Pump Operation' or RULE_SET_NAME = 'Allow Oracle Data Pump Operation';

Steps to authorize users to perform TTS operations

Starting with 11.2.0.4 the DV authorization for Transportable Tablespace Sets(TTS)was introduced:

exec dbms_macadm.authorize_tts_user(uname,tsname)
exec dbms_macadm.unauthorize_tts_user(uname,tsname)

5. Starting with 11.2, the exporting user must also be granted appropriate privileges on the SYSMAN schema, this is required to check for potential AQ metadata:

connect 
begin
   DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('[username]','SYSMAN');
end;

This should be the solution when the following errors are reported when exporting data:

ORA-39127: unexpected error from call to export_string
:=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_LOADER_QTABLE_S','SYSM
AN',1,1,'11.02.00.00.00',newblock)
ORA-01031: insufficient privileges