DATAPUMP_IMP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE is one of predefined roles in Oracle database, who provides a bunch of system and object privileges required to perform full database imports using the data pump import utility.
Furthermore, Oracle develops the role based on 2 earlier data migration roles, IMP_FULL_DATABASE and EXP_FULL_DATABASE.
To grant the role to an user who wants to take the job to import data by data pump, we only need a granting like this:
SQL> grant datapump_imp_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_IMP_FULL_DATABASE.
SQL> set pagesize 1000;
SQL> select granted_role from dba_role_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by 1;
There're 2 roles granted to DATAPUMP_IMP_FULL_DATABASE.
- EXP_FULL_DATABASE
- IMP_FULL_DATABASE
As we can see, DATAPUMP_IMP_FULL_DATABASE bases on not only IMP_FULL_DATABASE, but also EXP_FULL_DATABASE role!
System Privileges
We use the following SQL statement to check the system privileges in DATAPUMP_IMP_FULL_DATABASE.
SQL> select privilege from dba_sys_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by 1;
There're 2 system privileges granted to DATAPUMP_IMP_FULL_DATABASE.
- CREATE SESSION
- CREATE TABLE
Object Privileges
We use the following SQL statement to check the object privileges in DATAPUMP_IMP_FULL_DATABASE.
SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by owner, table_name, privilege;
There're 2 object privileges granted to DATAPUMP_IMP_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_EXP_FULL_DATABASE role, a full-fledged set of privileges to do so.