Hi, mkb
I made this one, hope it works for you as it did for me.
It validates also some roles that were assigned to the DBA group.
Luck,
Ramonaa
----------------------------------------------------------------
CREATE OR REPLACE TRIGGER sys.Block_Tools_T
after logon on database
Declare
user_name varchar2(30);
program_name varchar2(40);
machine_name varchar2(40);
logon_date date;
sesiones integer := 0;
autorizado varchar2(1) := 'N';
external_tool boolean := false;
cursor c1 is select username, program , machine, sysdate
from v$session
where audsid=userenv('sessionid');
Begin
open c1;
fetch c1 into user_name, program_name, machine_name, logon_date;
close c1;
select count(*) into sesiones
from v$session
where username = user_name
and machine = machine_name;
select 'S' into autorizado from dba_role_privs
where grantee = user_name
and granted_role in
('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
if upper(program_name) LIKE '%PLUS%' or
upper(program_name) LIKE '%TOAD%' or
upper(program_name) LIKE 'SQLNAV%' then
external_tool := true;
end if ;
if (external_tool and autorizado = 'N') then
raise_application_error(-20001,'Usted No puede conectarse
utilizando esta aplicacion.');
elsif (autorizado = 'N' and sesiones >= 1) then
raise_application_error(-20001,'No puede hacer mas conexiones desde
esta terminal.');
end if;
Exception when others then
raise_application_error(-20001,'No tiene ningun privilegio asignado,
contacte el depto de seguridad de sistemas.');
End;
/
-----Original Message-----
Sent: Tuesday, October 29, 2002 12:29 PM
To: Multiple recipients of list ORACLE-L
Sorry in advance, but I didn't think I'd need this.
There was a discussion about 4 weeks ago if memory
serves correct, about denying users logging on to
Oracle directly either through SQL*Plus or other tools
such as TOAD. Tried searching the archives but
getting too many hits.
Someone posted trigger code that did this. Anyone
have a copy of this?
Thanks
mkb
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ramon E. Estevez
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
