EXP_FULL_DATABASE, How and Why

EXP_FULL_DATABASE

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 original export utility.

To grant the role to an user who wants to take the job to export data by original utility, we only need a granting like this:


SQL> grant exp_full_database to hr;



Grant succeeded.

The role is usually granted along with IMP_FULL_DATABASE to users who are responsible for data migration.

Here we list privileges in EXP_FULL_DATABASE by the following categories.

Roles
System Privileges
Object Privileges

Roles

We use the following SQL statement to check the roles in EXP_FULL_DATABASE.


SQL> set pagesize 1000;

SQL> select granted_role from dba_role_privs where grantee = 'EXP_FULL_DATABASE' order by 1;

There're 2 roles granted to EXP_FULL_DATABASE.

EXECUTE_CATALOG_ROLE
SELECT_CATALOG_ROLE

System Privileges

We use the following SQL statement to check the system privileges in EXP_FULL_DATABASE.


SQL> select privilege from dba_sys_privs where grantee = 'EXP_FULL_DATABASE' order by 1;

There're 14 system privileges granted to EXP_FULL_DATABASE.

ADMINISTER RESOURCE MANAGER
ADMINISTER SQL MANAGEMENT OBJECT
ANALYZE ANY
BACKUP ANY TABLE
CREATE SESSION
CREATE TABLE
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXEMPT REDACTION POLICY
FLASHBACK ANY TABLE
READ ANY FILE GROUP
RESUMABLE
SELECT ANY SEQUENCE
SELECT ANY TABLE

Object Privileges

We use the following SQL statement to check the object privileges in EXP_FULL_DATABASE.


SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'EXP_FULL_DATABASE' order by owner, table_name, privilege;

There're 62 object privileges granted to EXP_FULL_DATABASE.

READ on SYS.DATA_PUMP_DIR
WRITE on SYS.DATA_PUMP_DIR
EXECUTE on SYS.DBMSZEXP_SYSPKGGRNT
EXECUTE on SYS.DBMS_AQ_IMPORT_INTERNAL
EXECUTE on SYS.DBMS_AUTO_TASK_EXPORT
EXECUTE on SYS.DBMS_IREFRESH
EXECUTE on SYS.DBMS_PSWMG_IMPORT
EXECUTE on SYS.DBMS_RULE_EXIMP
EXECUTE on SYS.DBMS_TRANSFORM_EXIMP
SELECT on SYS.EXU10LNK
SELECT on SYS.EXU8LNK
SELECT on SYS.EXU8PHS
SELECT on SYS.EXU9LNK
DELETE on SYS.INCEXP
INSERT on SYS.INCEXP
UPDATE on SYS.INCEXP
DELETE on SYS.INCFIL
INSERT on SYS.INCFIL
UPDATE on SYS.INCFIL
DELETE on SYS.INCVID
INSERT on SYS.INCVID
UPDATE on SYS.INCVID
SELECT on SYS.KU$_11_2_PSW_HIST_VIEW
READ on SYS.KU$_12AUDIT_POLICY_ENABLE_VIEW
READ on SYS.KU$_AUDIT_CONTEXT_VIEW
READ on SYS.KU$_AUDIT_POLICY_ENABLE_VIEW
READ on SYS.KU$_AUDIT_POLICY_VIEW
SELECT on SYS.KU$_PSW_HIST_LIST_VIEW
SELECT on SYS.KU$_PSW_HIST_VIEW
SELECT on SYS._DBA_APPLY
SELECT on SYS._DBA_APPLY_BATCH_SQL_STATS
SELECT on SYS._DBA_APPLY_CDR_INFO
SELECT on SYS._DBA_APPLY_CHANGE_HANDLERS
SELECT on SYS._DBA_APPLY_CONF_HDLR_COLUMNS
SELECT on SYS._DBA_APPLY_COORDINATOR_STATS
SELECT on SYS._DBA_APPLY_DML_CONF_COLUMNS
SELECT on SYS._DBA_APPLY_DML_CONF_HANDLERS
SELECT on SYS._DBA_APPLY_ERROR
SELECT on SYS._DBA_APPLY_ERROR_HANDLER
SELECT on SYS._DBA_APPLY_ERROR_TXN
SELECT on SYS._DBA_APPLY_HANDLE_COLLISIONS
SELECT on SYS._DBA_APPLY_MILESTONE
SELECT on SYS._DBA_APPLY_PROGRESS
SELECT on SYS._DBA_APPLY_READER_STATS
SELECT on SYS._DBA_APPLY_REPERROR_HANDLERS
SELECT on SYS._DBA_APPLY_SERVER_STATS
SELECT on SYS._DBA_APPLY_SOURCE_OBJ
SELECT on SYS._DBA_APPLY_SOURCE_SCHEMA
SELECT on SYS._DBA_APPLY_SPILL_TXN
SELECT on SYS._DBA_APPLY_TABLE_STATS
SELECT on SYS._DBA_CAPTURE
SELECT on SYS._DBA_STREAMS_MESSAGE_CONSUMERS
SELECT on SYS._DBA_STREAMS_MESSAGE_RULES
SELECT on SYS._DBA_STREAMS_MSG_NOTIFICATIONS
SELECT on SYS._DBA_STREAMS_PRIVILEGED_USER
SELECT on SYS._DBA_STREAMS_RULES
SELECT on SYS._DBA_STREAMS_RULES_H
SELECT on SYS._DBA_STREAMS_STMTS
SELECT on SYS._DBA_STREAMS_TRANSFORMATIONS
SELECT on SYS._DBA_XSTREAM_CONNECTION
SELECT on SYS._DBA_XSTREAM_OUTBOUND
SELECT on SYS._DBA_XSTREAM_PARAMETERS

To enable importing full DB data from original dump files, you need IMP_FULL_DATABASE role, a full-fledged set of privileges to do so.

EXP_FULL_DATABASE, How and Why