Hi,

In fact, any user that has the CREATE SESSION system privilege, are able to export your own objects. Otherwise, you can try prevent that users try to use exp or imp utilities creating a database trigger as demonstrated below:

SQL> create or replace trigger prevent_exp_imp

  2  after logon on database

  3  begin

  4  if user='TEST' and upper(sys_context('userenv','module')) in ('EXP.EXE','IMP.EXE') then

  5  raise_application_error (-20001, 'You are not allowed to export or import data.');

  6  end if;

  7  end;

  8  /



Trigger created.



SQL> exit

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production



C:\>exp test/test file=test



Export: Release 10.2.0.1.0 - Production on Sun Jan 27 11:47:56 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:

Cheers

Legatti

While I agree that anyone not courteous enough to provide operating system info is probably using Windows so that might be a valid assumption, it should be noted that this will not work on Linux.

Same trigger, in linux:

[oracle@instructor ~]$ exp test/test



Export: Release 10.2.0.2.0 - Production on Sun Jan 27 07:27:55 2008



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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >



Export file: expdat.dmp >



(2)U(sers), or (3)T(ables): (2)U >



Export grants (yes/no): yes >



Export table data (yes/no): yes >



Compress extents (yes/no): yes >



Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user TEST

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user TEST

About to export TEST's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export TEST's tables via Conventional Path ...

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

[oracle@instructor ~]$

Note that on Linux (OEL 4.5) and Oracle 10.2.0.3, we see

SQL> select distinct module from v$session;



MODULE




emagent@instructor.example.com (TNS V1-V3)

OEM.BoundedPool

exp@instructor.example.com (TNS V1-V3)

OEM.SystemPool

OMS

SQL*Plus

OEM.CacheModeWaitPool



8 rows selected.



SQL>

whereas the same query in Windows XP with 10.2.0.1 XE we see

SQL> select distinct module from v$session;



MODULE




SQL*Plus

exp.exe

Hi,

You are absolutely right. I didn't make a test on Linux/Unix boxes ...

Cheers

Legatti

can't we control this by revoking any privilege from the user??

this is the current setting i have, i created a specific role for the user

CREATE ROLE "USERNAME_DEFAULT_ROLE" NOT IDENTIFIED;
GRANT CREATE SESSION TO "USERNAME_DEFAULT_ROLE"
GRANT DELETE ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT EXECUTE ANY PROCEDURE TO "USERNAME_DEFAULT_ROLE"
GRANT INSERT ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT SELECT ANY SEQUENCE TO "USERNAME_DEFAULT_ROLE"
GRANT SELECT ANY TABLE TO "USERNAME_DEFAULT_ROLE"
GRANT UPDATE ANY TABLE TO "USERNAME_DEFAULT_ROLE"
CREATE USER "USERNAME" PROFILE "DEFAULT"
IDENTIFIED BY "dbnamei27r3blz" DEFAULT TABLESPACE "USERNAME"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "USERNAME";
GRANT "USERNAME_DEFAULT_ROLE" TO "USERNAME";

The USERNAME can be any user.

I can still export objects from the schema USERNAME using USERNAME user.

Sorry for the USERNAME thing.

I believe that any user can export or import to its own schema.

Do you think a thin client has exp.exe and imp.exe installed?
Isn't the solution simply do NOT install exp.exe and imp.exe on ALL clients?

--
Sybrand Bakker
Senior Oracle DBA

Hi,

>>I believe that any user can export or import to its own schema.
Exactly. Any user that has the CREATE SESSION system privilege, are able to export your own objects and perform imports ...

Cheers

Legatti