Posts
提问: 何如禁止用户使用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-
