Performing a Data Pump Export of an Application Schema

8 Performing a Data Pump Export of an Application Schema

When you want to export data that is protected by Oracle Database Vault realms or command rules, you must authorize the user to perform the task. Because Database Vault is enforcing a mandatory access control policy, only having the system privileges to perform an Oracle Data Pump export is not enough.

If the user is expected to perform Oracle Data Pump full database export, import into another schema, or transportable tablespace operations, then they must have additional authorizations and configuration. For more information on these scenarios, please see DBA Operations in an Oracle Database Vault Environment in the Oracle Database Vault Administrator's Guide.

This quick start guide will focus on two types of Oracle Data Pump exports:

Full-schema exports
Table-only exports

If you are not exporting the schema or table as the schema owner, then you must have the appropriate Oracle Database system roles to perform an export. You will create a dedicated database user to perform these two export operations.

As C##JSMITH, create a PDB database user named DP_MGR:
1. Connect as C##JSMITH to the pluggable database:
```
connect c##jsmith@pdb_name
```
2. Create the DP_MGR named user:
```
CREATE USER DP_MGR IDENTIFIED BY password;
GRANT CONNECT, EXP_FULL_DATABASE TO DP_MGR;
GRANT UNLIMITED TABLESPACE TO DP_MGR;
```
Attempt to perform an Oracle Data Pump Export as DP_MGR:
```
expdp dp_mgr@pdb_name SCHEMAS=HR directory=DATA_PUMP_DIR REUSE_DUMPFILES=y dumpfile=test1.dmp logfile=test1.log
```
You will encounter the following error for each of the table data objects you are attempting to export. This is because the objects are protected by the Database Vault realm, Protect HR tables.
```
ORA-31693: Table data object "HR"."COUNTRIES" failed to load/unload and is being skipped due to error:
ORA-47415: Insufficient Oracle Database Vault authorization for DATAPUMP.
```
Identify the database users who have system privileges to perform Data Pump exports or imports:
1. Connect as C##JSMITH to the pluggable database:
```
connect c##jsmith@pdb_name
```
2. Run the following command:
```
SELECT GRANTEE, GRANTED_ROLE 
    FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE IN ('EXP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE')
ORDER BY 1,2;
```
Authorize DP_MGR to only export a single table based on their database privileges.
With Oracle Database Vault enabled on the pluggable database, it is necessary to authorize users to use their privileges. System privileges are not enough to perform the Data Pump export.
```
EXEC DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('DP_MGR', 'HR', 'EMPLOYEES');
```

Review the Oracle Database Vault Data Pump authorizations:

SELECT * FROM DBA_DV_DATAPUMP_AUTH;

Only TABLE exports by DP_MGR should be authorized.

GRANTEE    SCHEMA    OBJECT       TYPE    ACTION
__________ _________ ____________ ________ _________
DP_MGR     HR        EMPLOYEES    TABLE    %

Run the Data Pump Export command:

expdp dp_mgr@pdb_name SCHEMAS=HR directory=DATA_PUMP_DIR REUSE_DUMPFILES=y dumpfile=test1.dmp logfile=test1.log

You will find the HR.EMPLOYEES table has been exported but you will still receive ORA-31693 errors for all other schema tables and views.

ORA-31693: Table data object "HR"."COUNTRIES" failed to load/unload and is being skipped due to error:
ORA-47415: Insufficient Oracle Database Vault authorization for DATAPUMP.
. . exported "HR"."EMPLOYEES" 17.32 KB 107 rows

To perform the Data Pump Export, without the Database Vault violations, you must specify the HR.EMPLOYEES table for export:
```
expdp dp_mgr@pdb_name TABLES=HR.EMPLOYEES directory=DATA_PUMP_DIR REUSE_DUMPFILES=y dumpfile=test1.dmp logfile=test1.log
```
To authorize DP_MGR to export a schema, based on their database privileges, revoke the table-specific authorization, and authorize the schema export:
```
EXEC DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER('DP_MGR', 'HR', 'EMPLOYEES');
EXEC DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('DP_MGR', 'HR');
```

Review the Oracle Database Vault Data Pump authorizations. Only HR schema exports by DP_MGR should be authorized:

SELECT * FROM DBA_DV_DATAPUMP_AUTH;

GRANTEE    SCHEMA    OBJECT       TYPE    ACTION
__________ _________ ____________ ________ _________
DP_MGR     HR        %            %        %

Authorize DP_MGR to export schema, based on their database privileges, run the following command:
```
EXEC DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER('DP_MGR', 'HR');
EXEC DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('DP_MGR');
```
Note:
This does not include performing a full Oracle Data Pump export. To perform a full export, the DP_MGR requires the DV_OWNER role. It is recommended to be more specific than full Data Pump exports. Export specific schemas or objects based on your requirements rather than the full database.

Review the Oracle Database Vault Data Pump authorizations for DP_MGR:

SELECT * FROM DBA_DV_DATAPUMP_AUTH;

GRANTEE    SCHEMA    OBJECT       TYPE    ACTION
__________ _________ ____________ ________ _________
DP_MGR     %        %            %        %

Now, your DP_MGR user can perform a Data Pump export like this example:

expdp dp_mgr@pdb_name SCHEMAS=HR,SCOTT,SH directory=DATA_PUMP_DIR REUSE_DUMPFILES=y dumpfile=test1.dmp logfile=test1.log

To revoke the authorizations for DP_MGR to use their database privileges, run the following command:
```
exec dbms_macadm.unauthorize_datapump_user('DP_MGR');
```
The query to review your Data Pump authorizations should return no row:
```
SELECT * FROM DBA_DV_DATAPUMP_AUTH; 
```