Required Roles for Data PumpExport and Import Operations
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.)
The DATAPUMP_EXP_FULL_DATABASE role affects only export operations. The DATAPUMP_ IMP_FULL_DATABASE role affects import operations and operations that use the Import SQLFILE parameter. These roles allow users performing exports and imports to do the following:
-
Perform the operation outside the scope of their schema
-
Monitor jobs that were initiated by another user
-
Export objects (such as tablespace definitions) and import objects (such as directory definitions) that unprivileged users cannot reference
These are powerful roles. Database administrators should use caution when granting these roles to users.
Although the SYSschema does not have either of these roles assigned to it, all security checks performed by Data Pump that require these roles also grant access to the SYS schema.
Default Locations for Dump, Log, and SQL Files
On UNIX and Windows NT systems, a default directory object, DATA_PUMP_DIR, is created at database creation or whenever the database dictionary is upgraded. By default, it is available only toprivileged users. (The user SYSTEMhas read and write access to the DATA_PUMP_DIRdirectory, by default.)
If you are not a privileged user, then before you can run Data Pump Export or Data Pump Import, a directory object must be created by a database administrator (DBA) or by any user with the CREATE ANY DIRECTORYprivilege.
After a directory is created, the user creating the directory object must grant READor WRITEpermission on the directory to other users. For example, to allow the Oracle database to read and write files on behalf of user hrin the directory named by dpump_ dir1, the DBA must execute the following command:
SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;
根据文档内容，我们获取以下相关信息：
-
备份用户如果要对其他用户进行备份，必须具有DATAPUMP_EXP_FULL_DATABASE角色以及在相应目录对象上（例如dpump_dir1）的READ、WRITE权限。 但在实际测试中，我们发现，对于已经被赋予了DATAPUMP_EXP_FULL_DATABASE角色的用户来说，如果使用DATA_PUMP_DIR所指定的目录，则不需要赋予READ、WRITE权限。下面的试验将解释原因。
-
需要强调的是，如不使用DATA_PUMP_DIR作为指定的目录，即便赋予DATAPUMP_EXP_FULL_DATABASE角色，仍需要对目录赋予READ、WRITE权限。
Expriment 1
-
创建用户test，赋予RESOURCE、CREATE SESSION和DATAPUMP_EXP_FULL_DATABASE角色及权限。进行备份（hr和scott用户），备份成功。 SQL> CREATE USER TEST/TEST;
-
SQL> CREATE USER TEST IDENTIFIED BY TEST
-
SQL> GRANT RESOURCE TO TEST;
-
SQL> GRANT CREATE SESSION TO TEST;
-
-
SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO TEST;
-
Grant succeeded.
-
-
SQL> SELECT * FROM ALL_TAB_PRIVS WHERE GRANTEE='TEST';
-
no rows selected
-
-
[oracle@197198~]$ expdp test/test schemas=hr,scott directory=data_pump_dir dumpfile=export_test.dmp logfile=exp_test.log
-
-
Export: Release 11.2.0.1.0 - Production on Tue Nov 15 15:40:07 2016
-
-
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-
-
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** schemas=hr,scott directory=data_pump_dir dumpfile=export_test.dmp logfile=exp_test.log
-
Estimate in progress using BLOCKS method...
-
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
-
Total estimation using BLOCKS method: 1.062 MB
-
Processing object type SCHEMA_EXPORT/USER
-
. . .
-
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01" 194.5 KB 1099 rows
-
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
-
. . .
-
. . exported "SCOTT"."BONUS" 0 KB 0 rows
-
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
-
******************************************************************************
-
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
-
/u01/app/oracle/admin/ora198/dpdump/export_test.dmp
-
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:40:57
Expriment 2
-
收回test用户的DATAPUMP_EXP_FULL_DATABASE权限，再次进行对test进行备份，备份失败。错误信息：无法打开日志文件。
-
SQL> REVOKE DATAPUMP_EXP_FULL_DATABASE FROM TEST;
-
[oracle@197198 ~]$ expdp test/TEST schemas=test directory=data_pump_dir dumpfile=export_test1.dmp logfile=exp_test.log
-
-
Export: Release 11.2.0.1.0 - Production on Tue Nov 15 16:17:54 2016
-
-
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-
-
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
ORA-39002: invalid operation
-
ORA-39070: Unable to open the log file.
-
ORA-39087: directory name DATA_PUMP_DIR is invalid
Expriment 3
-
对test用户在目录data_pump_dir赋予read和write权限后，再次进行备份，备份成功。
-
SQL> grant read,write on directory data_pump_dir to test;
-
[oracle@197198 ~]$ expdp test/TEST schemas=test directory=data_pump_dir dumpfile=export_test1.dmp logfile=exp_test.log
-
-
Export: Release 11.2.0.1.0 - Production on Tue Nov 15 16:19:48 2016
-
-
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-
-
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test
DIGGING
-
查询DATAPUMP_EXP_FULL_DATABASE角色拥有的系统权限
-
SQL> select * from dba_sys_privs where grantee='DATAPUMP_EXP_FULL_DATABASE';
-
查询DATAPUMP_EXP_FULL_DATABASE角色拥有角色
-
SQL> select * from dba_role_privs where grantee='DATAPUMP_EXP_FULL_DATABASE';
-
-
GRANTEE GRANTED_ROLE ADM DEF
-
------------------------------------------- ------------------------------ ------ ---
-
DATAPUMP_EXP_FULL_DATABASE EXP_FULL_DATABASE NO YES
-
查询DATA_PUMP_DIR目录下那些角色拥有READ、WRITE权限
-
SQL> select * from dba_tab_privs where table_name='DATA_PUMP_DIR' order by grantee;
-
-
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
-
EXP_FULL_DATABASE SYS DATA_PUMP_DIR SYS WRITE NO NO
-
EXP_FULL_DATABASE SYS DATA_PUMP_DIR SYS READ NO NO
-
IMP_FULL_DATABASE SYS DATA_PUMP_DIR SYS WRITE NO NO
-
IMP_FULL_DATABASE SYS DATA_PUMP_DIR SYS READ NO NO
至此可以看出，由于赋予了DATAPUMP_EXP_FULL_DATABASE角色，用户同时拥有了EXP_FULL_DATABASE角色，该角色对DATA_PUMP_DIR目录具有READ、WRITE权限，因此在使用DATA_PUMP_DIR作为directory参数指定值时，不需要额外赋予READ、WRITE权限，而使用用户自定义的目录（例如dump_dir1）作为指定目录时，仍需要赋予对目录的READ、WRITE权限。
|
