Re: Help with my logon trigger

You need to GRANT SELECT ON SYS.V_$SESSION TO PUBLIC; This grant is generally missing.

Hemant K Chitale

"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
Received on Sat Feb 16 2002 - 09:13:42 CST