IMP_FULL_DATABASE
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 original import utility.
To grant the role to an user who wants to take the job to import data by original utility, we only need a granting like this:
SQL> grant imp_full_database to hr;
Grant succeeded.
The role is usually granted along with EXP_FULL_DATABASE role to users who are responsible for data migration.
Here we list privileges in IMP_FULL_DATABASE by the following categories.
Roles
We use the following SQL statement to check the roles in IMP_FULL_DATABASE.
SQL> set pagesize 1000;
SQL> select granted_role from dba_role_privs where grantee = 'IMP_FULL_DATABASE' order by 1;
There're 2 roles granted to IMP_FULL_DATABASE.
- EXECUTE_CATALOG_ROLE
- SELECT_CATALOG_ROLE
System Privileges
We use the following SQL statement to check the system privileges in IMP_FULL_DATABASE.
SQL> set pagesize 1000;
SQL> select privilege from dba_sys_privs where grantee = 'IMP_FULL_DATABASE' order by 1;
There're 81 system privileges granted to IMP_FULL_DATABASE.
- ADMINISTER DATABASE TRIGGER
- ADMINISTER RESOURCE MANAGER
- ADMINISTER SQL MANAGEMENT OBJECT
- ALTER ANY PROCEDURE
- ALTER ANY TABLE
- ALTER ANY TRIGGER
- ALTER ANY TYPE
- ALTER DATABASE
- ALTER PROFILE
- ALTER RESOURCE COST
- ALTER TABLESPACE
- ALTER USER
- ANALYZE ANY
- AUDIT ANY
- AUDIT SYSTEM
- BECOME USER
- COMMENT ANY TABLE
- CREATE ANY CLUSTER
- CREATE ANY CONTEXT
- CREATE ANY DIMENSION
- CREATE ANY DIRECTORY
- CREATE ANY INDEX
- CREATE ANY INDEXTYPE
- CREATE ANY LIBRARY
- CREATE ANY MATERIALIZED VIEW
- CREATE ANY OPERATOR
- CREATE ANY PROCEDURE
- CREATE ANY SEQUENCE
- CREATE ANY SQL PROFILE
- CREATE ANY SYNONYM
- CREATE ANY TABLE
- CREATE ANY TRIGGER
- CREATE ANY TYPE
- CREATE ANY VIEW
- CREATE DATABASE LINK
- CREATE PROFILE
- CREATE PUBLIC DATABASE LINK
- CREATE PUBLIC SYNONYM
- CREATE ROLE
- CREATE ROLLBACK SEGMENT
- CREATE SESSION
- CREATE TABLESPACE
- CREATE USER
- DELETE ANY TABLE
- DROP ANY CLUSTER
- DROP ANY CONTEXT
- DROP ANY DIMENSION
- DROP ANY DIRECTORY
- DROP ANY INDEX
- DROP ANY INDEXTYPE
- DROP ANY LIBRARY
- DROP ANY MATERIALIZED VIEW
- DROP ANY OPERATOR
- DROP ANY OUTLINE
- DROP ANY PROCEDURE
- DROP ANY ROLE
- DROP ANY SEQUENCE
- DROP ANY SQL PROFILE
- DROP ANY SYNONYM
- DROP ANY TABLE
- DROP ANY TRIGGER
- DROP ANY TYPE
- DROP ANY VIEW
- DROP PROFILE
- DROP PUBLIC DATABASE LINK
- DROP PUBLIC SYNONYM
- DROP ROLLBACK SEGMENT
- DROP TABLESPACE
- DROP USER
- EXECUTE ANY OPERATOR
- EXECUTE ANY PROCEDURE
- EXECUTE ANY TYPE
- GLOBAL QUERY REWRITE
- GRANT ANY OBJECT PRIVILEGE
- GRANT ANY PRIVILEGE
- GRANT ANY ROLE
- INSERT ANY TABLE
- MANAGE ANY QUEUE
- RESUMABLE
- SELECT ANY TABLE
- UPDATE ANY TABLE
Object Privileges
We use the following SQL statement to check the object privileges in IMP_FULL_DATABASE.
SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'IMP_FULL_DATABASE' order by owner, table_name, privilege;
There're 26 object privileges granted to IMP_FULL_DATABASE.
- READ on SYS.DATA_PUMP_DIR
- WRITE on SYS.DATA_PUMP_DIR
- EXECUTE on SYS.DBMS_AQADM
- EXECUTE on SYS.DBMS_AQ_IMPORT_INTERNAL
- EXECUTE on SYS.DBMS_AUTO_TASK_ADMIN
- EXECUTE on SYS.DBMS_AUTO_TASK_EXPORT
- EXECUTE on SYS.DBMS_DEFER_IMPORT_INTERNAL
- EXECUTE on SYS.DBMS_IJOB
- EXECUTE on SYS.DBMS_IREFRESH
- EXECUTE on SYS.DBMS_JVM_EXP_PERMS
- EXECUTE on SYS.DBMS_PSWMG_IMPORT
- EXECUTE on SYS.DBMS_REDACT
- EXECUTE on SYS.DBMS_REPCAT
- EXECUTE on SYS.DBMS_REPCAT_MIG
- EXECUTE on SYS.DBMS_RULE_EXIMP
- EXECUTE on SYS.DBMS_SERVER_ALERT
- EXECUTE on SYS.DBMS_TRANSFORM_EXIMP
- DELETE on SYS.EXPIMP_TTS_CT$
- INSERT on SYS.EXPIMP_TTS_CT$
- SELECT on SYS.EXPIMP_TTS_CT$
- UPDATE on SYS.EXPIMP_TTS_CT$
- SELECT on SYS.IMPRBS
- SELECT on SYS.KET$_CLIENT_CONFIG
- DELETE on SYS.RADM_FPTM$
- INSERT on SYS.RADM_FPTM$
- SELECT on SYS.RADM_FPTM$
To enable exporting full DB data into original dump files, you need EXP_FULL_DATABASE role, a full-fledged set of privileges to do so.