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

parag_ch_at_yahoo.com (Parag) writes:

> 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.

...

> 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 ?

Preventing SQL*Plus access only gives the most limited security. Other DB-based solutions would be better.

You can access the APPINFO variable by calling a PL/SQL package as explained in the SQL*Plus User Guide and Reference, see SET APPINFO. However since SQL*Plus has already connected and presumably the trigger fired before SQL*Plus updates the APPINFO values, I don't know how the trigger would work.

Note that the default value of APPINFO necessarily changed to OFF in SQL*Plus 9.0.1 to allow some privileged DB operations to be done.

One potential way to stop SQL*Plus (in a shared ORACLE_HOME) from running is to put an EXIT statement in the glogin.sql file. The PROMPT command can be used to give an explanation to users.

Chris

-- 
Christopher Jones, Oracle Corporation, Australia.