Re: Blocking 'Some' applications

I have the same problem that John Bringolf originally wrote about.

The solution that Oliver Critchley describes will work, but it is security which depends on the users' not knowing that they can do the SET ROLE command in VB, SQL*Plus, MS Access, etc. Nevertheless, it is about the closest solution that I can think of.

It's a difficult problem, I think. I have this same requirement but I can't get at the application source code to affect what happens at connect time. And I can't figure out a way to set a trigger on a logon which can set someone's role (other than the trigger owner's).

The suggestion by Billy Verreynne to monitor the V$SESSION table would work. It seems awkward and would still allow damage to be done between polling cycles.

Anyone have any more ideas???

Thanks
Jerry

Oliver Critchley wrote:
>
> JBringolf wrote:
> >
> > Trying to find out if anyone has looked into the possibilities which
> > would allow the Database to allow connections from specific clients and
> > block connections from others. Example: Allow an individual access to
> > shared database tables using a company supplied program but not allow
> > him/her to connect if they try using the same UserID/Password in
> > MSAccess.
>
> Something I heard on this newsgroup a while back but I've never tried myself:
>
> Give the user two roles, one a default role which doesn't allow them to do anything and one a
> password-protected role which is *not* their default and which allows them access to the sensitive
> tables.
>
> When the user connects using MS Access or SQL*Plus, they get their default role and can't do
> anything. When they connect via the custom application, the application issues a SET ROLE command to
> change the user's role to the password-protected one (the application knows the role's password).
> Hey-presto the user can see all the sensitive tables via the application.
>
> Hope this works!
>
> Oliver.
 

-- 
=================================================
Jerry Apfelbaum           email: japfelba_at_ican.ca
Eastern Sun Group Inc.    phone:     416.240.9695 
Toronto, Canada