能select的表，可以exp嗎？有exp_full_database就能exp其他使用者的表嗎？

達芬奇的夢發表於2018-05-02
Database
能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 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 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 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/31444259/viewspace-2153672/，如需轉載，請註明出處，否則將追究法律責任。

相關文章