关于expdp,impdp操作需要的权限

一直对 DATAPUMP_EXP_FULL_DATABASE 和 DATAPUMP_IMP_FULL_DATABASE 这两个权限有误解，以为是导全库的时候才需要这个权限，翻了文档后才知道，不是的.

Many Data Pump Export and Import operations require the user to have the DATAPUMP_EXP_FULL_DATABASE role and/or the DATAPUMP_IMP_FULL_DATABASE role. These roles are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. (Note that although the names of these roles contain the word FULL, these roles are actually required for all export and import modes, not only Full mode.)

我们可以从$ORACLE_HOME/rdbms/admin/catdpb.sql 一窥该权限的全貌。

01
-- $ORACLE_HOME/rdbms/admin/catdpb.sql
02

03
...
04
Rem    NAME
05
Rem      catdpb.sql - Main install script for all DataPump package body
06
Rem                   components
07

08
...
09

10
-------------------------------------------------------------------------
11
Rem Set up application roles to to be enabled for privileged users
12
-------------------------------------------------------------------------
13

14
CREATE ROLE datapump_exp_full_database;
15
CREATE ROLE datapump_imp_full_database;
16

17
GRANT exp_full_database          TO datapump_exp_full_database;
18
Rem Following grant needed for fgac test in dpx3f2
19
GRANT create table               TO datapump_exp_full_database;
20
GRANT create session             TO datapump_exp_full_database;
21

22
GRANT alter resource cost        TO datapump_imp_full_database;
23
GRANT alter user                 TO datapump_imp_full_database;
24
GRANT audit any                  TO datapump_imp_full_database;
25
GRANT audit system               TO datapump_imp_full_database;
26
GRANT create session             TO datapump_imp_full_database;
27
GRANT alter profile              TO datapump_imp_full_database;
28
GRANT create profile             TO datapump_imp_full_database;
29
GRANT delete any table           TO datapump_imp_full_database;
30
GRANT execute any operator       TO datapump_imp_full_database;
31
GRANT grant any privilege        TO datapump_imp_full_database;
32
GRANT grant any object privilege TO datapump_imp_full_database;
33
GRANT grant any role             TO datapump_imp_full_database;
34
GRANT imp_full_database          TO datapump_imp_full_database;
35
GRANT select any table           TO datapump_imp_full_database;
36
GRANT alter database             TO datapump_imp_full_database;
37

38
Rem The following grant is needed to make loopback network jobs work right
39
Rem Since the application role makes it disappear otherwise.
40

41
GRANT exp_full_database          TO datapump_imp_full_database;
42

43
GRANT export full database TO dba;
44
GRANT import full database TO dba;
45
GRANT datapump_exp_full_database TO dba;
46
GRANT datapump_imp_full_database TO dba;
47

48
Rem DataPump roles are not documented so also grant them to old exp/imp roles
49

50
Rem Following grant needed for fgac test in dpx3f2
51
GRANT create table               TO exp_full_database;
52
GRANT create session             TO exp_full_database;
53

54
GRANT alter resource cost        TO imp_full_database;
55
GRANT alter user                 TO imp_full_database;
56
GRANT audit any                  TO imp_full_database;
57
GRANT audit system               TO imp_full_database;
58
GRANT create session             TO imp_full_database;
59
GRANT alter profile              TO imp_full_database;
60
GRANT create profile             TO imp_full_database;
61
GRANT delete any table           TO imp_full_database;
62
GRANT execute any operator       TO imp_full_database;
63
GRANT grant any privilege        TO imp_full_database;
64
GRANT grant any object privilege TO imp_full_database;
65
GRANT grant any role             TO imp_full_database;
66
GRANT select any table           TO imp_full_database;
67
GRANT alter database             TO imp_full_database;
68

69

70

71
-------------------------------------------------------------------------
72
--     Public view defs (DBA_/USER_*) go here.
73
-------------------------------------------------------------------------
74

75
--  Fixed (virtual) View Declarations, Synonyms, and Grants
76
CREATE OR REPLACE VIEW SYS.V_$DATAPUMP_JOB AS
77
  SELECT * FROM SYS.V$DATAPUMP_JOB;
78
CREATE OR REPLACE PUBLIC SYNONYM V$DATAPUMP_JOB FOR SYS.V_$DATAPUMP_JOB;
79
GRANT SELECT ON SYS.V_$DATAPUMP_JOB TO SELECT_CATALOG_ROLE;
80

81
CREATE OR REPLACE VIEW SYS.V_$DATAPUMP_SESSION AS
82
  SELECT * FROM SYS.V$DATAPUMP_SESSION;
83
CREATE OR REPLACE PUBLIC SYNONYM V$DATAPUMP_SESSION FOR
84
  SYS.V_$DATAPUMP_SESSION;
85
GRANT SELECT ON SYS.V_$DATAPUMP_SESSION TO SELECT_CATALOG_ROLE;
86

87
CREATE OR REPLACE VIEW SYS.GV_$DATAPUMP_JOB AS
88
  SELECT * FROM SYS.GV$DATAPUMP_JOB;
89
CREATE OR REPLACE PUBLIC SYNONYM GV$DATAPUMP_JOB FOR SYS.GV_$DATAPUMP_JOB;
90
GRANT SELECT ON SYS.GV_$DATAPUMP_JOB TO SELECT_CATALOG_ROLE;
91

92
CREATE OR REPLACE VIEW SYS.GV_$DATAPUMP_SESSION AS
93
  SELECT * FROM SYS.GV$DATAPUMP_SESSION;
94
CREATE OR REPLACE PUBLIC SYNONYM GV$DATAPUMP_SESSION FOR
95
  SYS.GV_$DATAPUMP_SESSION;
96
GRANT SELECT ON SYS.GV_$DATAPUMP_SESSION TO SELECT_CATALOG_ROLE;