Tuesday, March 18, 2008

Restrict user to export its own objects

One of my Senior DBA's - Bhupinder scripted the below trigger...
Prevent the schema owner to take export on its own objects:

SQL> conn / as sysdba

create or replace trigger prevent_exp_imp
after logon on database
begin
if user='USER1' and upper(sys_context('userenv','module')) Like ('%EXP%') then
raise_application_error (-20001, 'You are not allowed to export data.');
end if;
end;
/


From my client machine:

F:\>exp USER1/USER1@ora file=exp.dmp
Export: Release 10.2.0.2.0 - Production on Tue Mar 18 18:02:09 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to export or import data.
ORA-06512: at line 3
Username:
EXP-00030:

Similarly it can be configured for Import activity too.

No comments: