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

I see you've already received lots of suggestions, and all perfectly good. Another approach I'd suggest is to make sure that the privileges needed to update your tables are granted to users via roles.

That way you can password protect the roles.

And then you code your application to supply the required passwords. (This is indeed the main reason why password-protected roles were invented in the first place).

Voila: it matters not how someone accesses your database (SQL Plus, Access, whatever), unless they know the role passwords, they're not going to have the privileges to do anything to the tables directly. The only thing that can supply the password is your app., therefore the only functional access to your data is via your app. Problem solved.

(Son't forget that 9i extends this idea even further: the protection of roles via a procedure, package or function).

Regards
HJR "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 Wed Aug 07 2002 - 17:41:53 CDT