Re: accessing SQLPlus variables.................

You might want to look at the PRODUCT_USER_PROFILE table (search Metalink to get dozens of hits or look in App E of SQL*Plus User's Guide and Reference). It doesn't prevent users from connecting via sqlplus, but it does allow you to prevent them from using certain SQL (DML and DDL) commands. You can restrict 'connect', but it only prevents them from connecting to another schema once they're in (eg, can't connect system/manager from sqlplus prompt). This security happens even if they rename sqlplus.exe to test.exe because test.exe would still be sqlplus in disguise and it will check the PUP table. A very long time ago (in a galaxy far away), when I worked for the government, we used to allow people to use sqlplus, but used it to prevent them from using the 'host' command to get to the o/s.

-Roger Crowley

"Parag" <parag_ch_at_yahoo.com> 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
Received on Tue Aug 06 2002 - 19:52:11 CDT