Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with my logon trigger
a couple of notes:
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]...Received on Fri Feb 15 2002 - 12:55:50 CST
> 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