Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: accessing SQLPlus variables.................
parag_ch_at_yahoo.com (Parag) wrote in message news:<acea9170.0208061435.75722451_at_posting.google.com>...
> Hello All,
>
> I want to restrict users to use application instead of using sqlplus.
> To do that using logon trigger and some reading from various sites /
> groups I got it to work.
>
> The trigger is as follows:
>
> create or replace trigger connect_restriction after logon on database
> when (user != 'SYSTEM' and user != 'VVVVVV' and user != 'MMMMMM')
> declare v_username number;
> Begin
> select count(*) into v_username from v$session
> where upper(username) = user and status='ACTIVE' and upper(program)
> like 'SQLPLUS%' and appinfo like '%SQL%';
> if v_username > 0 then raise_application_error(-20002,'no sqlplus
> please'); end if;
> end connect_restriction;
> /
>
> If I rename my sqlplus.exe as test.exe, the trigger is fooled.
> Now if I could access sqlplus environment variable appinfo
> (sqlplus> show appinfo) in the PL/SQL block I could get the thing
> done.
>
> How do I ?
>
> Thanks in advance.
> Parag
This is from 8.1.7.3
.....
AND UPPER(module) like '%SQL*PLUS%';
Module is always constant as "SQL*Plus" for sqlplus.exe or sqlplusw.exe no matter what the exe name is changed to.. BTW.."appinfo" column does not exist in v$session in 8.1.7.3. Are you on 9i because I have not upgraded yet. Could it be replacement of "module" from 8.1.7?
HTH
//Rauf Sarwar
Received on Tue Aug 06 2002 - 22:56:22 CDT