Tricks to restrict EXP/IMP tools to access DB

Traditional exp/imp tools are getting decommission soon .. In heterogeneous environments we still support interfaces. exp/imp are client tools so often found that many unwanted users uses to export data from production system.  It sometimes degrade db performance.

To prevent such scenario you can use below tricks to avoid unwanted access to production DB.

Trick #1 Revoke SELECT privilege from EXU8FUL table. If connecting user have DBA, SELECT ANY DICTIONARY and EXP_FULL_DATABASE grants then trick will not work.

GOTO DB server –>

$ sqlplus “/ as SYSDBA”

SQL> revoke select on EXU8FUL from public;
Revoke succeeded.

And, now try to exp from client machine.

$ exp scott/tiger@oracle_db file=xp.dmp log=xp.log
Export: Release 10.2.0.4.0 – Production on Mon Jan 24 16:02:05 2011
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

Trick #2: Write an oracle trigger on database level.

To make trick manageable create an oracle table no_exp_imp_users as below.

create table no_exp_imp_users ( block_user varchar2(30) not null );

create or replace trigger no_exp_imp
after logon on database
begin
if  ( user in (select block_user from no_exp_imp_users )
and
( upper(sys_context(‘userenv’,’module’)) in (‘exp’,’imp’)
OR
upper(sys_context(‘userenv’,’module’)) in (‘EXP.EXE’,’IMP.EXE’)
)
then
raise_application_error (-20999, ‘DOOMED! You are not allowed to run exp/imp’);
end if;
end;
/

Now insert record into no_exp_imp_users to prevent them to access it.

insert into no_exp_imp_user values (‘SCOTT’);
commit;

C:\>exp scott/tiger@oracle_db file=xp.dmp log=xp.log

Export: Release 10.2.0.4.0 – Production on Mon Jan 24 16:02:05 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-20999: ‘DOOMED! You are not allowed to run exp/imp
ORA-06512: at line 3

NOTE: One can break this trick if smart guy change exp.exe to anyname.exe 😉 or exp to anyname.

Hope this helps to reader!