Re: Help with my logon trigger

a couple of notes:

1. user is UPPERCASE.
2. check v$session.status for being ACTIVE.

so the final trigger would look like this:

create or replace trigger connect_restriction   after logon on database
 when (user = 'TEST')
 declare
   v_username number;
 Begin
   select count(*)
   into v_username
   from v$session
   where username='TEST'
   and status='ACTIVE'
   and upper(program) like 'SQLPLUS%';
   if v_username > 0 then
     raise_application_error(-20002,'no sqlplus please');    end if;
end connect_restriction;

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message
news:l.1013796306.1941314697@[64.94.198.252]...


> I was trying to create a logon trigger that prevent certain people from

>  using sqlplus

>

>      create or replace trigger connect_restriction

>           after logon on database

>      when (user in ('test'))

>          declare

>          v_username number ;

>          Begin

>        select count(*)

>        into v_username

>        from v$session

>        where username='test'

>        and program = 'SQLPLUSW.EXE';

>      if v_username > 0 then

>      raise_application_error(-20002,'no sqlplus please');

>      end if;

>  end connect_restriction;

>

> the trigger can only be created in sys, if I try to create it in

> system, oracle will complain

> PLS-00201: identifier 'SYS.V_$SESSION' must be declared, why?

>

> Even though the trigger can be created in sys, it will prevent user

> test connect to database in any application, not only sqlplus,

> what is wrong with my trigger?

>

> Thanks for you help.

>

>

>

>

>

>

>

> --

> Sent  by dbadba62 from hotmail subpart of  com

> This is a spam protected message. Please answer with reference header.

> Posted via http://www.usenet-replayer.com/cgi/content/new