Oracle-L: RE: Logon Trigger

From: Ramon E. Estevez <com.banilejas_at_codetel.net.do>
Date: Wed, 30 Oct 2002 12:33:31 -0800
Message-ID: <F001.004F7DF5.20021030123331@fatcity.com>

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: mkb125_at_yahoo.com

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: ListGuru_at_fatcity.com (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: com.banilejas_at_codetel.net.do

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------