DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE is one of predefined roles in Oracle database, who provides a bunch of system and object privileges required to perform full database exports using the data pump export utility.
Furthermore, Oracle develops the role based on an earlier export role EXP_FULL_DATABASE which has enough rights to export full data from a database.
To grant the role to an user who wants to take the job to export data by data pump, we only need a granting like this:
SQL> grant datapump_exp_full_database to hr;
Grant succeeded.
The role is usually granted along with DATAPUMP_EXP_FULL_DATABASE to users who are responsible for data migration.
Here we list privileges in DATAPUMP_IMP_FULL_DATABASE by the following categories.
Roles
We use the following SQL statement to check the roles in DATAPUMP_EXP_FULL_DATABASE.
SQL> set pagesize 1000;
SQL> select granted_role from dba_role_privs where grantee = 'DATAPUMP_EXP_FULL_DATABASE' order by 1;
There's only 1 role granted to DATAPUMP_EXP_FULL_DATABASE.
- EXP_FULL_DATABASE
As we can see, DATAPUMP_EXP_FULL_DATABASE truly inherits privileges from EXP_FULL_DATABASE, the most used role for exporting data prior to data pump.
System Privileges
We use the following SQL statement to check the system privileges in DATAPUMP_EXP_FULL_DATABASE.
SQL> select privilege from dba_sys_privs where grantee = 'DATAPUMP_EXP_FULL_DATABASE' order by 1;
There're 2 system privileges granted to DATAPUMP_EXP_FULL_DATABASE.
- CREATE SESSION
- CREATE TABLE
In fact, the base role EXP_FULL_DATABASE has already been granted these privileges.
Object Privileges
We use the following SQL statement to check the object privileges in DATAPUMP_EXP_FULL_DATABASE.
SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'DATAPUMP_EXP_FULL_DATABASE' order by owner, table_name, privilege;
There're 2 object privileges granted to DATAPUMP_EXP_FULL_DATABASE.
- EXECUTE on SYS.DBMS_JVM_EXP_PERMS
- EXECUTE on SYS.SDO_RDF_EXP_IMP
To enable importing full DB data from data pump dump files, you need DATAPUMP_IMP_FULL_DATABASE role, a full-fledged set of privileges to do so.