a user to use only import

From: tof <tof_at_debian.linux>
Date: Mon, 21 Aug 2006 21:07:57 +0200
Message-ID: <pan.2006.08.21.19.07.57.841054@debian.linux>

Hi,

i create a user for use only for import and for export. batch_export with exp_full_database role <- It run batch_import with imp_full_database role <- don't run

P:\>sqlplus batch_export/batch

SQL*Plus: Release 10.1.0.2.0 - Production on Lun. Ao¹t 21 17:21:58 2006

ERROR:

ORA-00604: une erreur s'est produite au niveau SQL rÚcursif 1
ORA-20000: Connexion refusee
ORA-06512: Ó ligne 41

Entrez le nom utilisateur :

P:\>sqlplus batch_import/batch_at_rfsage

SQL*Plus: Release 10.1.0.2.0 - Production on Lun. Ao¹t 21 17:03:36 2006

ConnectÚ Ó :
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> exit

the trigger as run

create or replace trigger batch_export.check_connexion after logon on database declare

V_MODULE SYS.V_$SESSION.module%TYPE;
V_TERMINAL SYS.V_$SESSION.terminal%TYPE;
V_SID SYS.V_$SESSION.SID%TYPE;
V_SERIAL SYS.V_$SESSION.SERIAL#%TYPE;
V_COMMAND varchar2(100);
V_CURRENT_USER varchar2(20);
V_CURRENT_SID SYS.V_$SESSION.SID%TYPE;

cursor connexion is
select substr(module,1,7) module, substr(terminal,1,12) terminal, sid, serial# from v$session T1 where schemaname='BATCH_EXPORT'; cursor ID_CUR is
select user from dual;
cursor SID_CUR is
select SYS_CONTEXT('USERENV','SID') sessionid from dual; --select SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid from dual;

Begin

open SID_CUR;
loop
fetch SID_CUR into V_CURRENT_SID ;
EXIT WHEN SID_CUR%NOTFOUND;
dbms_output.put_line('V_CURRENT_SID:'||V_CURRENT_SID); end loop;
close SID_CUR;

open ID_CUR;
loop
fetch ID_CUR into V_CURRENT_USER ;
EXIT WHEN ID_CUR%NOTFOUND;
if V_CURRENT_USER='BATCH_EXPORT' then
open connexion;
loop
fetch connexion into V_MODULE,V_TERMINAL,V_SID,V_SERIAL ; EXIT WHEN connexion%NOTFOUND;

if V_MODULE<>'EXP.EXE' then
dbms_output.put_line('V_SID:'||V_SID);
dbms_output.put_line('V_CURRENT_USER:'||V_CURRENT_USER);
if V_CURRENT_SID=V_SID then
dbms_output.put_line('MODULE:'||V_MODULE);

RAISE_APPLICATION_ERROR (-20000,'Connexion refusee'); end if;
end if;
end loop;
close connexion;
end if;
end loop;
close ID_CUR;
End;

as the same for import user.
I try with role in trigger but it don't, i see this in forum Oracle.

But i think EXP_FULL_DATABASE have not DBA rule, but IMP_FULL_DATABASE have.

How i do this ?
I want just to use a user to imp utilities, but not connexion in sqlplus.

Thanks for your help

Christophe Received on Mon Aug 21 2006 - 14:07:57 CDT