prevent users using exp or imp 禁止用户使用导出和导入

how to prevent users using exp or imp

提问: 何如禁止用户使用exp或imp
回答: 可以, 但禁止了也不能保证数据安全


1.
参考文档
$ORACLE_HOME/rdbms/admin/catexp.sql
和
Default Privileges Assigned To PUBLIC In 10.2.0.4 Database

只要从PUBLIC回收一些视图的权限, 便可以禁止用户使用exp或imp


2.
exp加trace=y, 跟踪一下

exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 05:51:18 2009

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, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件, 查看exp查询的EXU,IMP视图等

sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)

[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
EXU8OPT
EXU8USRU
EXU8FUL
EXU8VER
EXU81JAVT
EXU9XDBUID
EXU81CSC
EXU9GSAS
EXU9NLS
EXU10ADEFPSWITCHES
EXU10R2DEFPSWITCHES
EXU8NXPU
EXU10TABS
EXU8NXPU
[oracle@DEV-RPT-2 ~]$

3. 跟踪imp

imp a/a file=a.dmp show=y log=a.imp.log trace=y

[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y log=a.imp.log trace=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 05:53:33 2009

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, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件

sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)

[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
imp
exu8opt
IMP9USR
EXU8FUL
EXU9NLS
IMP9COMPAT
SET_IMP_TIMEZONE
SET_IMPORT_MODE
SET_IMP_EVENTS
[oracle@DEV-RPT-2 ~]$

4. 根据前面文档和跟踪得出的结果, 取消exp,imp权限

禁止exp

revoke select on sys.exu8usru from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:10 2009

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


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:41 2009

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, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$

grant select on sys.exu8usru to public;

禁止imp

revoke select on sys.imp9usr from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:27 2009

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, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:35 2009

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, Data Mining and Real Application Testing options

IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$

grant select on sys.imp9usr to public;

禁止exp和imp

revoke select on sys.exu8opt from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:45 2009

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


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:51 2009

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


IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$

grant select on sys.exu8opt to public;

以上是10.2.0.4的测试结果, 其它版本的没试, 应该类似



外部链接:
Prevent the use of EXP and IMP for specified Oracle Users
Trace option in Export and Import
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump
Compatibility Matrix for Export And Import Between Different Oracle Versions



-fin-