能select的表，可以exp吗？有exp_full_database就能exp其他用户的表吗？

能select的表，可以exp吗？有exp_full_database就能exp其他用户的表吗？

 

下面来测试下：

 

1.建立导出测试用户，并授予相关权限

[oracle@GXDB1] $sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 3 15:11:19 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.

SQL> create user test_exp identified by a;

User created.

SQL> grant connect to test_exp;

Grant succeeded.

SQL> grant select on dep_study.bas_region to test_exp;

Grant succeeded.

 

2.导出dep_study.bas_region表

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:26:15 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export DEP_STUDY's table BAS_REGION
Export terminated successfully with warnings.
[oracle@GXDB1] $

 

3.给test_exp授予exp_full_database权限
SQL> grant exp_full_database to test_exp;

Grant succeeded.

 

4.成功导出表

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:29:20 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to DEP_STUDY
. . exporting table                     BAS_REGION         17 rows exported
Export terminated successfully without warnings.

--ok 成功导出

 

5.收回select权限，并导出表

SQL>  revoke select on dep_study.bas_region from test_exp;

Revoke succeeded.

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:32:15 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to DEP_STUDY
. . exporting table                     BAS_REGION
EXP-00056: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Export terminated successfully with warnings.

结论：

    exp其他用户的表，用户需要拥有select表和exp_full_database(角色)两个权限。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-759707/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-759707/