Re: Logon Trigger

Ramon , a few quick comments.

1. Never create an object owned by SYS. It just isn't good to contaminate the SYS schema with NON oracle related objects.
2. Anything owned by SYS will NOT be exported, refer to #1.
3. Create a new userid to store those kinds of objects so during an export it will be picked up and refer to #1. :)

joe

Ramon E. Estevez wrote:

>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: Joe Testa
  INET: jtesta_at_dmc-it.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).